| Lists: | pgsql-jdbc |
|---|
| From: | Nathan Crause <ncrause(at)uniclear(dot)com> |
|---|---|
| To: | pgsql-jdbc(at)postgresql(dot)org |
| Subject: | PreparedStatement |
| Date: | 2004-09-25 17:05:17 |
| Message-ID: | 4155A54D.10002@uniclear.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-jdbc |
<!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>
| Attachment | Content-Type | Size |
|---|---|---|
| unknown_filename | text/html | 2.3 KB |
| From: | Kris Jurka <books(at)ejurka(dot)com> |
|---|---|
| To: | Nathan Crause <ncrause(at)uniclear(dot)com> |
| Cc: | pgsql-jdbc(at)postgresql(dot)org |
| Subject: | Re: PreparedStatement |
| Date: | 2004-09-25 19:33:07 |
| Message-ID: | Pine.BSO.4.56.0409251419080.15566@leary.csoft.net |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-jdbc |
On Sat, 25 Sep 2004, Nathan Crause wrote:
> 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").
Please see the previous discussion thread starting here:
I know you are just trying to implement something that will make your
existing application work, but we need something that will work for all
situations. There are a number of questions I raise in the thread that no
one has really answered. I know you're looking to avoid the trigger
situation, so let's leave that out for now, but:
How would you return any generated keys for a statement like this:
CREATE TABLE mytable (a serial, b text);
INSERT INTO mytable (b) SELECT (b) FROM mytable;
with multiple rows currval is useless, it can even be useless in the one
row situation (for a pretty contrived case)
CREATE SEQUENCE myseq;
CREATE TABLE mytable (a int default nextval('myseq'), b int default
nextval('myseq'), c text);
INSERT INTO mytable (c) VALUES ('hi');
When writing a library (as opposed to an application) you need to be
prepared to handle all sorts of bizarre situations. At the moment I just
don't think it's possible to make this method work for anything other than
a specific subset of situations.
Kris Jurka
| From: | Kris Jurka <books(at)ejurka(dot)com> |
|---|---|
| To: | Nathan Crause <ncrause(at)uniclear(dot)com> |
| Cc: | pgsql-jdbc(at)postgresql(dot)org |
| Subject: | Re: PreparedStatement |
| Date: | 2004-09-26 19:40:02 |
| Message-ID: | Pine.BSO.4.56.0409261431220.24677@leary.csoft.net |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-jdbc |
On Sat, 25 Sep 2004, Nathan Crause wrote:
> Surely the use of CURRVAL in general would be useless in your example
> when using a SELECT in conjunction with the INSERT? Not only that,
> considering that you cannot guarantee the ORDER in which the SELECT will
> be returning records, I don't see how ANY autogenerated key result set
> would be meaningful, regardless of database engine (unless issuing a
> ORDER BY clause in the select).
Right, this is more a problem I have with the spec. It just says it
returns a ResultSet, but it doesn't tell us anything about what it
contains or how it should work for multiple rows.
> I do agree with your ascertion that when writing a library all sorts of
> bazaar situations need to be taken into account - that's why I asked the
> question I did on my original post. That said, you are contending that no
> development at all is better than something that may solve at least some
> situations. If that is the case, then I dare say that when Postgres was
> at version 7.2, 90% of the DatabaseMetaData implementation for the JDBC
> needed to be scrapped, because it returned absolute bollocks (I haven't
> tried with the more recent releases).
>
The DatabaseMetaData got a significant upgrade for the 7.3 release. Right
now the problems I'm aware of are that it always returns JDBC2 results
even though JDBC3 has added some more columns to some results and
getTypeInfo doesn't sort correctly. If you've noticed other problems
please complain about them.
Regarding your original post, I was perhaps too harsh. I don't mean to
discourge you, I just wanted to make sure you knew what you were up
against. I don't have a problem with a partial implementation as long as
that implementation does not return incorrect results for a situation it
cannot handle. If it errors out then, that's fine, I just suspect it will
be difficult to detect these cases.
Kris Jurka
| From: | Kris Jurka <books(at)ejurka(dot)com> |
|---|---|
| To: | Nathan Crause <ncrause(at)uniclear(dot)com> |
| Cc: | pgsql-jdbc(at)postgresql(dot)org |
| Subject: | Re: PreparedStatement |
| Date: | 2004-09-28 10:40:53 |
| Message-ID: | Pine.BSO.4.56.0409280530440.15926@leary.csoft.net |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-jdbc |
On Mon, 27 Sep 2004, Nathan Crause wrote:
> I'm wondering if there is some way to make a trigger on the sequences
> themselves. Create some sort of session-specific temporary table which
> contains records of all auto-generated sequences in that session, and
> then figure out some way to return only the portion which was created by
> the last INSERT (perhaps something as simple as flushing the table after
> each resultset it returned to the connection).
>
This looks like an application kind of fix, not a library solution. A
JDBC driver can't go around creating tables and triggers and other things.
It might be worthwhile to investigate how other JDBC drivers handle this.
So far I've only looked at MySQL, but checking MaxDB and Firebird's driver
source would be revealing. Checking commercial drivers would be good too,
but I'm not sure how legal that is.
Kris Jurka
| From: | Kris Jurka <books(at)ejurka(dot)com> |
|---|---|
| To: | Nathan Crause <ncrause(at)uniclear(dot)com> |
| Cc: | pgsql-jdbc(at)postgresql(dot)org |
| Subject: | Re: PreparedStatement |
| Date: | 2004-09-28 11:28:39 |
| Message-ID: | Pine.BSO.4.56.0409280611070.15926@leary.csoft.net |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Lists: | pgsql-jdbc |
On Tue, 28 Sep 2004, Kris Jurka wrote:
> It might be worthwhile to investigate how other JDBC drivers handle this.
> So far I've only looked at MySQL, but checking MaxDB and Firebird's driver
> source would be revealing. Checking commercial drivers would be good too,
> but I'm not sure how legal that is.
>
So far I've checked MaxDB, Firebird, and Oracle. All of them throw
an Exception saying the method is unimplemented. This makes me even less
excited about throwing in a hack solution. I was just assuming that
everyone else implemented this and we needed something to "keep up" with
portable applications.
Kris Jurka