Bind_param not working with negative and double values

--------------000402070002040503020306
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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

EMBL-EBI
Wellcome Trust Genome Campus, Hinxton
Cambridge CB10 1SD UK
------------------------------------------------


--------------000402070002040503020306
Content-Type: text/plain;
name="test_dbi.pl"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
filename="test_dbi.pl"

#!/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";


--------------000402070002040503020306
Content-Type: text/plain; charset=us-ascii


--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org
--------------000402070002040503020306--