<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Hi guys,<br>
<br>
First time posting to this mailing list, so please be patient with me.<br>
<br>
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 <code>INSERT</code> statement").<br>
<br>
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)?<br>
<br>
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).<br>
<br>
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.<br>
<br>
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 <span
class="moz-smiley-s4"><span> :-P </span></span><br>
<br>
<br>
</body>
</html>