Inserting BLOB data in DB2 using SQL Query

问题:

I am stuck into a situation where I need to insert data into a blob column by reading a file from the Filesystem in DB2 (DB2 Express C on Windows 7).

Somewhere on the internet I found this INSERT INTO ... VALUES ( ..., readfile('filename'), ...); but here readfile is not an inbuilt function but I need to create it using UDF (c language libraries), but that might not be a useful solution.

Can somebody update us how to insert BLOB values using Insert command.

回答1:

1) You could use LOAD or IMPORT via ADMIN_CMD. In this way, you can use SQL to call the administrative stored procedure that will call the tool. Import or Load can read files and then put that in a row.

You can also wrap this process by using a temporary table that will read the binary data from the file, insert it in the temporary table, and then return it to be from the table.

2) You can create an external stored procedure or UDF implemented in Java or C, that will read the data, and then insert that in the row.

回答2:

I have not tried, but you can also use the Built-in modules that handle LOBs http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.apdv.sqlpl.doc/doc/r0055115.html

This is only available in DB2 LUW since version 9.7

回答3:

I've succeeded to do this by using IBM Data Studio with following query:

INSERT INTO MY_TABLE (BLOB_COLUMN) values (?);

And selecting a file from pop-up dialog.

Somehow, same method from RAD 8 doesn't show an option to load blob column type the same way.

回答4:

First and foremost, per IBM docs all LOB data in DB2 must have the following corresponding items in addition to a LOB column defined in a table. See docs for example CREATE statements.

1. LOB tablespace (one for every LOB column in each partition)
2. Auxiliary table on above table space that points to the blob column in base table (also, one for every LOB column in each partition)
3. A unique index in auxiliary table

Once this schema is prepared you can then run a LOAD command that can import with other data fields where blob content is referenced by file paths. Below is a demo with an Employees table:

DB Table (example table)

CREATE TABLE EMPLOYEES (
   ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
   EMPLOYEE_NUMBER INTEGER,
   EMPLOYEE_NAME VARCHAR(255),
   EMPLOYEE_PIC BLOB(500K)
);

CSV FILE (comma being default delimiter in LOAD with no headers)

1234, "John Doe", johndoe.jpg
5678, "Jane Doe", janedoe.jpg
...

DB2 LOAD (simple version using defaults for many other LOAD parameters)

LOAD FROM "/path/to/file.csv"
  OF DEL
  LOBS FROM /path/to/picture/folder/   --PATH OF BLOB FILES WITH BASENAME IN CSV
                                       --MUST END IN FORWARD SLASH
  MODIFIED BY LOBSINFILE CHARDEL""
  DUMPFILE="/path/to/dump.txt"         --FOR FAILED IMPORTS
  METHOD P (1,2,3)                     --NUMBER REFERENCE OF COLS, OR USE N FOR FIELD NAMES
  MESSAGES "/path/to/messages.txt"     --FOR LOAD COMMAND MESSAGES
  REPLACE INTO "EMPLOYEES"             --REMOVES EXISTING FOR IMPORT, OR USE INSERT TO ADD
  (EMPLOYEE_NUMBER
   EMPLOYEE_NAME,
   EMPLOYEE_PIC);

Command lines

> db2 -tvf "/path/to/load_command.sql"

> db2 "SELECT LENGTH(EMPLOYEE_PIC) FROM EMPLOYEES"

回答5:

DB2 SQL query to insert JPG file into table

create table table_name(column_name BLOB) /* BLOB is data type

insert into table_name(column_name)values(blob('c:\data\winter.jpg'))

c:\data\ its a path and winter.jpg its a image name