View/Edit Oracle CLOB fields in ASP

[bulllfrogg]

I have seen many postings asking questions about how to retrieve/edit
a CLOB field in an ASP page from an Oracle database and I think I
might have found an answer.

I am using the Oracle ODBC driver version 8.01.07 (as listed in the
ODBC manager for windows 2000) and I am also using IIS version 5.

Because CLOBs are stored as binary data, the AppendChunk/ReadChunk
methods of the ADO field object must be used to access the data.

To read a CLOB field in your ASP page, the code that works for me
looks like this:

'create objects
Set Conn = Server.CreateObject("ADODB.Connection")
set recordset = server.createobject("ADODB.Recordset")

'open connection object to database
conn.open "DSN=theDatabase;User ID=userid;password=password"
sql="select CLOBField from CLOBFieldtable"

'open recordset object
recordset.Open sql, conn

'create the Field object for the CLOB field
clobFieldObject = recordset.Fields("CLOBField")

'Use the GetChunk Method to read the data.
'Pass the size of the data you want to retrieve
'in this case, we are retrieving 50K
strCLOBstring = clobFieldObject.GetChunk(50000)

Editing CLOB fields are a little more complicated because I have to
use the empty_clob() Oracle function to create an empty CLOB field,
and then use the AppendChunk method to write the binary data to the
empty field. The code looks like this:

'use the same connection and recordset objects as above
sql="Update CLOBFieldtable set CLOBField = empty_clob() where CLOBid =
1"

'no need to use a recordset here
conn.execute(sql)

'get your empty clob field in a recordset to update it.
sql="select CLOBField from CLOBFieldtable where CLOBid = 1"
recordset.Open sql, conn

'Get the field object we want to write to
CLOBFieldObj = recordset.Fields("CLOBField")

'Now use the AppendChunk Method to write your data
CLOBFieldObj.AppendChunk("Some Huge Long String to write to CLOB
field")

'Commit the transaction
recordset.update

Hopefully this will work for somebody - I spent many hours beating my
head against the wall on this one, and it sounds like there are others
out there doing the same thing.

Jeremy