Hi.
I'm working on applications which sometimes perform cascaded insertions into
database tables. By "cascaded insertions" I mean that there is often
situation, when an application inserts into table A, then into child table B
(B has a foreign key referencing table A) and so on.
Frequent situation is that one of the primary key attributes is generated
automaticaly (using a sequence, for instance). Consequently, in order to
insert into a child table I first have to retrieve generated key.
JDK 1.4 introduces this possibility in
java.sql.Statement.getGeneratedKeys(). If previously this statement was used
for an update operation, for instance, something like
statement.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
or
int[] columnIndexes = {1, 2, 3};
statement.executeUpdate(sql, columnIndexes);
was invoked, getGeneratedKeys() would return result set with required keys
or columns.
This effectively solves problems with cascaded insertions. Otherwise
solutions are very very awkward.
Currently, PostgreSQL JDBC driver does not support these new methods. My
question is if this support is planned and when?
There is a workaround possibility in PostgreSQL, though.
There is getLastOID() method in org.postgresql.Statement. After insertion
one may retrieve OID of inserted row and then, knowing the table, select
required fields.
Something like
statement.executeUpdate("INSERT INTO TEST (value) VALUES ('x')");
ResultSet resultSet = statement.executeQuery("SELECT * FROM TEST WHERE oid =
" + statement.getLastOID());
It looks like a way to implement getGeneratedKeys(). But what puzzles me is
how to retrieve name of the table. Is there any possibility to get table
name by tuple OID, which this table has (not browsing all the tables, of
course)? Or shoud I analyze SQL query?
Thanks in advance, any help will be appreciated.
ps. I want to stick to JDBC interfaces as much as possible.
Dipl.-Inform. Alexei Valikov e-mail : valikov@fzi.de
Forschungszentrum Informatik (FZI) Telefon : (+49) 721 9654 716
Haid-und-Neu-Str. 10-14 Fax : (+49) 721 9654 709
76131 Karlsruhe
GERMANY