Java. SQL. SQLException: The IN or OUT parameters are lost IN the index: x, and the outlook index is lost.

Java. SQL. SQLException: The IN or OUT parameters are lost IN the index: x, and the outlook index is lost.

When using JDBC, the following error occurs: java. SQL. SQLException: The IN or OUT parameters are lost IN the index: x

As shown in the following example, insertlog.exe cute (); this row throws this exception:

String logSQL = "insert into tbl_obj(id, obj, type, cont, proposer, operator, date, remark) "                       + "values(seq_tot.nextval, ?, ?, ?, ?, ?, SYSDATE, ?)";insertLog = conn.prepareStatement(logSQL);insertLog.setString(2, trace.getObj());insertLog.setString(3, trace.getType());insertLog.setString(4, trace.getCont());insertLog.setString(5, trace.getProposer());insertLog.setString(6, trace.getOperator());insertLog.setString(8, trace.getRemark());insertLog.execute();

I searched some posts and pointed out many reasons for this problem: "Too many parameters, inconsistent configuration file and database field types, or database index problems ".

According to the error message, there are two possible reasons for hitting the wall with the predecessors:

(1) is the index faulty? ("Missing indexes ")

(2) Does the field assignment match the database field type?

For the (1) argument, check the index of this table. This table uses ID as the primary key, and there is no other index. Therefore, there is only one primary key index, and the check status is also VALID, with no error:

SQL> select index_name, status from user_indexes where table_name='TBL_OBJ_TRACE';INDEX_NAME                     STATUS------------------------------ --------SYS_C0031302                   VALID

For (2) arguments,

First, I checked the definition of the field type in the set/get METHOD OF trace, Which is String and the Field Types in the corresponding library are VARCHAR2. There is no difference.

Next, let's look at setString, which corresponds to the field in VALUES. In fact, this is the problem. Let's take a look at the explanation of the setString method:

void java.sql.PreparedStatement.setString(int parameterIndex, String x) throws SQLExceptionSets the designated parameter to the given Java String value. The driver converts this to an SQL VARCHAR or LONGVARCHAR value (depending on the argument's size relative to the driver's limits on VARCHAR values) when it sends it to the database.Parameters:parameterIndex the first parameter is 1, the second is 2, ...x the parameter valueThrows:SQLException - if parameterIndex does not correspond to a parameter marker in the SQL statement; if a database access error occurs or this method is called on a closed PreparedStatement

You can see that the first parameter parameterIndex, parameter index, parameterIndex does not correspond to a parameter marker in the SQL statement (if there is no parameter identifier corresponding to the SQL statement) will throw the SQLException exception.

Values (seq_tot.nextval ,?, ?, ?, ?, ?, SYSDATE ,?) There are a total of 6 parameter identifiers, and the setString is also 6, but the order is incorrect. The index sequence number of the first parameter in setString must be consistent with that in SQL statements, it is not the location of the VALUES field in the SQL statement, but the serial number of the parameter identifier in the SQL statement VALUES.

The following format is correct:

String logSQL = "insert into tbl_obj(id, obj, type, cont, proposer, operator, date, remark) "                       + "values(seq_tot.nextval, ?, ?, ?, ?, ?, SYSDATE, ?)";insertLog = conn.prepareStatement(logSQL);insertLog.setString(1, trace.getObj());insertLog.setString(2, trace.getType());insertLog.setString(3, trace.getCont());insertLog.setString(4, trace.getProposer());insertLog.setString(5, trace.getOperator());insertLog.setString(6, trace.getRemark());insertLog.execute();

Summary:

The JDBC error message is obscure, but it feels like it is the kind of error that you can know the scope of the error next time after you touch it. The Troubleshooting should be smooth, for example, whether the index is faulty, whether the field types in the Code are consistent with the Field Types in the table, and whether the parameter indexes used in the Code are consistent with the parameter identifiers in the SQL statement (number, order, etc ).

EOF

Bisal @ 17JUN15