DBD::SQLite

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

DBD::SQLite

cscetbon
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 ?
Reply | Threaded
Open this post in threaded view
|

RE: DBD::SQLite

Chris Werner
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:[hidden email]]
Sent: Thursday, March 16, 2006 2:56 PM
To: [hidden email]
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 ?
Reply | Threaded
Open this post in threaded view
|

Re: DBD::SQLite

cscetbon
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:[hidden email]]
> Sent: Thursday, March 16, 2006 2:56 PM
> To: [hidden email]
> 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 ?
>
>  
Reply | Threaded
Open this post in threaded view
|

RE: DBD::SQLite

Chris Werner
In reply to this post by cscetbon
Can you run .schema on the table?

-----Original Message-----
From: Cyril Scetbon [mailto:[hidden email]]
Sent: Friday, March 17, 2006 1:16 AM
To: [hidden email]
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:[hidden email]]
> Sent: Thursday, March 16, 2006 2:56 PM
> To: [hidden email]
> 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 ?
>
>  
Reply | Threaded
Open this post in threaded view
|

Re: DBD::SQLite

cscetbon
.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:[hidden email]]
> Sent: Friday, March 17, 2006 1:16 AM
> To: [hidden email]
> 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:[hidden email]]
>> Sent: Thursday, March 16, 2006 2:56 PM
>> To: [hidden email]
>> 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 ?
>>
>>  
>>    
>
>  
Reply | Threaded
Open this post in threaded view
|

Re: DBD::SQLite

Clark Christensen
In reply to this post by cscetbon
Maybe you declared the column as numeric (integer, number)?  In that case, later versions of SQLite might be doing the conversion for you (I can't remember for sure, but I seem to recall this is the case).  You might need to declare the column as text.
 
 At least what you posted is missing the closing paren for $dbh->do();  But that would throw a perl syntax error, so assuming your syntax is correct, you might try it like this:

$dbh->do("insert into mytable(varint) values (?)", undef, '01234');

With the vagaries of quoting, I try to use placeholders wherever possible.

 -Clark


----- Original Message ----
From: Cyril Scetbon <[hidden email]>
To: [hidden email]
Sent: Thursday, March 16, 2006 11:15:30 PM
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:[hidden email]]
> Sent: Thursday, March 16, 2006 2:56 PM
> To: [hidden email]
> 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 ?
>
>  



Reply | Threaded
Open this post in threaded view
|

RE: DBD::SQLite

Chris Werner
In reply to this post by cscetbon
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 "$@\n$sql\n";
    exit(1);
}

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

eval { $sth->execute(); };
if ($@) {
    print STDERR "$@\n$sql\n";
    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:[hidden email]]
Sent: Friday, March 17, 2006 11:04 AM
To: [hidden email]
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:[hidden email]]
> Sent: Friday, March 17, 2006 1:16 AM
> To: [hidden email]
> 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:[hidden email]]
>> Sent: Thursday, March 16, 2006 2:56 PM
>> To: [hidden email]
>> 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 ?
>>
>>  
>>    
>
>  
Reply | Threaded
Open this post in threaded view
|

Re: DBD::SQLite

cscetbon
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 "$@\n$sql\n";
    exit(1);
}

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

eval { $sth->execute(); };
if ($@) {
    print STDERR "$@\n$sql\n";
    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 "$@\n$sql\n";
>     exit(1);
> }
>
> $sql = "SELECT varint FROM mytable";
> $sth = $dbh->prepare($sql);
>
> eval { $sth->execute(); };
> if ($@) {
>     print STDERR "$@\n$sql\n";
>     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:[hidden email]]
> Sent: Friday, March 17, 2006 11:04 AM
> To: [hidden email]
> 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:[hidden email]]
>> Sent: Friday, March 17, 2006 1:16 AM
>> To: [hidden email]
>> 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:[hidden email]]
>>> Sent: Thursday, March 16, 2006 2:56 PM
>>> To: [hidden email]
>>> 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 ?
>>>
>>>  
>>>    
>>>      
>>  
>>    
>
>