Thread: Re: JDBC and fetching the OID of an insert

Dear Michael,

    I asked this question a little while back too. :) I'm sure it is a
question that comes up quite often.
    The simple answer that I got is to do:

SELECT nextval(columnName);
INSERT INTO tableName (columnName) VALUES ([result of first query]);

    through JDBC calls. If the insert fails, nothing is lost. No real
need for a transaction here.
    I cannot speak on whether or not getGeneratedKeys() is
supported/implemented in the JDBC driver.
    Yes, you are correct in assuming that the only way to get a value
for a sequence via JDBC is to "SELECT currval('...')" resp. "SELECT
nextval('...')".

Sincerely,

    Daryl.


> -----Original Message-----
> From: Michael Paesold [mailto:mpaesold@gmx.at]
> Sent: Wednesday, September 18, 2002 9:37 AM
> To: pgsql-jdbc@postgresql.org
> Subject: [JDBC] JDBC and fetching the OID of an insert
>
>
> Hi,
>
> Does anyone of you know, how to get the OID of the just
> inserted Row after
> stmt.executeUpdate("INSERT... ")? I hope this is possible at all!
>
> If it's not, I would be willing to implement
> getGeneratedKeys() to return at
> least the OID of an insert. Assuming that would be possible
> and exceptable?
>
> (Btw. am I correct that the only way to get a value for a
> sequence via JDBC
> is to "SELECT currval('...')" resp. "SELECT nextval('...')"?)
>
>
> Best Regards,
> Michael
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

Hi Daryl,
Hi all,

> I asked this question a little while back too. :) I'm sure it is a
> question that comes up quite often.
> The simple answer that I got is to do:
>
> SELECT nextval(columnName);
> INSERT INTO tableName (columnName) VALUES ([result of first query]);
>
> through JDBC calls. If the insert fails, nothing is lost. No real
> need for a transaction here.

Well, so I can't use OIDs but only SEQUENCES, at least with JDBC.

> I cannot speak on whether or not getGeneratedKeys() is
> supported/implemented in the JDBC driver.

> public ResultSet getGeneratedKeys()
as well as corresponding methods like
> public int executeUpdate(String sql, String columnNames[])
are new in JDBC3.

They are not yet implemented in the PostgreSQL JDBC driver (I had a look at
the code in CVS).

To all the JDBC developers: do you think it would be possible to implement
these?
I would offer my help.

What I am thinking of is:
- do the insert
- get the OID of the inserted row (should be possible inside the driver,
right?)
- fetch the values of those fields that where requested

At least this would not require any help from the backend. On the other hand
it would still leave the question open how to decide what is considered a
generated key. I will have a look at the JDBC specs.

Best Regards,
Michael