On 11/01/2011 18:58, Hay, Grant F wrote:
Hi,
I'm fairly new to using DBD::ADO, but I was wondering whether there is a way to return both an
output parameter and a sql print statement from a stored procedure. I've been trying to do it and
have come across what seems like possibly an issue with the module (or my usage is incorrect or
never intended or something). For example, if I have a stored procedure set up which accepts 10
parameters, the 10th also being an output parameter, and in the stored procedure there is also a
print statement that prints either "inserted values" or "updated values"
depending on which occurred in the stored procedure, then when I run:
use DBD::ADO;
my $dbh = DBI->connect("dbi:ADO:PROVIDER=SQLOLEDB;Data Source=X;INITIAL CATALOG=catalogname;", "",
"", { 'PrintError' => 0, 'RaiseError' => 0, 'PrintWarn' => 0 }) or die $DBI::errstr;
my $myID = 0;
my $sth = $dbh->prepare('InsertUpdateTestProgram', { CommandType =>
'adCmdStoredProc' } ) or die $dbh->errstr;
$sth->bind_param_inout(10, \$myID,10,4);
$sth->execute(1..9);
I can catch the print statement with something like:
if($sth->state =~ /01000/) {
if($sth->errstr =~ /Description : (.+)/) {print "\t$1\n";}
} else {
die "goodbye cruel world . SQL Error: $DBI::errstr\n";
}
But $myID does not get set to the value of the output parameter which seems to
be due to lines 1234 and 1235 in the execute subroutine of ADO.pm where the
error code generated by the print statement causes execute to return through:
1234: return if DBD::ADO::Failed( $sth,"Can't Execute Command '$sql'");
1235: _retrieve_out_params( $sth );
So the output parameter never gets retrieved. Is there some way of handling
the print statement with the module that I am not using which would allow me to
retrieve the output parameter? Feedback is appreciated :)
-Grant
(resending this because I guess I wasn't actually subscribed to the list before
so I don't think it accepted it)
I don't use ADO but in ODBC and MS SQL Server a procedure has not
completed until SQLMoreResults has returned NO_MORE_DATA and there is a
method in DBD::ODBC to call SQLMoreResults (odbc_more_results). There
are ways in the procedure to stop this - 'set nocount on' perhaps, to
batch them up. Maybe ADO has an equivalent of SQLMoreResults. Also, in
DBD::ODBC it has its own error handler which you can set up to catch the
print statements.
Of course, none of this may help you if ADO exits on a print statement
but I thought you might find it useful information.
Martin
