如何在PHP中使用Microsoft SQL驱动程序选择SCOPE_IDENTITY() ?

I have a special MSSQL statement which I use to retrieve the identity of a specified row that either already exists or else INSERTs and then retrieves that identity. This SQL appears to work great in SQL Management Studio as well as a couple of my .NET C# apps.

我有一个特殊的MSSQL语句，用于检索已存在的或其他插入的指定行的标识，然后检索该标识。这个SQL看起来在SQL Management Studio以及我的几个。net c#应用程序中都很有用。

Unfortunately, I've been asked to use PHP for this particular task and I am having great difficulty trying to use PHP and the official Microsoft SQL driver.

不幸的是，我被要求在这个特定的任务中使用PHP，我很难使用PHP和Microsoft SQL驱动程序。

The statement isn't returning an ID after the INSERT (or if it is, I'm not able to retrieve it). Could somebody please see where and if I'm going wrong?

语句在插入之后没有返回ID(或者如果是，我无法检索它)。谁能看看我哪里出错了吗?

I'm using PHP 5.3.23 with Microsoft SQL Driver 3.0. Here is my code:

我使用的是PHP 5.3.23和Microsoft SQL Driver 3.0。这是我的代码:

$sql = ''
        . 'IF (SELECT COUNT(*) FROM users WHERE sAMAccountName = ?) = 0 '
        . 'BEGIN '
        . 'INSERT INTO users (role, sAMAccountName, fullName) '
        . 'VALUES (1, ?, ?) '
        . 'SELECT SCOPE_IDENTITY() '
        . 'END '
        . 'ELSE SELECT id FROM users WHERE sAMAccountName = ?';
$params = array($requester, $requester, $fullName, $requester);
$query = $this->SqlQuery($sql, $params);

function SqlQuery($sql, array $params) {
    $conn = $this->SqlConnection(null, null, null, null); // x4 null = use default connection options
    $query = sqlsrv_query($conn, $sql, $params);
    if ($query) {
        sqlsrv_fetch($query);
        echo 'Result: ' . sqlsrv_get_field($query, 0); // This should return an ID regardless
        sqlsrv_close($conn);
    }
}

Here is the result HTML (No Result):

这里是结果HTML(无结果):

Result:

3 个解决方案

#1

You need a SET NOCOUNT ON; at the beginning of your query.

你需要一个集合NOCOUNT ON;在查询的开头。

Your INSERT statement actually returns something: an empty recordset plus the message "(1 row affected)". SSMS just concatenates all the recordsets, but in sqlsrv you have to explicitly advance to the second recordset to see your SCOPE_IDENTITY() result. By turning off the rowcount feature the uninteresting results set is quashed, and it will do what you want.

插入语句实际上返回一些东西:一个空记录集加上消息“(受影响的一行)”。SSMS只是连接所有的记录集，但是在sqlsrv中，您必须显式地前进到第二个记录集，以查看SCOPE_IDENTITY()结果。通过关闭rowcount特性，无趣的结果集被取消，它将执行您想要的操作。

#2

Not sure if it is an option for you but: I would change the SQL statement into a stored procedure, and include the return of the identity. Once a stored procedure make these changes to the statement

我不确定它是否适合您，但是:我将把SQL语句更改为一个存储过程，并包含标识的返回。一旦存储过程对语句做了这些更改

'SELECT SCOPE_IDENTITY() '

来

'SELECT SCOPE_IDENTITY() as id'

Then within PHP you should be able to access the id as a datatable. Stored procedures will return data as a datatable, not sure on the PHP side, I'm a .NET person

然后在PHP中，您应该能够访问id作为一个datatable。存储过程将返回数据作为一个datatable, PHP方面不确定，我是。net人

If you need help with convert the statement into a stored proc, let me know.

如果您需要帮助将语句转换为存储的proc，请让我知道。

EDIT:
Assuming that sAMAccountName is the primary key on this table, and that what you state in your question that the you can't get the identity back during the INSERT only. Then changing the SQL to this will do the trick for you.

编辑:假设sAMAccountName是该表上的主键，并且您在问题中声明的是，您不能仅在插入期间获得标识。然后，将SQL更改为这个样式将对您有用。

$sql = ''
    . 'IF (SELECT COUNT(*) FROM users WHERE sAMAccountName = ?) = 0 '
    . 'BEGIN '
    . 'INSERT INTO users (role, sAMAccountName, fullName) '
    . 'VALUES (1, ?, ?) '
    . 'END '
    . 'SELECT id FROM users WHERE sAMAccountName = ?';

#3

You could try this out. Strip the extra single quotes and concatenations out and apply Dbloch's suggestion.

你可以试试这个。去掉多余的单引号和连接，并应用Dbloch的建议。

$sql = 'IF (SELECT COUNT(*) FROM users WHERE sAMAccountName = ?) = 0
            BEGIN
            INSERT INTO users (role, sAMAccountName, fullName)
            VALUES (1, ?, ?) 
            SELECT SCOPE_IDENTITY() as id
            END
            ELSE SELECT id FROM users WHERE sAMAccountName = ?';

#1