php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #28264 SQLite not doing multiple queries
Submitted: 2004-05-03 22:41 UTC Modified: 2005-04-16 13:10 UTC
Votes:2
Avg. Score:5.0 ± 0.0
Reproduced:2 of 2 (100.0%)
Same Version:1 (50.0%)
Same OS:0 (0.0%)
From: csaba at alum dot mit dot edu Assigned:
Status: Closed Package: SQLite related
PHP Version: 5CVS-2004-05-03 (dev) OS: Win 2K Pro
Private report: No CVE-ID: None
 [2004-05-03 22:41 UTC] csaba at alum dot mit dot edu
Description:
------------
The sqlite_query page in a prominent box declares that SQLite will execute multiple queries separated by semicolons, but this seems not to be the case according to the test below.  While I posted a workaround for this, it is not very efficient because you have to send each query separately.

I have tested the command line version of SQLite (2.8.13) and it does accept multiple queries separated by semicolons.

Also, if dealing with this problem, I'd like to suggest that if the final query is a SELECT or other statement which produces some result normally destined for the user, that it be returned to the caller that way.  The documentation makes it seems like maybe it will and maybe it won't, saying it's undefined.  In particular, it would be helpful if SELECT and PRAGMA returned their results.  If you indulge me on that, please extend the functionality to sqlite_array_query.

Thanks,
Csaba Gabor

Reproduce code:
---------------
$db = sqlite_open(':memory:', 0666, $sqliteerror);
$tbl = "delme";
$multiQuery = "CREATE TABLE $tbl (foo INTEGER PRIMARY KEY, bar TEXT);" .
              "INSERT INTO $tbl VALUES (null, 'Hi Mom');";
$dbRes = sqlite_query ($db, $multiQuery);
$creationTest = "PRAGMA table_info('$tbl');";
$insertTest = "SELECT * FROM $tbl;";
$acT = sqlite_array_query ($db, $creationTest, SQLITE_ASSOC);
var_dump ($acT);
print "<br><br>Table was created,<br>";
$aiT = sqlite_array_query ($db, $insertTest, SQLITE_ASSOC);
var_dump ($aiT);
print "<br>but nothing was inserted";


Expected result:
----------------
I expect to that the SQL INSERT statement was carried out

Actual result:
--------------
However, SQL statements after the first ';' seem to be ignored.  Thus, the second var_dump only gets an empty array.

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2004-05-27 00:15 UTC] saleh at sfsj dot net
I also want to add that even if you try to use transactions to execute multiple queries..
I tried to use BEGIN; and BEGIN TRANSACTION; but both of them will not work and nothing will happen..
 [2004-07-10 15:14 UTC] wez@php.net
This bug has been fixed in CVS.

Snapshots of the sources are packaged every three hours; this change
will be in the next snapshot. You can grab the snapshot at
http://snaps.php.net/.
 
Thank you for the report, and for helping us make PHP better.

"Fixed" in CVS.

http://cvs.php.net/diff.php/pecl/sqlite/sqlite.c?r1=1.62.2.21&r2=1.62.2.22&ty=u


 [2005-04-04 22:00 UTC] csaba at alum dot mit dot edu
This is still behaving in the same way as originally described (just tested on PHP 5.0.4-dev 11-Mar-2005).  Here is slightly nicer code to show the problem:

<?php
$db = sqlite_open(':memory:', 0666, $sqliteerror);
$tbl = "delme";
$multiQuery = "CREATE TABLE $tbl " .
              "(foo INTEGER PRIMARY KEY, bar TEXT);" .
              "INSERT INTO $tbl VALUES (null, 'Hi Mom');";
$dbRes = sqlite_query ($db, $multiQuery);
$creationTest = "PRAGMA table_info('$tbl');";
$insertTest = "SELECT * FROM $tbl;";
$acT = sqlite_array_query ($db, $creationTest, SQLITE_ASSOC);
print "<br><br>Table was created:<br>";
var_dump ($acT);
$aiT = sqlite_array_query ($db, $insertTest, SQLITE_ASSOC);
print "<br><br>";
if ($aiT)
{print "with rows:<br>"; var_dump ($aiT); }
else print "but nothing was inserted";
?>

Csaba
 [2005-04-05 01:19 UTC] sniper@php.net
It was fixed: You need to use sqlite_exec() instead of sqlite_query() 

(took about 2 minutes for me to figure out and I don't use SQLite at all..)

 [2005-04-05 01:49 UTC] csaba at alum dot mit dot edu
It's unclear to me from the earlier comments that fixed means there is another function which mostly does what this one is documented as supposed to be doing (and today's earlier rejoinder is the the first mention of sqlite_exec).  Nevertheless, I had tested with sqlite_exec before submitting the report earlier today, and yes, it does do multiple queries.


To me, fixed includes the documentation agreeing with observed behaviour.  As I noted in the original report, the documentation for sqlite_query says that SQLite will execute multiple queries.  If the code isn't going to change, perhaps the documentation should.  Therefore, I have reclassified the Category as 'Documentation problem'.
 [2005-04-05 09:43 UTC] vrana@php.net
It's already documented:

sqlite_query:
"SQLite will execute multiple queries ... However, this works only when the result of the function is not used - if it is used, only the first SQL statement would be executed."

Changing back the state to "Closed SQLite".
 [2005-04-15 12:33 UTC] csaba at alum dot mit dot edu
That same comment goes on to say:
When executing multiple queries, the return value of this function will be FALSE if there was an error, but undefined otherwise (it might be TRUE for success or it might return a result handle).


Is this supposed to be like a Heisenberg uncertainty principle? - The very action of testing for sqlite_query's return value causes the multiple queries not to be executed.  Specifically, assigning the return value to a variable (but otherwise not using it) or even an if (sqlite_query(...)); will cause the multiple queries not to be executed.  I have simplified my example to show this.

If you have already said earlier that the return value may not be used upon pain of first SQL statement only being executed, I don't think it makes sense to talk about the return value being FALSE or otherwise in a multiple execution context.


<?php
$db = sqlite_open(':memory:', 0666, $sqliteerror);
$tbl = "delme";
$multiSQL = "CREATE TABLE $tbl (foo INTEGER PRIMARY KEY, bar TEXT);" .
            "INSERT INTO $tbl VALUES (null, 'Hi Mom');" .
            "INSERT INTO $tbl VALUES (null, 'Hi Dad');";

//$dbRes = sqlite_query ($db, $multiSQL);  // only creates table
if (sqlite_query ($db, $multiSQL));        // only creates table
//sqlite_query ($db, $multiSQL);           // inserts both rows

$insertTest = "SELECT * FROM $tbl;";
$aIT = sqlite_array_query ($db, $insertTest, SQLITE_ASSOC);
print "Number of rows now in table: " . sizeof($aIT);
?>


Csaba

PS.  This really is incongruous.  How many other PHP functions are there where the act of assigning the function's return value causes a difference in how the function has behaved?
 [2005-04-16 13:10 UTC] sniper@php.net
This is the 1st then. Can you now please leave this issue closed? (won't fix)

 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Sat Dec 14 11:01:27 2024 UTC