ADO, Oracle and (Text in) BLOBs

in Web Applications > ASP

Questions to Karen Wallace

This tutorial covers loading a text into a BLOB using Oracle's OLEDB Provider. Why on earth, you make ask, would you want to do that when you could just put the text in a CLOB? Maybe you have data that could be text or binary and should all go into the same table because it's related and has the same properties. Real life example: You allow users to store HTML and images in the database for simple webpages. Yes, you could just store the images on disk, but you have a web farm and you don't want to deal with deployment and undeployment to and from multiple servers, and the traffic on these pages will be light. In other words, you're lazy and looking to program something new.

If you've read the related tutorial on storing binary data in BLOBs, you'll find this very similar.

I. Load the Text Data into the Database

Get an updatable recordset and shove the text 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"

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(lotsOfText)
rs.update
rs.close

conn.commitTrans
%>

II. Retrieve the Text Data from the Database

You can write to the browser or to disk. Streams default to UTF-8, which might not match your database's character set, you must convert it. Plus, the ASP engine doesn't like to read unicode.

<%
dim conn, rs

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

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

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

'Write it to disk
dim stream
set stream = server.createObject("adodb.stream")
stream.type = adTypeText
stream.charset = "iso-8859-1"
stream.open
stream.writeText(rs.fields("blobcolumn").value)
stream.saveToFile folderAndFileName, adSaveCreateOverWrite
stream.close
%>