Autonumber fields in JDBC

[Neil Barnwell]

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 />

 

[Chris Smith]

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".

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.

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

Chris Smith - Lead Software Developer/Technical Trainer
MindIQ Corporation

 

[hilz]

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

 

[Neil Barnwell]

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

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

<Barney />

 

[Andrew Thompson]

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.

 

[yeah]

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?

If the JDBC mechanism isn't supported you can always fall back on the
non-portable db-specific way if that's an option. For MS SQL Server
for instance, "SELECT @@IDENTITY" will give you the last auto-id
generated by your connection.

For MySQL it would be "SELECT last_insert_id()".