470,818 Members | 1,587 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,818 developers. It's quick & easy.

IDENTITY_VAL_LOCAL AND STORED PROCEDURES DB2 ver 7.2



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
1 2556
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.

Similar topics

8 posts views Thread by chunglun | last post: by
2 posts views Thread by Kent Lewandowski | last post: by
5 posts views Thread by Tim Marshall | last post: by
45 posts views Thread by John | last post: by
reply views Thread by mihailmihai484 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.