CODEWITHSUNDEEP
javastored-proceduresdb2prepared-statement
Mike
Mike

Reputation: 1899

Stored procedure and prepared statement error

I am calling a stored procedure from my Java application and getting the following error while setting the input parameter.

1: Java method for calling the procedure: 

public int callProc(String userid, String program_id, String procName)
        throws SQLException {

    int returnCode = -1;

    try {

        CallableStatement stmt;

        String sql = "CALL " + procName + "(?,?,?,?,?)";
        stmt = connection.prepareCall(sql);

        System.out.println("Calling stored procedure [" + procName + "]");

        // call setXXX() method to set values for input parameters
        // and input-output parameters
        System.out.println("jdbcadapter->callproc setting 1 parameter "+stmt);
        stmt.setObject(1, userid, java.sql.Types.CHAR);
        System.out.println("jdbcadapter->callproc setting 2 parameter "+sql);
        stmt.setObject(2, program_id, java.sql.Types.CHAR);
        System.out.println("jdbcadapter->callproc setting 3 parameter "+sql);
        stmt.setObject(3, session_id, java.sql.Types.CHAR);
        System.out.println("jdbcadapter->callproc setting 4 parameter "+sql);
        stmt.setObject(4, " ", java.sql.Types.CHAR);
        System.out.println("jdbcadapter->callproc setting 5 parameter "+sql);
        stmt.setObject(5, " ", java.sql.Types.CHAR);
        System.out.println("jdbcadapter->callproc setting 6 parameter "+sql);
        // call registerOutParameter() method to define the data types for output parameters
        stmt.registerOutParameter(4, java.sql.Types.CHAR); // rc
        System.out.println("jdbcadapter->callproc setting 7 parameter "+sql);
        stmt.registerOutParameter(5, java.sql.Types.CHAR); // result message
        System.out.println("jdbcadapter->callproc setting 8 parameter "+sql);

        stmt.execute();
        System.out.println("jdbcadapter->callproc after execute "+sql);

        String rc = stmt.getString(4);
        System.out.println("jdbcadapter->callproc after execute "+rc);
        String message = stmt.getString(5);
        System.out.println("jdbcadapter->callproc after execute "+message);
        Integer return_code = new Integer(rc.trim());
        System.out.println("jdbcadapter->callproc after execute "+return_code);
        returnCode = return_code.intValue();
        System.out.println("jdbcadapter->callproc after execute "+returnCode);
        return_message = message.trim();
        System.out.println("jdbcadapter->callproc after execute "+return_message);
        System.out.println("Returned code from stored procedure is ["+ rc.trim() + "]");
        stmt.close();

    } catch (Exception e) {
        throw ((SQLException) e);
    }

    return returnCode;

} // end callProc()

The problem is occuring while setting the USER ID in the stmt at 

stmt.setObject(1, userid, java.sql.Types.CHAR);

Definition of Stored procedure on database side

CREATE PROCEDURE "CDR     "."SPUBASE"
                (
                IN      USERID   CHARACTER(12),
                IN      PROGRAM_ID   CHARACTER(20),
                IN      SESSION_ID   CHARACTER(27),
                OUT     SQLCODE   CHARACTER(254),
                OUT     SQLMESSAGE   CHARACTER(254)
                )
                DYNAMIC RESULT SETS 0
                SPECIFIC SQL040816123726847
                EXTERNAL NAME '/cdrtst/sqllib/function/SPUBASE!SPUBASE'
                LANGUAGE C
                PARAMETER STYLE DB2DARI
                NOT DETERMINISTIC
                FENCED NOT THREADSAFE
                MODIFIES SQL DATA
                NO DBINFO;

and the error is 

SQLException: DB2 SQL Error: SQLCODE=-440, SQLSTATE=42884, SQLERRMC=SPUBASE;PROCEDURE, DRIVER=4.1.85

Could anyone suggest what can cause this.

Upvotes: 0

Views: 3981

Answers (1)

Vikdor
Vikdor

Reputation: 24134

From this page explaining the reasons for SQLCODE: -440, one of the reasons is

The right number of arguments was included in the routine reference, but the data type of one or more of the arguments is incorrect.

I suspect that your following method call 

stmt.setObject(1, userid, java.sql.Types.CHAR);

should be replaced with

stmt.setString(1, userid);

or 

stmt.setObject(1, userid, java.sql.Types.VARCHAR);

I would prefer the former, i.e. setString and leave it to the driver to do the appropriate conversion based on the underlying DB/column.

The same would apply to other statement.set* methods as well, calling the appropriate set* method like setString, setInt etc.,

Upvotes: 1

Related Questions