| Author |
Message
|
| wmb_wps_soa |
 Posted: Sun Feb 21, 2010 9:33 pm Post subject: Problem in using wildcard variables in message broker ESQL. |
 |
|
Acolyte
Joined: 19 Feb 2010
Posts: 65
Location: Detroit,Michigan,USA.
|
Hello All,
Im using broker version 6.1.0.5, my requirement is simple as below:
I have a variable named "wildCardInput", and declared as below:
DECLARE wildCardInput CHARACTER '%RENDR%';
Im using the SELECT function as below to get the value from database table. But it does not returns any value:
SET Environment.Variables.SourceList[] = SELECT ITEM T.VALUE from Database.
Table AS T WHERE T.VALUE LIKE wildCardInput;
I tried with PASSTHRU as well, but it too did not worked...
When I give the hardcode value (SET Environment.Variables.SourceList[] = SELECT ITEM T.VALUE from Database.
Table AS T WHERE T.VALUE LIKE'%RENDR%') it was working.
Could you please help me to resolve this issue?
Thank you in Advance,
Surendrakumar Jebakani |
|
| Back to top |
|
 |
| MrSmith |
| Posted: Tue Feb 23, 2010 2:38 am Post subject: |
|
|
Master
Joined: 20 Mar 2008
Posts: 215
|
Try wrapping the EVAL keyword round so that it is something like this
SET Environment.Variables.SourceList[] = EVAL('SELECT ITEM T.VALUE from Database.Table AS T WHERE T.VALUE LIKE' || wildCardInput); |
|
| Back to top |
|
|
| wmb_wps_soa |
| Posted: Tue Feb 23, 2010 4:09 am Post subject: Problem in using wildcard variables in message broker ESQL. |
|
|
Acolyte
Joined: 19 Feb 2010
Posts: 65
Location: Detroit,Michigan,USA.
|
Thank you very much....
I tried your solution, but it does not returns any value though there were few rows in the table for the condition...I tried changing the select query with EVAL..but no luck...
Could anybody please help?
Thanks |
|
| Back to top |
|
|
| mgk |
| Posted: Tue Feb 23, 2010 4:26 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1647
|
I think this should work, so it would be worth raising a PMR to get it checked out...
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Tue Feb 23, 2010 5:30 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| mgk wrote: |
| I think this should work, so it would be worth raising a PMR to get it checked out... |
Of course the first thing that anyone working that PMR is going to want to see is the user trace of the flow during execution.
Which may highlight what's going wrong anyway, and would save you the trouble of opening a PMR in the first place...
http://www-01.ibm.com/support/docview.wss?&uid=swg21177321 |
|
| Back to top |
|
|
| indu30111986 |
| Posted: Fri Feb 17, 2012 4:44 am Post subject: Environment variables not getting updated with the vaules |
|
|
Novice
Joined: 15 Nov 2011
Posts: 22
|
SET Environment.Variables.Reservation[] = SELECT X.* FROM Database.Indu.XMLPASSENGERTB AS X
WHERE X.FIRSTNAME = InputRoot.XMLNSC.PassengerQuery.FirstName
AND X.LASTNAME = InputRoot.XMLNSC.PassengerQuery.LastName;
I am trying to get some values based on values in my input..
but the environment variavles is not getting updated.
When i tried to hard code the value the query is working fine and environment variables is getting updated.
I tried the solution provided in this forum...
using EVAL ..but no luck
PLease help me... |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Fri Feb 17, 2012 5:01 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
Platform?
Broker Version + fixLevel
did you take a user trace and verify that the query was indeed building as expected?
Also,
It is usually better to open a new thread and refer to the old one via a URL Tag.
It makes like easier if your code is also formatted using CODE Tags.
For example
| Code: |
SET Environment.Variables.Reservation[] =
SELECT X.* FROM Database.Indu.XMLPASSENGERTB AS X
WHERE
X.FIRSTNAME = InputRoot.XMLNSC.PassengerQuery.FirstName
AND
X.LASTNAME = InputRoot.XMLNSC.PassengerQuery.LastName;
|
_________________
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 |
|
|
| mqjeff |
| Posted: Fri Feb 17, 2012 5:25 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
Are you sure that
| Code: |
| InputRoot.XMLNSC.PassengerQuery.FirstName |
is the correct path?
And it's not actually something like
| Code: |
| InputRoot.XMLNSC.ns1:PassengerQuery.ns2:FirstName |
? |
|
| Back to top |
|
|
| indu30111986 |
| Posted: Fri Feb 17, 2012 5:53 am Post subject: i have taken the user trace |
|
|
Novice
Joined: 15 Nov 2011
Posts: 22
|
| Code: |
2012-02-17 19:13:52.547264 5592 UserTrace BIP2537I: Node 'XML_PassengerQuery1.GetReservationsInformation': Executing statement ''BEGIN ... END;'' at ('.GetReservationsInformation.Main', '2.2').
2012-02-17 19:13:52.547317 5592 UserTrace BIP2537I: Node 'XML_PassengerQuery1.GetReservationsInformation': Executing statement ''SET OutputRoot = InputRoot;'' at ('.GetReservationsInformation.Main', '5.3').
2012-02-17 19:13:52.547397 5592 UserTrace BIP2539I: Node 'XML_PassengerQuery1.GetReservationsInformation': Evaluating expression ''InputRoot'' at ('.GetReservationsInformation.Main', '5.18'). This resolved to ''InputRoot''. The result was ''ROW... Root Element Type=16777216 NameSpace='' Name='Root' Value=NULL''.
2012-02-17 19:13:52.547431 5592 UserTrace BIP2568I: Node 'XML_PassengerQuery1.GetReservationsInformation': Copying sub-tree from ''InputRoot'' to ''OutputRoot''.
2012-02-17 19:13:52.547592 5592 UserTrace BIP2537I: Node 'XML_PassengerQuery1.GetReservationsInformation': Executing statement ''SET OutputRoot.XMLNSC.PassengerQuery = NULL;'' at ('.GetReservationsInformation.Main', '6.3').
2012-02-17 19:13:52.547733 5592 UserTrace BIP2567I: Node 'XML_PassengerQuery1.GetReservationsInformation': Assigning NULL to ''OutputRoot.XMLNSC.PassengerQuery'', thus deleting it.
2012-02-17 19:13:52.547943 5592 UserTrace BIP2537I: Node 'XML_PassengerQuery1.GetReservationsInformation': Executing statement ''DECLARE X, Y CHARACTER;'' at ('.GetReservationsInformation.Main', '7.3').
2012-02-17 19:13:52.548046 5592 UserTrace BIP2537I: Node 'XML_PassengerQuery1.GetReservationsInformation': Executing statement ''SET Environment.Variables.Reservation[ ] = (SELECT ROW (COLUMN(0) AS *:LASTNAME, COLUMN(1) AS *:FIRSTNAME, COLUMN(2) AS *:FLIGHTNO, COLUMN(3) AS *:FLIGHTDATE, COLUMN(4) AS *:CLASSTYPE, COLUMN(5) AS *:RESERVATIONNO) FROM DATABASE(, InputRoot.XMLNSC.PassengerQuery.FirstName, InputRoot.XMLNSC.PassengerQuery.LastName));'' at ('.GetReservationsInformation.Main', '10.3').
2012-02-17 19:13:52.548107 5592 UserTrace BIP2538I: Node 'XML_PassengerQuery1.GetReservationsInformation': Evaluating expression ''(SELECT ROW (COLUMN(0) AS *:LASTNAME, COLUMN(1) AS *:FIRSTNAME, COLUMN(2) AS *:FLIGHTNO, COLUMN(3) AS *:FLIGHTDATE, COLUMN(4) AS *:CLASSTYPE, COLUMN(5) AS *:RESERVATIONNO) FROM DATABASE(, InputRoot.XMLNSC.PassengerQuery.FirstName, InputRoot.XMLNSC.PassengerQuery.LastName))'' at ('.GetReservationsInformation.Main', '10.52').
2012-02-17 19:13:52.548221 5592 UserTrace BIP2573W: Node 'XML_PassengerQuery1.GetReservationsInformation': ('.GetReservationsInformation.Main', '10.52') : Finding first SELECT result.
2012-02-17 19:13:52.644790 5592 UserTrace BIP2539I: Node 'XML_PassengerQuery1.GetReservationsInformation': Evaluating expression ''InputRoot.XMLNSC.PassengerQuery.FirstName'' at ('.GetReservationsInformation.Main', '11.42'). This resolved to ''InputRoot.XMLNSC.PassengerQuery.FirstName''. The result was '''Mary'''.
2012-02-17 19:13:52.644882 5592 UserTrace BIP2539I: Node 'XML_PassengerQuery1.GetReservationsInformation': Evaluating expression ''InputRoot.XMLNSC.PassengerQuery.LastName'' at ('.GetReservationsInformation.Main', '12.39'). This resolved to ''InputRoot.XMLNSC.PassengerQuery.LastName''. The result was '''Smith'''.
2012-02-17 19:13:52.644916 5592 UserTrace BIP2544I: Node 'XML_PassengerQuery1.GetReservationsInformation': Executing database SQL statement ''SELECT X.LASTNAME, X.FIRSTNAME, X.FLIGHTNO, X.FLIGHTDATE, X.CLASSTYPE, X.RESERVATIONNO FROM Indu.XMLPASSENGERTB X WHERE ((X.FIRSTNAME)=(?))AND((X.LASTNAME)=(?))'' derived from ('', '1.1'); expressions ''InputRoot.XMLNSC.PassengerQuery.FirstName, InputRoot.XMLNSC.PassengerQuery.LastName''; resulting parameter values '''Mary', 'Smith'''.
2012-02-17 19:13:52.650104 5592 UserTrace BIP2539I: Node 'XML_PassengerQuery1.GetReservationsInformation': Evaluating expression ''DATABASE(, InputRoot.XMLNSC.PassengerQuery.FirstName, InputRoot.XMLNSC.PassengerQuery.LastName)'' at ('', '1.1'). This resolved to ''SELECT X.LASTNAME, X.FIRSTNAME, X.FLIGHTNO, X.FLIGHTDATE, X.CLASSTYPE, X.RESERVATIONNO FROM Indu.XMLPASSENGERTB X WHERE ((X.FIRSTNAME)=(?))AND((X.LASTNAME)=(?))''. The result was ''Complex result''.
2012-02-17 19:13:52.650836 5592 UserTrace BIP2570W: Node 'XML_PassengerQuery1.GetReservationsInformation': ('.GetReservationsInformation.Main', '10.52') : There were no items in the FROM clause satisfying the WHERE clause.
2012-02-17 19:13:52.650890 5592 UserTrace BIP2562I: Node 'XML_PassengerQuery1.GetReservationsInformation': Assigning a list to ''Environment.Variables.Reservation[]''.
2012-02-17 19:13:52.651256 5592 UserTrace BIP2537I: Node 'XML_PassengerQuery1.GetReservationsInformation': Executing statement ''CREATE FIELD OutputRoot.XMLNSC.PassengerInfoResponse.ListOfReservations;'' at ('.GetReservationsInformation.Main', '13.3').
2012-02-17 19:13:52.651397 5592 UserTrace BIP2537I: Node 'XML_PassengerQuery1.GetReservationsInformation': Executing statement ''DECLARE outres REFERENCE TO OutputRoot.XMLNSC.PassengerInfoResponse.ListOfReservations;'' at ('.GetReservationsInformation.Main', '14.3').
2012-02-17 19:13:52.651481 5592 UserTrace BIP2537I: Node 'XML_PassengerQuery1.GetReservationsInformation': Executing statement ''DECLARE I INTEGER 1;'' at ('.GetReservationsInformation.Main', '15.3').
2012-02-17 19:13:52.651546 5592 UserTrace BIP2537I: Node 'XML_PassengerQuery1.GetReservationsInformation': Executing statement ''DECLARE J INTEGER CARDINALITY(Environment.Variables.*:*[ ]);'' at ('.GetReservationsInformation.Main', '16.3').
2012-02-17 19:13:52.651603 5592 UserTrace BIP2543I: Node 'XML_PassengerQuery1.GetReservationsInformation': ('.GetReservationsInformation.Main', '16.33') : Failed to navigate to path element number '3' because it does not exist.
2012-02-17 19:13:52.651664 5592 UserTrace BIP2539I: Node 'XML_PassengerQuery1.GetReservationsInformation': Evaluating expression ''Environment.Variables.*:*[ ]'' at ('.GetReservationsInformation.Main', '16.33'). This resolved to ''Environment.Variables.*:*[]''. The result was ''EMPTY LIST''.
2012-02-17 19:13:52.651718 5592 UserTrace BIP2540I: Node 'XML_PassengerQuery1.GetReservationsInformation': Finished evaluating expression ''CARDINALITY(Environment.Variables.*:*[ ])'' at ('.GetReservationsInformation.Main', '16.21'). The result was ''0''.
2012-02-17 19:13:52.651756 5592 UserTrace BIP2537I: Node 'XML_PassengerQuery1.GetReservationsInformation': Executing statement ''WHILE I <= J DO ... END WHILE;'' at ('.GetReservationsInformation.Main', '17.3').
2012-02-17 19:13:52.651813 5592 UserTrace BIP2539I: Node 'XML_PassengerQuery1.GetReservationsInformation': Evaluating expression ''I'' at ('.GetReservationsInformation.Main', '17.9'). This resolved to ''I''. The result was ''1''.
2012-02-17 19:13:52.651847 5592 UserTrace BIP2539I: Node 'XML_PassengerQuery1.GetReservationsInformation': Evaluating expression ''J'' at ('.GetReservationsInformation.Main', '17.14'). This resolved to ''J''. The result was ''0''.
2012-02-17 19:13:52.651882 5592 UserTrace BIP2539I: Node 'XML_PassengerQuery1.GetReservationsInformation': Evaluating expression ''I <= J'' at ('.GetReservationsInformation.Main', '17.9'). This resolved to ''1 <= 0''. The result was ''FALSE''.
2012-02-17 19:13:52.651927 5592 UserTrace BIP2537I: Node 'XML_PassengerQuery1.GetReservationsInformation': Executing statement ''RETURN TRUE;'' at ('.GetReservationsInformation.Main', '24.3').
2012-02-17 19:13:52.652107 5592 UserTrace BIP4015I: Message propagated to the 'out' terminal of node 'XML_PassengerQuery1.GetReservationsInformation' with the following message trees: 'OutputLocalEnvironment, OutputRoot, OutputExceptionList'. |
|
|
| Back to top |
|
|
| Vitor |
| Posted: Fri Feb 17, 2012 5:55 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
Code tags inserted for the benefit of the readership.
Please use them for such inserts in future. This is the 2nd time in this thread it's been mentioned.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| Vitor |
| Posted: Fri Feb 17, 2012 5:59 am Post subject: Re: i have taken the user trace |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| indu30111986 wrote: |
| Code: |
2012-02-17 19:13:52.644790 5592 UserTrace BIP2539I: Node 'XML_PassengerQuery1.GetReservationsInformation': Evaluating expression ''InputRoot.XMLNSC.PassengerQuery.FirstName'' at ('.GetReservationsInformation.Main', '11.42'). This resolved to ''InputRoot.XMLNSC.PassengerQuery.FirstName''. The result was '''Mary'''.
2012-02-17 19:13:52.644882 5592 UserTrace BIP2539I: Node 'XML_PassengerQuery1.GetReservationsInformation': Evaluating expression ''InputRoot.XMLNSC.PassengerQuery.LastName'' at ('.GetReservationsInformation.Main', '12.39'). This resolved to ''InputRoot.XMLNSC.PassengerQuery.LastName''. The result was '''Smith'''. |
|
So we assume that the input message is looking for "Mary Smith".
| indu30111986 wrote: |
| Code: |
| 2012-02-17 19:13:52.650836 5592 UserTrace BIP2570W: Node 'XML_PassengerQuery1.GetReservationsInformation': ('.GetReservationsInformation.Main', '10.52') : There were no items in the FROM clause satisfying the WHERE clause. |
|
Seems kinda clear.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| indu30111986 |
| Posted: Fri Feb 17, 2012 6:53 am Post subject: |
|
|
Novice
Joined: 15 Nov 2011
Posts: 22
|
I still have entries in the datase for Mary Smith.
Thats why i wonder, why i am not getting the values.
SQL> SELECT * FROM Indu.XMLPASSENGERTB WHERE FIRSTNAME ='Mary' AND LASTNAME = 'S
mith';
LASTNAME FIRSTNAME FLIGH FLIGHTDA C
-------------------- -------------------- ----- -------- -
RESERVATIONNO
----------------------------------------
Smith Mary CA937 20030525 F
20030525CA9371
Smith Mary CA937 20030525 F
20030525CA9375
Smith Mary CA937 20030525 F
20030525CA93713
LASTNAME FIRSTNAME FLIGH FLIGHTDA C
-------------------- -------------------- ----- -------- -
RESERVATIONNO
----------------------------------------
Smith Mary CA937 20030525 F
20030525CA93717
Smith Mary CA937 20030525 F
20030525CA93721
Smith Mary CA937 20030525 F
20030525CA93725
LASTNAME FIRSTNAME FLIGH FLIGHTDA C
-------------------- -------------------- ----- -------- -
RESERVATIONNO
----------------------------------------
Smith Mary CA937 20030525 F
20030525CA9379
7 rows selected. |
|
| Back to top |
|
|
| murdeep |
| Posted: Fri Feb 17, 2012 8:32 am Post subject: |
|
|
Master
Joined: 03 Nov 2004
Posts: 211
|
| Verify that your ODBC definition points to the database that you think it should. Perhaps it is targetting a d/b that has no Mary Smiths in it. |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Fri Feb 17, 2012 11:08 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
| murdeep wrote: |
| Verify that your ODBC definition points to the database that you think it should. Perhaps it is targetting a d/b that has no Mary Smiths in it. |
Or a different Schema or user...
_________________
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 |
|
|
| indu30111986 |
| Posted: Fri Feb 17, 2012 11:19 pm Post subject: |
|
|
Novice
Joined: 15 Nov 2011
Posts: 22
|
The schema are the same.
In the user trace it is taking Indu.XMLPASSENGERTB
'XML_PassengerQuery1.GetReservationsInformation': Executing database SQL statement ''SELECT X.LASTNAME, X.FIRSTNAME, X.FLIGHTNO, X.FLIGHTDATE, X.CLASSTYPE, X.RESERVATIONNO FROM Indu.XMLPASSENGERTB X WHERE ((X.FIRSTNAME)=(?))AND((X.LASTNAME)=(?))'' derived from ('', '1.1'); expressions ''InputRoot.XMLNSC.PassengerQuery.FirstName, InputRoot.XMLNSC.PassengerQuery.LastName''; resulting parameter values '''Mary', 'Smith'''.
likewise in my sql command window i have given
SQL> SELECT * FROM Indu.XMLPASSENGERTB WHERE FIRSTNAME ='Mary' AND LASTNAME = 'S
mith';
the schema name is indu and from the database am able to retrive & rows when executed from command window |
|
| Back to top |
|
|
|
|
