IDENTITY_VAL_LOCAL AND STORED PROCEDURES DB2 ver 7.2
Question posted by: mike
(Guest)
on
November 12th, 2005 11:36 AM
If I try and do a "SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1"
after I have run a stored procedure in DB2 version 7.2, I get the last
generated Key before the CallableStatement was executed in the
transaction and not the key generated in my stored procedure.
Is there a way to set the IDENTITY_VAL_LOCAL or have the stored
procedure generated key show up in my IDENTITY_VAL_LOCAL value?
Thanks Mike
Java Code:
CallableStatement proc =
((Connection)this.getConnection()).prepareCall("{call
ADMIN.PROCEDURE4(?,?,?,?,?,?,?)}");
proc.setString(1, new String("TEST"));
proc.setString(2, new String("TEST"));
proc.setString(3, new String("TEST"));
proc.setString(4, new String(""));
proc.setString(5, new String("TEST"));
proc.setInt (6, 1);
proc.setString(7, new String("TEST")); proc.executeUpdate();
System.out.println("last generated ID");
String strSql = "SELECT IDENTITY_VAL_LOCAL() FROM
SYSIBM.SYSDUMMY1" ;
Statement
stmt=((Connection)this.getConnection()).createStat ement();
ResultSet result = stmt.executeQuery(strSql);
while(result.next()) {
String val = result.getString(1);
System.out.println("val = " + val);
}
stmt.close();
SQLJ stored procedure code:
package sqlj_fmb;
import java.sql.*;
import sqlj.runtime.ref.*;
public class PersonNameInsert {
public static void personNameInsert(String iFRST_NM,
String iMDL_NM,
String iLST_NM,
String iSFX_CD,
String iTP,
int iPRSN_LNK,
String iUPDT_USER
)
throws SQLException, Exception {
#sql {
INSERT INTO ADMIN.COPR_PRSN_NM
(
PRSN_NM_LNK,
FRST_NM,
MDL_NM,
LST_NM,
SFX_CD,
TP,
PRSN_LNK,
UPDT_USER,
UPDT_DTTM
)
VALUES
(
DEFAULT,
:iFRST_NM,
:iMDL_NM,
:iLST_NM,
:iSFX_CD,
:iTP,
:iPRSN_LNK,
:iUPDT_USER,
CURRENT TIMESTAMP
)
};
}
}
