Thread: JDBC API Tests

JDBC API Tests

From
Al Sutton
Date:
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

JDBC and fetching the OID of an insert

From
"Michael Paesold"
Date:
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


Re: JDBC and fetching the OID of an insert

From
Dave Cramer
Date:
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
>
>




Re: JDBC and fetching the OID of an insert

From
"Michael Paesold"
Date:
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



Re: JDBC and fetching the OID of an insert

From
Dave Cramer
Date:
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
>
>



Implementing JDBC3 methods (Was: JDBC and fetching the OID of an insert)

From
"Michael Paesold"
Date:
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



Re: Implementing JDBC3 methods (Was: JDBC and fetching the

From
Dave Cramer
Date:
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
>
>
>



Re: Implementing JDBC3 methods (Was: JDBC and fetching theOID of an insert)

From
"Michael Paesold"
Date:
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.


Re: Implementing JDBC3 methods (Was: JDBC and fetching

From
Dave Cramer
Date:
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)
>
>



Re: Implementing JDBC3 methods (Was: JDBC and fetching

From
Dave Cramer
Date:
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.
>
>



Re: Implementing JDBC3 methods (Was: JDBC and fetching

From
"Ross J. Reedstrom"
Date:
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

Re: Implementing JDBC3 methods (Was: JDBC and fetching

From
Dave Cramer
Date:
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
>
>