Michael Peppler
Sybase Consulting
Sybase on Linux
Install Guide for Sybase on Linux
General Sybase Resources
General Perl Resources
BCP Tool
Bug Tracker
Mailing List Archive
Downloads Directory
Sybase on Linux FAQ
Sybperl FAQ
Michael Peppler's resume

sybperl-l Archive

Up    Prev    Next    

From: "Raju, Ramakrishna (Equity)" <ramakrishna_raju at ml dot com>
Subject: Stored proc execution using DBI
Date: Feb 3 2005 8:49PM

From the name, this is a sybperl list. I'm not aware if there is one for DBI, so I am posting here.

I am writing a Perl script using DBI interface to execute a stored proc using prepare() , param_bind() & execute() calls. I had some trouble with datatypes of the arguments and after some research, I figured out the right way to do it. I had to import the sql_types " use DBI qw(:sql_types) " and then bind the params and call execute. It works fine. Here's a complete working code:

use DBI qw(:sql_types);
$dsn = 'dbi:Sybase:server=CHI_ASE;database=trades';
$user = 'trd';
$password = 'xyzxyz';

$dbh = DBI->connect ( $dsn, $user, $password );
if ( ! $dbh ) {
        print "Error connecting to the database: $DBI::errstr\n";
my $symbol = "ABC";
my $qty = 10;
my $price = 12.35;

$sth = $dbh->prepare( 'exec p_process_data ?,?,?,?' );
$sth->bind_param( 1, 1, SQL_INTEGER  );
$sth->bind_param( 2, $symbol , SQL_VARCHAR  );
$sth->bind_param( 3, $qty, SQL_INTEGER  );
$sth->bind_param( 4, $price, SQL_FLOAT  );

$ret_val =  $sth->execute();

You need to create a procedure called "p_process" in the trades database with the appropriate parameters. The above code works. It correctly understands the type of the parameters and passes them to Sybase and Sybase does not complain about impossible/illegal implicit conversions.

I am looking for some answers to some questions in this context :

1.	What exactly does execute() return ? It seems to always return a value of -1 irrespective of what the stored procedure returns in its return  statement.. In code for sub execute, the last statement in the sub is 
@$data || 'OEO';

'OEO' is  a substitute for the number 0, so that it tests true but equal to 0. Why is @$data -1 always ?

2.	If I return rows from the procedure using a "select" command, I don't see any output ( bcoz I need to fetchrow and print it) , whereas if I use the SQL print command in the stored proc, I can see the output. How does the print output reach the screen when there is no print statement in the user perl program. Is the driver printing it ? I suspect so. Is the message handler output being sent to the screen thru some error  handling routine ?
3.	How do I pass output variables to a Sybase stored procedure and get back the values from the stored procedure ? I see a lot of Oracle examples on the web/books but no Sybase example. I would appreciate a small but complete working code to demonstrate question # 3.

Thank you,

If you are not an intended recipient of this e-mail, please notify the sender, delete it and do not read, act upon, print, disclose, copy, retain or redistribute it. Click here for important additional terms relating to this e-mail.