php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #18149 INSERT/SELECT doesn't return resource, prevents any further queries
Submitted: 2002-07-03 14:41 UTC Modified: 2002-07-05 12:53 UTC
From: mhackett at ese dot ogi dot edu Assigned:
Status: Closed Package: MSSQL related
PHP Version: 4.2.1 OS: Windows 2000 Server
Private report: No CVE-ID: None
View Add Comment Developer Edit
Welcome! If you don't have a Git account, you can't do anything here.
You can add a comment by following this link or if you reported this bug, you can edit this bug over here.
(description)
Block user comment
Status: Assign to:
Package:
Bug Type:
Summary:
From: mhackett at ese dot ogi dot edu
New email:
PHP Version: OS:

 

 [2002-07-03 14:41 UTC] mhackett at ese dot ogi dot edu
When I do an INSERT statement that has a SELECT @@IDENTITY at the end, the result is not a resource, but just 1. I suppose that would make sense for an INSERT-only query, but this should be returning a resource for the result. (Transact SQL, the SQL flavor Microsoft SQL Server is powered by, has a special variable @@IDENTITY which returns the last primary key insert).

This alone seems to me to be a bug, but the bigger problem comes next. As sson as I attempt to execute any more queries, either on the same page, or on any others, even if I start a new browser session, PHP chokes. mssql_connect() or _pconnect() returns no error, but any query or mssql_select_db() fails. This continues until I restart Apache.

I've done a trace on the SQL server, and it shows that the last command (the INSERT ... SELECT @@IDENTITY) succeeds, but PHP subsequently stops sending commands. Even when PHP has apparently connected, the Enterprise Manger and trace show no new connections or USE statements.

Here is a test snippet I used:
mssql_pconnect(DB_DSN, DB_USER, DB_PASS);
mssql_select_db('cmxreg');
$ret = mssql_query("INSERT INTO Users (UserName) VALUES ('sql_test') SELECT @@IDENTITY AS UserID");
echo $ret.'<br />';
echo mssql_result($ret, 0, 'UserID');

And the error message for the first run:
Warning: mssql_result(): supplied argument is not a valid MS SQL-result resource in C:\SiteData\htdocs\regadmin\test.php on line 8

And subsequent runs:
Warning: MS SQL: Unable to select database: cmxreg in C:\SiteData\htdocs\regadmin\test.php on line 5
Warning: MS SQL: Query failed in C:\SiteData\htdocs\regadmin\test.php on line 6
Warning: mssql_result(): supplied argument is not a valid MS SQL-result resource in C:\SiteData\htdocs\regadmin\test.php on line 8

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2002-07-03 14:43 UTC] mhackett at ese dot ogi dot edu
By the way, I originally was testing this on Apache 2.0.39, but also tried it on the latest 1.3 and encountered the same problem.
 [2002-07-05 12:53 UTC] mhackett at ese dot ogi dot edu
I've done a little more testing and poking around Microsoft's site, and it is clear this is not a bug in PHP. The API more or less doesn't seem to support atomic INSERT/SELECT, thus the two operations must be seperated out. Although I originally did not want to do this, a Microsoft KB article has assuaged my fears that the @@IDENTITY value would be altered by INSERTs on other connections, so other than a little extra code, there's not much harm behind using two different queries.
 
PHP Copyright © 2001-2022 The PHP Group
All rights reserved.
Last updated: Thu Jan 27 23:03:33 2022 UTC