| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| UPDATE statement failing in ESQL |
« View previous topic :: View next topic » |
| Author |
Message
|
| sarathmattam |
 Posted: Wed Jun 18, 2014 4:07 am Post subject: UPDATE statement failing in ESQL |
 |
|
Voyager
Joined: 05 Sep 2008
Posts: 94
|
Dear Experts,
I am facing an issue with an UPDATE statement. The code snippet is below.
| Code: |
CREATE PROCEDURE pUpdatePermitDetails ( IN vPpsId INTEGER, IN vPermitNo INTEGER, IN vPermitSeqNo INTEGER, IN vUdbNo INTEGER, IN vStatus INTEGER, IN vMsgId CHARACTER, IN vMsgBody BLOB, IN vTimeStamp TIMESTAMP, IN dbschema CHARACTER)
BEGIN
UPDATE Database.{dbschema}.PERMIT AS P SET PERMIT_STATUS = vStatus WHERE P.PERMIT_NO = vPermitNo;
INSERT INTO Database.{dbschema}.PERMIT_DETAIL VALUES (vMsgId, vPermitNo, vStatus, vTimeStamp, vMsgBody);
END;
|
Both the database statements are getting executed and I can’t find any errors in the trace file. But, only INSERT is happening in DB, no UPDATE. Surprisingly the UPDATE statement works when I hardcode the values, but when the same values are passed through variables, UPDATE is failing
I am working with WMB 8.0.2 and Oracle 11g in Windows7 environment.
Reagrds,
Sarath |
|
| Back to top |
|
 |
| ganesh |
| Posted: Wed Jun 18, 2014 9:06 am Post subject: |
|
|
Master
Joined: 18 Jul 2010
Posts: 294
|
| Are you passing the value for vPermitNo in your procedure call? |
|
| Back to top |
|
|
| sarathmattam |
| Posted: Wed Jun 18, 2014 12:09 pm Post subject: |
|
|
Voyager
Joined: 05 Sep 2008
Posts: 94
|
| Yes .. I can see values for both vStatus and vPermitNo in the trace file |
|
| Back to top |
|
|
| Simbu |
| Posted: Wed Jun 18, 2014 12:55 pm Post subject: Re: UPDATE statement failing in ESQL |
|
|
Master
Joined: 17 Jun 2011
Posts: 289
Location: Tamil Nadu, India
|
| sarathmattam wrote: |
Dear Experts,
I am facing an issue with an UPDATE statement. The code snippet is below.
| Code: |
CREATE PROCEDURE pUpdatePermitDetails ( IN vPpsId INTEGER, IN vPermitNo INTEGER, IN vPermitSeqNo INTEGER, IN vUdbNo INTEGER, IN vStatus INTEGER, IN vMsgId CHARACTER, IN vMsgBody BLOB, IN vTimeStamp TIMESTAMP, IN dbschema CHARACTER)
BEGIN
UPDATE Database.{dbschema}.PERMIT AS P SET PERMIT_STATUS = vStatus WHERE P.PERMIT_NO = vPermitNo;
INSERT INTO Database.{dbschema}.PERMIT_DETAIL VALUES (vMsgId, vPermitNo, vStatus, vTimeStamp, vMsgBody);
END;
|
Both the database statements are getting executed and I can’t find any errors in the trace file. But, only INSERT is happening in DB, no UPDATE. Surprisingly the UPDATE statement works when I hardcode the values, but when the same values are passed through variables, UPDATE is failing
I am working with WMB 8.0.2 and Oracle 11g in Windows7 environment.
Reagrds,
Sarath |
Please check you UPDATE query again. It should be P.PERMIT_STATUS = vStatus. you query should be like below
| Code: |
| UPDATE Database.{dbschema}.PERMIT AS P SET P.PERMIT_STATUS= vStatus WHERE P.PERMIT_NO = vPermitNo; |
|
|
| Back to top |
|
|
| sarathmattam |
| Posted: Wed Jun 18, 2014 10:20 pm Post subject: |
|
|
Voyager
Joined: 05 Sep 2008
Posts: 94
|
Hi Simbu .. Thank you. I tried, but not working
Copied relevant portion of trace file below
| Quote: |
2014-06-19 09:52:40.783480 13136 UserTrace BIP2537I: Node 'ae.emaratech.esb.visionmoi.permit.EntryPermitFlow.UpdateEPermit': Executing statement ''BEGIN ... END;'' at ('ae.emaratech.esb.common.pUpdatePermitDetails', '2.1').
2014-06-19 09:52:40.783508 13136 UserTrace BIP2537I: Node 'ae.emaratech.esb.visionmoi.permit.EntryPermitFlow.UpdateEPermit': Executing statement ''UPDATE Database.{dbschema}.PERMIT SET PERMIT_STATUS = ... WHERE ...'' at ('ae.emaratech.esb.common.pUpdatePermitDetails', '4.2').
2014-06-19 09:52:40.783528 13136 UserTrace BIP2539I: Node '': Evaluating expression ''dbschema'' at ('ae.emaratech.esb.common.pUpdatePermitDetails', '4.19'). This resolved to ''dbschema''. The result was '''ESBDEV'''.
2014-06-19 09:52:40.783596 13136 UserTrace BIP2539I: Node '': Evaluating expression ''vStatus'' at ('ae.emaratech.esb.common.pUpdatePermitDetails', '4.56'). This resolved to ''vStatus''. The result was ''1''.
2014-06-19 09:52:40.783624 13136 UserTrace BIP2539I: Node '': Evaluating expression ''vPermitNo'' at ('ae.emaratech.esb.common.pUpdatePermitDetails', '4.89'). This resolved to ''vPermitNo''. The result was ''2144314000602''.
2014-06-19 09:52:40.783648 13136 UserTrace BIP2544I: Node 'ae.emaratech.esb.visionmoi.permit.EntryPermitFlow.UpdateEPermit': Executing database SQL statement ''UPDATE ESBDEV.PERMIT SET PERMIT_STATUS = ? WHERE (PERMIT_NO)=(?)'' derived from ('ae.emaratech.esb.common.pUpdatePermitDetails', '4.2'); expressions ''vStatus, vPermitNo''; resulting parameter values ''1, 2144314000602''.
2014-06-19 09:52:40.797004 13136 UserTrace BIP2537I: Node 'ae.emaratech.esb.visionmoi.permit.EntryPermitFlow.UpdateEPermit': Executing statement ''INSERT INTO Database.{dbschema}.PERMIT_DETAIL ( ) VALUES (vMsgId, vPermitNo, vStatus, vTimeStamp, vMsgBody ) '' at ('ae.emaratech.esb.common.pUpdatePermitDetails', '6.2').
2014-06-19 09:52:40.797052 13136 UserTrace BIP2539I: Node '': Evaluating expression ''dbschema'' at ('ae.emaratech.esb.common.pUpdatePermitDetails', '6.24'). This resolved to ''dbschema''. The result was '''ESBDEV'''.
2014-06-19 09:52:40.797120 13136 UserTrace BIP2539I: Node '': Evaluating expression ''vMsgId'' at ('ae.emaratech.esb.common.pUpdatePermitDetails', '6.56'). This resolved to ''vMsgId''. The result was '''6d549677-d901-4ea2-9320-8e830b101e2e'''.
2014-06-19 09:52:40.797148 13136 UserTrace BIP2539I: Node '': Evaluating expression ''vPermitNo'' at ('ae.emaratech.esb.common.pUpdatePermitDetails', '6.64'). This resolved to ''vPermitNo''. The result was ''2144314000602''.
2014-06-19 09:52:40.797172 13136 UserTrace BIP2539I: Node '': Evaluating expression ''vStatus'' at ('ae.emaratech.esb.common.pUpdatePermitDetails', '6.75'). This resolved to ''vStatus''. The result was ''1''.
2014-06-19 09:52:40.797212 13136 UserTrace BIP2539I: Node '': Evaluating expression ''vTimeStamp'' at ('ae.emaratech.esb.common.pUpdatePermitDetails', '6.84'). This resolved to ''vTimeStamp''. The result was ''TIMESTAMP '2014-06-19 09:52:40.558499'''.
2014-06-19 09:52:40.797432 13136 UserTrace BIP2539I: Node '': Evaluating expression ''vMsgBody'' at ('ae.emaratech.esb.common.pUpdatePermitDetails', '6.96'). This resolved to ''vMsgBody''. The result was ''X'3c4e53313a557064617465456e7472795065726d697452657120786'''.
2014-06-19 09:52:40.797860 13136 UserTrace BIP2544I: Node 'ae.emaratech.esb.visionmoi.permit.EntryPermitFlow.UpdateEPermit': Executing database SQL statement ''INSERT INTO ESBDEV.PERMIT_DETAIL VALUES (?, ?, ?, ?, ?)'' derived from ('ae.emaratech.esb.common.pUpdatePermitDetails', '6.2'); expressions ''vMsgId, vPermitNo, vStatus, vTimeStamp, vMsgBody''; resulting parameter values '''6d549677-d901-4ea2-9320-8e830b101e2e', 2144314000602, 1, TIMESTAMP '2014-06-19 09:52:40.558499', X'3c4e53313a557064617465456e7472795065726d697452657120786'''.
2014-06-19 09:52:40.832440 13136 UserTrace BIP2537I: Node 'ae.emaratech.esb.visionmoi.permit.EntryPermitFlow.UpdateEPermit': Executing statement ''RETURN FALSE;'' at ('ae.emaratech.esb.visionmoi.permit.BuildUpdateEntryPermitMessage.Main', '126.4').
2014-06-19 09:52:40.832612 13136 UserTrace BIP2537I: Node 'ae.emaratech.esb.visionmoi.permit.EntryPermitFlow.FindByEPermitNo': Executing statement ''RETURN FALSE;'' at ('ae.emaratech.esb.visionmoi.permit.FindByPermitNoMessage.Main', '54.3').
|
Regards,
Sarath |
|
| Back to top |
|
|
| aggarwal.intouch |
| Posted: Thu Jun 19, 2014 1:22 am Post subject: |
|
|
Acolyte
Joined: 30 May 2011
Posts: 56
Location: India
|
| Similar kind of situation working for me. Kindly check values in database. There could be the value mismatch scenario. |
|
| Back to top |
|
|
| sarathmattam |
| Posted: Fri Jun 20, 2014 8:35 pm Post subject: |
|
|
Voyager
Joined: 05 Sep 2008
Posts: 94
|
Hi Aggarwal,
Thanks .. I was also thinking of that possible mismatch scenario. So I hard coded the same values in code and ran it once. Then it updated the table successfully .. When values are passed in variables to the procedure, this behaviour is seen. Most probably I might be doing something wrong as its very simple UPDATE statement. but even after spending some time on it I am not able to spot that point where it goes wrong. 
Regards,
Sarath |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Fri Jun 20, 2014 10:37 pm Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
Perhaps the hard coding performed an implicit 'CAST'.
IMHO Oracle is very pernickerty when it comes to data types.
[/code]
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| sarathmattam |
| Posted: Sun Jun 22, 2014 3:02 am Post subject: |
|
|
Voyager
Joined: 05 Sep 2008
Posts: 94
|
I fixed it .. Its quite a crazy way though .. I really dont know why the UPDATE is happening only when I CAST it to CHARACTER even though the field is declared as INTEGER in Oracle DB
| Code: |
CREATE PROCEDURE pUpdatePermitDetails ( IN vPpsId INTEGER, IN vPermitNo INTEGER, IN vPermitSeqNo INTEGER, IN vUdbNo INTEGER, IN vStatus INTEGER, IN vMsgId CHARACTER, IN vMsgBody BLOB, IN vTimeStamp TIMESTAMP, IN dbschema CHARACTER)
BEGIN
UPDATE Database.{dbschema}.PERMIT AS P SET PERMIT_STATUS = vStatus WHERE P.PERMIT_NO = [b]TRIM(CAST(vPermitNo AS CHARACTER));[/b]
INSERT INTO Database.{dbschema}.PERMIT_DETAIL VALUES (vMsgId, vPermitNo, vStatus, vTimeStamp, vMsgBody);
END; |
Regards,
Sarath |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
|
|
