Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3732
  • Last Modified:

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.
0
minjiezen
Asked:
minjiezen
  • 3
  • 2
1 Solution
 
minjiezenAuthor Commented:
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.
0
 
Dave FordSoftware Developer / Database AdministratorCommented:
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;
}
0
 
minjiezenAuthor Commented:
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.
0
 
Dave FordSoftware Developer / Database AdministratorCommented:
Actually, I guess you don't technically need decimal.h and sqlproc.h  .  I just always include them at the top of every C program I write.

The code above should work without them (as you've probably already figured out).

DaveSlash
0
 
minjiezenAuthor Commented:
Thank you very much.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now