DB2-SQL-SP Result Set

[111999111]

Is it possible to mangage result sets from db2 sql stored procedures in perl ?

bernd

 

[MikeLacey]

bernd,

It may be yes.

The best way to use databases from within Perl seems to be the DBI - and there's a DB2 module (a DBD) for the DBI.

Have a look at the FAQ's in this forum talking about database access. (Back to the thread list and then click on the FAQ tab)

Then get yourself along to

http://www.cpan.org

and download the DBI and DBD:B2.

If you're using Activestate Perl you already have the DBI installed.

The DBD:B2 module is written and maintained by IBM, I've never used it but I would suspect it's rather good.
Mike
______________________________________________________________________
"Experience is the comb that Nature gives us after we are bald."

Is that a haiku?
I never could get the hang
of writing those things.

 

[111999111]

hello mike

surely i have not described very detailed my problem,
but i have not found anywhere how to get record sets from db2-sql-stored-procedures to perl with dbi::db2. fine i can manage single datasets with the bind_param_inout ... AND stored procedures, but not record sets.
ok, one possible way is to create in the procedure a result table and make a query in perl to this, but this i dont want .
and so iam looking for this ..
but maybe IBM has the answer i found -> recordsets to clients are only usable with jdbc, cli and sqlj.
but i hoped, that someone could give me an idea for dbd::db2.
so then , thanx and with kindly regards bernd

 

[MikeLacey]

Sorry I couldn't help you Bernd, I'm a DBI person but don't know anything about DB2. Have you considered writing to the DBD:B2 maintainer? Mike
______________________________________________________________________
"Experience is the comb that Nature gives us after we are bald."

Is that a haiku?
I never could get the hang
of writing those things.

 

[111999111]

hello mike

yes, i think so , if i have pointed out the right one :

-> db2perl@ca.ibm.com ??

with kindly regards for your efforts , bernd ..

 

[MikeLacey]

Yes - that looks right to me.

Probably a good idea to make you you've read all of the documentation - and checked the DBI mailing list archives before you approach Robert with a question. He gets quite a lot of mail. You'll need to give him the clear impression that you've thought carefully and done your own research before resorting to contacting him. Mike
______________________________________________________________________
"Experience is the comb that Nature gives us after we are bald."

Is that a haiku?
I never could get the hang
of writing those things.

 

[111999111]

------------------ SQL ------------------

------------------------------------------
-- RecordSet from journal for user_id --
-- call: dmt_selectContentBillingSet --
------------------------------------------

create procedure dmt_selectContentBillingSet (in spUser_id integer)
language sql
begin

declare sqlstate char(5) default '00000';

declare resultSet
cursor with return to client for

select contentID,datum,value
from journal
where user_id=spUser_id;

open resultSet;


end @

------------------ PERL ------------------


#!/usr/local/bin/perl


use DBI;
use DBD:B2::Constants;

$user_id=111;

$database='dbiB2:damit1';
$user='damit';
$password='timad';

$dbh = DBI->connect($database, $user, $password)
or die "Can't connect to $database: $DBI::errstr";

if (!defined($dbh)) { exit; }

$stmt = "call dmt_selectContentBillingSet(?)";

$sth = $dbh->prepare($stmt);
$sth->bind_param_inout(1,\$user_id,10);


$sth->execute();

do
{

while( @row = $sth->fetchrow_array ) {

print $row[0]." ",$row[1]." ",$row[2]."\n";

}

} while ($sth->{db2_more_results} );


# close selection criteria cursor
$sth->finish();
$dbh->disconnect();

 

[MikeLacey]

Hey and this works? Mike
______________________________________________________________________
"Experience is the comb that Nature gives us after we are bald."

Is that a haiku?
I never could get the hang
of writing those things.