FIX: 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, 2.1 SP2

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

A supported fix that corrects this problem is now available from Microsoft, but it has not been fully regression tested and should be applied only to systems experiencing this specific problem. If you are not severely affected by this specific problem, Microsoft recommends that you wait for the next Microsoft Data Access Components service pack that contains this fix.

To resolve this problem immediately, contact Microsoft Product Support Services to obtain the fix. For a complete list of Microsoft Product Support Services phone numbers and information on support costs, please go to the following address on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp

   Date       Version      Size    File Name     Platform
   ------------------------------------------------------
   12/07/99   2.12.4807.0  184KB   Msdaora.dll   All 

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 CommandButton 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(c) Microsoft Corporation 1999, All Rights Reserved. Contributions by Sujoy Majumdar, Microsoft Corporation.

Additional query words:

Keywords : kbole kbADO200bug kbADO210bug kbDatabase kbOracle kbGrpMDAC kbDSupport kbADO210sp2 kbMDAC210SP2
Version : WINDOWS:1.5,2.0,2.1 SP2
Platform : WINDOWS
Issue type : kbbug