Thanks for the tip. I thought that using placeholders can help with
execution plan caching. Nonetheless, at this point optimization is not my
#1 priority.
Here is a new peculiarity that I've discovered. For the T-SQL code bellow,
DBD::ODBC will return a single record set containing @ret and @b, while
DBD::Sybase returns 3 record sets:
1) 0 -- return code for the overall code block
2) 321 -- output @b parameter
3) 42, 321 -- results of the 'select' statement
Details are in DBD::Sybase docs under "Handling Multiple Result Sets".
Suggested work around include using while($sth->{syb_more_results}) loop.
Suffice to say that this is not exactly a cross-drive solution. Luckily,
DBD::ODBC contains an equivalent 'odbc_more_results'. Thus, the code
becomes
##############################
my $query = qq|
begin
declare @ret int, @b int
exec @ret = dbo.foo $a, @b out
select @ret, @b
end|;
my $sth = $db->prepare($query) or die $db->errstr;
$sth->execute();
my ($d, $next_rs_flag);
if ($^O eq 'MSWin32') {
$next_rs_flag = 'obdc_more_results';
} else {
$next_rs_flag = 'syb_more_results';
}
do {
while ($d = $sth->fetch) {
# 2 params returned by 'SELECT' recordset
# talk about "hackiness"
if (2 == @$d) {
print scalar @$d . "\n";
}
}
} while ($sth->{$next_rs_flag});
##############################
Thought you'd like to know.
Best,
Dmitry
David Goodman <[EMAIL PROTECTED]>
02/10/2005 01:19 PM
To
[email protected]
cc
Subject
[Spam: 05.3] Re: DBD::ODBC, DBD::Sybase and parameters to stored
procedures
Placeholders are only supported for values in select
statements. For your exec, you will need to substitute
in values with perl variables.
That's the essence of the error message.
regards,
David
--- [EMAIL PROTECTED] wrote:
> Hi all,
>
> Trying to solve a puzzle -- how to write a
> cross-platform code to call
> stored procedure with output parameters and return
> codes.
>
> Environment:
> - MS SQL 2000 database
> - Win32, ActivePerl - 5.8.6, DBD::ODBC
> - Linux, perl 5.8.0, DBD::Sybase (freeTDS)
>
> Stored procedure:
>
> ----------------------------------------------------
> CREATE proc dbo.foo
> @a int,
> @b int OUTPUT
> as
> set @b = 321
> return 42
> GO
> ----------------------------------------------------
>
> Perl code snippet:
>
> ##############################
>
> my $query = q|
> begin
> declare @ret int, @b int
> exec @ret = dbo.foo ?, @b out
> select @ret, @b
> end|;
>
> my $sth = $db->prepare($query) or die $db->errstr;
> $sth->execute($a);
>
> my (@d) = $sth->fetchrow_array;
> $ret = $d[0];
> $b = $d[1];
>
> ##############################
>
> The code runs with DBD::ODBC but blows up in
> DBD::Sybase with:
>
> Panic: dynamic SQL (? placeholders) are not
> supported by the server you
> are connecting to at ...
>
> Hence, the questions:
>
> 1. Do newer versions (> 1.61) of DBD::Sybase support
> dynamic placeholders?
>
> 2. Is there a better way to write the above
> cross-driver code?
>
> Prior to this hackiness I tried variations of a
> "clean" approach -- using
> $sth->bind_param_inout(). These mostly break down in
> DBD::Sybase.
>
>
> Thanks,
>
> Dmitry.
