Perl and Stored Procedures

Anybody have experience with executing a stored procedure to return data from a database using DBI? If so, would you mind posting example code? Also, any limitations for doing so with a DBI/ODBC connection to a Microsoft SQL database?

Thanks in advance.

- Billy

Find more posts tagged with

Comments

Sunil Mugalavally

Hi,
Here is some sample code to execute a MS SQL stored procedure. Here the stored procedure returns a value. I guess it will work if the SP returns a result set too.

use DBI;
my %attr = (
PrintError => 0,
RaiseError => 0,
);

my $dbh = DBI->connect("dbi Smiley Surprised

DBC:<DBNAME>", 'username', 'password', \%attr );
if($DBI::errstr)
{
print FOO "Exception while connecting: $dbh";
close FOO;
return ("ERROR-DATABASE . $DBI::errstr");
exit
}

my $sth = $dbh->prepare("EXEC jobQueueInsert '$deployment_name',' $resource', '$filelist_path', '$job_id', '$task_id', '$processID'"); # jobQueueInsert is a SQL stored procedure
if($dbh->errstr())
{
print FOO "Can't prepare stored procedure: ", $dbh->errstr(), "\n";
close FOO;
return ("ERROR-DATABASE . $dbh->errstr()");
exit;

}
$sth->execute;
if ($sth->errstr())
{
print FOO "Can't execute stored procedure: ", $sth->errstr(), "\n";
close FOO;
return ("ERROR-DATABASE . $sth->errstr()");
exit;
}
my @return_jobid = ();
while($data = $sth->fetch)
{
@return_jobid = @{$data};
print FOO "Return code Job ID = $return_jobid[0], $/ \n";
}
print FOO "Job ID = $return_jobid[0] \n";
$dbh->disconnect;
if ($dbh->errstr())
{
print FOO "Failed to disconnect: ", $dbh->errstr(), "\n";
}else{
print FOO "db disconnected\n";
}

whysguy

Very cool. Thanks Sunny. What I still need to figure out is how to handle a result set from a stored procedure. If anyone has experience with this, I would appreciate a reply.

Thanks

Migrateduser

> how to handle a result set from a stored procedure.

I think the same way you handle the results of a literal SQL statement. I do remember something about the DBD not supporting something, can't remember if it was record/resultset or the other option for what a stored proc can return (which term I also don't remember).

Sunil Mugalavally

Here is the code that handles SPs that return a record set.
Its exactally the same as the one I posted earlier except the return array is a record set and not a return code.
my $sth1 = $dbh->prepare("EXEC test1");
if($dbh->errstr())
{
print FOO "Can't prepare stored procedure TEST1: ", $dbh->errstr(), "\n";
close FOO;
return ("ERROR-DATABASE . $dbh->errstr()");
exit;

}
$sth1->execute;
if ($sth1->errstr())
{
print FOO "Can't execute stored procedure TEST1: ", $sth->errstr(), "\n";
close FOO;
return ("ERROR-DATABASE . $sth1->errstr()");
exit;
}
while($data1 = $sth1->fetch())
{
my @return_set = ();
@return_set = @{$data1}; # @result_set is a record
foreach $ret(@return_set){
print FOO "Return Set= $ret, "; #
}
print FOO "\n";

}
Let me know if you need anything else.