Doctrine 1
  1. Doctrine 1
  2. DC-323

when inserting a record into sql server (via odbc) doctrine fails to get last inserted ID

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 1.2.0
    • Fix Version/s: 1.2.2
    • Component/s: Record
    • Labels:
      None
    • Environment:

      Description

      in Doctrine/Sequence/Mssql.php the function lastInsertId() fails to retrieve the last inserted ID when inserting a record. Because the major release of my sql server is bigger than 8, it chooses $query = 'select SCOPY_IDENTITY()' which does not return a number (in fact it does not return anything), although the record IS inserted succesfully. The backtrace is attached.
      If, on the other hand, I force doctrine to use 'select @@IDENTITY' the query does return the correct number and everything goes fine!
      So I think the conditions which query to choose is incorrect. I tried both queries in SQL server itself and both queries returned the right number. But one of them gets lost somewhere when using it in doctrine.

      1. ff.txt
        0.6 kB
        aiso haikens
      2. mssql.patch
        0.7 kB
        Juozas Kaziukenas
      3. mssql2.patch
        1 kB
        aiso haikens

        Activity

        Hide
        aiso haikens added a comment -

        I tested your patch, but it didn't work since the $table var is empty in my case. This is because the function _assignIdentifier() in UnitOfWork won't set the $seq var in case of 'mssql'. So first of all we need to pass the table name to this function like this in UnitOfWork line 925:

        + } elseif ($driver == 'oracle' || $driver == 'mssql')

        { $seq = $table->getTableName(); }

        $id = $this->conn->sequence->lastInsertId($seq);

        And furthermore I need to quote the table name before it worked like this in Mssql line 141:

        $query = 'SELECT IDENT_CURRENT(\'' . $this->conn->quoteIdentifier($table) . '\')';

        After these changes it worked. But don't you think we have to get rid of the SCOPE_IDENTITY entirely?
        I attached this patch.

        Show
        aiso haikens added a comment - I tested your patch, but it didn't work since the $table var is empty in my case. This is because the function _assignIdentifier() in UnitOfWork won't set the $seq var in case of 'mssql'. So first of all we need to pass the table name to this function like this in UnitOfWork line 925: + } elseif ($driver == 'oracle' || $driver == 'mssql') { $seq = $table->getTableName(); } $id = $this->conn->sequence->lastInsertId($seq); And furthermore I need to quote the table name before it worked like this in Mssql line 141: $query = 'SELECT IDENT_CURRENT(\'' . $this->conn->quoteIdentifier($table) . '\')'; After these changes it worked. But don't you think we have to get rid of the SCOPE_IDENTITY entirely? I attached this patch.
        Hide
        aiso haikens added a comment -

        OK, what to do next? The last patch works fine for me. It seems to be the solution to the problem. So, can I check it in? Or should someone else do this? Or do I simply set the status to 'resolve' and someone else does the rest?

        Show
        aiso haikens added a comment - OK, what to do next? The last patch works fine for me. It seems to be the solution to the problem. So, can I check it in? Or should someone else do this? Or do I simply set the status to 'resolve' and someone else does the rest?
        Hide
        Craig Marvelley added a comment -

        I tried the last patch and it works fine for me on SQL Server 2005/2008. +1 for committing it

        Show
        Craig Marvelley added a comment - I tried the last patch and it works fine for me on SQL Server 2005/2008. +1 for committing it
        Hide
        Jonathan H. Wage added a comment -

        Thanks for everyones work on this issue.

        Show
        Jonathan H. Wage added a comment - Thanks for everyones work on this issue.
        Hide
        Brian B. added a comment -

        Tried the patch and it works on SQL Server 2005 as well. Lots of fun trying to find it. Thanks for the fix.

        Show
        Brian B. added a comment - Tried the patch and it works on SQL Server 2005 as well. Lots of fun trying to find it. Thanks for the fix.

          People

          • Assignee:
            Jonathan H. Wage
            Reporter:
            aiso haikens
          • Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: