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
Welcome back! If you're the original bug submitter, here's where you can edit the bug or add additional notes.
If this is not your bug, you can add a comment by following this link.
If this is your bug, but you forgot your password, you can retrieve your password here.
Password:
Status:
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-2024 The PHP Group
All rights reserved.
Last updated: Thu Mar 28 10:01:26 2024 UTC