Thread: JDBC API Tests

All,

I've been looking to see if anyone has been carrying out the Sun JDBC API Test Suite, and the only reference I've found
isto an old version ( as opposed to the current 1.3.1). 

I have PostgreSQL 7.2.1 setup with the pg73b1jdbc2 drivers and the can't get the databases to initialise due to tinyint
beingan unknown type. 

What I would like to know is;

a) Is running the tests useful for anyone other than myself?

b) Are there plans to test the JDBC drivers against the test suite?

Thanks,

Al.
--
Al Sutton
al@alsutton.com

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

There is or was actually a method in the driver ResultSet.getLastOID
which allowed you to do this.You will have to cast the resultset into a
AbstractJdbc(x)ResultSet, where x is the version; but Daryl's way is
much better since oid's can wrap, and there is a possiblity that they
don't exist ( you can turn them off in postgres).

Dave

On Wed, 2002-09-18 at 09:36, Michael Paesold wrote:
> 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
>
>

Dave Cramer wrote:

> There is or was actually a method in the driver ResultSet.getLastOID
> which allowed you to do this.You will have to cast the resultset into a
> AbstractJdbc(x)ResultSet, where x is the version; but Daryl's way is
> much better since oid's can wrap, and there is a possiblity that they
> don't exist ( you can turn them off in postgres).
>
> Dave

I don't really care that oid's can wrap. I don't need them as sequences.
Instead I was just looking for the easiest way to get a reference to the row
I just inserted.

So my considerations about getGeneratedKeys() would only work if oid's are
not turned off -- very bad for a driver...

Michael

Michael,

Well, the method in the driver will work for you.

dave
On Wed, 2002-09-18 at 10:21, Michael Paesold wrote:
> Dave Cramer wrote:
>
> > There is or was actually a method in the driver ResultSet.getLastOID
> > which allowed you to do this.You will have to cast the resultset into a
> > AbstractJdbc(x)ResultSet, where x is the version; but Daryl's way is
> > much better since oid's can wrap, and there is a possiblity that they
> > don't exist ( you can turn them off in postgres).
> >
> > Dave
>
> I don't really care that oid's can wrap. I don't need them as sequences.
> Instead I was just looking for the easiest way to get a reference to the row
> I just inserted.
>
> So my considerations about getGeneratedKeys() would only work if oid's are
> not turned off -- very bad for a driver...
>
> Michael
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>

Dave,

So do you think it would be a very bad idea to implement the new methods in
JDBC3 in a way that depends on OID's? Is there any other way to get the
information about the just inserted generated keys from the backend?

Michael

Dave Cramer wrote:

> Michael,
>
> Well, the method in the driver will work for you.
>
> dave
> On Wed, 2002-09-18 at 10:21, Michael Paesold wrote:
> > Dave Cramer wrote:
> >
> > > There is or was actually a method in the driver ResultSet.getLastOID
> > > which allowed you to do this.You will have to cast the resultset into
a
> > > AbstractJdbc(x)ResultSet, where x is the version; but Daryl's way is
> > > much better since oid's can wrap, and there is a possiblity that they
> > > don't exist ( you can turn them off in postgres).
> > >
> > > Dave
> >
> > I don't really care that oid's can wrap. I don't need them as sequences.
> > Instead I was just looking for the easiest way to get a reference to the
row
> > I just inserted.
> >
> > So my considerations about getGeneratedKeys() would only work if oid's
are
> > not turned off -- very bad for a driver...
> >
> > Michael

Michael,

What do you see as a generatedKey? you mean a serial type?

if you are writing for the driver then you can easily get the last
inserted oid

Dave
On Wed, 2002-09-18 at 10:58, Michael Paesold wrote:
> Dave,
>
> So do you think it would be a very bad idea to implement the new methods in
> JDBC3 in a way that depends on OID's? Is there any other way to get the
> information about the just inserted generated keys from the backend?
>
> Michael
>
> Dave Cramer wrote:
>
> > Michael,
> >
> > Well, the method in the driver will work for you.
> >
> > dave
> > On Wed, 2002-09-18 at 10:21, Michael Paesold wrote:
> > > Dave Cramer wrote:
> > >
> > > > There is or was actually a method in the driver ResultSet.getLastOID
> > > > which allowed you to do this.You will have to cast the resultset into
> a
> > > > AbstractJdbc(x)ResultSet, where x is the version; but Daryl's way is
> > > > much better since oid's can wrap, and there is a possiblity that they
> > > > don't exist ( you can turn them off in postgres).
> > > >
> > > > Dave
> > >
> > > I don't really care that oid's can wrap. I don't need them as sequences.
> > > Instead I was just looking for the easiest way to get a reference to the
> row
> > > I just inserted.
> > >
> > > So my considerations about getGeneratedKeys() would only work if oid's
> are
> > > not turned off -- very bad for a driver...
> > >
> > > Michael
>
>
>

Dave Cramer wrote:

> Michael,
>
> What do you see as a generatedKey? you mean a serial type?

I think so. I just read over the JDBC specification. It is not very
specific, about what is considered a generated key. I suppose it would be
any unique key (or field value?) that is automatically generated by the data
source / database. In postgresql, this could be a SERIAL, a field with a
default value from a SEQUENCE, of even an OID. This *could* also be a value
supplied by a user-defined trigger, depends on the point of view.

The chapter about auto generated keys in the JDBC 3.0 specification (chapter
13.6) start like this:
"Many database systems have a mechanism that automatically generates a
unique key field when a row is inserted."
They also have an example where they insert into an order table, the order
id is "generated".

I think OIDs should also be classified as a generated key, they are unique
keys, identifying the inserted row. With OIDs it's quite easy, I think. But
what about serials (or sequence fields)? How difficult would it be to decide
wether a column/field is auto generated?

>> int executeUpdate(String sql, int autoGeneratedKeys)
for this prototype it is hard to guess, what the programmer wants...

>> int executeUpdate(String sql, String[] columnNames)
whereas here the caller can specify, what they would like to have returned.
It depends on the driver if it can/will supply the value for the columns,
though.

> if you are writing for the driver then you can easily get the last
> inserted oid

Right, I had a look at the code.

Regards,
Michael

P.S. When reading my emails, please be aware of the fact that English is not
my first language.

On Wed, 2002-09-18 at 17:43, Michael Paesold wrote:
> Dave Cramer wrote:
>
> > Michael,
> >
> > What do you see as a generatedKey? you mean a serial type?
>
> I think so. I just read over the JDBC specification. It is not very
> specific, about what is considered a generated key. I suppose it would be
> any unique key (or field value?) that is automatically generated by the data
> source / database. In postgresql, this could be a SERIAL, a field with a
> default value from a SEQUENCE, of even an OID. This *could* also be a value
> supplied by a user-defined trigger, depends on the point of view.
I would think it would have to be a sequence, and an index This should
be possible to find out through a select.
ie find if the table has a default on an index column which selects
nextval.
>
> The chapter about auto generated keys in the JDBC 3.0 specification (chapter
> 13.6) start like this:
> "Many database systems have a mechanism that automatically generates a
> unique key field when a row is inserted."
> They also have an example where they insert into an order table, the order
> id is "generated".
>
> I think OIDs should also be classified as a generated key, they are unique
> keys, identifying the inserted row. With OIDs it's quite easy, I think. But
> what about serials (or sequence fields)? How difficult would it be to decide
> wether a column/field is auto generated?
I don't agree, they aren't index's, and they aren't unique
>
> >> int executeUpdate(String sql, int autoGeneratedKeys)
> for this prototype it is hard to guess, what the programmer wants...
>
> >> int executeUpdate(String sql, String[] columnNames)
> whereas here the caller can specify, what they would like to have returned.
> It depends on the driver if it can/will supply the value for the columns,
> though.
>
> > if you are writing for the driver then you can easily get the last
> > inserted oid
>
> Right, I had a look at the code.
>
> Regards,
> Michael
>
> P.S. When reading my emails, please be aware of the fact that English is not
> my first language.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>

On Thu, 2002-09-19 at 04:17, Michael Stephenson wrote:
> Dave wrote:
> >>I think so. I just read over the JDBC specification. It is not very
> >>specific, about what is considered a generated key. I suppose it would be
> >>any unique key (or field value?) that is automatically generated by the data
> >>source / database. In postgresql, this could be a SERIAL, a field with a
> >>default value from a SEQUENCE, of even an OID. This *could* also be a value
> >>supplied by a user-defined trigger, depends on the point of view.
> >
> > I would think it would have to be a sequence, and an index This should
> > be possible to find out through a select.
> > ie find if the table has a default on an index column which selects
> > nextval.
>
> That isn't really sufficient. There doesn't seem to be anything to stop you
> inserting an explicit value into a column which is of type serial, so you need
> to know whether the sequence was actually used or not. Trying to find that the
> obvious way you hit the problem that a sequence might have been used earlier in
> this transaction in a different statement (or even elsewhere in this statement)..
That's the only way, and will have to do. One thing to realize is that
once a sequence has been used, it can't be rolled back, so it is unique.
>
> Michael
>
> --
> Web Applications Developer
> Open World Ltd, 11 Riverside Court, Riverside Road, Bath, BA2 3DZ.
> Tel: +44 1225 444950  Fax: +44 1225 336738  http://www.openworld.co.uk/
>
> CONFIDENTIALITY NOTICE
> The information contained in this message is confidential, intended only for
> the use of the individual or the entity named as recipient. If the reader of
> this message is not that recipient, you are notified that any dissemination,
> distribution or copy of this message is strictly prohibited. If you have
> received this message in error, please immediately notify us by telephone on
> the number above. Your co-operation is appreciated.
>
>

On Thu, Sep 19, 2002 at 05:25:01AM -0400, Dave Cramer wrote:

> That's the only way, and will have to do. One thing to realize is that
> once a sequence has been used, it can't be rolled back, so it is unique.

Not really - you can set it to any number at all, or even reset it,
so the first call to nextval() returns the inital value. I do this
all the time when loading data into a schema, to set the sequences for
serial columns so they don't cause errors, something like:

select setval('mytable_id_seq',max(id)) from mytable

Here's demonstrating resetting to 'virgin':

test=# create sequence testit;
CREATE
test=# select nextval('testit');
 nextval
---------
       1
(1 row)

test=# select nextval('testit');
 nextval
---------
       2
(1 row)

test=# select currval('testit');
 currval
---------
       2
(1 row)

test=# select setval('testit',1,'f');
 setval
--------
      1
(1 row)

test=# select nextval('testit');
 nextval
---------
       1
(1 row)

Ross
--
Ross Reedstrom, Ph.D.                                 reedstrm@rice.edu
Executive Director                                  phone: 713-348-6166
Gulf Coast Consortium for Bioinformatics              fax: 713-348-6182
Rice University MS-39
Houston, TX 77005

Ross,

I realize that you can do this, but that is sort of going beyond what
most ppl will do. Also you can't insert a duplicate into a serial type.

But technically you are correct.

Dave
On Thu, 2002-09-19 at 10:57, Ross J. Reedstrom wrote:
> On Thu, Sep 19, 2002 at 05:25:01AM -0400, Dave Cramer wrote:
>
> > That's the only way, and will have to do. One thing to realize is that
> > once a sequence has been used, it can't be rolled back, so it is unique.
>
> Not really - you can set it to any number at all, or even reset it,
> so the first call to nextval() returns the inital value. I do this
> all the time when loading data into a schema, to set the sequences for
> serial columns so they don't cause errors, something like:
>
> select setval('mytable_id_seq',max(id)) from mytable
>
> Here's demonstrating resetting to 'virgin':
>
> test=# create sequence testit;
> CREATE
> test=# select nextval('testit');
>  nextval
> ---------
>        1
> (1 row)
>
> test=# select nextval('testit');
>  nextval
> ---------
>        2
> (1 row)
>
> test=# select currval('testit');
>  currval
> ---------
>        2
> (1 row)
>
> test=# select setval('testit',1,'f');
>  setval
> --------
>       1
> (1 row)
>
> test=# select nextval('testit');
>  nextval
> ---------
>        1
> (1 row)
>
> Ross
> --
> Ross Reedstrom, Ph.D.                                 reedstrm@rice.edu
> Executive Director                                  phone: 713-348-6166
> Gulf Coast Consortium for Bioinformatics              fax: 713-348-6182
> Rice University MS-39
> Houston, TX 77005
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>