Msphpsql: Obtaining the last inserted id

[BarryCarlyon]

Ubutunu 16.04, and PHP7 and thus basically a fresh new install of everything.

Tested a few variations but ran into a issue.

Perform an insert query, then chain that with

INSERT INTO FATest(acol) VALUES ('a test');
SELECT SCOPE_IDENTITY() as ID;

and/or in one go

INSERT INTO FATest(acol) VALUES (\'a test\');SELECT SCOPE_IDENTITY() as ID;

Results in a null/no row, and thus no ID.

Running:

INSERT INTO FATest(acol) OUTPUT INSERTED.* VALUES ('a test');

Returns a row, correctly, however this doesn't work with tables that have triggers on: (Err:

The target table 'SomeTable' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.

So, I change to:

DECLARE @T TABLE (ID int) INSERT INTO FATest(acol) OUTPUT INSERTED.ID INTO @T VALUES ('a test') SELECT * FROM @T

(this query works fine when run in M$ SQL Server Management Studio Express an ID/Result row is returned)

When run under PHP, Again returns no row/ID

This lack of a result row behaviour is consistent when using either PDO or straight sqlsrv_ based commands

sqlsrv_ based PHP Script example: ($mssql_database should be self explanatory)

<?php

        try
        {
            $serverName = "tcp:" . $mssql_database['db'];
            $connectionOptions = array("Database"=>$mssql_database['name'],
                "Uid"=>$mssql_database['u'], "PWD"=>$mssql_database['p']);
            $conn = sqlsrv_connect($serverName, $connectionOptions);
            if($conn == false)
                die(FormatErrors(sqlsrv_errors()));
        }
        catch(Exception $e)
        {
            echo("Error!");
        }


//            $tsql = "INSERT SalesLT.Product (Name, ProductNumber, StandardCost, ListPrice, SellStartDate) OUTPUT            INSERTED.ProductID VALUES ('SQL Server 1', 'SQL Server 2', 0, 0, getdate())";

$tsql = 'DECLARE @T TABLE (ID int) INSERT INTO FATest(acol) OUTPUT INSERTED.ID INTO @T VALUES (\'a test\') SELECT * FROM @T';

            //Insert query
            $insertReview = sqlsrv_query($conn, $tsql);
            if($insertReview == FALSE)
                die(FormatErrors( sqlsrv_errors()));
            echo "Product Key inserted is :";
            while($row = sqlsrv_fetch_array($insertReview, SQLSRV_FETCH_ASSOC))
            {
//                echo($row['ProductID']);
print_r($row);
            }
            sqlsrv_free_stmt($insertReview);
            sqlsrv_close($conn);

Pardon the rubbish it's a loose copy/paste/adjust of https://docs.microsoft.com/en-us/sql/connect/php/step-3-proof-of-concept-connecting-to-sql-using-php

Output:

php test_alt.php
Product Key inserted is :

[meet-bhagdev]

@BarryCarlyon thanks for the script. We are going to try to repro it and will get back to you asap

[BarryCarlyon]

Oh, the table FATest is just

• column name ID, primary key (with ID spec) int
• column acol varchar(50)

No triggers on this table, trigger based testing was performed on another table where the issue was discovered and the query for trigger based table Insert ID fetching fails whether a trigger exists or not.

Basically, theres no 100% reliable way to get the insert ID for all table "types"

[yitam]

Hi @BarryCarlyon

I have no problem with a script that is very similar to the one in the documentation mentioned above. Then I realized that your sql statement $tsql = 'DECLARE @T TABLE (ID int) INSERT INTO FATest(acol) OUTPUT INSERTED.ID INTO @T VALUES (\'a test\') SELECT * FROM @T'; actually puts the result into @T then selects it from @T

Therefore, for your script to work, you call sqlsrv_next_result( $insertReview ); before sqlsrv_fetch_array() to advance to the right result set.

Please let us know if this doesn't work for you.

[yitam]

Any update @BarryCarlyon ?
FYI, here is a sample of the equivalent code using PDO_SQLSRV:

    $sql = "DECLARE @T TABLE (ID int) INSERT INTO $tableName (Name, SomeValue) OUTPUT INSERTED.ID INTO @T VALUES ('a test', 9876) SELECT * FROM @T";

    $stmt = $conn->query( $sql );
    echo "Primary Key inserted is : ";  

    $stmt->nextRowset();

    $result = $stmt->fetchColumn();
    echo($result);  

[BarryCarlyon]

Seems to work against a test script I have. I'll see about porting into my production scripts and see if I can get it running.

[yitam]

Sounds great @BarryCarlyon ! Hope this resolves the issue. If it doesn't work for any reason, please provide a more detailed script for further investigation.

[Hadis-Knj]

Closing due to inactivity, please feel free to reopen it

[livebit]

I also have this problem using PHP 7 and Ubuntu 16.04. Code below works fine on PHP 5.6 and Ubuntu 14:

INSERT INTO FATest(acol) VALUES ('a test');
SELECT SCOPE_IDENTITY() as ID;

is there a chance to make SELECT SCOPE_IDENTITY() as ID; just work on above configuration (without using hacks)?

[yitam]

Hi @livebit

First of all, we do not support Linux back in PHP 5.6, so I'm unsure why it works for you?

Also, let me clarify that my suggestion above was not a hack. It has a legitimate reason.
In your case, I believe you meant to send the queries in one batch as below:

INSERT INTO FATest(acol) VALUES ('a test'); SELECT SCOPE_IDENTITY() as ID;

Then the same suggestion works for you. The INSERT statement returns something like "1 row affected", so the result you're looking for is in the next set. On the other hand, if you send this query SELECT SCOPE_IDENTITY() as ID; by itself, then you don't need to make an extra call.

Hope this helps. If you want to follow up, please create a brand new issue and provide a more detailed repro scenario (whether you're using sqlsrv or pdo_sqlsrv) for the problem you've encountered. Feel free to reference this old issue however.

[livebit]

Hi @yitam , thank you for the quick reply.

I used unofficial set of libraries FreeTDS and that's probably why it works, for me. I'm sending above queries not in one batch but within transaction.

I guess I will stick to my current configuration for now.

[yitam]

You're welcome, @livebit
If you have any problem related to our drivers sqlsrv or pdo_sqlsrv, please feel free to open a new issue.

[pravdinalex]

@livebit ,
adding

set nocount on;

before

insert ... ;
select scope_identity() as id;

helped me (sqlsrv / pdo / php 7.1 / ubuntu 16) - id returned as expected.
@yitam , is it a good decision?

[yitam]

Hi @pravdinalex
Yes, in this particular example, by using SET NOCOUNT ON is indeed another way to retrieve the id without the need to call sqlsrv_next_result or nextRowset

[nareparmar]

I have using sqlsrv driver in codeigniter.
One table with trigger
I have call last insert id function but found trigger last insert id but we need first query id.please help

[david-puglielli]

@nareparmar I'm not entirely sure what you're asking. Could you provide a repro script with the actual and expected output? Including any error messages you may get.

[nareparmar]

I have called "SELECT SCOPE_IDENTITY() AS last_id" but return value is blank so please suggest.

[david-puglielli]

Are you using the latest version of the driver? We have made changes to the way lastInsertId works recently.The latest version is 5.4.0-preview, available here.

[stevebauman]

@david-puglielli I'm getting completely incorrect identifiers with lastInsertId() when using PHP 7.1 against SQL Server 2012.

Just tried with the latest driver...

[david-puglielli]

@stevebauman Could you provide a repro script along with the expected and actual results?

[stevebauman]

Hey @david-puglielli, appreciate the quick response!

I'll see what I can do. Give me little bit to get back to you.

[stevebauman]

Hey @david-puglielli,

This wasn't an issue with this driver, it's actually due to a SQL AFTER INSERT trigger on the database table I was inserting a record into.

This trigger inserted records into another table and SCOPE_IDENTITY() returns the last ID of the inserted record on that other table when inserting into the primary table.

Was driving me insane because it was working for other tables and just not this one specifically.

Sorry for the inconvenience!

[stevebauman]

For anyone else possibly experiencing this issue, I had to create a new temporary table and insert the inserted rows identifier into it to overwrite the SCOPE_IDENTITY() .

-- The trigger
ALTER TRIGGER [Inventory].[OnInsertTriggerHistory] ON [Inventory].[Inventory] 
FOR INSERT AS

SET NOCOUNT ON

-- The child table insert
INSERT INTO [Inventory].[InventoryHistory] (...) VALUES (...)

-- Reset the `SCOPE_IDENTITY()` by performing an insert into a temp table.
IF (SELECT COUNT(1) from inserted) = 1 BEGIN  
    CREATE TABLE #TempIdTable (ResetIdentity int identity(1, 1))

    SET identity_insert #TempIdTable ON
        INSERT INTO #TempIdTable (ResetIdentity)
        SELECT InventoryId FROM inserted SET identity_insert #TempIdTable OFF
END

-- Drop the temp table if it exists.
IF OBJECT_ID('tempdb..#TempIdTable') IS NOT NULL DROP TABLE #TempIdTable

The proper ID is now returned from the PDO driver.

Stack overflow link: https://stackoverflow.com/questions/15883304/stop-trigger-changing-scope-identity

Not sure if there is a cleaner way around this? Would love to hear it if so!

https://docs.microsoft.com/en-us/sql/t-sql/functions/scope-identity-transact-sql?view=sql-server-2017

http://www.sqlbadpractices.com/how-not-to-retrieve-identity-value/

[andrescellini]

@stevebauman did you find an alternative? i cant change triggers because its a shared db.

i was using lastinsertedid ( 4.3 ) but now its not working with the last drivers ( 5.6 )

thks!

[stevebauman]

Hi @andrescellini,

Unfortunately I’m not sure how you would do this without modifying the trigger.

The only other workaround would be to relocate or execute another SQL query to find the record that you just inserted, rather than relying on the returned inserted ID as it is incorrect.

This is may be a bit difficult since you’ll have to trust that your SQL query retrieves the new record properly after you’ve ran the insert statement. You won't be able to use a SELECT MAX(ID) query since your DB is shared and another record could be created near the same time. The query you execute will likely need to search for all the values you inserted into it to locate it.

I hope this helps!

[andrescellini]

@stevebauman exactly, in the end i did another query using SELECT IDENT_CURRENT(table_name)

But like you said, its not reliable in all the scenarios. My app dont have huge trafic, so i guess it will be fine. But in case of 2 sessions inserting almost at the same time could be some problems