?
Solved

How to insert BLOB into a table?

Posted on 2005-03-01
5
Medium Priority
?
3,445 Views
Last Modified: 2008-02-01
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
Comment
Question by:minjiezen
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 

Author Comment

by:minjiezen
ID: 13430616
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
 
LVL 18

Expert Comment

by:Dave Ford
ID: 13432230
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
 

Author Comment

by:minjiezen
ID: 13444844
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
 
LVL 18

Accepted Solution

by:
Dave Ford earned 200 total points
ID: 13490723
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
 

Author Comment

by:minjiezen
ID: 13845674
Thank you very much.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

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…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses
Course of the Month10 days, 14 hours left to enroll

770 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