Hi Ed:
This is from the discussion I had with David when I encountered
problems on
calling stored procedures.
David had the following comments
" I can think of two approaches. The first involves dynamically
creating a temporary table to hold the result set your procedure
currently
prints, then using a Perl/DBI query to fetch the results in a while
loop (as you
tried in the script below). The other approach is actually easier to
dream up
than it is to realize, and infact may not work with the perl DBI
module. It
goes as follows:
Create a package and define in it a REF CURSOR and a function or
procedure to accept the query parameters and return the REF CURSOR.
That much
is straight forward (and works from SQL*+) but I am stalled as to
how to
handle the returning REF CURSOR in perl/DBI HTH "
I was actually able to solve the problem using REF cursor it works
very neatly.
For handling the perl/DBI part use the DBD::Oracle qw(ORA_RSET) . Let
me
know if you have problems I shall write a small example for you that
will help you
understand things better.
Cheers always!!
Murli
------------------------------------
Dr. T. Murlidharan Nair,
San Diego Supercomputer Center,
Univ Of Cal, San Diego,
La Jolla, Callifornia.
-------------------
> ########################################
> use DBI;
> use DBD::DB2::Constants;
> use DBD::DB2;
>
> $dbh = DBI->connect("dbi:DB2:TEST", "admin", "admin");
>
> #$sql = "CALL TEST.WRITEFILESTATUS ('test','missing')";
> $sql = "INSERT INTO TEST.FILE_TABLE (FILE, STATUS) VALUES ('Tone',
> 'Missing')";
>
> $sth = $dbh->prepare($sql);
> $sth->execute();
> ....
> #######################################
>
> It works for the 2nd $sql (a normal insert statement) but it fails
for the
> 1st $sql (call a stored procedure instead).
>
> The error:
> DBD::DB2::st execute failed: [IBM][CLI Driver][DB2/NT] SQL1109N The
> specified DLL "TEST.WRITEFILESTATUS" could not be loaded.
>
> When I paste and run the 1st $sql: "CALL TEST.WRITEFILESTATUS
> ('test','missing')" in the DB2 command centre and it works.
>
> Please let me know how I should invoke stored procedures within
Perl.
>
> Many Thanks.
>
> Ed
>
>
>
>
>
>
> _________________________________________________________________
> Chat with friends online, try MSN Messenger:
http://messenger.msn.com
>
