Re: [sqlite] preventing text to integer conversion of bind variables in perl
Please verify that they have implemented a 'natural sort' vs. a 'lexical sort' Tezo. Original Message Follows From: Darren Duncan <[EMAIL PROTECTED]> Reply-To: sqlite-users@sqlite.org To: sqlite-users@sqlite.org Subject: Re: [sqlite] preventing text to integer conversion of bind variables in perl Date: Wed, 15 Jun 2005 18:12:26 -0700 At 12:55 AM + 6/16/05, Mr. Tezozomoc wrote: sqlite is typeless I have addressed this issue in the following HOWTO: http://www.mail-archive.com/sqlite-users@sqlite.org/msg03205.html Please refer to it. Tezozomoc. I think not. From your own HOWTO: Assumption... this is based on SQLITE 2.8.6... forgive the aging.. I believe that this is out of date and not applicable. The SQLite 3.x line is not typeless and has distinct numerical and text and binary data types. Therefore it should be able to take columns declared as numbers and sort them as such. -- Darren Duncan
Re: [sqlite] preventing text to integer conversion of bind variables in perl
At 12:55 AM + 6/16/05, Mr. Tezozomoc wrote: sqlite is typeless I have addressed this issue in the following HOWTO: http://www.mail-archive.com/sqlite-users@sqlite.org/msg03205.html Please refer to it. Tezozomoc. I think not. From your own HOWTO: Assumption... this is based on SQLITE 2.8.6... forgive the aging.. I believe that this is out of date and not applicable. The SQLite 3.x line is not typeless and has distinct numerical and text and binary data types. Therefore it should be able to take columns declared as numbers and sort them as such. -- Darren Duncan
Re: [sqlite] preventing text to integer conversion of bind variables in perl
sqlite is typeless
I have addressed this issue in the following HOWTO:
http://www.mail-archive.com/sqlite-users@sqlite.org/msg03205.html
Please refer to it.
Tezozomoc.
Original Message Follows
From: Darren Duncan <[EMAIL PROTECTED]>
Reply-To: sqlite-users@sqlite.org
To: sqlite-users@sqlite.org, Matt Sergeant <[EMAIL PROTECTED]>
Subject: Re: [sqlite] preventing text to integer conversion of bind
variables in perl
Date: Wed, 15 Jun 2005 17:45:44 -0700
At 11:39 AM -0400 6/15/05, Matt Sergeant wrote:
I added it because of another bug report that was incorrectly sorting
integer columns based on text sort order. For example if you inserted:
("k1", 8);
("k2", 9);
("k3", 10);
("k4", 11);
and then asked for: SELECT * FROM t ORDER BY Column2
you get back:
k3, 10
k4, 11
k1, 8
k2, 9
Which seems obviously incorrect.
In answer to your question though, yes you can remove that bit of code, as
long as you're aware of the above side effect.
Well, if Column2 is explicitly defined as an integer, then it should always
sort as an integer, and making that work is the responsibility of SQLite
itself.
I see that preserving the input data in all situations where the declared
column type can handle it is of the utmost importance, and DBD::SQLite
should do this.
So I vote to remove any de-stringification code you have in DBD::SQLite.
To be honest I'm not entirely sure what the correct fix is - maybe ignore
the above bug and tell the requestor he has to: SELECT * FROM t ORDER BY
int(Column2)
Yes, go ahead and do that. Alternately, tell the person to use SQLite in
strict mode so that it only ever stores ints in int columns. Like most
databases do.
Matt, I would also appreciate it if a new DBD::SQLite was released asap that
embeds and is known to work well with the 3.2 series, specifically 3.2.2.
Thank you in advance.
-- Darren Duncan
Re: [sqlite] preventing text to integer conversion of bind variables in perl
At 11:39 AM -0400 6/15/05, Matt Sergeant wrote:
I added it because of another bug report that was incorrectly
sorting integer columns based on text sort order. For example if you
inserted:
("k1", 8);
("k2", 9);
("k3", 10);
("k4", 11);
and then asked for: SELECT * FROM t ORDER BY Column2
you get back:
k3, 10
k4, 11
k1, 8
k2, 9
Which seems obviously incorrect.
In answer to your question though, yes you can remove that bit of
code, as long as you're aware of the above side effect.
Well, if Column2 is explicitly defined as an integer, then it should
always sort as an integer, and making that work is the responsibility
of SQLite itself.
I see that preserving the input data in all situations where the
declared column type can handle it is of the utmost importance, and
DBD::SQLite should do this.
So I vote to remove any de-stringification code you have in DBD::SQLite.
To be honest I'm not entirely sure what the correct fix is - maybe
ignore the above bug and tell the requestor he has to: SELECT * FROM
t ORDER BY int(Column2)
Yes, go ahead and do that. Alternately, tell the person to use
SQLite in strict mode so that it only ever stores ints in int
columns. Like most databases do.
Matt, I would also appreciate it if a new DBD::SQLite was released
asap that embeds and is known to work well with the 3.2 series,
specifically 3.2.2.
Thank you in advance.
-- Darren Duncan
Re: [sqlite] preventing text to integer conversion of bind variables in perl
On 15 Jun 2005, at 17:02, Jonathan H N Chin wrote: So perhaps the check no longer performs a useful function now that sqlite allows one to specify the data type of the column? Perhaps indeed. I think I'll remove it from the next release. Matt. __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __
Re: [sqlite] preventing text to integer conversion of bind variables in perl
Matt Sergeant wrote: >I added it because of another bug report that was incorrectly sorting >integer columns based on text sort order. [...] >To be honest I'm not entirely sure what the correct fix is - maybe >ignore the above bug and tell the requestor he has to: SELECT * FROM t >ORDER BY int(Column2) My copy of sqlite3 doesn't like "int(...)" in that context. However, if I patch my copy of DBD::SQLite to removes the looks_like_number() check and then run: sqlite3 test 'create table t ( k text unique, v int);' perl -e 'use DBI; $db = DBI->connect( "dbi:SQLite:dbname=test" ); $db->do( qq[REPLACE INTO t VALUES(?,?);], undef, "k1", "9"); $db->do( qq[REPLACE INTO t VALUES(?,?);], undef, "k2", "8"); $db->do( qq[REPLACE INTO t VALUES(?,?);], undef, "k3", "11"); $db->do( qq[REPLACE INTO t VALUES(?,?);], undef, "k4", "10"); ' sqlite3 test 'select * from t;' sqlite3 test 'select * from t order by v;' rm test sqlite3 test 'create table t ( k text unique, v int);' perl -e 'use DBI; $db = DBI->connect( "dbi:SQLite:dbname=test" ); $db->do( qq[REPLACE INTO t VALUES(?,?);], undef, "k1", "9"); $db->do( qq[REPLACE INTO t VALUES(?,?);], undef, "k2", "8"); $db->do( qq[REPLACE INTO t VALUES(?,?);], undef, "k3", "11"); $db->do( qq[REPLACE INTO t VALUES(?,?);], undef, "k4", "10"); ' sqlite3 test 'select * from t;' sqlite3 test 'select * from t order by v;' then I get: k1|9 k2|8 k3|11 k4|10 k4|10 k3|11 k2|8 k1|9 k1|9 k2|8 k3|11 k4|10 k2|8 k1|9 k4|10 k3|11 So perhaps the check no longer performs a useful function now that sqlite allows one to specify the data type of the column? -jonathan -- Jonathan H N Chin, 2 dan | deputy computer | Newton Institute, Cambridge, UK <[EMAIL PROTECTED]> | systems mangler | tel/fax: +44 1223 767091/330508 "respondeo etsi mutabor" --Rosenstock-Huessy
Re: [sqlite] preventing text to integer conversion of bind variables in perl
On 15 Jun 2005, at 11:56, Jonathan H N Chin wrote:
I would be interested to know what version of DBD::SQLite Puneet Kishor
is using, since I believe I have tracked the issue to a test in
the sqlite_st_execute() function in dbdimp.c :
else if (looks_like_number(value)) {
/* bind ordinary numbers as numbers - otherwise we might sort
wrong */
retval = sqlite3_bind_double(imp_sth->stmt, i+1, SvNV(value));
}
This test appears in all the versions of DBD::SQLite that I can find
and appears to be what causes the text to be treated as a number.
Does this check actually perform any useful function (as per the
comment)
or will it be safe to delete it?
I added it because of another bug report that was incorrectly sorting
integer columns based on text sort order. For example if you inserted:
("k1", 8);
("k2", 9);
("k3", 10);
("k4", 11);
and then asked for: SELECT * FROM t ORDER BY Column2
you get back:
k3, 10
k4, 11
k1, 8
k2, 9
Which seems obviously incorrect.
To be honest I'm not entirely sure what the correct fix is - maybe
ignore the above bug and tell the requestor he has to: SELECT * FROM t
ORDER BY int(Column2)
In answer to your question though, yes you can remove that bit of code,
as long as you're aware of the above side effect.
Matt.
__
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
__
Re: [sqlite] preventing text to integer conversion of bind variables in perl
On Jun 15, 2005, at 11:56 AM, Jonathan H N Chin wrote: Thanks for the suggestions. DRH's workaround, while it would work, seems very ugly. I would be interested to know what version of DBD::SQLite Puneet Kishor is using, since I believe I have tracked the issue to a test in the sqlite_st_execute() function in dbdimp.c : 1.08, methinks, the latest one on CPAN. It supports sqlite3. And DBI 1.48. Hope this helps. -- Puneet Kishor
Re: [sqlite] preventing text to integer conversion of bind variables in perl
Thanks for the suggestions.
DRH's workaround, while it would work, seems very ugly.
I would be interested to know what version of DBD::SQLite Puneet Kishor
is using, since I believe I have tracked the issue to a test in
the sqlite_st_execute() function in dbdimp.c :
else if (looks_like_number(value)) {
/* bind ordinary numbers as numbers - otherwise we might sort wrong */
retval = sqlite3_bind_double(imp_sth->stmt, i+1, SvNV(value));
}
This test appears in all the versions of DBD::SQLite that I can find
and appears to be what causes the text to be treated as a number.
Does this check actually perform any useful function (as per the comment)
or will it be safe to delete it?
Another workaround would be for the "do" of Perl's DBI:
$rv = $dbh->do($statement, \%attr, @bind_values);
to be exteneded in some way to allow bind_types to be passed in.
One could use a prepare, bind_param, execute sequence, but that would
seem to defeat the point of having the do() shortcut in the first place.
-jonathan
--
Jonathan H N Chin, 2 dan | deputy computer | Newton Institute, Cambridge, UK
<[EMAIL PROTECTED]> | systems mangler | tel/fax: +44 1223 767091/330508
"respondeo etsi mutabor" --Rosenstock-Huessy
Re: [sqlite] preventing text to integer conversion of bind variables in perl
At 3:34 PM -0400 6/14/05, D. Richard Hipp wrote: On Tue, 2005-06-14 at 20:18 +, [EMAIL PROTECTED] wrote: I have textual data that may look like integers (eg. "0325763213"). On insertion, any leading "0" will vanish. How do I prevent this and make the data be inserted verbatim? Simple illustration: sqlite3 test 'create table t ( k text unique, v text);' perl -e 'use DBI; $db = DBI->connect( "dbi:SQLite:dbname=test" ); $db->do( qq[REPLACE INTO t VALUES(?,?);], undef, "key", "0325763213");' sqlite3 test 'select * from t;' returns: > key|325763213 It looks like perl is making this conversion for you. SQLite does not do this. As a work-around, consider prepending a single 'x' character to every "v" column entry then strip of the 'x' before you use it. Actually, Perl itself wouldn't be doing that. Perl only converts a string to a number when it is used in a numerical context; eg, '$bar = $foo + 0'; otherwise it continues representing it as a string. Since the inserted value was string quoted when it was defined, it started out as a string. I suspect that it is the DBD::SQLite module, or the DBI module, that is the problem. As I recall, DBD::SQLite was never updated to use the prepared statements feature added to SQLite 3 and continues to emulate that feature which DBI defines (as it did for SQLite 2). It does this by substituting the values into the raw SQL and executing that as a SQL string without variables. Moreover, I think this functionality will examine the variable, and if it looks like a number, will insert it into the SQL as a number rather than a character string, hence the loss of the zero. In that case, neither SQLite nor the Perl core is at fault, but the intermediary between them, and hence the best solution is to fix that so it at least always string-quotes (or ask Matt to do it). I ruled out SQLite because you were using version 3 and explicitly defined the field as a character string. Meanwhile, you could follow the the workaround that DRH mentioned. -- Darren Duncan
Re: [sqlite] preventing text to integer conversion of bind variables in perl
[EMAIL PROTECTED] wrote:
I have textual data that may look like integers (eg. "0325763213").
On insertion, any leading "0" will vanish. How do I prevent this
and make the data be inserted verbatim?
Simple illustration:
sqlite3 test 'create table t ( k text unique, v text);'
perl -e 'use DBI; $db = DBI->connect( "dbi:SQLite:dbname=test" );
$db->do( qq[REPLACE INTO t VALUES(?,?);], undef, "key", "0325763213");'
sqlite3 test 'select * from t;'
returns:
key|325763213
I am using the sqlite3_3.2.1-1 and libdbd-sqlite3-perl_1.08-1 packages
from Debian, in case it matters.
Perhaps it matters, because I don't get the results you get...
D:\testers>sqlite3 test
SQLite version 3.2.1
Enter ".help" for instructions
sqlite> create table t (k text unique, v text);
sqlite> .q
---test.pl
#!perl -w
use DBI;
$db = DBI->connect("dbi:SQLite:dbname=test");
$db->do(qq[REPLACE INTO t VALUES (?, ?);], undef, "key", "0325763213");
---
D:\testers>test.pl
D:\testers>sqlite3 test
SQLite version 3.2.1
Enter ".help" for instructions
sqlite> select * from t;
key|0325763213
sqlite>
I am using the latest DBI and DBD::SQLite
