Java stored procedure: java.lang.NoSuchMethodError

Question

I'm trying to execute a Java store procedure which is fired from a DB2 stored procedure, however, I get an error.

My DB2 stored procedure:

CREATE PROCEDURE SP_INSERT_UPDATE_REPNUMHD (
    IN RECNUM             INTEGER, 
    IN REPNUMHD_ID        INTEGER,   
    IN BINNEN_DATUM       DATE,
    IN BINNEN_INI         VARCHAR(2),
    IN REP_STATUS         SMALLINT,
    IN BEVESTIGD          CHAR(1),
    IN BEVESTIGD_DATUM    DATE,
    IN BEVESTIGD_INI      VARCHAR(2),
    IN KLANT_REF          VARCHAR(20),
    IN ACTION             CHAR(1)
)
SPECIFIC SP_INSERT_UPDATE_REPNUMHD
DYNAMIC RESULT SETS 0
DETERMINISTIC
LANGUAGE JAVA
PARAMETER STYLE JAVA
NO DBINFO
NOT FENCED
THREADSAFEre
MODIFIES SQL DATA
PROGRAM TYPE SUB
EXTERNAL NAME 'RepairMigration!insertRepairHeader'

My java stored procedure is as follows:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Date;
import java.util.Properties;
import java.util.logging.Logger;
import java.util.logging.FileHandler;
import java.io.InputStream;
import java.io.FileInputStream;
import java.io.IOException;

public class RepairMigration {

    private static final Properties config = new Properties();
    private static final Logger log = Logger.getLogger( RepairMigration.class.getName() );

    /**
     * Inserts a record from table REPNUMHD (DB2) to repair_repair (Postgres) 
     * <p>
     *
     * @param  db2RecNum        RECNUM,
     * @param  repairId         REPNUMHD_ID,
     * @param  receivedDate     BINNEN_DATUM,
     * @param  receivedIni      BINNEN_INI,
     * @param  repairStatusId   REP_STATUS,
     * @param  confirmed        BEVESTIGD,
     * @param  confirmedDate    BEVESTIGD_DATUM,
     * @param  confirmedIni     BEVESTIGD_INI,
     * @param  customerRef      KLANT_REF,
     * @param  customerId       KLANTNUMMER,
     * @param  action           ACTION,
     */
    public static void insertRepairHeader(
        int db2RecNum,
        int repairNumber,
        Date receivedDate,
        String receivedIni,
        int repairStatusId,
        String confirmed,
        Date confirmedDate,
        String confirmedIni,
        String customerRef,
        int customerId,
        String action
    ) throws Exception {
        String query = "";
        PreparedStatement pstmt = null;
        Connection con = RepairMigration.connect();
        if (action == "I")
        {
            query = "INSERT INTO repair_repair("+
                    " status_id, " +
                    " customer_id, " +
                    " contact_id, " +
                    " repair_number, " +
                    " received_date, " +
                    " received_uid, " +
                    " received_initials, " +
                    " confirmed, " +
                    " confirmed_date, " +
                    " confirmed_uid, " +
                    " confirmed_initials, " +
                    " customer_reference, " +
                    " db2_recnum) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)";
            pstmt = con.prepareStatement(query);
            pstmt.setInt(1, getStatusId(repairStatusId, con)); 
            pstmt.setInt(2, getCustomerId(customerId, con)); 
            pstmt.setNull(3, java.sql.Types.INTEGER);
            pstmt.setInt(4, repairNumber);
            pstmt.setDate(5, receivedDate);
            pstmt.setNull(6, java.sql.Types.INTEGER);
            pstmt.setString(7, receivedIni);
            pstmt.setBoolean(8, confirmed == "1" ? true : false);
            pstmt.setDate(9, confirmedDate);
            pstmt.setNull(10, java.sql.Types.INTEGER);
            pstmt.setString(11, confirmedIni);
            pstmt.setString(12, customerRef);
            pstmt.setInt(13, db2RecNum);
            pstmt.executeUpdate();
        }
        else{ // action = "U"
            query = "UPDATE repair_repair "+
                    " SET received_date = ?, " +
                    " received_initials = ?, " +
                    " status_id = ?, " +
                    " confirmed = ?, " +
                    " confirmed_date = ?, " +
                    " confirmed_initials = ?, " +
                    " customer_reference = ? " +
                    " WHERE db2_recnum = ?";
            pstmt = con.prepareStatement(query);
            pstmt.setDate(1, receivedDate);
            pstmt.setString(2, receivedIni);
            pstmt.setInt(3, getStatusId(repairStatusId, con));
            pstmt.setBoolean(4, confirmed == "1" ? true : false);
            pstmt.setDate(5, confirmedDate);
            pstmt.setString(6, confirmedIni);
            pstmt.setString(7, customerRef);
            pstmt.setInt(8, db2RecNum);
            pstmt.executeUpdate();
        }
    }
}

Checking the db2diag.log

2014-10-13-14.17.37.654542+120 I2604998158E538     LEVEL: Warning
PID     : 18214                TID  : 139965772773120PROC : db2fmp (
INSTANCE: db2inst1             NODE : 000
FUNCTION: DB2 UDB, BSU Java support, sqlejLogException, probe:10
MESSAGE : java.lang.NoSuchMethodError: RepairMigration.insertRepairHeader(IILjava/sql/Date;Ljava/lang/String;SLjava/lang/String;Ljava/sql/Date;Ljava/lang/String;Ljava/lang/String;Ljava/lang/String;)V
DATA #1 : Hexdump, 4 bytes
0x00007F4C522A9B30 : 0000 0000 

According to the error message, it seems my integer parameters are gone. Is that the problem? If so, how can I solve this?

Thanks in advance!

Answer 1

According to the error message, it seems my integer parameters are gone. Is that the problem?

Not exactly. The meaning of this signature

RepairMigration.insertRepairHeader(IILjava/sql/Date;Ljava/lang/String;
                                   SLjava/lang/String;Ljava/sql/Date;
                                   Ljava/lang/String;Ljava/lang/String;
                                   Ljava/lang/String;)V

is:

void RepairMigration.insertRepairHeader(int, int, java.sql.Date, String,
                                   short, String, java.sql.Date,
                                   String, String, String)

Comparing that with your code, it looks like the type of repairStatusId has changed, and the customerId parameter is missing.

Basically, the problem is that there is a mismatch between the stored procedure class, and the parameter types in the CREATE STORED PROCEDURE ... statement. Compare them. Even the number of parameters is different between the SQL and the java code.