Re: [sqlite] DBD::SQLite

[Cyril Scetbon]

I've just add an unlink of the db in the script to test it and found 
that the code does not work correctly with DBD::SQLite version 1.08 (on 
my host)  but works with version 1.11 (on another host).

So I'll upgrade my host version. Thanks a lot.
use strict;
use DBI qw(:sql_types);
my $db='/tmp/stest.db';
my $sql;
my $sth;
my $dbh = DBI->connect(
   "dbi:SQLite:dbname=$db",
   {
       RaiseError => 1,
       AutoCommit => 1
   }
);
$dbh->do("CREATE TABLE mytable(varint text);");
my $stmt = $dbh->prepare("insert into mytable(varint) values(?)");
$stmt->bind_param( 1, '01237', { TYPE => SQL_VARCHAR } );
eval { $stmt->execute(); };
if ($@) {
   print STDERR "[EMAIL PROTECTED]";
   exit(1);
}

$sql = "SELECT varint FROM mytable";
$sth = $dbh->prepare($sql);

eval { $sth->execute(); };
if ($@) {
   print STDERR "[EMAIL PROTECTED]";
   exit(1);
}
my $ary_ref = $sth->fetchrow_arrayref;
print STDERR "RETURN IS $ary_ref->[0] \n";
unlink $db;

__END__


Chris Werner a écrit :
I cannot reproduce the problem. Your [slightly modified] code and output
follows:  {you did "use strict" of course...}

#!/opt/web/bin/perl -w

use strict;
use DBI qw(:sql_types);
my $sql;
my $sth;
my $dbh = DBI->connect(
    "dbi:SQLite:dbname=/tmp/stest.db",
    {
        RaiseError => 1,
        AutoCommit => 1
    }
);
$dbh->do("CREATE TABLE mytable(varint text);");
my $stmt = $dbh->prepare("insert into mytable(varint) values(?)");
$stmt->bind_param( 1, '01237', { TYPE => SQL_VARCHAR } );
eval { $stmt->execute(); };
if ($@) {
    print STDERR "[EMAIL PROTECTED]";
    exit(1);
}

$sql = "SELECT varint FROM mytable";
$sth = $dbh->prepare($sql);

eval { $sth->execute(); };
if ($@) {
    print STDERR "[EMAIL PROTECTED]";
    exit(1);
}
my $ary_ref = $sth->fetchrow_arrayref;
print STDERR "RETURN IS $ary_ref->[0] \n";

__END__
bash-3.00$ rm /tmp/stest.db
bash-3.00$ ./tst.pl 
RETURN IS 01237 

-----Original Message-----
From: Cyril Scetbon [mailto:[EMAIL PROTECTED]
Sent: Friday, March 17, 2006 11:04 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] DBD::SQLite


.schema

CREATE TABLE mytable(varint text);

Sorry, but it's working with the do function.
However it's not working when I use bind variables even if I force the 
SQL_VARCHAR type :

use DBI;

$dbh= DBI->connect("dbi:SQLite:dbname=./test.db",{ RaiseError => 1, 
AutoCommit => 1 });
$stmt=$dbh->prepare("insert into mytable(varint) values(?)");
$stmt->bind_param(1, '01237', { TYPE => SQL_VARCHAR });
#$stmt->execute('01238');
$stmt->execute();


Chris Werner a écrit :
  
Can you run .schema on the table?

-----Original Message-----
From: Cyril Scetbon [mailto:[EMAIL PROTECTED]
Sent: Friday, March 17, 2006 1:16 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] DBD::SQLite


just a $dbh->do("insert into mytable(varint) values ('01234')";

It's not working correctly with SQLite but no problem with Oracle.

Chris Werner a écrit :
  
    
Can you give a code example? I have just tried, and can load string
      
values
  
with a leading 0 and m/^\d+$/

I suspect the problem is in your treatment of perl...

Christian Werner

-----Original Message-----
From: Cyril Scetbon [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 16, 2006 2:56 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] DBD::SQLite


Hi,

I use DBD::SQLite for accessing a SQLite database, but there's an issue 
when I tyr to insert a number starting with a 0. In fact, DBD::SQLite 
seems to trim the starting 0 of the number. So, when I insert 0234 in a 
table I  find 234 instead.

Anyone has encoutered and resolved this bug ?