Bug #18149 Updated: INSERT/SELECT doesn't return resource, prevents any further queries

ID: 18149 Updated by: mhackett@ese.ogi.edu Reported By: mhackett@ese.ogi.edu -Status: Open +Status: Closed Bug Type: MSSQL related Operating System: Windows 2000 Server PHP Version: 4.2.1 New Comment: 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. Previous Comments: ------------------------------------------------------------------------ [2002-07-03 14:43:06] mhackett@ese.ogi.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-03 14:41:22] mhackett@ese.ogi.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 ------------------------------------------------------------------------ -- Edit this bug report at http://bugs.php.net/?id=18149&edit=1