At 3:34 PM -0400 6/14/05, D. Richard Hipp wrote:
On Tue, 2005-06-14 at 20:18 +0000, [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
