php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login

go to bug id or search bugs for

Bug #16277 Multiple statement in a query cause subsequent queries to fail
Submitted: 2002-03-25 22:49 UTC Modified: 2002-12-23 01:00 UTC
Votes:2
Avg. Score:4.0 ± 1.0
Reproduced:2 of 2 (100.0%)
Same Version:0 (0.0%)
Same OS:2 (100.0%)
From: rbanks at arel dot com dot au Assigned:
Status: No Feedback Package: MSSQL related
PHP Version: 4.1.2 OS: NT4
Private report: No CVE-ID: None
View Developer Edit
 [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.

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [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
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved. 		Last updated: Sat Dec 21 13:01:31 2024 UTC