Results 1 to 12 of 12
  1. #1
    Join Date
    Oct 2002
    Location
    Tampa
    Posts
    1

    Unanswered: ADO - BLOB Question

    I am attempting to insert a BLOB data into DB2. When the code runs I recieve the following error:
    CLI0102E Invalid Conversion. SQLSTATE=07006

    Below is a sample of my code:


    Any suggestions?

    sSql = "SELECT tf_elec_bill_id, tf_dt_stamp, tf_doc_type, tf_pdf_blob "
    sSql = sSql & "FROM " & F_Name & ".wmint06a "
    sSql = sSql & "WHERE tf_elec_bill_id='" & iInternetId & "';"

    adoRs.Open sSql, adoConn, adOpenKeyset, adLockOptimistic

    If adoRs.EOF Then
    adoRs.AddNew
    adoRs.Fields("tf_pdf_blob").AppendChunk bPDF
    adoRs("tf_elec_bill_id") = iInternetId
    adoRs.Update
    Else
    adoRs("tf_pdf_blob") = bPDF
    adoRs("tf_dt_stamp") = Now()
    adoRs.Update
    End If

    adoRs.Close

  2. #2
    Join Date
    Nov 2002
    Posts
    7
    I have managed to get reading and writing BLOB's from/too DB2 working through ADO. You just can't do it the way you would expect. When accessing the BLOB it has to be accessed before any other field is accessed, or you get null or an error. When updating you have to use a statement

    FileSize = FileLen(LocalFileName)
    Set cmd = New ADODB.Command
    Set cmd.ActiveConnection = con
    cmd.CommandType = adCmdText
    cmd.CommandText = "UPDATE PDFDOC SET PDF = ? WHERE DOCID = " & CStr(DocID)

    Set prm = New ADODB.Parameter
    prm.Attributes = adParamLong + adParamNullable
    prm.Name = "PDFDATA"
    prm.Direction = adParamInput
    prm.Type = adLongVarBinary
    prm.Size = FileSize
    cmd.Parameters.Append prm

    fn = FreeFile()
    Open LocalFileName For Binary Access Read As fn

    curRead = FileSize
    While Not EOF(fn) And curRead > 0
    If (curRead > 4096) Then
    ReDim pData(4095) As Byte
    curRead = curRead - 4096
    Else
    ReDim pData(curRead - 1) As Byte
    curRead = 0
    End If
    Get fn, , pData
    varData = pData
    prm.AppendChunk pData
    Wend

    Close fn

    cmd.Execute

  3. #3
    Join Date
    Mar 2003
    Posts
    11
    Could u tell me how to read from a BLOB field?

  4. #4
    Join Date
    Mar 2003
    Posts
    11
    i don't get it working. It gives me the same error "Invalid Conversion"

  5. #5
    Join Date
    Nov 2002
    Posts
    7
    Originally posted by faisalkm
    Could u tell me how to read from a BLOB field?
    It is relatively simple, just remember that with DB2 the BLOB field has to be accessed by itself. Here is some sample code that should get you close to what you want. Since the BLOBs I was dealing with could be quite large I just wrote them to a file without trying to hold them in memory. There is no reason you couldn't read the whole thing into memory if you know it will fit.

    Dim varData As Variant
    Dim bData() As Byte
    Dim fn As Integer
    Dim FileSize As Long
    Dim curRead As Long

    fn = FreeFile()
    Open LocalFileName For Binary Access Write As fn

    Set rs = New ADODB.Recordset
    rs.Open "SELECT DOCID, PDFDATA FROM PDF WHERE DOCID = " & DocID

    FileSize = rs.Fields.Item("PDFData").ActualSize
    curRead = FileSize
    varData = 1
    While Not IsNull(varData) And curRead > 0
    If (curRead > 4096) Then
    varData = rs.Fields.Item("PDFData").GetChunk(4096)
    curRead = curRead - 4096
    Else
    varData = rs.Fields.Item("PDFData").GetChunk(curRead)
    curRead = 0
    End If

    If (Not IsNull(varData)) Then
    bData = varData
    Put fn, , bData
    End If
    Wend

    rs.Close
    Close fn

  6. #6
    Join Date
    Mar 2003
    Posts
    11
    I got it working. I found this BLOB sample from db2 and changed the connection parameters to Microsoft ODBC driver. Now it works. Anyway Thanks.

  7. #7
    Join Date
    Nov 2002
    Posts
    7
    Originally posted by faisalkm
    i don't get it working. It gives me the same error "Invalid Conversion"
    Are you sure it is a BLOB and not a CLOB? For a clob the param type would be adLonVarChar or if it is unicode it would need a different setting (can't remember it off the top of my head). I remember getting this and started playing with the data type and other parameters. Unfortunately I don't remember what exactly I did to get it working.

  8. #8
    Join Date
    Dec 2003
    Posts
    9

    reading CLOBs from DB2 using ADO

    Originally posted by faisalkm
    I got it working. I found this BLOB sample from db2 and changed the connection parameters to Microsoft ODBC driver. Now it works. Anyway Thanks.
    I am facing a similar problem. I am using IBMDADB2.1 OLEDB connection which does not seem to be working for CLOBs. In this forum I read that ODBC driver approach would work. Is it the ODBC driver by Microsoft for DB2 or the ODBC driver by IBM for DB2 that supports CLOB?

    Thanks

  9. #9
    Join Date
    Nov 2002
    Posts
    7

    Re: reading CLOBs from DB2 using ADO

    Originally posted by skg1
    I am facing a similar problem. I am using IBMDADB2.1 OLEDB connection which does not seem to be working for CLOBs. In this forum I read that ODBC driver approach would work. Is it the ODBC driver by Microsoft for DB2 or the ODBC driver by IBM for DB2 that supports CLOB?

    Thanks
    We were using the IBM driver that came with the DB2 Connect.

  10. #10
    Join Date
    Dec 2003
    Posts
    9
    Thanks for the prompt reply. Could you post the details of the driver for eg., version etc.

    I have an ODBC driver installed that is made by IBM and was installed when I installed the Fix1 Pak for ver 7.2 of DB2 for Windows.

    Running Fix1 pak installs IBMDADB2.1 (OLEDB developed by IBM) as well as the ODBC driver. My DB2 (ver 7.2) is on Windows Xp and I do not think I need DB2 Connect for it (or can even install DB2 Connect for that matter).

  11. #11
    Join Date
    Nov 2002
    Posts
    7
    Originally posted by skg1
    Thanks for the prompt reply. Could you post the details of the driver for eg., version etc.

    I have an ODBC driver installed that is made by IBM and was installed when I installed the Fix1 Pak for ver 7.2 of DB2 for Windows.

    Running Fix1 pak installs IBMDADB2.1 (OLEDB developed by IBM) as well as the ODBC driver. My DB2 (ver 7.2) is on Windows Xp and I do not think I need DB2 Connect for it (or can even install DB2 Connect for that matter).
    We used 7.2 as well. We had to use DB2 Connect since we were connection from Microsoft Windows boxes to a Mainframe DB2 instance. We used the OLEDB driver from VB programs and the ODBC driver from Java programs.

    We did make changes to the ODBC setup.

    - Set the Isolation lever to read uncommitted.
    - Asynchronous ODBC to false
    - Long Object Binary Treatment to as LONGVAR Data (LOB just didn't work)
    - MVS current SQLID to our catalog name. This was more of an ease of programming then a necessity.
    - Multithreading turned off
    - Selected Known Workarounds Permit MS Access, VisualBasic Handshake.

    It has been a year and a half since we used this stuff. We have since started using the JDBC drivers from IBM and doing everything in Java.

  12. #12
    Join Date
    Dec 2003
    Posts
    9
    You are good, Sherter!

    I made changes to the ODBC settings as per your suggestions and was able to retrieve CLOBs using my VB application.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •