# SQLite weirdness

- Thread: https://externals.io/message/10973
- Original archive: https://news-web.php.net/php.internals/10973
- Started by Derick Rethans on 2004-07-08T08:57:52+00:00
- Messages: 4
- Reply by email to internals@lists.php.net
- More for agents: search with https://externals.io/search.md?q={query} · JSON API at https://externals.io/api · documentation at https://externals.io/llms.txt

---

## #10973 — Derick Rethans — 2004-07-08T08:57:52+00:00

URL: https://externals.io/message/10973#10973

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:
1. Without the "$ret = " it should throw a PHP warning for each broken
   query. (That's current what it's doing).
2. 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

---

## #11017 — Derick Rethans — 2004-07-09T07:06:30+00:00

URL: https://externals.io/message/10973#11017 · In reply to: #10973

> 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:
> 1. Without the "$ret = " it should throw a PHP warning for each broken
>    query. (That's current what it's doing).
> 2. 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

---

## #11034 — Wez Furlong — 2004-07-09T18:04:09+00:00

URL: https://externals.io/message/10973#11034 · In reply to: #11017

Stas already came up with a solution for the non-oo api; it probably
just needs porting to the oo interface.

--Wez.


> 
> 
> > […]
> 
> 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:
> > […]
> 
> IMO this should be fixed ASAP, I can perhaps try to come up with a patch
> myself too.
> 
> 
> 
> regards,
> Derick

---

## #11114 — Derick Rethans — 2004-07-12T08:39:32+00:00

URL: https://externals.io/message/10973#11114 · In reply to: #11034

> 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