Perl and Stored Procedures

whysguy

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

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("dbiDBC:<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";
}