Skip to Content.
Sympa Menu

freetds - [freetds] There is a problem passing parameters

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "Philip" <philip_38 AT hotmail.com>
  • To: <freetds AT lists.ibiblio.org>
  • Subject: [freetds] There is a problem passing parameters
  • Date: Mon, 13 Jun 2005 15:41:18 -0400

The parameter-passing is not working in my DBD-Sybase perl package using
freetds. I use the latest Freetds. I don't think Freetds has anything to do
with it, because I can successfully execute this stored procedure below from
tsql and get the correct results. It has to do with DBD-Sybase. I am sing
Red Hat Enterprise Linux 3.0, updated. I tried with the latest DBD-Sybase
and also with DBD-Sybase-1.02.6. In this case I expect three types of data:
selec from rows, selects like 'Total'=@variable and parameters passed. The
later don't work. If Myke Peppler reads this and wishes to take a look,
please contact me.
Philip


CREATE PROCEDURE dbo.sp_GetBooksByPrice
@minPrice money,
@maxPrice money,
@lowestPricedBook varchar(100) OUTPUT,
@highestPricedBook varchar(100) OUTPUT

AS
BEGIN
DECLARE @realminPrice money, @realmaxPrice money, @totalBooks int
SELECT top 1 * FROM pubs..titles WHERE price >=@minPrice AND price
<=@maxPrice
SELECT @realminPrice = min(price) FROM pubs..titles WHERE price >=@minPrice
SELECT @realmaxPrice = max(price) FROM pubs..titles WHERE price <=@maxPrice
SELECT @lowestPricedBook =title FROM pubs..titles WHERE price =
@realminPrice
SELECT @highestPricedBook =title FROM pubs..titles WHERE price =
@realmaxPrice
SELECT @totalBooks = COUNT(title) FROM pubs..titles WHERE price >=
@minPrice AND price <= @maxPrice
select 'Total'=@totalbooks
RETURN @totalBooks
END


My perl test is:

#!/usr/bin/perl

use strict;
use DBI;
my $server = "server:1433";
my $db = "databse";
my $username = "username";
my $password = "passwd";

my $dbh = DBI->connect("dbi:Sybase:$server", $username,$password);
$dbh->do("use $db");
my $query = "declare \@minPriceBook varchar(100), \@maxPriceBook
varchar(100)
exec sp_GetBooksByPrice \@minPrice =2.00 , \@maxPrice = 20.00,
\@lowestPricedBook = \@minPriceBook OUTPUT, \@highestPricedBook =
\@maxPriceBook OUTPUT";
my $sth = $dbh->prepare($query);
$sth->execute();
do {
while(my $d = $sth->fetchrow_arrayref) {
if ($sth->{syb_result_type}==4040){
print join("\t", @$d),"\n";
}
if ($sth->{syb_result_type}==4042){
print "The lowest price book is: ", $d->[0], "\n";
print "The highest price book is: ", $d->[1], "\n";
}
if ($sth->{syb_result_type}==4043){
print "There are total: ", $d->[0], " books returned\n";
}
}
} while($sth->{syb_more_results});
$sth=undef;
$dbh->disconnect;





Archive powered by MHonArc 2.6.24.

Top of Page