| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Using CorrelId in WHERE clause of database UPDATE.... |
« View previous topic :: View next topic » |
| Author |
Message
|
| Empeterson |
 Posted: Thu Aug 14, 2003 10:07 am Post subject: Using CorrelId in WHERE clause of database UPDATE.... |
 |
|
Centurion
Joined: 14 Apr 2003
Posts: 125
Location: Foxboro, MA
|
Hello,
We have an app that puts a transaction to a queue for WMQI to pick up, and inserts a row into database for statistical reasons and uses the last 16 characters of the CorrelId as the unique identifier. Throughout various points in the flow, I am updating the previously inserted row with certain information.
I use the following esql to grab those characters in WMQI:
SET OutputLocalEnvironment.Variables.StatusID = SUBSTRING
("InputRoot"."MQMD"."CorrelId" FROM 17);
This is the esql used to update the database in the various nodes:
UPDATE Database."PIF_FNOL_STATUS"
SET C_LOCATION = 'DEV'
,C_STATUS = 'FLOWSTART'
WHERE "PIF_FNOL_STATUS"."N_PIF_FNOL_STS_ID"=LocalEnvironment.Variables.StatusID;
When I run in debug mode, my StatusID is exactly what it should be, ie that value exists in the table. But, I am getting an exception thrown that says that no rows were found. Just as a test, I tried inserting a row into the table with that StatusId, and a row got inserted but the value was different than what it said it was in debugger. My question is, is there a conversion going on behind the scenes? How can I get it to not convert? My broker is running on AIX and the database I am updating resides on os/390.
_________________
IBM Certified Specialist: MQSeries
IBM Certified Specalist: Websphere MQ Integrator |
|
| Back to top |
|
 |
| Empeterson |
| Posted: Thu Aug 14, 2003 11:54 am Post subject: |
|
|
Centurion
Joined: 14 Apr 2003
Posts: 125
Location: Foxboro, MA
|
To clarify the problem a little bit, the CorrelId field in the MQMD is a hex field. The field in the database is a CHAR field of length 16. I guess to make this a little simpler, what I need to do is transform a hex value, say x'A1' to a character field with a value of 'A1', not to what x'A1' would convert to. Hopefully that simplifies the problem a bit. 
_________________
IBM Certified Specialist: MQSeries
IBM Certified Specalist: Websphere MQ Integrator |
|
| Back to top |
|
|
| EddieA |
| Posted: Thu Aug 14, 2003 3:55 pm Post subject: |
|
|
Jedi
Joined: 28 Jun 2001
Posts: 2453
Location: Los Angeles
|
You need to CAST the Correlid AS CHAR before the SUBSTRING.
SET OutputLocalEnvironment.Variables.StatusID = SUBSTRING
(CAST("InputRoot"."MQMD"."CorrelId" AS CHAR) FROM 35 FOR  ;
Cheers,
_________________
Eddie Atherton
IBM Certified Solution Developer - WebSphere Message Broker V6.1
IBM Certified Solution Developer - WebSphere Message Broker V7.0 |
|
| Back to top |
|
|
| EgilsJ.Rubenis |
| Posted: Thu Aug 14, 2003 11:46 pm Post subject: |
|
|
Acolyte
Joined: 18 Nov 2002
Posts: 63
Location: Germany, Alfeld
|
Hi,
I'm making inserts into a DB2-Table on OS390 via CicsTxn. In ESQL I'm just passing the whole MsgID. The receiving table field is CHAR 24.
The Message comes from SUN going to OS390
SET "OutputRoot"."MRM"."XML_HEADER"."AUSLOESER_MSG_ID" = "InputRoot"."MQMD"."MsgId";
Are you sure you're fine with using just a part of the ID? You
could become problems with duplicates on your table?
Egils |
|
| Back to top |
|
|
| Empeterson |
| Posted: Fri Aug 15, 2003 7:24 am Post subject: |
|
|
Centurion
Joined: 14 Apr 2003
Posts: 125
Location: Foxboro, MA
|
Thank you Eddie, that worked. I had to do an UPPER as well, as the letters were lowercased in WMQI and were uppercase in the database. Took a few minutes to figure that one out.
EgilsJ.Rubenis,
I assume the folks who wrote the application that initially inserts the row are making sure those 16 characters are unique. But then again, assuming anything in this business only causes more problems. Perhaps I should ask them.
Thank you all for the assistance.
_________________
IBM Certified Specialist: MQSeries
IBM Certified Specalist: Websphere MQ Integrator |
|
| 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
|
|
|
|
