On 05-Jun-2002 Kennis Koldewyn wrote:
> I'm trying to call a Microsoft SQL Server 2000 stored procedure from DBI that
> will return a result set as well as a value via a variable bound using
> bind_param_inout. Consider the following stored procedure:
>
> ----- Begin stored procedure -----
> CREATE PROCEDURE foo
> (@foo_out INTEGER OUTPUT)
> AS
> SELECT @foo_out = 2 + 3
> SELECT * FROM some_table
> ----- End stored procedure -----
>
> And consider the following Perl code:
>
> ----- Begin Perl snippet -----
> my $foo_out = 0;
> $sth = $dbh->prepare("EXEC foo ?");
> $rv = $sth->bind_param_inout(1, \$foo_out, 10);
> $rv = $sth->execute;
> ----- End -----
if you add:
my @row;
while(@row = $sth->fetchrow_array) {
print $foo_out, "\n";
}
print $foo_out, "\n";
you will see the last print for foo_out is 5 (at least it is for me) so it looks
as though the output parameter is not available until the result-set produced by
the select is consumed. I think there is a SQLGetInfo call that you can make to
determine if this is the case in the driver but I cannot remember it right now.
Martin
> If the line "SELECT * FROM some_table" is removed from the stored procedure
> foo, then the Perl variable $foo_out will receive the value 5 after
> $sth->execute (although there won't be a result set to fetch). But if you
> add the "SELECT * FROM some_table" line back in, then you can use one of the
> fetch functions within Perl to get the result set after $sth->execute, but
> the value of $foo_out stays zero. Am I asking for too much, or is there a
> trick for making this work?
>
> My specs:
>
> Perl v. 5.6.1 (ActiveState build 631, under Microsoft Windows 2000)
> DBI v. 1.23
> DBD::ODBC v. 0.41
>
> Thanks for any ideas you can share,
>
> - Kennis
>
>
> ---- Kennis Koldewyn (koldewyn<at>cloud9.net) ----
> Without computers, it would be virtually impossible
> for us to accompliowur xow;gkc,mf(&( - Dave Barry
--
Martin J. Evans
Easysoft Ltd, UK
Development
