如何使用PDO和SQL Server获取最后一个INSERTed行的ID？

Under other circumstances I might be tempted to use

在其他情况下，我可能会试图使用

$result = mssql_query("INSERT INTO table (fields) VALUES (data); 
                       SELECT CAST(scope_identity() AS int)");

but as I will be inserting user-submitted data, I want to continue to use PDO, which returns an empty array.

但由于我将插入用户提交的数据，我想继续使用PDO，它返回一个空数组。

Unfortunately, I'm running PHP on a Linux server and using dblib to interface with Microsoft SQL Server, which doesn't support PDO::lastInsertID().

不幸的是，我在Linux服务器上运行PHP并使用dblib与Microsoft SQL Server连接，后者不支持PDO :: lastInsertID（）。

Please help!

请帮忙！

Update to include code example

更新以包含代码示例

Here's the code I'm using: col1 is a field of type int identity and col2 is a datetime with a default of getdate().

这是我正在使用的代码：col1是int identity类型的字段，col2是datetime，默认值为getdate（）。

//  Connect to db with PDO
$pdo = new PDO( 'dblib:host=' . $host . ';dbname=' . $database . ';', $username, $password, array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION) );
//  Connect to db with MSSQL
$sql = mssql_connect( $host, $username, $password );
mssql_select_db( $database, $sql );
//  Create SQL statement
$query = "INSERT INTO [table] ( col3, col4, col5 )
          VALUES ( 'str1', 'str2', 'str3' );
          SELECT SCOPE_IDENTITY() AS theID;";
//  Run with MSSQL
echo "Using MSSQL...\n";
$result = mssql_query( $query );
$the_id = mssql_result( $result, 0, 'theID' );
echo "Query OK. Returned ID is " . $the_id . "\n";
// Run with PDO
echo "\nUsing PDO...\n";
$stmt = $pdo->query( $query );
$result = $stmt->fetchAll( PDO::FETCH_ASSOC );
print_r( $result );

And this is what was displayed:

这就是显示的内容：

Using MSSQL...
Query OK. Returned ID is 149

Using PDO...
Array
(
)

I would love to find out that I'd done something stupid, rather than come up against a horrible dead end :)

我很想知道我做了一些愚蠢的事情，而不是遇到一个可怕的死胡同:)

2 个解决方案

#1

You've got a few choices:

你有几个选择：

SELECT @@IDENTITY - return the last ID created by actions of the current connection, regardless of table/scope

SELECT SCOPE_IDENTITY() - last ID produced by the current connection, in scope, regardless of table

SELECT IDENT_CURRENT('name_of_table'); - last ID produced on that table, regardless of table/scope/connection

Of the three, SCOPE_IDENTITY() is the best candidate.

在这三个中，SCOPE_IDENTITY（）是最佳候选者。

#2

Maybe you are getting two rowsets returned. Try adding SET NOCOUNT ON; to eliminate the INSERT's rowset, or use $stmt->nextRowset if your driver supports it.

也许你得到两个返回的行集。尝试添加SET NOCOUNT ON;消除INSERT的行集，或者如果您的驱动程序支持它，则使用$ stmt-> nextRowset。

#1