Perl, Postgres stored procedure returning refcursor, transaction, etc
- From: pia.gronqvist@xxxxxxxxx (Pia)
- Date: Tue, 21 Jun 2005 15:30:29 -0700
I spent a good deal of time to get the following working after
searching through Google, so I thought maybe there's someone else that
could benefit from this.
The function that is called takes a refcursor as an input parameter
and returns the *same* refcursor as the output. I hardcoded the name
of the refcursor into the statement because I couldn't figure out how
else to get it working...for now it doesn't matter, this works for me,
again, for now. If anyone else has ideas how to avoid this unelegant
hardcoding, please, oh please, let me know.
#!/usr/bin/perl -w
use strict;
use DBI;
my $dbh = DBI->connect(
'dbi:Pg:dbname=database',
'user',
'password',
{ PrintError => 0,
RaiseError => 1,
AutoCommit => 0 } ) ||
die "Could not connect to database: $DBI::errstr";
#$dbh->{TraceLevel} = 1;
my $sql = qq{ SELECT bitemeFunction(?) };
my $sth = $dbh->prepare( $sql );
my $func;
eval {
$func = $dbh->prepare(q{
SELECT xa_get_domain_for_spider_fn( 'biteme' );
FETCH ALL IN biteme;
});
$func->execute;
$dbh->commit;
};
if( $@ ) {
warn "Execution of stored procedure failed: $DBI::errstr\n";
$dbh->rollback;
}
my $count = 0;
my @biteme = ();
while ( @biteme = $func->fetchrow_array ) {
print "$biteme[0]\n";
}
$func->finish;
$dbh->disconnect;
exit;
[Ja nyt se toimii, helkkari soikoon.]
.
- Follow-Ups:
- Prev by Date: Can't install DBI.pm in cygwin under Windows 2003 Server
- Next by Date: Re: Perl, Postgres stored procedure returning refcursor, transaction, etc
- Previous by thread: Can't install DBI.pm in cygwin under Windows 2003 Server
- Next by thread: Re: Perl, Postgres stored procedure returning refcursor, transaction, etc
- Index(es):
Relevant Pages
|
|
