Get Last Inserted IDENTITY

[frankmusion]

Hi, I need to get the last Identity from a table that was inserted into from a Stored Pocedure.

Im trying this and it doesn work.

(IN PartName VARCHAR(256),
IN PartPrice DECIMAL(19,2),
OUT PartID BIGINT)

INSERT INTO NULLID.PARTS
(PART_NAME, PART_PRICE)
VALUES
(PartName, PartPrice);

SET PartID = IDENTITY_VAL_LOCAL();

I read a post from Serge Rielau of IBM and tried this too but this gives me invalid conversion.

SELECT PART_ID INTO PartID FROM NEW TABLE(
INSERT INTO NULLID.PARTS
(PART_NAME, PART_PRICE)
VALUES
(PartName, PartPrice));


Im migrating from SQL Server to DB2 but my expirence has not been good. I can do this with a simple RETURN SCOPE_IDENTITY() in MSSQL. Can anyone tell me what im doing wrong? THANKS!!!

[veeracb]

This can be a little help to you.

http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0004231.htm

Hi, I need to get the last Identity from a table that was inserted into from a Stored Pocedure.

Im trying this and it doesn work.

(IN PartName VARCHAR(256),
IN PartPrice DECIMAL(19,2),
OUT PartID BIGINT)

INSERT INTO NULLID.PARTS
(PART_NAME, PART_PRICE)
VALUES
(PartName, PartPrice);

SET PartID = IDENTITY_VAL_LOCAL();

I read a post from Serge Rielau of IBM and tried this too but this gives me invalid conversion.

SELECT PART_ID INTO PartID FROM NEW TABLE(
INSERT INTO NULLID.PARTS
(PART_NAME, PART_PRICE)
VALUES
(PartName, PartPrice));


Im migrating from SQL Server to DB2 but my expirence has not been good. I can do this with a simple RETURN SCOPE_IDENTITY() in MSSQL. Can anyone tell me what im doing wrong? THANKS!!!

[chandu031]

This can be a little help to you.

http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0004231.htm

Hi,

The problem with your query "Select part_id into part_id..." is that it returns multiple values when you are inserting multiple rows. So all you have to do is use 'SELECT MAX(PART_ID) INTO PART_ID....

Hope this helps you....

[frankmusion]

Hi,

The problem with your query "Select part_id into part_id..." is that it returns multiple values when you are inserting multiple rows. So all you have to do is use 'SELECT MAX(PART_ID) INTO PART_ID....

Hope this helps you....

I don't think that will return multiple PART_ID's since that "New Table" only cares about that current scope. I wanted to use IDENTITY_VAL_LOCAL() since it's non deterministic but I havn't been able to get it to work. Inserts don't need a cursor but if I use the IDENTITY_VAL_LOCAL() function do I need to provide one? I want to put IDENTITY_VAL_LOCAL() in a output parameter. I also tried RETURN IDENTITY_VAL_LOCAL(); but that didn't work either. Thanks for the help so far guys I really appreciate it!!!!

[chandu031]

Hi,

If you are returning an output parameter from a procedure then a Return statement cannot be used. In order to use an output parameter from the called procedure in the calling procedure you will have to use

CALL <SCHEMA>.<CALLED_PROCNAME> (PART_ID);

where part id is an output parameter from the called procedure.. In the called procedure you just have to set the value

CREATE PROCEDURE <CALLED_PROCNAME>(OUT PART_ID BIGINT)
LANGUAGE SQL
BEGIN

INSERT......
SET PART_ID = IDENTITY_VAL_LOCAL();
END

[frankmusion]

Hi,

If you are returning an output parameter from a procedure then a Return statement cannot be used. In order to use an output parameter from the called procedure in the calling procedure you will have to use

CALL <SCHEMA>.<CALLED_PROCNAME> (PART_ID);

where part id is an output parameter from the called procedure.. In the called procedure you just have to set the value

CREATE PROCEDURE <CALLED_PROCNAME>(OUT PART_ID BIGINT)
LANGUAGE SQL
BEGIN

INSERT......
SET PART_ID = IDENTITY_VAL_LOCAL();
END

Hi chandu031,

I tried that and I get an Invalid Conversion error. I'm guessing I'm getting Null for the value and you can't convert Null to Integer. Is there anything else that I could be doing wrong?

[chandu031]

Hi chandu031,

I tried that and I get an Invalid Conversion error. I'm guessing I'm getting Null for the value and you can't convert Null to Integer. Is there anything else that I could be doing wrong?

Hi,

Casting NULL to integer wont raise an error as any operation done on NULL in DB2 results in a NULL. May be if I had some more information I would be able to figure out what the problem is.

[frankmusion]

Hi,

Casting NULL to integer wont raise an error as any operation done on NULL in DB2 results in a NULL. May be if I had some more information I would be able to figure out what the problem is.

All im tring to do is return the last identity inserted either by the return statement of an output parameter. Im using the .NET DB2 Framework. Im calling the DB from my VB.NET application. I set up a stored procedure that would insert a automotive part in the PARTS table and return me the Identity "PART_ID" of that insert. Can you post a stored proc that would do this? with RETURN statement or an output parameter? thanks!!!!

[chandu031]

All im tring to do is return the last identity inserted either by the return statement of an output parameter. Im using the .NET DB2 Framework. Im calling the DB from my VB.NET application. I set up a stored procedure that would insert a automotive part in the PARTS table and return me the Identity "PART_ID" of that insert. Can you post a stored proc that would do this? with RETURN statement or an output parameter? thanks!!!!

Hi,

Here's a sample proc:

CREATE PROCEDURE DB2ADMIN.GET_IDENTITY_AFTR_INSERT(IN L_PART_NAME VARCHAR(100), IN L_PART_PRICE INTEGER,OUT L_PART_ID BIGINT)
LANGUAGE SQL
BEGIN

INSERT INTO PARTS(PART_NAME,PART_PRICE) VALUES(L_PART_NAME,L_PART_PRICE);

VALUES IDENTITY_VAL_LOCAL() INTO L_PART_ID;

END


If this is not workin you can open a cursor on SELECT IDENTITY_VAL_LOCAL FROM SYSIBM.SYSDUMMY1.This is guaranteed to return one row. But since you are saying it is giving a "type conversion error" I don't think this is where you are facing a problem.