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
Welcome back! If you're the original bug submitter, here's where you can edit the bug or add additional notes.
If you forgot your password, you can retrieve your password here.
Password:
Status:
Package:
Bug Type:
Summary:
From: rbanks at arel dot com dot au
New email:
PHP Version: OS:

 

 [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: Sun Dec 22 01:01:30 2024 UTC