Bind_param not working with negative and double values

Hi,

I am using DBI version 1.48 and DBD::mysql 3.0002 to run a very simple 
script (the original one is much bigger, this is just to show the 
problem), and I get an error when trying to select data, passing a 
negative argument to the bind_param call. Using the trace I get the 
following messages:

   -> bind_param for DBD::mysql::st 
(DBI::st=HASH(0x140130a90)~0x140130a80 2 '-1' -6)
    <- bind_param= 1 at ./test_dbi.pl line 23

but when trying to execute the query, the -1 it is not there:

Binding parameters: SELECT * from transcript where gene_id = 95536 and 
seq_region_strand =

However, if I change the SQL_TYPE from SQL_INTEGER or SQL_TINYINT to 
SQL_CHAR, the script works fine. Had anyone seen a similar behaviour ??

And if I run the same script in a much older version of DBI (1.21) and 
DBD::mysql (2.0419), the script works fine with the SQL_TINYINT as the 
type for the negative number. There is a similar problem when the number 
is a double/real: it only stores the integer part of it

One solution would be using the SQL_CHAR type for negative and real 
numbers, but apart from being not really elegant, I think I might run 
into problems when I use a different database (like Oracle).

How can I fix this problem ?

 Thanks for your help,

   Daniel.

-- 
------------------------------------------------
Daniel Rios           Phone: +44 (0) 1223 494684
Ensembl developer     Fax:   +44 (0) 1223 494468
<[email protected]>
EMBL-EBI
Wellcome Trust Genome Campus, Hinxton
Cambridge CB10 1SD UK
------------------------------------------------


-- 
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe:    http://lists.mysql.com/[email protected]

#!/usr/local/ensembl/bin/perl
use warnings;
use strict;

use DBI;
use DBI qw(:sql_types);
use Data::Dumper;

my $db = 'homo_sapiens_core_34_35g';
my $host = 'ensembldb.ensembl.org';
my $port = 3306;
my $username = 'anonymous';
my $password = '';
my $driver = 'mysql';
my $dsn = "DBI:$driver:database=$db;host=$host;port=$port;mysql_local_infile=1";

my $dbh = DBI->connect($dsn,$username, $password,{'RaiseError' =>1});

my $gene_id = 95536;
my $strand = -1;
my $sth = $dbh->prepare("SELECT * from transcript where gene_id = ? and seq_region_strand = ?");

$sth->bind_param(1,$gene_id,SQL_INTEGER);
$sth->bind_param(2,$strand,SQL_TINYINT);

$sth->execute();
print Dumper($sth->dump_results),"\n";