Extract BLOB column data to a file with CL or SQL?

[JohnO]

Hi All,

I have an application that is generating various kinds of files (mostly PDFs) and saving them in BLOB columns in a db2 for i database table.

I may need to write a utility to extract these out to an IFS file. I can do this with an C embedded SQL program but wonder if this can be done with a SQL stored procedure or with a CL command - is that possible? It seems there's no SELECT ... INTO :file type command for a SQL procedure so I suspect not...

Thanks
John

[Emile]

The table:

Create Table BPGR/ITEMPIC
( ItemNo Num Not Null,
Picture BLOB(1M) With Default Null,
Primary Key( ItemNo )
)

The RPGLE:

D pic s SQLTYPE(BLOB_FILE)
D out s SQLTYPE(BLOB_FILE)
D item s 5 0

C/Exec SQL
C+ Set Option Commit=*NONE, DatFmt=*ISO, Naming=*SYS
C/End-Exec

C eval item = 10002
C eval pic_fo = SQFRD
C eval pic_name = '/home/PDF/gb.pdf'
C eval pic_nl = %len(%trimr(pic_name))

C/EXEC SQL Insert Into BPGR/ITEMPIC Values (:item,:pic)
C/END-EXEC

C SQLCODE Ifne 0
C SQLCODE dsply
C Endif

C eval out_fo = SQFOVR
C eval out_name = '/home/PDF/gbout.pdf'
C eval out_nl = %len(%trimr(out_name))

C/EXEC SQL Select picture

C+ Into :out
C+ From BPGR/ITEMPIC
C+ Where itemno = :item
C/END-EXEC
C SQLCODE Ifne 0
C SQLCODE dsply
C Endif
C* OUT_DL now contains the length of the data that was written to the IFS.

C Eval *inlr = *on
C Return

[ga...@cci-consultants.com]

Hello -

I need to do the opposite, ie copy a bunch of ifs files to a blob column in a table. Each record in the table will have one ifs file and I know the key values to populate the other fields in the table. Does anybody have any suggestions how to do in RPGLE or SQL.

Thanks
Gary W.