| Author |
Message
|
| ushak |
 Posted: Wed Jan 30, 2013 6:54 am Post subject: ESQL equalent code to IDENTITY_VAL_LOCAL() |
 |
|
Newbie
Joined: 28 Jan 2013
Posts: 8
|
HI ,
I'm using below code to get the last generated IDENTITY after an insert statement. Basically I'm doing an insert which will auto generate an ID in the table and I need the ID value back to WMB for further processing.
PASSTHRU('SELECT IDENTITY_VAL_LOCAL() AS ID FROM SYSIBM.SYSDUMMY1');
Other than using passthru , is there a way to get the IDENTITY using native esql code? |
|
| Back to top |
|
 |
| lancelotlinc |
| Posted: Wed Jan 30, 2013 7:05 am Post subject: Re: ESQL equalent code to IDENTITY_VAL_LOCAL() |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
| ushak wrote: |
HI ,
I'm using below code to get the last generated IDENTITY after an insert statement. Basically I'm doing an insert which will auto generate an ID in the table and I need the ID value back to WMB for further processing.
PASSTHRU('SELECT IDENTITY_VAL_LOCAL() AS ID FROM SYSIBM.SYSDUMMY1');
Other than using passthru , is there a way to get the IDENTITY using native esql code? |
You mean by using the Database node? What's wrong with PASSTHRU?
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| ushak |
| Posted: Wed Jan 30, 2013 7:13 am Post subject: |
|
|
Newbie
Joined: 28 Jan 2013
Posts: 8
|
I'm using compute node.
I do not want passthru as I dont want any code to be DB specific. It should work for a different database also. |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Wed Jan 30, 2013 7:24 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
| ushak wrote: |
I'm using compute node.
I do not want passthru as I dont want any code to be DB specific. It should work for a different database also. |
SELECT IDENTITY_VAL_LOCAL() AS ID FROM SYSIBM.SYSDUMMY1 is specific to DB2. You must write SQL statements in the database de'jour.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| Vitor |
| Posted: Wed Jan 30, 2013 7:25 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| ushak wrote: |
| I do not want passthru as I dont want any code to be DB specific. It should work for a different database also. |
But most database systems store & generate id values in different places and in different formats, so any ESQL code that relies on this value in inherently DB specific.
If all you want is an identity value to use in your ESQL you can obtain it from one of the UUIDAS.... ESQL functions.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed Jan 30, 2013 8:23 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
If you want to return database specific information to Broker using a non-database specific method, you need to write a database specific stored procedure and use the non-specific CALL statement in ESQL to invoke the stored procedure.
Then you change the definition of the stored procedure across different databases but not the declaration. |
|
| Back to top |
|
|
|
|
