Stored procedures and DBD::Sybase
Hello,
I am using DBI v. 1.38, DBD::Sybase v. 1.01 (?), and FreeTDS 0.61.2 to
connect to an MSSQL 6.5 database from a Linux client and I am having
problems retrieving the values of out parameters to a stored procedure
call.
I followed the example in the DBD::Sybase docs modifying for my purpose
as follows:
#!/usr/bin/perl
use DBI;
use DBD::Sybase;
my $dbh = DBI-connect('dbi:Sybase:server=',
'sa', '', {PrintError = 1, AutoCommit = 1});
$dbh-do('use tempdb');
my $sql = q{
declare @a int
declare @b varchar(10)
exec foo @a output, @b output
declare @c varchar(255)
select @c = @b + ' : ' + convert(varchar(3), @a)
print @c
};
my $sth = $dbh-prepare($sql);
$sth-execute;
do {
while ($d = $sth-fetch) {
$rt = $sth-{'syb_result_type'};
print(Iterating: $rt\n);
if ($rt == DBD::Sybase::CS_PARAM_RESULT) {
print(found param result [EMAIL PROTECTED]);
$a = $$d[0];
$b = $$d[1];
} elsif ($rt == DBD::Sybase::CS_ROW_RESULT) {
print(found row result [EMAIL PROTECTED]);
} elsif ($rt == DBD::Sybase::CS_STATUS_RESULT) {
print(found status result [EMAIL PROTECTED]);
}
}
} while($sth-{'syb_more_results'});
print(Retrieved values $a $b\n);
And received the following output:
Iterating: 4043
found status result [0]
hello : 5
Retrieved values
As I understand the docs, the there should have been a CS_PARAM_RESULT
result set but there was not. As you see, I even printed out the
results from the sql to make sure the out params were being set. Am I
doing something wrong or is this a known limitation when using my
configuration. Perhaps I can use the Sybase Open Client libraries
instead?
I help will be greatly appreciated.
Thank you,
Steven
Re: Stored procedures and DBD::Sybase
Steven,
I believe that you are misunderstanding what it is that you are
receiving. The only output from your commands is the print @c. This
comes through the error handler since a print statement is a level 0 or
level 10 message from the server and not a result set. Try changing the
print to a select and then you can receive it as a row result.
Regards,
Chuck
[EMAIL PROTECTED] wrote:
Hello,
I am using DBI v. 1.38, DBD::Sybase v. 1.01 (?), and FreeTDS 0.61.2 to
connect to an MSSQL 6.5 database from a Linux client and I am having
problems retrieving the values of out parameters to a stored procedure
call.
I followed the example in the DBD::Sybase docs modifying for my purpose
as follows:
#!/usr/bin/perl
use DBI;
use DBD::Sybase;
my $dbh = DBI-connect('dbi:Sybase:server=',
'sa', '', {PrintError = 1, AutoCommit = 1});
$dbh-do('use tempdb');
my $sql = q{
declare @a int
declare @b varchar(10)
exec foo @a output, @b output
declare @c varchar(255)
select @c = @b + ' : ' + convert(varchar(3), @a)
print @c
};
my $sth = $dbh-prepare($sql);
$sth-execute;
do {
while ($d = $sth-fetch) {
$rt = $sth-{'syb_result_type'};
print(Iterating: $rt\n);
if ($rt == DBD::Sybase::CS_PARAM_RESULT) {
print(found param result [EMAIL PROTECTED]);
$a = $$d[0];
$b = $$d[1];
} elsif ($rt == DBD::Sybase::CS_ROW_RESULT) {
print(found row result [EMAIL PROTECTED]);
} elsif ($rt == DBD::Sybase::CS_STATUS_RESULT) {
print(found status result [EMAIL PROTECTED]);
}
}
} while($sth-{'syb_more_results'});
print(Retrieved values $a $b\n);
And received the following output:
Iterating: 4043
found status result [0]
hello : 5
Retrieved values
As I understand the docs, the there should have been a CS_PARAM_RESULT
result set but there was not. As you see, I even printed out the
results from the sql to make sure the out params were being set. Am I
doing something wrong or is this a known limitation when using my
configuration. Perhaps I can use the Sybase Open Client libraries
instead?
I help will be greatly appreciated.
Thank you,
Steven
RE: Stored procedures and DBD::Sybase
Chuck,
Thanks for the response. Although, the DBD::Sybase docs do detail
retrieving sproc out params as I have done, using the select statement
to explicitly form a result is an interesting solution.
One additional question rises from the fact that DBD::Sybase can handle
returning many result sets and my real life procedure (not this test
one) actually does return many result sets. With this solution the only
way I can know that I am getting my procedure params is by placing my
select statement last and assuming that the last result row is the one I
am interested in. Is there a better way of doing this? Also, can I move
the client-side cursor to the end of the result set without having to
fetch all the intermediate results?
Thank you greatly,
Steven
-Original Message-
From: Chuck Fox [mailto:[EMAIL PROTECTED]
Sent: Monday, November 10, 2003 12:51 PM
To: Steven Velez
Cc: [EMAIL PROTECTED]
Subject: Re: Stored procedures and DBD::Sybase
Steven,
I believe that you are misunderstanding what it is that you are
receiving. The only output from your commands is the print @c. This
comes through the error handler since a print statement is a level 0 or
level 10 message from the server and not a result set. Try changing the
print to a select and then you can receive it as a row result.
Regards,
Chuck
[EMAIL PROTECTED] wrote:
Hello,
I am using DBI v. 1.38, DBD::Sybase v. 1.01 (?), and FreeTDS 0.61.2 to
connect to an MSSQL 6.5 database from a Linux client and I am having
problems retrieving the values of out parameters to a stored procedure
call.
I followed the example in the DBD::Sybase docs modifying for my purpose
as follows:
#!/usr/bin/perl
use DBI;
use DBD::Sybase;
my $dbh = DBI-connect('dbi:Sybase:server=',
'sa', '', {PrintError = 1, AutoCommit = 1});
$dbh-do('use tempdb');
my $sql = q{
declare @a int
declare @b varchar(10)
exec foo @a output, @b output
declare @c varchar(255)
select @c = @b + ' : ' + convert(varchar(3), @a)
print @c
};
my $sth = $dbh-prepare($sql);
$sth-execute;
do {
while ($d = $sth-fetch) {
$rt = $sth-{'syb_result_type'};
print(Iterating: $rt\n);
if ($rt == DBD::Sybase::CS_PARAM_RESULT) {
print(found param result [EMAIL PROTECTED]);
$a = $$d[0];
$b = $$d[1];
} elsif ($rt == DBD::Sybase::CS_ROW_RESULT) {
print(found row result [EMAIL PROTECTED]);
} elsif ($rt == DBD::Sybase::CS_STATUS_RESULT) {
print(found status result [EMAIL PROTECTED]);
}
}
} while($sth-{'syb_more_results'});
print(Retrieved values $a $b\n);
And received the following output:
Iterating: 4043
found status result [0]
hello : 5
Retrieved values
As I understand the docs, the there should have been a CS_PARAM_RESULT
result set but there was not. As you see, I even printed out the
results from the sql to make sure the out params were being set. Am I
doing something wrong or is this a known limitation when using my
configuration. Perhaps I can use the Sybase Open Client libraries
instead?
I help will be greatly appreciated.
Thank you,
Steven
Re: Stored procedures and DBD::Sybase
On Mon, 2003-11-10 at 06:30, Steven Velez wrote: Hello, I am using DBI v. 1.38, DBD::Sybase v. 1.01 (?), and FreeTDS 0.61.2 to connect to an MSSQL 6.5 database from a Linux client and I am having problems retrieving the values of out parameters to a stored procedure call. MS-SQL doesn't handle OUTPUT parameters the same way as Sybase at the TDS protocol level, so you won't be receiving any CS_STATUS_RESULT return data. If you change your print @c to select @c you will get your data. As for multiple result sets - they will be returned to you in the order that they are generated, but here you are limited by FreeTDS's capabilities, as well as the fact that MS-SQL 6.x still talks TDS 4.2, rather than version 5 (Sybase) or 7/8 (MS_SQL 7.x and 2000). So in short you're going to have to find some other method of identifying result sets - possibly by using a dummy column: select 'dummy'='status', 'data' = @c Michael -- Michael Peppler Data Migrations, Inc. [EMAIL PROTECTED] http://www.mbay.net/~mpeppler Sybase T-SQL/OpenClient/OpenServer/C/Perl developer available for short or long term contract positions - http://www.mbay.net/~mpeppler/resume.html
RE: Stored Procedures with DBD::Sybase and FreeTDS
();
}
### define the the required vars
my $sv = shift;
my $db = shift;
my $qs = shift;
### set up our optional username and password
my $user = shift || username;
my $passwd = shift || password ;
if ($passwd =~ /null/ ){
undef $passwd;
}
my $trace = shift;
## connect to the database
my $dbh = DBI-connect(DBI:Sybase:server=$sv;port=1433:database=$db,
$user, $passwd)||die $!;
if ( $trace ne '' ) {
$dbh-trace($trace);
}
#different $dbi call for inserts or updates
if ($qs =~ /^insert|^update|^delete/i){
$dbh-do ($qs) || die $dbh-errstr;
}else{
### deals with selects
my $sth = $dbh-prepare($qs)|| die $dbh-errstr;
# $dbh-do ($qs) || die $dbh-errstr;
$sth -execute() || die $dbh-errstr;
while (my @tmp = $sth-fetchrow_array()){
print join ,, @tmp;
print \n;
}
}
## disconnects from server
$dbh -disconnect;
Here is the stored procedure:
CREATE PROCEDURE InsertMe
@Text1 AS char(10),
@Text2 AS char(10)
AS
INSERT INTO test1 (
text1,
text2
)
VALUES (
@Text1,
@Text2
);
RETURN 0
GO
-Original Message-
From: Michael Peppler [mailto:[EMAIL PROTECTED]]
Sent: Friday, December 20, 2002 11:06 AM
To: [EMAIL PROTECTED]
Subject: Re: Stored Procedures with DBD::Sybase and FreeTDS
On Thu, 2002-12-19 at 13:35, [EMAIL PROTECTED] wrote:
Has anyone had a problem calling to a stored procedure that performs an
insert and returns no data ?
I am currently having that and the error that I am getting is:
dbih_setup_fbav: invalid number of fields: 0, NUM_OF_FIELDS attribute
probably not set right at
/usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi/DBD/Sybase.pm line
445.
The sql statement works from the MSSql client and if I call a stored
procedure that returns data it works.
Try asking on the FreeTDS list.
Alternatively send me (but not the list) a trace (DBI-trace(3)) from
running your script.
Thanks
Michael
--
Michael Peppler Data Migrations, Inc.
[EMAIL PROTECTED] http://www.mbay.net/~mpeppler
Sybase T-SQL/OpenClient/OpenServer/C/Perl developer available for short or
long term contract positions - http://www.mbay.net/~mpeppler/resume.html
Stored Procedures with DBD::Sybase and FreeTDS
Has anyone had a problem calling to a stored procedure that performs an insert and returns no data ? I am currently having that and the error that I am getting is: dbih_setup_fbav: invalid number of fields: 0, NUM_OF_FIELDS attribute probably not set right at /usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi/DBD/Sybase.pm line 445. The sql statement works from the MSSql client and if I call a stored procedure that returns data it works. TIA
Re: Stored Procedures with DBD::Sybase and FreeTDS
On Thu, 2002-12-19 at 13:35, [EMAIL PROTECTED] wrote: Has anyone had a problem calling to a stored procedure that performs an insert and returns no data ? I am currently having that and the error that I am getting is: dbih_setup_fbav: invalid number of fields: 0, NUM_OF_FIELDS attribute probably not set right at /usr/lib/perl5/site_perl/5.8.0/i386-linux-thread-multi/DBD/Sybase.pm line 445. The sql statement works from the MSSql client and if I call a stored procedure that returns data it works. Try asking on the FreeTDS list. Alternatively send me (but not the list) a trace (DBI-trace(3)) from running your script. Thanks Michael -- Michael Peppler Data Migrations, Inc. [EMAIL PROTECTED] http://www.mbay.net/~mpeppler Sybase T-SQL/OpenClient/OpenServer/C/Perl developer available for short or long term contract positions - http://www.mbay.net/~mpeppler/resume.html signature.asc Description: This is a digitally signed message part
