Why Invalid conversion requested ERROR CODE: 17132?

Question

I am trying to get last inserted row id while inserting using JDBC prepared statements. I have an auto increment primary key column as identity column in table. My code is below:

public static String insertMeetingToDB(String organizer,String subject,String location,String start_date_time,String end_date_time,String description) throws Exception {
    Connection dbConnection = null;
    PreparedStatement preparedStatement = null;
    Integer last_inserted_id=0;


String insertTableSQL = "INSERT INTO MEETINGS"
                   + "(ORGANIZER_EMAIL, SUBJECT, MEETING_LOCATION, START_DATE_TIME, END_DATE_TIME, MEETING_DESCRIPTION) VALUES"
                   + "(?,?,?,?,?,?)";

SimpleDateFormat from = new SimpleDateFormat("yyyyMMdd'T'HHmmss");
from.setTimeZone(TimeZone.getTimeZone("IST"));  //--CONVERTING DATE/TIME TO INDIAN STANDARD TIME

SimpleDateFormat datetimeFormat = new SimpleDateFormat("yyyy-MM-dd' 'HH:mm:ss");

Date input_start_date_val =  from.parse(start_date_time);
Date input_end_date_val =  from.parse(end_date_time);

String input_start_date =  datetimeFormat.format(input_start_date_val);
String input_end_date =  datetimeFormat.format(input_end_date_val);

try {

        dbConnection = getConnection();

        //--INSERTING MEETING DETAILS
        preparedStatement = dbConnection.prepareStatement(insertTableSQL, preparedStatement.RETURN_GENERATED_KEYS);

        preparedStatement.setString(1, organizer);
        preparedStatement.setString(2, subject); 
        preparedStatement.setString(3, location);
        preparedStatement.setTimestamp(4, java.sql.Timestamp.valueOf(input_start_date));
        preparedStatement.setTimestamp(5, java.sql.Timestamp.valueOf(input_end_date));
        preparedStatement.setString(6, description);

        // execute insert SQL stetement
        preparedStatement.executeUpdate();

        ResultSet rs = preparedStatement.getGeneratedKeys();
        if(rs.next())
        {
            last_inserted_id = rs.getInt(1);
        }

        return last_inserted_id.toString();

} catch (SQLException e) {
        return e.getMessage()+" ERROR CODE: "+e.getErrorCode();
} finally {
        if (preparedStatement != null) {
                preparedStatement.close();
        }
        if (dbConnection != null) {
                dbConnection.close();
                dbConnection = null; 
        }
  }
}

If I delete this line then I didn't got this error:

last_inserted_id = rs.getInt(1);

But after googleing it seems to be ok with this code, which should return me last inserted row id.

Table Description:

CREATE TABLE MEETINGS (
  MEETING_ID  NUMBER GENERATED ALWAYS AS IDENTITY,
  ORGANIZER_EMAIL VARCHAR2(100),
  SUBJECT VARCHAR2(250),
  START_DATE_TIME TIMESTAMP,
  END_DATE_TIME TIMESTAMP,
  ATTENDEES_LIST_CONFIDENTIAL CHAR(1),
  ATTENDEES_CONF_CONFIDENTIAL CHAR(1),
  ATTENDEES_COUNT_INTERNAL NUMBER(11),
  ATTENDEES_COUNT_EXTERNAL NUMBER(11),
  CONFIRMED_COUNT_INTERNAL NUMBER(11),
  CONFIRMED_COUNT_EXTERNAL NUMBER(11),
  PREVIOUS_MEETING_ID NUMBER(20),
  APPOINTMENT_SOURCE CHAR(1),
  MEETING_LOCATION VARCHAR(100),
  LATITUDE FLOAT(10),
  LONGITUDE FLOAT(10),
  MEETING_DESCRIPTION VARCHAR2(1000),
  PRIMARY KEY(MEETING_ID)
);

Solution

The Oracle JDBC documentation says:

If key columns are not explicitly indicated, then Oracle JDBC drivers cannot identify which columns need to be retrieved. When a column name or column index array is used, Oracle JDBC drivers can identify which columns contain auto-generated keys that you want to retrieve. However, when the Statement.RETURN_GENERATED_KEYS integer flag is used, Oracle JDBC drivers cannot identify these columns. When the integer flag is used to indicate that auto-generated keys are to be returned, the ROWID pseudo column is returned as key. The ROWID can be then fetched from the ResultSet object and can be used to retrieve other columns.

You aren't specifying the columns (as shown in their sample code) so you're retrieving the ROWID; trying to get that with getInt() is causing the error you see. (I actually see Invalid column type: getInt not implemented for class oracle.jdbc.driver.RowidAccessor ERROR CODE: 17004 but I think that's down to a different driver version).

You need to specify the column that gets the auto-generated value. If it is called MEETING_ID then you'd do:

    String returnCols[] = { "MEETING_ID" };
    preparedStatement = dbConnection.prepareStatement(insertTableSQL, returnCols);

... passing the array of columns - only one in this case - rather than the RETURN_GENERATED_KEYS flag.

The rs.getInt(1) will then be retrieving that numeric value.

Comments

[Mick Mnemonic]

Can you post the table description (DDL) and the full error message?

[Himanshu]

Added table description, full error message is given in question.