Lukas Eder created DERBY-6953:
---------------------------------
Summary: Support the SQL Standard
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)
