Charles Wilt wrote:
Every example I've seen so far shows populating a BLOB in a
table.
I'm looking for a way to populate an in memory results set
containing a BLOB column.
Charles Wilt wrote:
I've got a .PDF file in the IFS I want to return as a BLOB
column in a result set from a stored procedure.
Stored procedure can be written in SQL or RPG, it doesn't
matter. (Thus the cross post, sorry David :)
The stored procedure will return just a single row.
Can I do this directly without loading the BLOB column into
a temporary table?
Links and/or examples appreciated.
FWiW, even if managing to avoid your own [temporary] table, the DB2
actually will create an effective temporary file [a dataspace at least]
into which the data for a LOB is loaded in order to effect any I/O. Not
to diminish the benefits of eliminating your own table as additional
overhead, just explaining a bit about how it works; from which, the idea
of how a LOCATOR works may seem more obvious.
Presumably a typical example of populating a table is as follows?:
EXEC SQL INSERT INTO TableA VALUES ( :hv_ref_PDF ) /* where
hv_ref_PDF is the SQL LOB-reference-file host-variable which identifies
which PDF file by name & its path, plus a file-option SQFRD that would
suggest the named file is to be read */:
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/sqlp/rbafyexampinsertclob.htm
So you want to get the data directly from the IFS [binary .pdf]
stream file into a BLOB column of a row of a result set, instead of into
a table which is then queried to become the result set? I would expect
that an approach, by mimicking scenarios with other data types, would
suffice. For example when a need arises to extract data from something
other than a database file in order to present the data area data in an
SQL result, for example from a data area using an API or RTVDTAARA, a
UDF would seem to be appropriate.
So perhaps a scalar external user defined function which returns data
type BLOB, which is then specified on the column list for the result set
that is generated? I have never used the AS LOCATOR nor a BLOB as the
return value for a SQL UDF, but it would seem a reasonable approach;
i.e. mimics the character data from a data area approach.
Create a procedure [ReadPDF] in a service program which opens the
stream file and returns the data as the return value for the function.
Define the parameters using one of the SQL UDF /PARAMETER STYLE/
definitions. Then create the SQL function which would invoke that
procedure from SQL; something like:
create function pdfasblob ( ...
returns blob(50M) /* as locator */
...
parameter style ...
external name 'MYLIB/MYSQLUDFS(ReadPDF)'
In an SQL procedure returning a result set including the data from
fields x, y, and the pdf [according to something identified by field z],
that might have for example, a result set of:
select x, y, pdfaslob(z, ...) from somefile ...
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/apis/sqludf_create_locator_with_ccsid.htm
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/sqlp/rbafylobs.htm
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/sqlp/rbafylobrefvar.htm
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/sqlp/rbafyloblocators.htm
References to LOB variables, References to LOB locator variables, and
References to LOB file reference variables at:
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/db2/rbafzmstch2refvar.htm#wq323
The EXEC SQL VALUES ( :hv_ref_PDF ) INTO :local_variable may
function to load the data directly from the stream file, but without it
being an INSERT INTO a table? That is, directly into a program
variable. I did not see /variable/ in the syntax diagram on the left of
INTO to suggest the LOB file reference variable is appropriate, but in
the examples for VALUES INTO it shows a LOB locator being used in a
SUBSTR function:
http://publib.boulder.ibm.com/infocenter/systems/scope/i5os/topic/db2/rbafzvalinto.htm
Regards, Chuck
As an Amazon Associate we earn from qualifying purchases.