Consider this piece of code:
<?php
$db = new SQLiteDatabase("./crm.db", 0666, &$error)
or die("Failed: $error");
$create_query = "
CREATE TABLE document (
id INTEGER PRIMARY KEY,
title,
intro,
body
);
REATE TABLE dictionary (
id INTEGER PRIMARY KEY,
word
);
CREATE TABLE lookup (
document_id INTEGER,
word_id INTEGER,
position INTEGER
);
REATE UNIQUE INDEX word ON dictionary(word);
";
$ret = $db->query($create_query);
if ($db->lastError() != SQLITE_OK) {
echo $db->lastError();
}
?>
As you can see there are two typoes (REATE instead of CREATE) so
obviously I expected to get a result value other than SQLITE_OK back,
but that didn't happen, and no php warnings either. Then I fixed those
two typoes, ran the script and to my surprise it only showed the first
table in the database. Making a typo in the first create table statement
did throw a warning (unexpected when $ret = is used) and the return
value was set to SQLITE_ERROR.
After some investigation I found that if I removed the "$ret = " before
the query() call, then all queries are run just fine, and throw also a
warning when I put back in the typoes.
I would have expected something else though:
- Without the "$ret = " it should throw a PHP warning for each broken
query. (That's current what it's doing). - With the "$ret = " it should NOT throw any warnings, and set the
$db->lastError() value accordingly whether there were errors or not.
In the code I do see that there is a check for if the return value is
used, so this behavior is possible and definitely wanted IMO. The
current behavior is just wrong and uninituitive.
regards,
Derick
After some investigation I found that if I removed the "$ret = " before
the query() call, then all queries are run just fine, and throw also a
warning when I put back in the typoes.
I did some more investigations and it seems that as soon you try to
assign the result of sqlite_query() to a variable it always only execute
the first query in a multi-query query-string. Not only for CREATE as I
wrote before but it's also a problem for multiple INSERTs, or a mixed
INSERT/SELECT. In the code it's the difference between sqlite_compile
(sqlite.c:1472) (used when there is no assignment) and sqlite_exec
(sqlite.c:1559) when there is an assignment.
A slightly modified "expected" list:
I would have expected something else though:
- Without the "$ret = " it should throw a PHP warning for each broken
query. (That's current what it's doing).- With the "$ret = " it should NOT throw any warnings, and set the
$db->lastError() value accordingly whether there were errors
(with the last query) or not.
IMO this should be fixed ASAP, I can perhaps try to come up with a patch
myself too.
regards,
Derick
Stas already came up with a solution for the non-oo api; it probably
just needs porting to the oo interface.
--Wez.
After some investigation I found that if I removed the "$ret = " before
the query() call, then all queries are run just fine, and throw also a
warning when I put back in the typoes.I did some more investigations and it seems that as soon you try to
assign the result of sqlite_query() to a variable it always only execute
the first query in a multi-query query-string. Not only for CREATE as I
wrote before but it's also a problem for multiple INSERTs, or a mixed
INSERT/SELECT. In the code it's the difference between sqlite_compile
(sqlite.c:1472) (used when there is no assignment) and sqlite_exec
(sqlite.c:1559) when there is an assignment.A slightly modified "expected" list:
I would have expected something else though:
- Without the "$ret = " it should throw a PHP warning for each broken
query. (That's current what it's doing).- With the "$ret = " it should NOT throw any warnings, and set the
$db->lastError() value accordingly whether there were errors
(with the last query) or not.IMO this should be fixed ASAP, I can perhaps try to come up with a patch
myself too.regards,
Derick
Stas already came up with a solution for the non-oo api; it probably
just needs porting to the oo interface.
It already is it seams, the execQuery() method. Though I still don't
like the way how things are working :)
regards,
Derick