DBD::Sybase and stored procedures 
Author Message
 DBD::Sybase and stored procedures

I am having some trouble preparing and executing a Sybase stored procedure
using DBI.

My stored procedure has a numeric output parameter, and I would like to
use placeholders so that I can prepare the procedure once and call it
multiple
times.  The stored procedure is used by other applications, so I don't
really have the ability to change it.

Here is an example stored procedure:

___BEGIN SP___

create procedure sp_test


as begin

end

___END SP___

Here is what I am attempting with perl:

____BEGIN PERL SNIPPET____

use DBI ":sql_types";
my $dbh = DBI->connect('dbi:Sybase:server=server;database=db', 'pw',
'user');
my $sth = $dbh->prepare("exec sp_test ?, ? OUTPUT");
$sth->bind_param(1, 'test', SQL_CHAR);
$sth->bind_param(2, 1, SQL_NUMERIC);
$sth->execute();

____END PERL SNIPPET____

When I run this I get the following error on the $sth->execute call:

"Arithmetic overflow during implicit conversion of NUMERIC value '12345'
to a NUMERIC field ."

By the way, if I change the output parameter in the test stored
procedure to a character datatype, then I do not get errors. I suppose
this has something to do with "DBD::Sybase defaults all the parameters
to SQL_CHAR" (from the DBD::Sybase docs).

Also, I do have success if I try the following prepare:


But this doesn't allow me to use placeholders.  If I replace 'test' with
a placeholder, I get the following error on the $dbh->prepare call:

"The untyped variable ? is allowed only in a WHERE clause or the SET
clause of an UPDATE statement or the VALUES list of an INSERT statement"

I suppose this comes from the fact that when using placeholders "the
stored procedure call must be initiated with an exec and it must be the
only statement in the batch that is being prepared" (from the
DBD::Sybase docs).

So, if I understand everything here (unlikely!), you cannot use
placeholders with a Stored Procedure that has a numeric output
parameter.  If this is true, then I suppose I can use string
concatenation and prepare the statement for each call to the SP.
However, I fear that this will not be as fast, and I will have to deal
with escaping quotes, etc...

Thanks,
Kevin Healey

(Remove _SPAMMENOT to send me email)



Mon, 21 Nov 2005 14:10:06 GMT  
 DBD::Sybase and stored procedures
    Hi Kevin,


Quote:
> I am having some trouble preparing and executing a Sybase stored procedure
> using DBI.

> My stored procedure has a numeric output parameter, and I would like to
> use placeholders so that I can prepare the procedure once and call it
> multiple
> times.

    the DBD::Sybase manpage on my system states that
    you can't.

        For those of you who are used to Transact-SQL there are
        some limitations to using this feature: In particular you
        can not pass parameters this way to stored procedures...

    Regards,

        Peter Dintelmann



Tue, 22 Nov 2005 10:19:02 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. DBD::Oracle through DBD::Proxy .. stored procedures help

2. DBD::Oracle through DBD::Proxy .. stored procedures help

3. HELP: Need a little help on using PerlV5.003 with a Sybase stored procedure

4. HELP: Need a little help on using PerlV5.003 with a Sybase stored procedure

5. HELP: Need a little help on using PerlV5.003 with a Sybase stored procedure

6. Stored procedures with DBD::Informix

7. Stored Procedures and DBD::ODBC

8. Using Embedded SQL vs Stored procedures in Perl DBI?DBD for Oracle

9. DBD::Oracle accessing stored procedures returning a ref cursor seg fault

10. DBD::Oracle Stored Procedures

11. DBD Oracle: Stored Procedures vs Perl

12. DBD::ORACLE and Stored Procedures

 

 
Powered by phpBB® Forum Software