phpsql-serverpear

T-SQL : How can I retrieve the OUTPUT from an INSERT statement with PEAR DB?

This is my first time using T-SQL

I am currently trying to get the last inserted ID from an INSERT statement in T-SQL using this query:

INSERT INTO TICKET( STATE, RECORD_DATE, ID_USER, TICKET_TYPE, TICKET_COM)
OUTPUT INSERTED.ID_TICKET AS lastId
VALUES ( 1, CONVERT(datetime, '24/02/2022 09:25:53'), 100, 1, 'It does not work')

As you've guessed, ID_TICKET is the identity, with auto_increment. When I run this through MS SQL server management studio, I get the intended result : one row, with a unique lastId column containing the value of the last inserted id.

However on PHP, when I am running this using the query() method from PEAR database, I am running into some issue. From reading the documentation, DB->query() will only return a resource in case of a SELECT query, while an INSERT query will just return a DB_OK type of answer: which is exactly what I am getting in PHP. Hence my question : how can I retrieve the OUTPUT from an INSERT statement with PEAR DB?

I'd like to continue using PEAR, as I am adding functionality to an existing intranet heavily relying on it. It is hosted on IIS 7 with SQL Server 9.0 using PHP 5.2.9 and PEAR DB 1.1.2.2.

Solution

  • Finally, I just did a simple INSERT by removing the OUTPUT INSERTED.ID_TICKET AS lastId line. I then did a second query() call immediately after : SELECT @@IDENTITY as lastId and it now gives the desired results.

    Pretty much what Álvaro González suggested.

    Now, following Dan Guzman sugestion and adding SET NOCOUNT ON; at the beggining of my original INSERT with the OUTPUT clause indeed solved the problem ! I now get a result set with a unique row and column lastId as desired. My hat off to both of you.