Autonumber fields in JDBC

Thread view:

Neil Barnwell - 04 Jul 2004 23:54 GMT

Hi, all.

I'm using Java and JDBC to connect to an Access database. This is only a
sample application I'm writing to get used to things (I'm moving to SQL
Server once I get round to bringing the disk home lol).

The thing I want to know is, how do I find out what the new value of an
autonumber field is when I add a new record?

I have a table:

empno - autonumber
surname - text
forename - text
deptno - text

I've tried using various combinations of:

"Statement.executeUpdate(String arg0, int[] arg1)",
"Statement.getGeneratedKeys()",
"Statement.getRecordSet()".

but all to no avail - I get "java.lang.UnsupportedOperationException". Is
this because Access or the "sun.jdbc.odbc.JdbcOdbcDriver" JDBC driver I'm
using don't support this? Does SQL Server or Oracle support something like
this? I know that in Oracle there's no such thing, and that the only way to
replicate an autonumber field is to have a bit of PL/SQL in a trigger, but
it's the java bit that I'm stuck on.

Either I'm on the wrong track, or I'm not implementing this correctly,
either way - I could do with your help.

Cheers,

<Barney /

Reply to this Message

Chris Smith - 05 Jul 2004 03:43 GMT

> I've tried using various combinations of:
>
[quoted text clipped - 3 lines]
>
> but all to no avail - I get "java.lang.UnsupportedOperationException".

Well, getGeneratedKeys() is "the" way to do it. Unfortunately, this is
far from universally implemented by database systems, especially since
many of them don't, at their core, distinguish between auto-increment
columns and any other kind of default value. I don't know if Oracle or
SQL Server implement this method in their drivers.

In the absense of getGeneratedKeys(), the options include:

a. Generate keys prior to insert (with something like a sequence,
whether it's explicit or simulated with a table).
b. Run a select using other unique information in the row to
discover the ID.

Signature

www.designacourse.com
The Easiest Way to Train Anyone... Anywhere.

Chris Smith - Lead Software Developer/Technical Trainer
MindIQ Corporation

Reply to this Message

hilz - 05 Jul 2004 06:24 GMT

I am not sure if this is supported in the jdbcodbc driver, but it is worth
the try, and it should be compatible with the access autonumber and the
sqlserver trigger generated ids

Connection databaseConnection = .....

ResultSet rs;

Statement stmt = databaseConnection.createStatement(
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE
);

rs = stmt.executeQuery("SELECT table_name.* FROM table_name"

rs.moveToInsertRow();

rs.insertRow();

and then get the automatically generated key by rs.getInt or whatever it was

oh, and why, but Why the cross-posting?
hilz

Reply to this Message

Neil Barnwell - 07 Jul 2004 22:40 GMT

Thanks very much - I'll try it A.S.A.P.

Sorry 'bout the "cross-posting" - what's the alternative?

<Barney />

> I am not sure if this is supported in the jdbcodbc driver, but it is worth
> the try, and it should be compatible with the access autonumber and the
[quoted text clipped - 19 lines]
> oh, and why, but Why the cross-posting?
> hilz

Reply to this Message

Andrew Thompson - 08 Jul 2004 01:49 GMT

> Sorry 'bout the "cross-posting" - what's the alternative?

Usually I discourage crossposting myself, but as
I reread *this* thread, it does seem justifiable
for you to cross-post it to the 2 groups as you did.

I suppose it's down to 'people are different',
though it really seemed more a casual question
than an accusation, as I read it.. (shrugs)

Another thing I might ask of you though, is
that you place your comments after the immediate
part of what you are replying to, and trimming
excess stuff from prior posts 'bottom-posting'..
<http://www.physci.org/codes/javafaq.jsp#netiquette>

Hope you get your problem sorted.

Signature

Andrew Thompson
http://www.PhySci.org/ Open-source software suite
http://www.PhySci.org/codes/ Web & IT Help
http://www.1point1C.org/ Science & Technology

Reply to this Message

Autonumber fields in JDBC

Free Magazines