javajdbcdb2jt400

JDBC com.ibm.as400.access.AS400JDBCDriver CallableStatement java.sql.SQLException

We have below code using JDBC ODBC bridge to connect to IBM iAccess but since JDK 8 upgrade the support for JDBC ODBC bridge is no longer there. 

DriverManager.registerDriver((Driver) Class.forName("sun.jdbc.odbc.JDBCODBCDriver").newInstance()); Connection con = DriverManager.getConnection(jdbcURL, userName, password);

CallableStatement cs = con.prepareCall("{ call IDBMOD.SPGIFTHOLD(?,?,?,?) }");

String acctNum = doc.getElementsByTagName("account_number").item(0).getTextContent();
String amount = doc.getElementsByTagName("amount").item(0).getTextContent();
String valueDate = doc.getElementsByTagName("value_date").item(0).getTextContent(); 
String returnCode = "";

cs.setString(1, acctNum); 
cs.setString(2, amount);
cs.setString(3, valueDate); 
cs.registerOutParameter(4, Types.VARCHAR);

boolean ret = cs.execute();

So we used correct jt400.jar JDBC driver for ibm iAccess and changed just connection code, but we get error

"java.sql.SQLException: The number of parameter values set or registered does not match the number of parameters"

Changed code is here.

DriverManager.registerDriver((Driver) Class.forName("com.ibm.as400.access.AS400JDBCDriver").newInstance());
Connection con = DriverManager.getConnection(jdbcURL, userName, password);

CallableStatement cs = con.prepareCall("{ call IDBMOD.SPGIFTHOLD(?,?,?,?) }");

String acctNum = doc.getElementsByTagName("account_number").item(0).getTextContent();
String amount = doc.getElementsByTagName("amount").item(0).getTextContent();
String valueDate = doc.getElementsByTagName("value_date").item(0).getTextContent();
String returnCode = "";

cs.setString(1, acctNum);
cs.setString(2, amount);
cs.setString(3, valueDate);
cs.registerOutParameter(4, Types.VARCHAR);

boolean ret = cs.execute();

Not sure why we get Exception java.sql.SQLException: The number of parameter values set or registered does not match the number of parameters

Solution

  • This got fixed by setting fourth parameter as INOUT parameter as done below.

    cs.setString(4,"");
cs.registerOutParameter(4,Types.VARCHAR);