UPDATE: In the end, I was able to work the problems I was having out, and all should be well. For more somewhat humorous details, see my comment below (after you read the rest of this post, of course)!

So, we’ve been converting all this old claims data over from a Microsoft SQL 2000 server, into our existing Oracle server, so that it’s accessible by our pre-existing products. We’ve got a handle on most of the stuff, which has just involved a couple of funky SQL queries here and there. The only remaining problem is the actual images of the scanned claims. For accuracy and verification reasons, these need to be available to customer service reps so they can check various points.

The only problem is… We store all our images on a massive storage server in native format (in our case, PNG). They’re named sequentially by claim number and page (ie: 5832934-001.png, 5832934-002.png, 5832935-001.png, etc.), and when a claim is needed, we look up the claim number in the database and snag that file off the network drive. Well, the company whose data we’re importing didn’t like that solution. Instead, they chose to store all their images in BLOB fields in their SQL database (in SQL 2000’s case, there’s a special “image” field type, but it’s really just a blob field).

So, since everyone else is occupied with other projects, I volunteered to take on this seemingly simple one. Worst case scenario, I figured I could export all the data and import it into Oracle / MySQL, so that I could whip up a PHP script to go through and parse out all the images into actual files. Well, the first problem is, you can’t export these images into CSV format, lest you butcher the data. I also can’t get any of the MySQL ODBC drivers to work correctly for any of the MySQL servers I have access to, so a direct export from SQL 2000 to MySQL is also out.

This leaves me with a native language… They had a web-based utility they used to look up claim images which is written in, you guessed it: ASP. Well, I don’t know ASP. I also don’t want to know ASP and I have no idea where I’d begin to learn ASP even if I did. So I throw myself upon the mercy of you, my readers. If anyone has ASP experience, please help me!

I started with this example, which takes the image indicated and displays it in the browser. I need to edit it so that it loops through the entire database (SELECT * FROM Image) and writes out the images (stored in the blob field “Image”) to C:\images\, naming them “<ImageID>.tif”. Surely this can’t be that complicated, but I’m in over my wee little head to be sure!

[asp]
<%

Dim SQL

Dim myConnString

Dim myConnection

Dim rs

Response.Buffer = True

Response.ContentType="image/tiff"

Response.Expires = 0

myConnString = "DSN=ImageDB;DRIVER={SQL Server};UID=webuser;PWD=webuser"

Set myConnection = Server.CreateObject("ADODB.Connection")

myConnection.Open myConnString

SQL = "SELECT image FROM image WHERE ImageID = " & request("ImageID")

Set rs = myConnection.Execute(SQL)

imgSize = rs(0).ActualSize

img = rs(0).GetChunk(imgSize)

Response.BinaryWrite img

%>
[/asp]

Please, someone (anyone!) save me from this ASP damnation! I beg of you!!