sql server - Microsoft´s sqlsrv driver for PHP not returning any result when querying "SELECT SCOPE_IDENTITY() AS id"

Question

this query works fine using the php_mssql driver:

INSERT INTO Table(columnName) VALUES ('text');
SELECT SCOPE_IDENTITY() AS id;

Table does have an id column, which is an identity. I would execute that query, and get the last id in the table.

The same code doesn′t work if the query is executed using Microsoft′s php_sqlsrv driver.

I don′t get any error when executing the query (sqlsrv_query function) , but i get the following error when calling sqlsrv_fetch_array: "The active result for the query contains no fields"

I′ve googled a lot, and didn′t find no answer, it was a big surprise for me that no one faced this problem before, it seems like nobody is using this driver, even though is the "official" one since PHP 5.3 release...

Thanks.

Answer 1

In the initial CTP the field indices started at 1. Later they were changed to start at 0.

Try something like this:

// connection to the dbserver

$result = sqlsrv_query("INSERT INTO Table(columnName) VALUES ('text'); SELECT SCOPE_IDENTITY() AS ID");

echo "The last insert_id is".lastId($result);

function lastId($queryID) {
     sqlsrv_next_result($queryID);
     sqlsrv_fetch($queryID);
     return sqlsrv_get_field($queryID, 0);
}