DBD::ODBC, DBD::Sybase and parameters to stored procedures

DMeleshko_at_factset.com
Date: 02/10/05 

To: dbi-users@perl.org
Date: Thu, 10 Feb 2005 12:15:24 -0500

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.