Scope identity sql php

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

PHP for SQL Server Version 2.0 PDO LastInsertId and fetching scope_identity in sqlsrv not works. Both have bugs.

Question

try
$pdo_con = new PDO( «sqlsrv:server=$serverName ; Database=$db_name», «$db_user», «$db_pw»);
$pdo_con->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
>
catch(Exception $e)
die( print_r( $e->getMessage() ) );
>

$tsql = «INSERT INTO [sampledb].[dbo].[tbUsers] (UserId,NameSurname) VALUES (newId(),?)»;

try $insert_user = $pdo_con->prepare($tsql);
$insert_user->execute($params);
>
catch(Exception $e)
die( print_r( $e->getMessage() ) );
>

Everything works perfect. But i cant get lastInsertId() i think it not works correctly.

There is a note on http://msdn.microsoft.com/en-us/library/ff628155%28SQL.90%29.aspx

Returns the identifier for the row most recently inserted into a table in the database. The table must have an IDENTITY NOT NULL column.

My UserId coulmn is (PK,uniqueidentifier,not null) i think there is no problem in my table.

It not worked for sqlsrv driver too.

$serverName = «192.168.1.4»;
$db_user = «user»;
$db_pw = «password»;
$db_name = «sampledb»;

$connectionInfo = array(«UID» => «$db_user», «PWD» => «$db_pw», «Database»=>»$db_name»);

$ms_con = sqlsrv_connect( $serverName, $connectionInfo);
if( $ms_con === false )
echo «Could not connect.\n»;
die( print_r( sqlsrv_errors(), true));
>

$tsql = «INSERT INTO [sampledb].[dbo].[tbUsers] (UserId,NameSurname) VALUES (newId(),?); SELECT SCOPE_IDENTITY() AS NewUserId»;

$stmt = sqlsrv_prepare( $ms_con, $tsql, $params);

if( $stmt === false )
die( FormatErrors( sqlsrv_errors() ) );
>

if( sqlsrv_execute( $stmt) === false)

die( FormatErrors( sqlsrv_errors() ) );
>

/*Skip the open (first) result set (rows affected). */
$next_result = sqlsrv_next_result($stmt);

// Fetch the next result set.
if( sqlsrv_fetch($stmt) === false)

// Get the first field — the identity from INSERT.
$insert_id = sqlsrv_get_field($stmt, 0);

This version doesnt works too.

Answers

Hello. Noone seemed to answer this satisfactorily, here, possibly owing to misunderstanding the problem. Or maybe I’ve misunderstood the problem! Anyway, I was having what I think is the same problem, with scope_identity() not returning after using parameters using sqlsrv on php. Eventually I found the solution online, but it doesn’t seem to come near the top of search engine results, so thought I would put it here as well. To recap: Table ‘test’ definitely has an identity column id. However you can not do (apologies for pseudo-code) doquery(«INSERT INTO test (col1, col2) VALUES (. )», $parameters); doquery(«SELECT SCOPE_IDENTITY()»); because these two queries are in different scopes, so there is nothing to return. However, this also doesn’t work as expected: $query = «INSERT INTO test (col1, col2) VALUES (. ); SELECT SCOPE_IDENTITY()»; $arrParams[]=»1″; $arrParams[]=»2″; $resource=sqlsrv_query($conn, $query, $arrParams); $arrRow = sqlsrv_fetch_array($resource, SQLSRV_FETCH_ASSOC); echo $arrRow[0]; You expect to see the last insert id, but you see nothing. This is because the resource is currently pointing to the ‘true’ from the first query (the insert query) and so the insert id is ‘hidden’ in another row of the resource. Solution: . as before, but then: $resource=sqlsrv_query($conn, $query, $arrParams); sqlsrv_next_result($resource); sqlsrv_fetch($resource); echo sqlsrv_get_field($resource, 0); This will give you what you want! This will work similarly with something like «INSERT INTO test (col1, col2) VALUES (. ); SELECT * from test WHERE and again you have to move through the resource to get at the data you want. I hope this helps someone, since it’s just wasted a good two hours of my time! Also, sorry for any errors in the code — I’m afraid I’ve written it off the top of my head, but hopefully you get the idea.

All replies

There is a note on http://msdn.microsoft.com/en-us/library/ff628155%28SQL.90%29.aspx

Returns the identifier for the row most recently inserted into a table in the database. The table must have an IDENTITY NOT NULL column. Ok i finf at last. Key point is uniqueidentifier. You cant get last insert id if your column is not Identity. For uniqueidentifier you have to first $query=»select newId() as last_id»; $inf = assoc($query); $last_id = $inf[last_id]; after insert $query = «insert into table (user_id,name_surname) values (. )»; $params = array(&$last_id,&$name_surname); pdo::prepare($query); pdo::execute($params); logic is first select newId then insert this to table.

Источник

Scope identity sql php

Как выбрать SCOPE_IDENTITY() с помощью драйвера Microsoft SQL в PHP?

Ответы (3)

Как выбрать SCOPE_IDENTITY () с помощью Microsoft SQL Driver в PHP?