Articles: ADO, Oracle and BLOBs

in Web Applications > ASP

Questions to Karen Wallace

This tutorial covers loading a binary file into a BLOB using Oracle's OLEDB Provider, starting with uploading a file to the server. It uses SoftArtisan's FileUp COM object. There are others out there, and there are even examples of doing an upload without a component if you have a host that doesn't allow third-party components, but FileUp seems to be the standard. Visit their support site for documentation and whatnot.

By popular request, there is now a related tutorial on storing text in BLOBs.

I. Upload the File

Make a form with a file input box. You must use POST, and the form's enctype must be multipart/form-data.

<form action="formHandler.asp" method="post" enctype="multipart/form-data">
Select a file to upload: 
<input type="file" name="binaryFile"> <input type="submit">
</form>

In the form handling page, use FileUp to upload the file and save it to disk. FileUp has the capability to save directly to a BLOB column, but one often wants to play with the file first.

<%
dim up, element, filename
set up = server.createObject("SoftArtisans.FileUp")
up.path = locationToUseForTemporaryFiles
up.save

filename = up.userFilename  'full path on user's machine
filename = mid(filename, inStrRev(filename, "\") + 1)  'chop off the path
%>

II. Read the File from Disk

Once the file is on the server, read it in with an ADO stream.

<%
dim stream
set stream = server.createObject("adodb.stream")
stream.open
stream.type = adTypeBinary
stream.loadFromFile(up.path & filename)
%>

III. Load the Binary Data into the Database

Once you've got a file stream open, get an updatable recordset and shove the contents of your file stream into the recordset. As working with LOBs of any sort involves transactions whether you want them or not, it's advisable to wrap your databasing in a transaction. Always initialize your BLOB field with empty_blob().

<%
dim conn, rs

set conn = server.createObject("adodb.connection")
conn.open "Provider=OraOLEDB.Oracle;Data Source=oracle.mydomain.com;" _
	& "User ID=scott;PASSWORD=tiger;Persist Security Info=True"

set rs = server.createObject("adodb.recordset")

conn.beginTrans

'Use this for an update
conn.execute "UPDATE blobtable SET blobcolumn = empty_blob() WHERE id = 7"
rs.open "SELECT blobcolumn FROM blobtable WHERE id = 7", conn, _
	adOpenStatic, adLockOptimistic

'Or this for an insert
conn.execute "INSERT INTO blobtable (id, blobcolumn) " _
	& "VALUES (blobtable_seq.nextVal, empty_blob())"
rs.open "SELECT blobcolumn FROM blobtable WHERE id = blobtable_seq.currVal", _
	conn, adOpenStatic, adLockOptimistic

rs.fields("blobcolumn").appendChunk(stream.read)
rs.update
rs.close

conn.commitTrans
%>

IV. Retrieve the Binary Data from the Database

There are two things to do with binary data. You can write to the browser or to disk.

<%
dim conn, rs

set conn = server.createObject("adodb.connection")
conn.open "Provider=OraOLEDB.Oracle;Data Source=oracle.mydomain.com;" _
	& "User ID=scott;PASSWORD=tiger;Persist Security Info=True"

set rs = conn.execute("SELECT blobcolumn FROM blobtable WHERE id = 7")

'Write it to the browser
response.binaryWrite rs.fields("blobcolumn").value

'Write it to disk
dim stream
set stream = server.createObject("adodb.stream")
stream.type = adTypeBinary
stream.open
stream.write(rs.fields("blobcolumn").value)
stream.saveToFile folderAndFileName, adSaveCreateOverWrite
stream.close
%>

You can also store character data in BLOBs. Beware writing character data from a BLOB to disk with a stream — streams default to UTF-8, which might not match your database's character set. You can change the stream's character set with the charset property.