?
Solved

Append Blob Data using SQL statements (Through Vb6 ADDO)

Posted on 2005-05-05
3
Medium Priority
?
1,549 Views
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.

eg.

Data: AB10BA2A0BC2
Content of Blob:
AB10BA

After appending to the existing Blob data and it wil becomes what is shown below after another SQL statement:
AB10BA2A0BC2
0
Comment
Question by:jcwh
  • 2
3 Comments
 
LVL 7

Accepted Solution

by:
db2inst1 earned 2000 total points
ID: 13998563

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.

HTH!!

0
 

Author Comment

by:jcwh
ID: 14000869
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?
0
 
LVL 7

Expert Comment

by:db2inst1
ID: 14000917

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!!
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Is your organization moving toward a cloud and mobile-first environment? In this transition, your IT department will encounter many challenges, such as navigating how to: Deploy new applications and services to a growing team Accommodate employee…
Suggested Courses
Course of the Month9 days, 15 hours left to enroll

571 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