Re: JDBC gripe list

Hello,


----- Original Message -----
From: "Dave Cramer" <pg@fastcrypt.com>
> So in general I'm having trouble understanding things where the
> implementation says we can do whatever we want you have a requirement
> ? For example getGeneratedKeys from a batch statement? What is it you
> are looking for ? AFAICT we are implementing the spec here. The fact
> that mysql does something else is irrelevant. Please note this is just
> a talking point since I do believe it is possible for us to return the
> generated keys here.

I think the most common use case is, or possibly the only real use case is,
to get auto-generated primary keys when you insert multiple rows at once
into a table which has a sequence. An example is shown in "14.1.4
PreparedStatement Objects" of JDBC 4.0 specification. The following code is
a slightly modified version of that example to use auto-generated keys.

CODE EXAMPLE 14-2 Creating and executing a batch of prepared statements
(slightly modified)
--------------------------------------------------
String[] cols = {"emp_id"};

// turn off autocommit
con.setAutoCommit(false);

PreparedStatement stmt = con.prepareStatement(
"INSERT INTO employees VALUES (?, ?)", cols);

stmt.setInt(1, 2000);
stmt.setString(2, "Kelly Kaufmann");
stmt.addBatch();

stmt.setInt(1, 3000);
stmt.setString(2, "Bill Barnes");
stmt.addBatch();

// submit the batch for execution
int[] updateCounts = stmt.executeBatch();
--------------------------------------------------


Though the table definition is not shown in the spec, assume the one below:

CREATE TABLE employees (
    emp_id SERIAL PRIMARY KEY,
    salary INT,
    name VARCHAR(20)
);

Then, pstmt.getGeneratedKeys() is expected to return a ResultSet that
contains two rows which consist only of emp_id column. The order of those
two rows should correspond to the order of employee records added in the
batch with addBatch().

My opinion is that the users should explicitly specify the columns they want
to be returned. I don't find it useful to specify
Statement.RETURN_GENERATED_KEYS, because the JDBC spec says that JDBC
drivers decide which columns are returned (i.e. implementation defined
behavior). Please keep this in mind when reading the next.

So which columns should be returned when you pass
Statement.RETURN_GENERATED_KEYS instead of "cols" to
Connection.prepareStatement() in the above example code? What if the table
does not have an auto-generated primary key?

I think it would be reasonable to return "ctid" system column when the user
specifies Statement.RETURN_GENERATED_KEYS, because he/she does not show any
intention about what he/she wants. For example, Oracle returns ROWID in this
case, as described as follows in the manual:

--------------------------------------------------
The getGeneratedKeys() method enables you to retrieve the auto-generated key
fields. The auto-generated keys are returned as a ResultSet object.
If key columns are not explicitly indicated, then Oracle JDBC drivers cannot
identify which columns need to be retrieved. When a column name or column
index array is used, Oracle JDBC drivers can identify which columns contain
auto-generated keys that you want to retrieve. However, when the
Statement.RETURN_GENERATED_KEYS integer flag is used, Oracle JDBC drivers
cannot identify these columns. When the integer flag is used to indicate
that auto-generated keys are to be returned, the ROWID pseudo column is
returned as key. The ROWID can be then fetched from the ResultSet object and
can be used to retrieved other columns.
--------------------------------------------------

What do you think?

Regards
MauMau