oracle 为什么无效转换请求错误代码：17132？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32112907/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Why Invalid conversion requested ERROR CODE: 17132?
提问by Himanshu
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:
我试图在使用 JDBC 准备好的语句插入时获取最后插入的行 ID。我有一个自动增量主键列作为表中的标识列。我的代码如下：
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.
但是在谷歌搜索之后，这段代码似乎没问题，它应该返回我最后插入的行 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)
);
采纳答案by Alex Poole
The Oracle JDBC documentationsays:
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_KEYSinteger 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, theROWIDpseudo column is returned as key. TheROWIDcan be then fetched from theResultSetobject and can be used to retrieve other columns.
如果未明确指示键列，则 Oracle JDBC 驱动程序无法识别需要检索哪些列。使用列名或列索引数组时，Oracle JDBC 驱动程序可以识别哪些列包含要检索的自动生成的键。但是，当使用
Statement.RETURN_GENERATED_KEYS整数标志时，Oracle JDBC 驱动程序无法识别这些列。当整数标志用于指示要返回自动生成的键时，ROWID伪列作为键返回。然后ROWID可以从ResultSet对象中获取，并可用于检索其他列。
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: 17004but I think that's down to a different driver version).
您没有指定列（如他们的示例代码中所示），因此您正在检索ROWID; 试图得到它getInt()会导致你看到的错误。（我实际上看到了，Invalid column type: getInt not implemented for class oracle.jdbc.driver.RowidAccessor ERROR CODE: 17004但我认为这取决于不同的驱动程序版本）。
You need to specify the column that gets the auto-generated value. If it is called MEETING_IDthen you'd do:
您需要指定获取自动生成值的列。如果它被调用，MEETING_ID那么你会这样做：
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.
...传递列数组 - 在这种情况下只有一个 - 而不是 RETURN_GENERATED_KEYS 标志。
The rs.getInt(1)will then be retrieving that numeric value.
该rs.getInt(1)则将检索该数值。
