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!