bind_param not working with negative 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.

One solution would be using the SQL_CHAR type for negative 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
<dani@xxxxxxxxx>
EMBL-EBI
Wellcome Trust Genome Campus, Hinxton
Cambridge CB10 1SD UK
------------------------------------------------

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

Relevant Pages

  • Re: DBD::CSV - UPDATE corrupts data!
    ... DBI 1.607, DBD::CSV 0.20, both runs deliver the same result from your first run. ... This are remnants from the shortening of the original program to generate a minimal test script. ... will try UPDATE Projects ...
    (perl.dbi.users)
  • Re: DBD::CSV - UPDATE corrupts data!
    ... I stumbled over somthing very strange: When I try to update data in a table, the input parameters are going into the right fields - exept the first data row in the table / file. ... Below is a script which demonstrate the thing. ... Running the code below copied and pasted on Linux 2.6.26.5, Perl 5.8.8, DBI 1.607, DBD::CSV 0.20, both runs deliver the same result from your first run. ...
    (perl.dbi.users)
  • Re: DBD::Oracle problem with bind_param_inout and UTF-8
    ... my $uidpwd = 'usr/pwd@db'; ... Using DBI 1.605 and DBD::Oracle 1.23 ... Sym = € ... Consider the following script ... ...
    (perl.dbi.users)
  • Re: Cant ctrl-c to exit with DBI, DBD::Oracle
    ... I'm running into a strange problem with a script that uses DBI and ... Once my script connects to my database, ... Otherwise the database handle seems to work fine. ... use DBI; ...
    (perl.dbi.users)
  • Re: Apache, Oracle, Perl DBI Problems
    ... I believe at the start of every perl cgi script interfacing ... 'perl dbi oracle'. ...
    (perl.dbi.users)