Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-6953

Support Standard <data change delta table> syntax for retrieving INSERTed key values

    XMLWordPrintableJSON

    Details

    • Type: Improvement
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.13.1.1
    • Fix Version/s: None
    • Component/s: SQL
    • Labels:
      None

      Description

      The SQL standard supports an interesting syntax that can be used as a <table reference>:

      <data change delta table> ::=
        <result option> TABLE <left paren> <data change statement> <right paren>
      
      <data change statement> ::=
          <delete statement: searched>
        | <insert statement>
        | <merge statement>
        | <update statement: searched>
      
      <result option> ::=
          FINAL
        | NEW
        | OLD
      

      This is currently supported by DB2. Databases like Firebird, Oracle (in PL/SQL), PostgreSQL support an alternative syntax through the RETURNING keyword that can be appended to <data change statement>. SQL Server has an OUTPUT keyword that can be placed in the middle of a <data change statement>.

      These statements are incredibly useful to retrieve generated ID values but also trigger-generated values after a DML operation for an arbitrary number of inserted / updated / deleted / merged rows.

      It would allow people to bypass the many problems that are currently still open related to Statement.getGeneratedKeys(). Quite likely, if these clauses were made available, Statement.getGeneratedKeys() could be implemented by patching the user-defined SQL to be wrapped with a <data change delta table> clause.

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              lukas.eder Lukas Eder
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated: