| Author |
Message
|
| Vangmayi |
 Posted: Tue Nov 13, 2012 8:49 am Post subject: How do i retrieve an auto generated value after Insert stmt? |
 |
|
Novice
Joined: 05 Oct 2012
Posts: 11
|
Hi,
I am trying to insert values into 3 tables from my input message. EmpNo. is primary key to 1st table, while it is foreign key to the rest tables. I want to retrieve the value of EmpNo after 1st Insert statement and declare it to a variable and use that variable as EmpNo value in the other Insert statements. Also my EmpNo is an auto generating number.
What i see for the declared variable of EmpNo in the debug is Unknown:NULL, and thus when it goes to 2nd Insert Statement it is throwing an exception saying EmpNo cannot be NULL? 
Any idea how i can retrieve that value?
Thanks |
|
| Back to top |
|
 |
| lancelotlinc |
| Posted: Tue Nov 13, 2012 8:51 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
| Code: |
| SELECT EmpNo from FirstTable A where A.origval1 = 'knownvalue' and A.origval2 = 'knownvalue' |
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| Vangmayi |
| Posted: Tue Nov 13, 2012 9:13 am Post subject: |
|
|
Novice
Joined: 05 Oct 2012
Posts: 11
|
This is how i wrote: Is the logic correct? If it is correct then why is the Eno Unknown and Null?
DECLARE EFName CHARACTER InputRoot.XMLNSC.Employee.Details.nameFirst;
DECLARE ELName CHARACTER InputRoot.XMLNSC.Employee.Details.nameLast;
DECLARE Eage CHARACTER InputRoot.XMLNSC.Employee.Details.age;
INSERT into Database.E1.EmpDetails(FIRST_NAME,LAST_NAME,AGE) VALUES (EFName,ELName,Eage);
DECLARE Eno CHARACTER;
SET Environment.Variables.DBResult[] = PASSTHRU('SELECT EMP_NO FROM Database.E1.Details WHERE FIRST_NAME=?' VALUES ('InputRoot.XMLNSC.Employee.Details.nameFirst'));
SET Eno = Environment.Variables.DBResult[1].EMP_NO;
DECLARE EAdd1 CHARACTER InputRoot.XMLNSC.Employee.Address.address1;
DECLARE ECity CHARACTER InputRoot.XMLNSC.Employee.Address.city;
DECLARE EState CHARACTER InputRoot.XMLNSC.Employee.Address.state;
DECLARE Ezip CHARACTER InputRoot.XMLNSC.Employee.Address.zip;
INSERT into Database.E1.EmpAdd(EMP_NO,ADDRESS_1,CITY,STATE,ZIP) VALUES (Eno,EAdd1,Ecity,Estate,Ezip);
Thanks |
|
| Back to top |
|
|
| bsiggers |
| Posted: Tue Nov 13, 2012 9:14 am Post subject: |
|
|
Acolyte
Joined: 09 Dec 2010
Posts: 53
Location: Vancouver, BC
|
You've basically got to create the key value in the primary table via a 'dummy select', before you do the main insert to the primary table - the implementation of which will depend on your database system. In Oracle you'd do it using a "SELECT ... from DUAL" or something simlar. So you get the next sequence value or whatever, then just use it in your INSERT statements.
Keep in mind there is a lot of things that can go wrong, and the end solution will depend on what you're trying to accomplish specifically. Watch out for your transaction boundaries and exception handling for example, otherwise you may get sequence number gaps, things in the primary table but not in the secondary tables, etc.
Hope this helps. Best Regards,
Ben Siggers. |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Tue Nov 13, 2012 9:20 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
Agree with bsiggers. For DB2, you get next val from SYSIBM.SYSDUMMY1.
Also, construct your SQL statement fully into a string before you call passthru (ie. don't call passthru with a bunch of operands). Spit out your passthru SQL statement into the log (using log4j or Trace nodes) before you call passthru. This technique will allow you to validate your entire SQL statement when troubleshooting without having to worry about if the InputRoot was set right (namespaces and all).
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| bsiggers |
| Posted: Tue Nov 13, 2012 9:29 am Post subject: |
|
|
Acolyte
Joined: 09 Dec 2010
Posts: 53
Location: Vancouver, BC
|
Note that the company you are implementing this for must also have a policy against anyone having the same first names.
Note also that having such a policy would be a great idea in order to reduce complexity of meetings. If you had a non-unique first name, you could be assigned a new one.  |
|
| Back to top |
|
|
| Vangmayi |
| Posted: Tue Nov 13, 2012 9:39 am Post subject: |
|
|
Novice
Joined: 05 Oct 2012
Posts: 11
|
Yes my company has the policy for same first names, what i mentioned in the code is just an example. There is something else which is an auto generated number and am trying to capture that value from the primary table and allot it to the other tables. I now understand that what i am trying to do is completely wrong. But what bothers me is the DUAL part, i think ESQL doesnot support DUAL. Am i correct?
Thanks |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Tue Nov 13, 2012 9:42 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
PASSTHRU supports any native keyword you can use in interactive SQL. Thats why I suggest to construct your SQL statement entirely in a string first, so you see what is being sent via PASSTHRU.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Tue Nov 13, 2012 11:07 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
you might also hit a problem passing the employee number as a character into the second SQL... 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| kash3338 |
| Posted: Wed Nov 14, 2012 1:18 am Post subject: |
|
|
Shaman
Joined: 08 Feb 2009
Posts: 709
Location: Chennai, India
|
| Vangmayi wrote: |
| Code: |
DECLARE Eno CHARACTER;
SET Eno = Environment.Variables.DBResult[1].EMP_NO;
|
|
Is your EmpNo of CHAR type or INTEGER? If its being generated using a Sequence then I guess it might be INTEGER. You can CAST and try it.
As suggested by others here, its better to get the value using a DUAL or DUMMY1 before inserting the Primary Key into your first table. |
|
| Back to top |
|
|
|
|
