LOTOFILE Function

Gurus, Is it possible to use the LOTOFILE function within a stored procedure? Examples very helpful. Thanks Sam

Hello, Here is an example of LOTOFILE usage, on a Windows server, IDS 11.10, but works the same in previous IDS versions (9.40, 10.00). Hope it helps: - Using superstores_demo db, created with dbaccessdemo9 script - Table used: catalog. Here's the schema: Column name Type catalog_num serial8 stock_num smallint manu_code char(3) unit char(4) advert row advert_descr clob Example of first row: catalog_num 10001 stock_num 1 manu_code HRO unit case advert ROW('<SBlob Data>','Your First Season''s Baseball Glove') advert_descr Brown leather. Specify first baseman's or infield/outfield style. Specify right - What we want: Given a catalog_num (serial, so integer as well), unload it's description, which is a clob (LO), into a file with prefix 'descr.clob', under c:\\\\temp directory, on the server machine (I could use 'client' too, where I ran the dbaccess to execute the unload clob function): Example of what we want the function to do: > select lotofile(advert_descr,'c:\\\\temp\\\\descr.clob','server') from catalog where catalog_num = 10001 ; (expression) c:\\\\temp\\\\descr.clob.0000000047443ee0 1 row(s) retrieved. The returned value is the actual name of the file where the large object (clob in this case) was unloded. It's an LVARCHAR value with the absolute path to the file. - Function creation: drop function unload_clob; create function unload_clob (p_catalog_num integer) returning lvarchardefine return_filename lvarchar; select lotofile(advert_descr,'c:\\\\temp\\\\descr.clob','server') into return_filename from catalog where catalog_num = p_catalog_num ; return return_filename; end function; Routine created. - Execution of the function: A few ways to call it: One way: select unload_clob (10001) from catalog where catalog_num=10001 expression) c:\\\\temp\\\\descr.clob.35 1 row(s) retrieved. Another way: execute function unload_clob (10001); (expression) c:\\\\temp\\\\descr.clob.29739 1 row(s) retrieved. - These two executions above created these two files under c:\\\\temp at the server (because I specified 'server' in 3rd parameter, but the destination could be the 'client' machine too, if I had said so in LOTOFILE call): dir c:\\\\temp 11/21/2007 09:50 AM 98 descr.clob.29739 11/21/2007 09:49 AM 98 descr.clob.35 - Contents of these two files: The clob value: C:\\\\temp>more descr.clob.35 Brown leather. Specify first baseman's or infield/outfield style. Specify right- or left-handed. C:\\\\temp>more descr.clob.29739 Brown leather. Specify first baseman's or infield/outfield style. Specify right- or left-handed. ----- Here you can find more information about LOTOFILE: LOToFile http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.bu ilt.doc/built63.htm Smart-Large-Object Functions (IDS) http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sq ls.doc/sqls1034.htm Regards, Veronica. > To: ids@iiug.org > From: samuel_jakabowski@wildblue.net > Subject: LOTOFILE Function [10437] > Date: Wed, 21 Nov 2007 09:10:56 -0500 > > Gurus, > > Is it possible to use the LOTOFILE function within a stored procedure? > Examples very helpful. > > Thanks > Sam > > > ******************************************************************************* > Forum Note: Use 'Reply' to post a response in the discussion forum. > _________________________________________________________________ Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/

Many thanks Veronica for poiting us in the right direction. Working fine now.

-- Example Table create table docs ( id serial , doc blob ) ; --Example procedure to load Blob into table create procedure loadBlob ( fileName varchar(255) ) ; insert into docs values ( 0 , filetoblob ( fileName , "client" ) ) ;end procedure ; -- Example usage execute procedure loadBlob ( "/etc/passwd" ) ; -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of SAMUEL JAKABOWSKI Sent: Thursday, 22 November 2007 1:11 AM To: ids@iiug.org Subject: LOTOFILE Function [10437] Gurus, Is it possible to use the LOTOFILE function within a stored procedure? Examples very helpful. Thanks Sam ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. *************************************************************** This message is intended for the addressee named and may contain confidential information. If you are not the intended recipient, please delete it and notify the sender. Views expressed in this message are those of the individual sender, and are not necessarily the views of the Department of Lands. This email message has been swept by MIMEsweeper for the presence of computer viruses. ***************************************************************