× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.