Hi guys,

First time posting to this mailing list, so please be patient with me.

I have a need for the Connection.prepareStatement(String sql, int autoGeneratedKeys) method to be functional. Obviously, such keys would only ever be generated during an INSERT (as per the Java API specification: "This parameter is ignored if the SQL statement is not an INSERT statement").

Unless someone strongly objects, I am going to be looking into coding such functionality. I do have several questions, though. Now, I personally have only ever seen INSERT statements for a single table. Is it even vaguely possible for an INSERT statement to directly affect more than one table (by directly, I mean excluding any tables which may be affected by a trigger)?

If not, then what I am proposing is for the Connection.prepareStatement() method to use regular expression parsing (via the java.util.regex package) to extract the table name being inserted into, and querying the system tables to check for an int (or bigint) columns which contain a default which refers to the "NEXTVAL" function, and which do NOT form part of the actual insert statement (i.e. the default value will be inserted, hence using the sequence number system).

I would obviously need to create a special PreparedStatement implentation which would store these column names. Upon execution of this prepared statement, the class would then internally execute all "CURRVAL()" queries for the columns known to have been autogenerated.

So, I'm thinking that someone is probably wondering why the heck I want to do this? Surely I could just code the "CURRVAL()" queries right within my program. Well, my motivation is that the company I am working for is trying to move our systems from a MySQL database to PostgreSQL. Many of the data insertion areas on the system use the auto-generated-keys resultset feature, and I personally don't want to fish through all the programs and change them. Hehehe - lazy dude :-P