PHP :: Bug #16277 :: Multiple statement in a query cause subsequent queries to fail

[2002-03-25 22:49 UTC] rbanks at arel dot com dot au

I am creating tables and indexes in a few places.  To make life a little easier I want to create a table and it's indexes in one go.

The following code fails:
----
$queries[0]["query_string"]="CREATE TABLE objects (
	[id] integer IDENTITY (1,1) PRIMARY KEY,
	[type] varchar(16) DEFAULT '' NOT NULL,
	[object] text,
	[vtype] varchar(16) DEFAULT '' NOT NULL,
	[lastchanged] TIMESTAMP);
CREATE INDEX objects_lastchanged ON objects (lastchanged);";

$queries[1]["query_string"]="CREATE TABLE nodes (
	path varchar(127) DEFAULT '' NOT NULL PRIMARY KEY,
	parent varchar(127) DEFAULT '' NOT NULL,
	object numeric(11) DEFAULT '0' NOT NULL,
	priority numeric(11) DEFAULT '0' NOT NULL
);";

while ((list($key, $query)=each($queries)) && (!$error)) {
	echo $query["query_string"];
	$exec=mssql_query($query["query_string"]);
}

----

But if I separate out the create index into a separate query it works fine.

[2002-03-26 08:01 UTC] sander@php.net

RTM! You can only run one query per call to mysql_query()!

[2002-03-26 08:45 UTC] hholzgra@php.net

RTFBR ;) it is MS SQL, not mySQL

[2002-12-07 01:52 UTC] iliaa@php.net

Please try using this CVS snapshot:

  http://snaps.php.net/php4-latest.tar.gz
 
For Windows:
 
  http://snaps.php.net/win32/php4-win32-latest.zip

[2002-12-23 01:00 UTC] php-bugs at lists dot php dot net

No feedback was provided for this bug for over 2 weeks, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

[2005-11-01 07:42 UTC] nicked at gmail dot com

(I know this is an old bug, but the latest version has the same problem)

PHP/5.1.0RC1
MSSQL 7.0
Apache/2.0.54 (Win32)

I want to do several hundred database INSERTs, and it's much faster to combine them into a single "compound" statement separated by semicolons.

If the records already exist in the database, I get a duplicate key message and the statement fails. Normally this is fine, I just go on to the next INSERT. But now that I use this compound statement, when that fails, every subsequent (unrelated) query also fails.

The only way I can continue is by closing the database connection and opening it again.

A related problem is that now the duplicate key warnings are printed even if you call mssql_query with the '@' operator. They seem to be output when the connection is closed, because if you call @mssql_close manually they don't appear. Another option is to use mssql_min_message_severity(15).

-Nick

Patches

Pull Requests

History

	php.net \| support \| documentation \| report a bug \| advanced search \| search howto \| statistics \| random bug \| login
go to bug id or search bugs for


Copyright © 2001-2024 The PHP Group All rights reserved.	Last updated: Thu Apr 25 02:01:30 2024 UTC