Stored Procedures, BLOBs, and errors...
Hello, all. I've recently come to a point in my work where I need to
extract thumbnail images stored in our Informix database (Universal Server
9.12.UC2X1) for easy viewing via a Java interface. The thumbnails are
stored as BLOBs from files in the Mac PICT image format. There are a
number of tables in the database, but each one has a column named preview
that contains the thumbnail. What I would like to do is create a stored
procedure that can be accessed from Java that will unload the BLOB data
into a file and invoke a shell script to convert the file from PICT to GIF
format. This is what I have so far:
---------------------------------------------------------------------------
CREATE PROCEDURE get_preview( requested_index VARCHAR(16) )
DEFINE filename VARCHAR(45);
DEFINE result BLOB;
-- define the directory where the files will be dumped and converted
-- and append the index number as the name of the file.
LET filename = '/export/home/tj/database/temp/' || requested_index;
-- dump the BLOB data from the database into the indicated file
SELECT LOTOFILE(previev, filename || '.pict!', 'server') FROM tmimage
INTO result
WHERE index_number = requested_index;
-- call the script to convert from the Mac PICT format to the GIF format
SYSTEM '/export/home/tj/bin/picttogif ' || filename;
END PROCEDURE;
---------------------------------------------------------------------------
Ideally, I would like the SP to also accept a table name as an argument,
but I have seen nothing in the documentation that discusses this. I am
not certain of the proper commands to use in registering this stored
procedure...I have tried to 'Run' this using DB-Access in menu mode, and
to CREATE PROCEDURE FROM 'get_preview.sql' in DB-Access in command-line
mode, and neither works. Invariably I get the -201: syntax error message.
Any ideas why? I am alos wondering what exactly is returned by the
LOTOFILE function. The docs don't say, and I just guessed (most likely
incorrectly...) =P
Thanks for your help in advance,
TJ
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Those have most power to hurt us, that we love.
-Francis Beaumont
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~