Easysoft.com Documentation Knowledge Base Products  |  Solutions  |  OEM Partner Program  |  Support  |  Applications  |  Developer  |  Company

---

This article provides a workaround if you are using either the Easysoft ODBC-InterBase Driver or the Easysoft ODBC-Firebird Driver to write to BLOBs from ADO and get the following error:

Run-time error '2147217887 (80040e21):'

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

To reproduce this error:

1. Create the following table:

   CREATE TABLE "BLBCHECK"
   (
     "KY"	INTEGER NOT NULL,
     "BLB"	BLOB SUB_TYPE 1,
     PRIMARY KEY ("KY")
   );
   

2. Using Microsoft Visual Basic or Visual Basic for Applications (VBA), add a reference to Microsoft ActiveX Data Objects 2.5+.
3. Run the following code:

   Dim con As New ADODB.Connection
   Dim rs As New ADODB.Recordset
   
   con.Open "DSN=IB"
   rs.Open "select * from blbcheck", con, adOpenDynamicm, _
       adLockOptimistic
   
   rs.AddNew
   rs.Fields(0) = 21
   rs.Fields(1) = "1"
   rs.Update
   rs.Close
   con.Close
   

The error occurs when you try to insert "1" into Fields(1). To work around this problem, use the AppendChunk method to append the data instead.

Dim conADO As New ADODB.Connection
Dim cmdADO As New ADODB.Command
Dim prmText As New ADODB.Parameter

Dim strSQL As String
Dim strText As Variant
Dim lngTrxNo As Long
Dim lngOccurNo As Long
Dim lngSeq As Long

Dim intChar As Integer
Const conChunkSize = 100

'-------------------------------------------------------'
' Declare the variables.
'-------------------------------------------------------'

lngTrxNo = 1
lngOccurNo = 1
lngSeq = 13

strSQL = "insert into blbcheck ( ky, blb ) values ( 100, ? )"

' To prove this works, we will use 2500 character As,
' with a line feed on each 100 characters.

strText = ""
For intChar = 1 To 2500
    strText = strText & "A"
    If intchr = 100 Then
        strText = strText & Chr(10)
    End If
Next

'-------------------------------------------------------'
' Connect to the data source.
'-------------------------------------------------------'

conADO.Open "DSN=IBv4"

'-------------------------------------------------------'
' Once connected, prepare the SQL statement.
'-------------------------------------------------------'

cmdADO.ActiveConnection = conADO
cmdADO.CommandText = strSQL
Set prmText = cmdADO.CreateParameter("blb", adLongVarChar, _
    adParamInput, Len(strText), "")
If lngSize = 0 Then
    lngSize = Len(strText)
End If
lngOffset = 0

'-------------------------------------------------------'
' Insert the bound parameter a bit at a time.
'-------------------------------------------------------'
Do While lngOffset < lngSize
    varChunk = Left(Right(strText, lngSize - lngOffset), conChunkSize)
    prmText.AppendChunk varChunk
    lngOffset = lngOffset + conChunkSize
Loop

cmdADO.Parameters.Append prmText

'-------------------------------------------------------'
' Execute the query.
'-------------------------------------------------------'

cmdADO.Execute
conADO.Close

Use the AppendChunk method on a Field or Parameter object to fill it with long binary or character data. In situations where system memory is limited, you can use the AppendChunk method to manipulate long values in parts rather than in their entirety.

Applies To

• Products
  • Easysoft ODBC-InterBase Driver
  • Easysoft ODBC-Firebird Driver
• Languages
  • Visual Basic