Append Blob Data using SQL statements (Through Vb6 ADDO)

Posted on 2005-05-05
Last Modified: 2012-05-05
I have a row with a column defined as BLOB (Size: 1 MB).

I used VB 6 ADDO to insert records into the DB2:

cmdstr = "INSERT INTO event(eventtype, eventdata) VALUES ("3002," + ", BLOB(X'" + data + "'))"

INSERT INTO event(eventtpe, eventdata) VALUES ( '3002', BLOB(X'AB10BA') )

The column, eventdata is in Blob format.
I used the string, data (defined as string type) to hold a long string of hexadecimal values.
eg. 01ABBA28AA...

When the length of the hexadecimal string reaches greater than 34000 characters, I got an SQL error stating that the string is too long.

I think that the SQL statement has a limit of how long can a SQL statement be.

So the only way is to insert the data seperately into DB2.
I want to enter the whole of the data Blob into a SINGLE row / record.

Therefore, I need to append the Blob data.


Data: AB10BA2A0BC2
Content of Blob:

After appending to the existing Blob data and it wil becomes what is shown below after another SQL statement:
Question by:jcwh
    LVL 7

    Accepted Solution


    Do you receive SQL0102N?

    Also in Java i know executeUpdate() works fine instead of executeQuery(). You might have to try the equivalent in VB

    This is simple update to append data. TEST TO MAKE SURE YOUR BLOB DATA IS NOT BEING CORRUPTED

    db2 update tablename set col1 = col1 || 'appendtext' where id = value

    || operator is used to concatenate. And the id could be your eventtype

    db2 update event set eventdata = eventdata || 'appendtext' where eventtype = 3002

    If you still receive the same error with this update than the size of your column is not big enough.



    Author Comment

    I am getting SQL0102N error message, stating message is too long.

    I have not try with the actual string, but I tried on other string data type column and it works.

    I have to ask you is that by using this command, all the data that are already exist in the column will be loaded in DB2's own buffer upon executing the command that you mentioned?

    Then I guess the buffer definitely can hold to whatever maximum size a column of a particular datatype can be defined?
    LVL 7

    Expert Comment


    Yea.. go ahead and try testing with the real data.

    Be sure to have a system temporary tablespace of appropraite page size (32K since you are working with large objects.)

    Good Luck!!

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
    Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now