1

I'm trying to retreive the connection_id column while inserting into Connections table. I have a table being generated at the start of the application like this with the column_id being auto generated.

CREATE TABLE connections (connection_ID INT not null primary key GENERATED ALWAYS AS IDENTITY(START WITH 1, INCREMENT BY 1), 
connection_name VARCHAR(100) unique not null, user_name VARCHAR(100) not null, password VARCHAR (100) not null, server_url 
VARCHAR(100) not null, port_number VARCHAR(10) not null),

I'm using the following statement to retrieve the liat of connection_ids 

PreparedStatement stmt = dbconn.prepareStatement(insertString, new String[] {"connection_id"});

But I get the following exception even though my connection_id is auto-generated,

java.sql.SQLException: Table 'CONNECTIONS' does not have an auto-generated column named 'connection_id'.
Sam
  • 2,702
  • 5
  • 31
  • 45
  • Which database are you using? I've seen some databases that do not support auto-incrementing ID's in this fashion. That might explain this result. – Ewald Oct 27 '11 at 10:09
  • 1
    I'm using "Java DB" and the auto generate works if I take out the new String[] {Connection ID} and the getGeneratedKeys() functions off the code! – Sam Oct 27 '11 at 10:14
  • Does it work with connection_ID and/or CONNECTION_ID? Maybe Java DB treats it case-sensitive – Mark Rotteveel Oct 27 '11 at 11:21
  • No, I've tried all possible combinations but it simple doesn't work! – Sam Oct 27 '11 at 13:02

2 Answers2

0

Use the PreparedStatement.RETURN_GENERATED_KEYS instead of new String[] {"ID_colname"} - see a_horse_with_no_name's answer to this question:

Retrieve id of record just inserted into a Java DB (Derby) database

For example:

PreparedStatement ps = connection.prepareStatement("insert .... ", PreparedStatement.RETURN_GENERATED_KEYS);
ps.executeUpdate();
ResultSet results = ps.getGeneratedKeys();

I have just had the same problem (8 years later!) and 'this works for me'.

I don't know why the new String[] {"ID_colname"} doesn't work, but at least there is this alternative to try.

MartinMlima
  • 1,736
  • 1
  • 12
  • 13
0


I do not have much experience with Java DB, but I hope this will help.
Maybe what you need is to make the auto generated keys returnable. Check the Java DB Reference Manual and the Connection class definition

skw
  • 516
  • 7
  • 19