Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-14679 META: DB layer 2.0
  3. MDL-17794

ADOdb ODBC Insert_ID() function under MSSQL doesn't work

    XMLWordPrintable

    Details

    • Type: Sub-task
    • Status: Closed
    • Priority: Blocker
    • Resolution: Won't Fix
    • Affects Version/s: 2.0
    • Fix Version/s: 2.0
    • Component/s: Database SQL/XMLDB
    • Labels:
      None
    • Affected Branches:
      MOODLE_20_STABLE
    • Fixed Branches:
      MOODLE_20_STABLE

      Description

      Was implementing the MSSQL when I discovered that:

      • inserting records using placeholders AND
      • retrieving the last id with Insert_ID()

      are completely broken!!

      Explanation from: http://docs.moodle.org/en/Development:dmllib_2.0_problems

      Problem: Queries using placeholders under MSSQL are executed by one sp_executesql() procedure call. In MSSQL procedures, triggers are executed into new scopes (like "shells"), so the Insert_ID() function (internally usind SCOPE_IDENTITY()) doesn't work at all, because the insert has been executed in another scope.

      Solution: Patch ADOdb driver so, each time one INSERT is going to be executed by sp_executesql(), we'll add one SCOPE_IDENTITY() in the same procedure execution (that way, both the insert and the identity will be in the same scope). Once executed, return the value of the identity by the standard Insert_ID() function. BC compatible 100% with current code if implemented that way.

        Attachments

          Issue Links

            Activity

              People

              • Votes:
                0 Vote for this issue
                Watchers:
                0 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Fix Release Date:
                  24/Nov/10