How to get results back from SQL Stored Procedure using Perl

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

Hi Monks!
I am trying to call a SQL stored procedure with Perl to extract its value but this is not going anywhere. Could anyone point me to the right direction if you had done this kind of coding before?
Here is a sample code:

# at this point connection to the DB is ready
...

my $input='12345';
my $sth = $dbh->prepare('CALL ZIPCODES('.$dbh->quote($input).')') or d
+ie $dbh -> errstr;

$sth->execute() || die $sth->errstr;

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

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

}

$sth->finish();
[download]

Thanks!

Comment on How to get results back from SQL Stored Procedure using Perl Download Code

Replies are listed 'Best First'.
Re: How to get results back from SQL Stored Procedure using Perl by roboticus (Chancellor) on Apr 03, 2012 at 19:46 UTC
You've omitted too much information to know how to answer. Helpful items would be: What are the error messages? What database are you using? How is the stored procedure declared? What database driver are you using? Without that, all I could do is guess, and likely I would guess wrong. ...roboticus When your only tool is a hammer, all problems look like your thumb.	[reply]
Re: How to get results back from SQL Stored Procedure using Perl by JavaFan (Canon) on Apr 03, 2012 at 20:35 UTC
This part of the documentation of the DBI is relevant: Calling stored procedures is currently not defined by the DBI. Some drivers, such as DBD::Oracle, support it in non-portable ways. See driver documentation for more details. Which means that 1) `prepare`, `execute`, `fetch` is unlikely to work, 2) without knowing which driver you're using, any answer is going to be guesswork, and 3) be prepared that you may have to result to using a more specific interface to your database client libraries instead of the DBI, which implements the lowest common denominator, and hardly anything else.	[reply] [d/l] [select]
Re^2: How to get results back from SQL Stored Procedure using Perl by Anonymous Monk on Apr 03, 2012 at 21:27 UTC
Sorry, using ODBC to connecto to AS400 database.	[reply]
Re^3: How to get results back from SQL Stored Procedure using Perl by roboticus (Chancellor) on Apr 03, 2012 at 23:30 UTC
Sorry, I don't have any experience on an AS/400. I assume you're using DB2, then? If so, you might find the IBM document Perl programming with DB2 Universal Database useful. It contains an example of calling a stored procedure. While it doesn't show how to retrieve a recordset from the stored procedure, further poking around on the IBM site may give you what you're looking for. Another place you can look is the test code (presumably) provided with the DBD::DB2 driver. If you don't have it on hand, just download it from CPAN and expand it locally, and go into the t directory. There ought to be a test for retrieving from stored procedures. I notice that the "what changed" document mentions a couple SPROC changes, including one about retrieving multiple recordsets. ...roboticus When your only tool is a hammer, all problems look like your thumb.	[reply]