Error "Executing SQL directly, without cursor" when using SCOPE_IDENTITY / IDENT_CURRENT
There hasn't been much on google about this error, so I'm asking here. I will switch PHP web application using MySQL to SQL Server 2008 (using ODBC, not php_mssql). Query execution or whatever is not a problem, but when I try to do scope_identity (or any similar function) I get the error "Executing SQL directly, no cursor". I do this right after the insert, so it should still be in scope. Running the same insert statement that the query for the insert id works fine in SQL Server Management Studio. Here's my code right now (everything else in the database wrapper class works fine for other queries, so I'm guessing it's not relevant right now):
function insert_id(){
return $this->query_first("SELECT SCOPE_IDENTITY() as insert_id");
}
query_first is a function that returns the first result from the first query field (basically the equivalent of execute_scalar () on .net).
Complete error message: Warning: odbc_exec () [function.odbc-exec]: SQL error: [Microsoft] [SQL Server Native Client 10.0] [SQL Server] Execute SQL directly; no cursor., SQL 01000 status in SQLExecDirect in C: [...] \ Database_MSSQL.php at line 110
a source to share
you can try using the OUTPUT clause as a job:
INSERT INTO YourTable
(col1, col2, col3)
OUTPUT INSERTED.YourIdentityCol as insert_id
VALUES (val1, val2, val3)
this single statement will insert a row and return a result set of identity values.
working sample:
create table YourTestTable (RowID int identity(1,1), RowValue varchar(10))
go
INSERT INTO YourTestTable
(RowValue)
OUTPUT INSERTED.RowID as insert_id
VALUES
('abcd')
OUTPUT:
insert_id
-----------
1
(1 row(s) affected)
this is fine too if you insert multiple lines at once:
INSERT INTO YourTestTable
(RowValue)
OUTPUT INSERTED.RowID as insert_id
SELECT 'abcd'
UNION SELECT '1234'
UNION SELECT 'xyz'
OUTPUT:
insert_id
-----------
2
3
4
(3 row(s) affected)
a source to share
I'm not sure how you are executing the actual insert statement, but scope_identity () only returns the last identity value for this session, i.e. the same SPID.
If you connect to the DB and do an insert and then reconnect to the DB, scope_identity () always returns NULL as they are in two different sessions.
a source to share