db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (DERBY-6953) Support Standard <data change delta table> syntax for retrieving INSERTed key values
Date Sun, 23 Jul 2017 01:11:02 GMT

     [ https://issues.apache.org/jira/browse/DERBY-6953?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Rick Hillegas updated DERBY-6953:
---------------------------------
    Summary: Support Standard <data change delta table> syntax for retrieving INSERTed
key values  (was: Support the SQL Standard)

> Support Standard <data change delta table> syntax for retrieving INSERTed key values
> ------------------------------------------------------------------------------------
>
>                 Key: DERBY-6953
>                 URL: https://issues.apache.org/jira/browse/DERBY-6953
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>    Affects Versions: 10.13.1.1
>            Reporter: Lukas Eder
>
> The SQL standard supports an interesting syntax that can be used as a <table reference>:
> {code}
> <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
> {code}
> 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.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

Mime
View raw message