FAQ
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.

Search Discussions

  • David Goodman at Feb 10, 2005 at 6:19 pm
    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
    --- DMeleshko@factset.com 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.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupdbi-users @
categoriesperl
postedFeb 10, '05 at 5:15p
activeFeb 10, '05 at 6:19p
posts2
users2
websitedbi.perl.org

2 users in discussion

David Goodman: 1 post Dmeleshko: 1 post

People

Translate

site design / logo © 2022 Grokbase