IDS Forum

RE: LOTOFILE Function

Posted By: Veronica Gomes
Date: Wednesday, 21 November 2007, at 10:04 a.m.

In Response To: LOTOFILE Function (SAMUEL JAKABOWSKI)

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 lvarchar
define 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.built.doc/built63.htm

Smart-Large-Object Functions (IDS)
http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.sqls.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/

Messages In This Thread

• LOTOFILE Function
  SAMUEL JAKABOWSKI -- Wednesday, 21 November 2007, at 9:10 a.m.
  • RE: LOTOFILE Function
    Veronica Gomes -- Wednesday, 21 November 2007, at 10:04 a.m.
    • Re: RE: LOTOFILE Function
      SAMUEL JAKABOWSKI -- Wednesday, 21 November 2007, at 3:09 p.m.
  • RE: LOTOFILE Function
    Stuart McCann -- Monday, 26 November 2007, at 3:51 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.