473,889 Members | 1,770 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Get Last Inserted IDENTITY

10 New Member
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_LO CAL();

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!!!
Apr 1 '07 #1
8 26150
veeracb
1 New Member
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_LO CAL();

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!!!
Apr 2 '07 #2
chandu031
78 Recognized Expert New Member
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....
Apr 2 '07 #3
frankmusion
10 New Member
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_LO CAL() 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_LO CAL() function do I need to provide one? I want to put IDENTITY_VAL_LO CAL() in a output parameter. I also tried RETURN IDENTITY_VAL_LO CAL(); but that didn't work either. Thanks for the help so far guys I really appreciate it!!!!
Apr 2 '07 #4
chandu031
78 Recognized Expert New Member
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>.<CALLE D_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_PROCNAM E>(OUT PART_ID BIGINT)
LANGUAGE SQL
BEGIN

INSERT......
SET PART_ID = IDENTITY_VAL_LO CAL();
END
Apr 3 '07 #5
frankmusion
10 New Member
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>.<CALLE D_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_PROCNAM E>(OUT PART_ID BIGINT)
LANGUAGE SQL
BEGIN

INSERT......
SET PART_ID = IDENTITY_VAL_LO CAL();
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?
Apr 3 '07 #6
chandu031
78 Recognized Expert New Member
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.
Apr 4 '07 #7
frankmusion
10 New Member
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!!!!
Apr 4 '07 #8
chandu031
78 Recognized Expert New Member
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_ID ENTITY_AFTR_INS ERT(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_N AME,L_PART_PRIC E);

VALUES IDENTITY_VAL_LO CAL() INTO L_PART_ID;

END


If this is not workin you can open a cursor on SELECT IDENTITY_VAL_LO CAL FROM SYSIBM.SYSDUMMY 1.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.
Apr 5 '07 #9

Sign in to post your reply or Sign up for a free account.

Similar topics

1
8755
by: PT | last post by:
I got a problem. And thats..... First of all, I got these three tables. ------------------- ------------------ ---------------------- tblPerson tblPersonSoftware tblSoftware ------------------- ------------------ ---------------------- PID PName PID* SID* SID SWName --- ----- --- --- --- ------ 1 Thomas 1 1 ...
0
1706
by: rodrigo | last post by:
How to get get the last inserted key (value NOT column) in a specific dataTable dt right after dt.Rows.InsertAt according to code and schema below. The key is a autoincrement column. foreach (MyContainer item in al) {
2
5852
by: Danko Greiner | last post by:
I'm trying to update every table in DataSet, but getting inserted "id_user" gets me an exception: column "id_user" is read only if(_dataSet.HasChanges()) { SqlDataAdapter _adapter; SqlCommandBuilder _builder; SqlConnection conn = new SqlConnection(GlobalSettings.ConnectionString);
3
2853
by: Susanne Klemm | last post by:
Hello! I use a procedure to insert a new row into a table with an identity column. The procedure has an output parameter which gives me the inserted identity value. This worked well for a long time. Now the identity value is over 700.000 and I get errors whiles retrieving the inserted identitiy value. If I delete rows and reset the identity everything works well again. So I think it is a data type problem. My Procedure:
2
1554
by: imranabdulaziz | last post by:
Dear all, i am using asp.net ,C# (VS 2005) and sql server 2005. i have written sp for inserting the the data which written last inserted idendity no. i would like to which method should i use(reader , nonexecutequery or executescalar ) so that i get that value and display the value in the form. please guide me. thanks
13
4439
rajiv07
by: rajiv07 | last post by:
Hi to all, I want to know how to select a last inserted record which is the primary key is not an integer. my table ramstr(Primary)----name--service XTC01-------Rajiv---service XTC02------kumar--Service
20
3533
by: canabatz | last post by:
hi all !! i got my results like that 1 user0 55 2 user1 53 3 user2 49 4 user2 48 5 user3 47 <------- last inserted row 6 user4 46
5
6657
by: aniketk | last post by:
How can i get last inserted record. if i don't have any date column in table?
4
2516
semanticnotion
by: semanticnotion | last post by:
Hi guys how can i retrieve the last inserted record from datebase. my primary key is not auto_increment its type is bigint because i want to insert SSN no as a primary key. when i retrive the data using select query it retrieve the first record store in database but i want to display the last inserted record because i want then to print that record. so any idea plz help me my queries are $query="INSERT INTO form1...
0
9967
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9810
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11202
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10792
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10443
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9611
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7151
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
4650
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3256
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.