troubleshooting Question

Classic ASP: store and retrieve CLOB in Oracle

Avatar of BlueKarana
BlueKarana asked on
Oracle DatabaseASP
2 Comments1 Solution3636 ViewsLast Modified:
I'm storing a long string, about 17,000 chars, (base64 of an image) in a CLOB field in an Oracle database.

How do I retrieve the CLOB into a string, that is, the same base64 image string?

Currently I'm only getting 996 chars, then it cuts off.

*** all code is classic ASP ***

Code for storing CLOB:
BinRead = Request.BinaryRead(ByteCount)
Dim ImageStr
ImageStr= BinaryToString(BinRead)
    Set rsVisitReservation = Server.CreateObject ("ADODB.Command")
    rsVisitReservation.ActiveConnection = MM_NetBadgeOracle_STRING
    rsVisitReservation.CommandText = "UPDATE netbadge.VISITORRESERVATION SET PHOTOCLOB=? WHERE PASSID = " & PassID
   rsVisitReservation.Prepared = true
    rsVisitReservation.Parameters.Append rsVisitReservation.CreateParameter("param2", 201, 1, len(ImageStr), ImageStr) ' adLongVarChar
   Set rsVR = rsVisitReservation.Execute

Code for retrieving CLOB:
	ConnectStr = MM_NetBadgeOracle_STRING
	set rs = Server.CreateObject("ADODB.Recordset")
	SQL = "SELECT PHOTOCLOB FROM netbadge.visitorreservation WHERE passid =1443"
	Set rs = Server.CreateObject("adodb.recordset")
	rs.Open SQL, ConnectStr, 2, 3
	Response.BinaryWrite rs("PHOTOCLOB")

So, how do I get rs("PHOTOCLOB") into a string without truncation?

I've read about using ADO getchunks, but I haven't figured out how to do that successfully. It still truncates.

like so:

    ' Copy the string to a variable in chunks
	Dim varScan
	Dim VarChunk
	Dim lngOffset
	Dim varImgStr
    lngLogoSize = Len(rs("PHOTOCLOB"))
    lngOffset = 0
    Do While lngOffset < lngLogoSize
        varChunk = rs("PHOTOCLOB").GetChunk(conChunkSize)
        varImgStr = varImgStr & varChunk
        lngOffset = lngOffset + conChunkSize
   response.write "varImgStr = "& varImgStr

(The BinaryWrite only writes out 996 characters.)

Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros