Re: Proposal: RETURNING primary_key()

On 3/8/2016 4:42 PM, Craig Ringer wrote:
> On 9 March 2016 at 05:40, Igal @ Lucee.org <igal(at)lucee(dot)org
> <mailto:igal(at)lucee(dot)org>> wrote:
>
> I will try to gather more information about the other DBMSs and
> drivers and will post my findings here when I have them.
>
>
> Thanks. I know that's not the most fun thing to do in the world, but
> it's often needed when implementing something where part of the goal
> is being compatible with other vendors, etc.

It seems that the implementations vary by the driver, and not the
server, as evidenced by the Microsoft SQL Server drivers -- I tested
both the official MS driver and the open sourced jTDS driver.

I noticed that you usually don't put html in the emails here, but I
think that it's appropriate here to show the information in a clear way
(also, according to my computer it's 2016). I hope that it will be
rendered properly:

*MySQL* *DB2* *SQL Server (MS)* *SQL Server (jTDS)* *Oracle*
*Returned Type* SET SET ROW ROW ROW
*Column Name* GENERATED_KEY [name of identity col] GENERATED_KEYS
ID ROWID
*Column Type* Unknown (numeric) integer numeric numeric ROWID
*Value* Each inserted value to identity column Each inserted value to
identity column Last inserted value to identity column Last inserted
value to identity column internal address location that does not change
on UPDATE
*Example* (1), (2) (1), (2) (2) (2) AAAE5nAABAAALCxAAM

Some notes and observations:

It's the Wild West! Each implementation does something completely
different. Even when something looks similar, e.g. the returned column
name from MySQL and SQL Server (MS), it's not: notice the plural in SQL
Server's column name, which is ironic as they only return a single
value, as opposed to MySQL which returns a SET.

This has been an "interesting experience" as it was my first exposure to
some of those DBMSs. It only reinforced my decision to choose PostgreSQL
moving forward, over the alternatives (after using SQL Server for about
20 years).

More notes on the different DBMSs:

The first thing that I tested was against *MySQL*:

CREATE TABLE IF NOT EXISTS test_jdbc(name VARCHAR(64), id SERIAL);

An insert to that table via JDBC, with int flag RETURN_GENERATED_KEYS
returns a result set with a column named "GENERATED_KEY " and type
"UNKNOWN" (as per ResultSetMetaData's getColumnTypeName()), each row in
the result set corresponded with an inserted record, so for example:

INSERT INTO test_jdbc(name) VALUES ('JDBC'), ('PostgreSQL');

returned two rows with the value of the "id" column for the inserted row
in each, e.g.

GENERATED_KEY
-------------
7
8

Trying to add multiple SERIAL columns to a table results in an error:

CREATE TABLE IF NOT EXISTS jdbc(j_name VARCHAR(64), j_id SERIAL,
id2 SERIAL)

Error Code: 1075. Incorrect table definition; there can be only one auto
column and it must be defined as a key

*SQL Server*: via the Microsoft driver

Created table with the command:

CREATE TABLE dbo.jdbc (
j_name varchar(64) NOT NULL,
j_id int IDENTITY(1,1) NOT NULL
)

Generated Keys return a single row with a column named "GENERATED_KEYS"
of type numeric, and the value is the last inserted id (i.e. sequence).
This is different from MySQL which returns a row with the id for each
inserted record.

*SQL Server*: via the jTDS driver

Generated Keys return a single row with a column named "ID" of type
numeric, and the value is the last inserted id (i.e. sequence). The
behavior is similar to the Microsoft driver, but the column name is
different.

*Oracle*:

Oracle returns the column ROWID which is of type ROWID as well:
https://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns008.htm

This seems to be similar to PostgreSQL's ctid, but unlike ctid -- when I
UPDATE the record the ROWID remains unchanged.

In my test I got the value "AAAE5nAABAAALCxAAM", and when I later ran:

SELECT * FROM jdbc WHERE ROWID='AAAE5nAABAAALCxAAM';

I got the information back from that row. Updating that row does not
change its ROWID.

When I tried to insert multiple values with RETURN_GENERATED_KEYS I got
an error: java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL
statement

INSERT INTO jdbc(j_name) SELECT 'PG 9.5.0' FROM DUAL UNION SELECT
'PG 9.5.1' FROM DUAL

The rows are, however, inserted into the table. Running the same INSERT
command without RETURN_GENERATED_KEYS works without error.

(Side note: This was my first, and hopefully my last, experience with
Oracle database, and it's been a real PITA. If I had tried it out some
20 years ago then the experience would have probably led me to sell the
stock short, which would have probably ended with my bankruptcy. Go
figure...)

*IBM DB2*:

CREATE TABLE jdbc(j_name VARCHAR(64), j_id INT NOT NULL GENERATED
ALWAYS AS IDENTITY)

Generated Keys return a set with the column named "J_ID" of type
integer. One row for each inserted row.

(Side note: after wasting almost a full day setting up and connecting to
the DB2 server I realized why Oracle was so successful)