Getting SELECT statement after INSERT in PHP (SQLServer)

From PHP I send several queries to SQL Server containing INSERT first, and then SELECT the last identifier entered (identifier is on sequence). The echo function in PHP returns [].

From the database console, everything is fine (Select works):

<?php
$serverName = ..
$connectionInfo = ..
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn === false ) {
     die( print_r( sqlsrv_errors(), true));
}

$sql = "BEGIN TRANSACTION
        DECLARE @variable TABLE (value INT)
        INSERT into abc (col1, col2) 
        OUTPUT INSERTED.identifier INTO @variable(value)
        values ('1','34');

        SELECT value as gek FROM @variable;
        COMMIT
        ";

$stmt = sqlsrv_query( $conn, $sql);
if( $stmt === false ) {
     die( print_r( sqlsrv_errors(), true));
}

$res = [];
while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC) ) {
      $res[] = $row;
}

echo json_encode($res,JSON_INVALID_UTF8_SUBSTITUTE);
;?>

2 answers

  • answered 2020-07-14 16:50 allmhuran

    The first "result set" will be the rows affected by the insert statement. This is just a row count - it has no rows, so sqlsrv_fetch_array gets you nothing.

    A quick way to get around this is to add set nocount on; to the start of your TSQL

    Alternatively, you can call sqlsrv_next_result to move past the rowcount and get the actual result set of the select statement. This lets you use the rowcount (if you ever need to).

  • answered 2020-07-15 00:57 kaladin_storm

    I tested your code and had the same results. You can simplify the query to not need the table variable though and it worked for me after.

    BEGIN TRANSACTION
    INSERT into abc (col1, col2) 
    OUTPUT INSERTED.identifier
    values ('1','34');
    COMMIT