| Subject: | DBD::SQLite ignores parameter bindings |
Hello,
trying to insert some string containing numbers only into a sqlite3 DB via DBD::SQLite does always insert an integer. This appears to be a problem if the string starts with zeros.
This is what happens with sqlite3 directly (and how it would be okay in the more or less typeless sense of sqlite):
Show quoted text
sqlite> CREATE TABLE foo(a varchar(4), x varchar(4), y varchar(4));
sqlite> INSERT INTO foo(a, x, y) VALUES('010A', '010A', '010A');
sqlite> INSERT INTO foo(a, x, y) VALUES('0107', '0107', '0107');
sqlite> INSERT INTO foo(a, x, y) VALUES(0107, 0107, 0107);
sqlite> SELECT * FROM foo;
010A|010A|010A
0107|0107|0107
107|107|107
When I try to achieve the same with DBD:SQLite as in the following script fragment...
unlink "te.db";
$database = DBI->connect("dbi:SQLite:dbname=te.db", "", "") or die;
$database->do("CREATE TABLE foo(a varchar(4), x varchar(4), y varchar(4))") or die;
$i = $database->prepare("INSERT INTO foo(a, x, y) VALUES(?, ?, ?)") or die;
$i->execute('010A', '010A', '010A');
$i->execute('0107', '0107', '0107');
$i->bind_param(1, '0107', { TYPE => SQL_VARCHAR });
$i->bind_param(2, '0107', { TYPE => SQL_VARCHAR });
$i->bind_param(3, '0107', { TYPE => SQL_VARCHAR });
$i->execute;
$i->execute(0107, 0107, 0107);
$database->disconnect();
... the result is different:
Show quoted textsqlite> SELECT * FROM foo;
010A|010A|010A
107|107|107
107|107|107
71|71|71
The last line is okay, since this is due to perls octal-conversion. However, at least one of the both lines in the middle should contain 0107 because of the explicite bind_param() (in fact, IMHO both lines should contain 0107 because of the implicite bind_param() that should have been done with the first execute()).
regards
Mario