Parameter instance recId is invalid for the requested conversion. ERRORCODE=-4461

Question

I am using spring JdbcNamedTemplate to insert data in database. For other operations (such as delete) it is working for fine, But while insert I am getting -4461 error from DB2.

I already went to these pages: http://www-01.ibm.com/support/docview.wss?uid=swg21622381

Invalid data conversion: Parameter instance 50.0/100 is invalid for the requested conversion. ERRORCODE=-4461, SQLSTATE=42815

But no help. Below is a snippet of my code.

@Override
public void insert(FLIGHT fw) {
    String sql = "INSERT INTO FLIGHT (FLT,LEG,ORG,DST,BOOKED) VALUES  (:flt,:leg,:org,:dst,:booked)";


MapSqlParameterSource namedParameters = new MapSqlParameterSource();
namedParameters.addValue("flt", fw.getFlightNumber());
namedParameters.addValue("leg", fw.getFlightLeg());
namedParameters.addValue("org", fw.getOrg());
namedParameters.addValue("dst", fw.getDst());
namedParameters.addValue("booked", fw.getBooked());
getNamedParameterJdbcTemplate().update(sql, namedParameters);

}

Also Here is my Flight Table schema for db2

CREATE TABLE FLIGHT
(
    FLT VARCHAR(4) NOT NULL,
    LEG VARCHAR(1) NOT NULL,
    ORG VARCHAR(3),
    DST VARCHAR(3),
    BOOKED DECIMAL(3),
    REC_ID INT NOT NULL GENERATED ALWAYS AS IDENTITY(START WITH 1 INCREMENT BY 1),
    CONSTRAINT PK_FLIGHT PRIMARY KEY (FLT, LEG)
);
CREATE UNIQUE INDEX UQ268_FLIGHT ON FLIGHT(REC_ID);

And this is the error I am getting

Caused by: com.ibm.db2.jcc.am.SqlSyntaxErrorException: [jcc][1091][10404][4.13.80] Invalid data conversion: Parameter instance REC_ID is invalid for the requested conversion. ERRORCODE=-4461, SQLSTATE=42815
    at com.ibm.db2.jcc.am.id.a(id.java:677)
    at com.ibm.db2.jcc.am.id.a(id.java:60)
    at com.ibm.db2.jcc.am.id.a(id.java:103)
    at com.ibm.db2.jcc.am.kc.a(kc.java:774)
    at com.ibm.db2.jcc.am.kc.a(kc.java:689)
    at com.ibm.db2.jcc.am.go.a(go.java:1583)
    at com.ibm.db2.jcc.am.go.setString(go.java:1538)
    at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.setString(WrappedPreparedStatement.java:637)
    at org.springframework.jdbc.core.StatementCreatorUtils.setValue(StatementCreatorUtils.java:421) [spring-jdbc-4.2.5.RELEASE.jar:4.2.5.RELEASE]
    at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:235) [spring-jdbc-4.2.5.RELEASE.jar:4.2.5.RELEASE]
    at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:150) [spring-jdbc-4.2.5.RELEASE.jar:4.2.5.RELEASE]
    at org.springframework.jdbc.core.PreparedStatementCreatorFactory$PreparedStatementCreatorImpl.setValues(PreparedStatementCreatorFactory.java:292) [spring-jdbc-4.2.5.RELEASE.jar:4.2.5.RELEASE]
    at org.springframework.jdbc.core.PreparedStatementCreatorFactory$PreparedStatementCreatorImpl.createPreparedStatement(PreparedStatementCreatorFactory.java:244) [spring-jdbc-4.2.5.RELEASE.jar:4.2.5.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:623) [spring-jdbc-4.2.5.RELEASE.jar:4.2.5.RELEASE]
    ... 200 more

Can you execute the same query using a database tool (ideally one you can feed the same JDBC driver you're using, like SQuirreL SQL)? — Arthur Noseda, Jul 25 '16 at 19:00
According to the error message, you are trying to provide a value for `REC_ID`, which, obviously, is not allowed. Apparently you template generates a wrong statement. — mustaccio, Jul 25 '16 at 20:39
@User45 : Did you got any solution? Can you share please? I am also getting same error :/ — NIKHIL CHAURASIA, Nov 27 '17 at 06:42

score 0 · Answer 1 · answered Jul 26 '16 at 11:13

You can try this in your code

namedParameters.addValue("flt", fw.getFlightNumber(),Types.VARCHAR);
namedParameters.addValue("leg", fw.getFlightLeg(),Types.VARCHAR);
namedParameters.addValue("org", fw.getOrg(),Types.VARCHAR);
namedParameters.addValue("dst", fw.getDst(),Types.VARCHAR);
namedParameters.addValue("booked", fw.getBooked(),Types.DOUBLE)

this run correctly,

PreparedStatement ps = connection.prepareStatement(
            "INSERT INTO FLIGHT (FLT,LEG,ORG,DST,BOOKED) VALUES  (?,?,?,?,?)");
            ps.setString(1,"b");
            ps.setString(2 ,"b");
            ps.setString(3 ,"b");
            ps.setString(4 ,"b");
            ps.setDouble(5 ,1);
            ps.executeUpdate();
            ps.close();


user@host:/db2inst1:>db2 "select * from FLIGHT"

FLT  LEG ORG DST BOOKED REC_ID     
---- --- --- --- ------ -----------
a    a   a   a       1.           1
b    b   b   b       1.           2

  2 record(s) selected.

Parameter instance recId is invalid for the requested conversion. ERRORCODE=-4461

1 Answers1