Todo: Identity needs work....

[mikepizzo]

Current escape syntax for getting/using the identity column(s) needs work.

1) Do we to support the client specifying the key field(s), or can the driver always determine the fields (in which case we could just select into a variable representing the row and then support selecting from that): INSERT INTO table VALUES (1,2,3) {return identity()}; SELECT id, total from table where {identity()} 2) How does this work for multi-part keys? this is easier if client doesn't have to specify identity columns... 3) Should we support arrays of keys for array insert?

For reference: Oracle has RETURNING INTO Postgres has INSERT...RETURNING DB2 has SELECT * FROM FINAL TABLE (INSERT...) SQL Server has OUTPUT

[mikepizzo]

Native query examples:

SQLServer: 
    INSERT [<top-clause>] INTO <target-expression> OUTPUT inserted.* <value-expression>
    UPDATE [top-clause] tablename SET update-expression> OUTPUT updated.* [<where-expression>]
    DELETE [<top-clause>] FROM  <target-expression> OUTPUT deleted.* [<where-expression>]
Oracle:
    INSERT INTO <target-expression> <value-expression> RETURNING … [BULK COLLECT] INTO <variables>
    UPDATE tablename SET <update-expression> [<where-expression>] RETURNING … [BULK COLLECT] INTO  <variables>
    DELETE FROM <target-expression> [<where-expression>] RETURNING … [BULK COLLECT] INTO <variables>
Postgres
    INSERT INTO <target-expression> <value-expression> RETURNING ...
    UPDATE tablename SET<update-expression> [<where-expression>] RETURNING…
    DELETE FROM <target-expression> [<where-expression>] RETURNING ...
DB2
    SELECT * FROM FINAL TABLE (<insert-statement>|<update-statement>|<delete-statement>)

Issues: 1) Some select into host variables, others return results. 2) All support returning multiple rows, Oracle supports returning a single-row through a different syntax 3) The clause to specify what to return appears in different places -DB2 puts it at the beginning -Postres/Oracle put it at the end -SQL Server puts it in the middle 4) SQL Server has a "top clause" that can go before the into specification

For 1) ODBC doesn't define a common syntax for host variables, so we should return results. Those that return host variables can append a select from those host variables. For 2) since returning multiple rows is more general, the Oracle driver can generate a BULK COLLECT into a temporary table and select from that. For 3) We can either: a) limit use of returning to the simple insert/update/delete syntaxes defined in the standard grammar, b) require that the driver parse the vendor-specific Insert/Update/Delete statement to figure out where to put the clause that defines what to return, or c) define an escape syntax that separates the target expression from the source expression (for insert) and search clause (for update/delete)

Something like:

{return select-list from (
        INSERT INTO {vendor-insert-target-expression} vendor-insert-source-expression} 
    | UPDATE tablename SET {vendor-update-expression} [WHERE vendor-search-clause]
    | DELETE FROM tablename [WHERE vendor-search-clause]
)}

Examples:

    {return c1 from (INSERT INTO {t1 (c1,c2,c3)} values (1,2,3)) }
    {return c1 from (UPDATE t1 SET {c1 = 10} where c1=1)}
    {return c1 from (DELETE FROM t1 where c1=1)}

For 4) We can either restrict using top with returning or further separate the parts of the escape clause

[mikepizzo]

For 3) we should be able to do b) and require the driver figure out where to put the vendor-specific clause to return data; it should be easy enough in all cases to determine.

We should include GetInfo that lets the driver return support for either getting only the key, or any arbitrary columns, back from INSERT, UPDATE, or DELETE, and perhaps differentiate between a single row or collection of rows (at least for INSERT).

We may want to have a slightly different syntax for saying "just return the key fields" if there are scenarios where the client wouldn't know what to put in the select-list.

[mikepizzo]

Consider syntax such as: {return {id} from (UPDATE table SET {amount=amount*10} WHERE id > 10)}

where the driver would return whatever key/rowid values used to identify the row.

And add to the getinfo: Supports retrieving the set of columns/rowid values that uniquely identify the inserted, updated, or deleted row.

Literally, a shorthand for writing out the key columns. can be combined with column names, as in: {return {id}, total...

If no columns uniquely identify, or specific named columns don't exist, error should be the same as for an unknown column in a select list of a regular select. The updates must not be performed.

Driver not capable if a column is valid but cannot be returned. Note that drivers might return the invalid column in select list for this case as well.