By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,333 Members | 1,214 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,333 IT Pros & Developers. It's quick & easy.

IDENTITY_VAL_LOCAL AND STORED PROCEDURES DB2 ver 7.2

P: n/a


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("{c all
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
)

};

}

}

Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
mike wrote:

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?

Interesting. That's not how identity_val_local() is meant to work.
Arguably you could open a PMR on it (presuming you have support for V7.2.

Cheers
Serge

PS: identity_val_local() is superceded by SELECT FROM INSERT in V8.1.4.

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.