dbi module and stored procedures

[wimvanherp]

I work with DBI and ODBC . It's easy to work with sql scripts in perl, but i don't manage to get to work with stored procedures. I do not find how I can get the result back from a stored procedure into perl .
I can start the procedure with a sql script like : exec procedure arg1 arg2. but the problem is how to get the result back

Wim Vanherp
Wim.Vanherp@belgacom.net

 

[edog1]

try something like this:

$sth = $dbh->prepare( "BEGIN my.packagw.proc(?,?,?);END;" );
$sth->bind_param( 1, $ARGV[0]);
$sth->bind_param( 2, $ARGV[1]);
$sth->bind_param_inout( 3, \$ret_val, 50 );

using an in_out parameter

 

[missbarbell]

Below is my wrapper around DBI, which will work when I use CSV files, MySQL and currently ODBC to MS SQL Server 7. I've taken out my error checking and the like, to make it more readable, and you simply let DBI handle all the returning for you.

sub GetQuery {
my ($dbi,$type,$sql,@args) = @_;
# prepare the sql statement for executing
my $sth = $dbi->prepare($sql);

# execute the SQL using any values sent to the function
# to be placed in the sql
$sth->execute(@args)) {

my @result;
# grab the data in the right way
if ( $type eq 'array' ) {
while ( my $row = $sth->fetchrow_arrayref() ) {
push @result, [@{$row}];
}
} else {
while ( my $row = $sth->fetchrow_hashref() ) {
push @result, $row;
}
}

# finish with our statement handle
$sth->finish;
# return the found datastructure
return @result;
}

Barbie
Leader of Birmingham Perl Mongers

http://birmingham.pm.org

 

[homefish]

missbarbell, how do you find that this affects performance?

Thanks,

fish

 

[missbarbell]

homefish: Affect performance between what? If you meant between CSV, MySQL and MS SQL Server, it all depends where your DB server is located. The latter in my case is remote and the others are local, so its not a true representation of perfomance. However, all three are not too dissimilar in performance, depending upon your application.

Barbie
Leader of Birmingham Perl Mongers

http://birmingham.pm.org

 

[wimvanherp]

barbie,edog1


thanks for your reply, but i do not see how i can get the result back from a stored procedure when there is no parameter passed to this procedure. f.i. there is a stored procedure on the database itself that does need no parameters but returns an integer. the procedure is called r_dosomething and the way to access it through sql is : " exec pr_dosomething" . But how can I get retreive the return value ?

Wim Vanherp
Wim.Vanherp@belgacom.net

 

[missbarbell]

my $row = $sth->fetchrow_arrayref();
my $value_from_stored_procedure = $row->[0]->[0];

If you're unsure of values in a hash, array or object, it's a good idea to have something like the following:

use Data:umper qw(DumperX);
print STDERR DumperX($row);

so you can see what's there.

Barbie
Leader of Birmingham Perl Mongers

http://birmingham.pm.org

 

[edog1]

If there is no out parameter and it returns a value, isn't that a function, anyway, check out this site, it helped me when I was working with the dbi module:

http://www.saturn5.com/~jwb/dbi-examples.html