PRB: AppendChunk Truncates Data Using OlE DB Provider for Oracle

• Microsoft OLE DB Provider for Oracle, version 2.0
• ActiveX Data Objects (ADO), versions 1.5, 2.0

SYMPTOMS

When trying to store large texts or images into an Oracle database using the OLE DB provider for Oracle, the data is truncated.

RESOLUTION

At present, there is no resolution. The workaround is to use the OLE DB Provider for ODBC and the Microsoft Oracle ODBC driver.

STATUS

Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article.

MORE INFORMATION

Steps to Reproduce Behavior

1. Create a Standard EXE project in Visual Basic. Form1 is created by default.



2. Go to the Project/References menu and add a reference to Microsoft ActiveX Data Objects 2.0 Library.



3. Add three CommandButtons to the default form.



4. Add the following code in the code window of the form. You will have to modify the connection string to connect to your Oracle server:





   Option Explicit
   ' Connection String
   Const cnstr = "Provider=MSDAORA;Data Source=Ora8.world;User" & _
     " ID=demo;Password=demo;"
   Dim sqlstr As String

   Private Sub Command1_Click()
     Dim rs As New ADODB.Recordset
     Dim cn As New ADODB.Connection
     ' Don't throw an error in case the table does not exist
     On Error Resume Next
     sqlstr = "Create Table BlobTable (MYID Numeric Primary Key," & _
     " TXTFLD Long)"
     cn.Open cnstr
     ' Drop the table if it already exists
     cn.Execute "Drop table Blobtable"
     cn.Execute sqlstr
     cn.Close
     Set cn = Nothing
   End Sub

   Private Sub Command2_Click()
     Dim rs As New ADODB.Recordset
     Dim cn As New ADODB.Connection
     Dim i As Integer
     sqlstr = "Select MYID,TXTFLD from Blobtable where MYID=1"
     cn.CursorLocation = adUseClient
     cn.Open cnstr
     With rs
        .ActiveConnection = cn
        .CursorType = adOpenStatic
        .LockType = adLockOptimistic
        .Open sqlstr
     End With
     If rs.EOF Then
       rs.AddNew
       rs("MYID") = 1
       rs.Update
       rs.Requery
     End If
     For i = 1 To 1000
      'Store a 1 MB string; smaller strings may seem to work.
      rs.Fields("TXTFLD").AppendChunk String(1024, "x")
     Next i
     rs.Update
     rs.Close
     Set rs = Nothing
     cn.Close
     Set cn = Nothing
   End Sub

   Private Sub Command3_Click()
     Dim cn As New ADODB.Connection
     Dim rs As New ADODB.Recordset
     sqlstr = "Select MYID,TXTFLD from Blobtable where MYID=1"
     cn.CursorLocation = adUseClient
     cn.Open cnstr
     With rs
        .ActiveConnection = cn
        .CursorType = adOpenStatic
        .LockType = adLockOptimistic
        .Open sqlstr
     End With
     ' Print the actual size of the string stored in the database
     Debug.Print rs.Fields("TXTFLD").ActualSize
     rs.Close
     Set rs = Nothing
     cn.Close
     Set cn = Nothing
   End Sub

   Private Sub Form_Load()
    Command1.Caption = "Create Table"
    Command2.Caption = "Insert Data"
    Command3.Caption = "Retrieve Actual Size"
   End Sub 




5. Click the CommandButton with the caption "Create Table" to create the test table.



6. Click the CommandButton with the caption "Insert Data" to insert the data.



7. Click the Command button with the caption "Retrieve Actual Size" to retrieve the actual size of the text field. You will see a incorrect value printed in the Immediate window.

REFERENCES

For more information, please see the following articles in the Microsoft Knowledge Base:

Q192743 HOWTO: Use ADO GetChunk/AppendChunk with Oracle for TEXT Data

Q185958 HOWTO: Use ADO GetChunk/AppendChunk with Oracle for BLOB Data

Additional query words:

Keywords          : kbole kbADO200bug kbADO210bug kbDatabase kbOracle kbProvider 
Version           : WINDOWS:1.5,2.0
Platform          : WINDOWS 
Issue type        : kbprb 

Last Reviewed: March 25, 1999