Re: ADO - returning stored procedure output parameter and sql print statements?

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