Re: JDBC gripe list - Mailing list pgsql-jdbc

On Tue, Mar 29, 2011 at 11:21 AM, MauMau <maumau307@gmail.com> wrote:
> 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
>

Well we already have a workable solution to this, we simply return all
the columns in this case

Dave