Home -> Usenet -> comp.databases.oracle -> Oracle CLOBS vs ODBC/ADO

Oracle CLOBS vs ODBC/ADO

From: Morten Andreasen <moan_at_preform.dk>
Date: 1999/02/02
Message-ID: <796ri6$7me$1_at_holmes.dk.uu.net> 






This error is driving me nuts - any help would be greatly appriciated



Error description



When trying to display a CLOB(Character Large OBject) type field from an
ORACLE 8.04 database, through ODBC(Open DataBase Connectivity), in
ASP(Active Server Pages). Instead of getting the expected textstring, i get
only the first 4 chars, nothing at all or an error depending on which ODBC
driver i use.



First of all, an explanation of my set-up. I have defined a connection to my
database in Oracle Net8, on top of that i have defineded a ODBC datasource.
Using following ODBC drivers:



INTERSOLV 3.11 32-Bit Oracle8


Oracle ODBC driver 8.00.5


Microsoft ODBC for Oracle 2.573.2927



I have istalled Microsoft Data Access Components 2.0 SP2




here is some example of the ADO setups, ASP code i use and corresponding
error codes:




Example 1



(based on the assumption that ADO understands a CLOB field as a regualar
textfield - the code is copy'ed from microsoft own site, from a page
regarding connection to an oracle database)



//---START





   <%_at_ Language=VBScript %>
   <html>
   <head>
   <title>Oracle Test</title>
   </head>
   <body>
   <center>
   <%
     Set objConn = Server.CreateObject("ADODB.Connection")
     objConn.Open "DSN=oracle_ebmain_extern;uid=eb;pwd=eb;"

     Set objRs = objConn.Execute("SELECT content FROM websider")

     Response.Write "<table border=1 cellpadding=4>"
     Response.Write "<tr>"

     For I = 0 To objRS.Fields.Count - 1
       Response.Write "<td><b>" & objRS(I).Name & "</b></td>"
     Next

     Response.Write "</tr>"

     Do While Not objRS.EOF
       Response.Write "<tr>"

       For I = 0 To objRS.Fields.Count - 1
         Response.Write "<td>" & objRS(I) & "</td>"
       Next

       Response.Write "</tr>"

       objRS.MoveNext
     Loop

     Response.Write "</table>"

     objRs.Close
     objConn.Close




   %>

   </center>
   </body>
   </html>





//---STOP




Results:



Intersolv:


Asp page displayes, "nothing" from one record, even though there is plenty
of text in it



Oracle:


Asp page displayes, "nothing" from one record, even though there is plenty
of text in it



Microsoft:


Provider error '80004005'


Unspecified error


/blob/test.asp, line 43






Example 2



(based on the assumption that ADO understands a CLOB field as a BLOB and
therefor we should use the GetChunk() method - the code is copy'ed from
microsoft own site, from a page regarding connection to an oracle database)



//---START




<%



Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open "DSN=oracle_ebmain_extern; UID=eb; PWD=eb"



BlockSize = 4096




Query = "SELECT content FROM websider WHERE webkunderid = '96'"



Set oRS = oConn.Execute(Query)


oRS.MoveFirst


Set Field = oRS("content")


FileLength = Field.ActualSize


NumBlocks = FileLength \ BlockSize


LeftOver = FileLength Mod BlockSize


Response.Write Field.GetChunk(LeftOver)


For intLoop = 1 To NumBlocks


Response.Write Field.GetChunk(BlockSize)
Next


oRS.Close



%>



//---STOP




Results:



Intersolv:


Asp page displayes, the first 5 chars of one record, even though there is
plenty of text in it



Oracle:


Asp page displayes, the first 5 chars of one record, even though there is
plenty of text in it



Microsoft:


Provider error '80004005'


Unspecified error


/blob/test.asp, line 43






Example 3



(a little off-topic, cause what im trying to do here is to retrieve a BLOB -
the code is copy'ed from the microsoft site, only DSN and fieldnames are
modyfied)



//---START





<%_at_ LANGUAGE="VBSCRIPT" %>


<%

      'Clear existing HTTP header information.
      Response.Expires = 0
      Response.Buffer = TRUE
      Response.Clear


      'Set the HTTP header to an image type, if you want to display
      'a jpg you need to use the "image/jpeg" content type.
      Response.ContentType = "image/gif"


      Dim strTemp


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


     'You need to change this line to reflect your DSN, UID
     'and PWD.
      oConn.Open "DSN=ebexter_m_OraODBC8.05;UID=eb;PWD=eb;"


     'Change this line to use your table that contains a raw or
     'long raw field.  In this case, ID is the primary key of the
     'IMAGE table and IMG is the RAW or LONG RAW data column.
      sSQL = "Select PIC_ID, DATA from PICTURE where PIC_ID = 1000"

      Set oRS = Server.CreateObject("ADODB.Recordset")
      oRS.Source = sSQL
      oRS.ActiveConnection = oConn


     'The cursor type does not seem to matter.  A keyset cursor was used
     'with success for this article; however, you will not be able to
     'scroll with it because the content type of this page is set for
     '"image/gif".


      oRS.Open


      strtemp = oRS("DATA")
      Response.BinaryWrite(strTemp)
      Response.End


      oRS.Close
      Set oRS = nothing
      oConn.Close
      Set oConn = nothing




%>



//---STOP





Intersolv:


Response object error 'ASP 0106 : 80020005'
Type Mismatch


/blob/test.asp, line 47




An unhandled data type was encountered.




Microsoft:


Provider error '80004005'


Unspecified error


/blob/test.asp, line 43





Oracle:


error '80004005'


Unspecified error


/blob/test.asp, line 46


Received on Tue Feb 02 1999 - 00:00:00 CET












Original text of this message