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

    • Sub-task
    • Status: Closed
    • Blocker
    • Resolution: Won't Fix
    • 2.0
    • 2.0
    • Database SQL/XMLDB
    • None
    • MOODLE_20_STABLE
    • 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

              stronk7 Eloy Lafuente (stronk7)
              pcecere Pablo Cecere
              Andrew Lyons, Huong Nguyen, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze, Stevani Andolo
              Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:
                24/Nov/10