Howto fetch procedure return value

WestaII has asked for the wisdom of the Perl Monks concerning the following question:

I´m connecting to MS SQL 2000 using DBD::ODBC running on W2K.

Could someone give me a code example of how to fetch the return value from a procedure call.

Here is my latest attemp:
...

my $result= $sth->execute() or die("error...");

# Fetch return value from procedure
do {
   while (my @aRet = $sth->fetchrow_array) {
      $ret = $aRet[0];
      }
   } while ($sth->odbc_more_results);
[download]

update (broquaint): added formatting + <code> tags

Comment on Howto fetch procedure return value Select or Download Code

Replies are listed 'Best First'.
Re: Howto fetch procedure return value by dga (Hermit) on Apr 03, 2003 at 20:36 UTC
You may want to also look in the DBI manual about setting RaiseError => 1 on your database handle. This allows errors to throw exceptions and saves a lot of 'or die' coding. `eval { $sth->execute(); #fetch rows here }; if($@) # if anything bad happened { print STDERR "My clever message here: $@"; # or even #die "Message"; }` [download] Now if DBI enounters any serious error with the connection etc., it will call die and the eval will catch it and set $@ and then reading $@ allows your script to handle the error which might be as simple as calling die in the if block. But this way you only need 1 die and you cannot forget to check for errors on your DBI interactions since every interaction done on that handle gets error checked automatically. Beware that if you use that handle outside an eval and there is an error, die is called which will end the script (which may very well be the desired behaviour).	[reply] [d/l]
Re: Re: Howto fetch procedure return value by aquarium (Curate) on Apr 04, 2003 at 10:57 UTC
..very true and correct. The 'execute' only submits the query for execution and DBI (only) does some little checking. If the SQL was trying to retrieve from a non-existent table though the die never gets called and your script falls over when trying to read resulting rows. Lookup any DBI book on how to handle errors, and check the DBI error string (can't remember exact var name) in a error trap routine. Chris	[reply]
Re: Howto fetch procedure return value by Super Monkey (Beadle) on Apr 03, 2003 at 17:48 UTC
I think the only piece you are missing is returning the records. you can do this a number or ways. the simplest would be to push() the $ret value onto an array. then return the array at the end of your subroutine. Here is a simple example of how I have done it in the past. `while (@row = $sth->fetchrow) { if (@row > 1) { push(@ret,[@row]); } else { push(@ret,@row); } } $sth->finish;` [download] Here I push the entire contents of the fetched rows.	[reply] [d/l]
Re: Howto fetch procedure return value by draper7 (Scribe) on Apr 04, 2003 at 14:48 UTC
Here's something that you might want to look into...(bind_columns). It binds columns to variable names. `$select = $db->prepare($sql); $select->execute(); $select->bind_columns(undef,\$var1,\$var2); while ($select->fetch) { print "$var1 $var2\n"; }` [download] --JD	[reply] [d/l]