[freetds] There is a problem passing parameters

Philip philip_38 at hotmail.com
Mon Jun 13 15:41:18 EDT 2005


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; 




More information about the FreeTDS mailing list