asked on

How to insert BLOB into a table?

Hello,
I have a table that has a BLOB field. The table is defined as follows:
create table raw_image(
image_id INTEGER NOT NULL,
image BLOB(2M))
I used the following statements to insert a row, from the DB2 command line processor, into the above table:
db2 "insert into raw_image values (1, '##/home/db2inst1/images/PICT0011.JPG')"
or
db2 "insert into raw_image values (1, '/home/db2inst1/images/PICT0011.JPG')"
but got the following error for both statements:
------------------
SQL0408N A value is not compatible with the data type of its assignment
target. Target name is "IMAGE". SQLSTATE=42821
------------------
I got the syntax '##' from somewhere on the web. It says that using '##' means to get the content of the image file. I guess it's not right for DB2. How can I insert a row then? If I can get this to work, then I can import bunch of images into the table by using a DEL file and DB2 import utility.
Thanks.

minjiezen

ASKER

I just found out that import utility facilitate the insertion of the LOBs to a table. But I cannot find an example on how to write a lobsinfile. I think the import statement will be:
------
db2 import from rawimage.del of del lobs from /home/db2inst1/images/ modified by lobsinfile commitcount 10 insert into raw_image
------
The rawimage.del will have the following content:
------
1,PICT0011.JPG
2,PICT0012.JPG
3,PICT0013.JPG
4,PICT0014.JPG
5,PICT0015.JPG
6,PICT0016.JPG
------
If I have the following images in /home/db2inst1/images/ directory, how should I write the lobsinfile?
PICT0011.JPG
PICT0012.JPG
PICT0013.JPG
PICT0014.JPG
PICT0015.JPG
PICT0016.JPG

Thanks for any help.

Member_2_2484401

The following C program successfully inserts a blob.

HTH,
DaveSlash

#include <stdio.h>
#include <ctype.h>
#include <decimal.h>
#include <string.h>
#include <stdlib.h>
#include <sqlproc.h>

EXEC SQL Include SQLCA;
void main(int argc, char* argv[]) {
exec sql begin declare section;
SQL TYPE IS BLOB_FILE hv_mine;

exec sql end declare section;

strcpy (hv_mine.name,"/myStuff/Graphics/MyGraphic1.jpg");
hv_mine.name_length = strlen(hv_mine.name);
hv_mine.file_options = 2;

exec sql insert into dford/withblob
values(:hv_mine);

if (sqlca.sqlcode < 0) {
strcpy (hv_mine.name,"error");
}
return;
}

minjiezen

ASKER

Hello DaveSlash,
Thanks for your response! I tried to compile your program but got error messages stating that it cannot find decimal.h and sqlproc.h. Where can I find these files? I have DB2 V8.1 installed, and the machine OS is Linux.
Thanks.

ASKER CERTIFIED SOLUTION

Member_2_2484401

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

minjiezen

ASKER

Thank you very much.