ASG
IBM
Zystems
Cressida
Icon
Netflexity
 
  MQSeries.net
Search  Search       Tech Exchange      Education      Certifications      Library      Info Center      SupportPacs      LinkedIn  Search  Search                                                                   FAQ  FAQ   Usergroups  Usergroups
 
Register  ::  Log in Log in to check your private messages
 
RSS Feed - WebSphere MQ Support RSS Feed - Message Broker Support

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Problem in using wildcard variables in message broker ESQL.

Post new topic  Reply to topic Goto page 1, 2  Next
 Problem in using wildcard variables in message broker ESQL. « View previous topic :: View next topic » 
Author Message
wmb_wps_soa
PostPosted: Sun Feb 21, 2010 9:33 pm    Post subject: Problem in using wildcard variables in message broker ESQL. Reply with quote

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
View user's profile Send private message
MrSmith
PostPosted: Tue Feb 23, 2010 2:38 am    Post subject: Reply with quote

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
View user's profile Send private message
wmb_wps_soa
PostPosted: Tue Feb 23, 2010 4:09 am    Post subject: Problem in using wildcard variables in message broker ESQL. Reply with quote

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
View user's profile Send private message
mgk
PostPosted: Tue Feb 23, 2010 4:26 am    Post subject: Reply with quote

Padawan

Joined: 31 Jul 2003
Posts: 1642

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
View user's profile Send private message
mqjeff
PostPosted: Tue Feb 23, 2010 5:30 am    Post subject: Reply with quote

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
View user's profile Send private message
indu30111986
PostPosted: Fri Feb 17, 2012 4:44 am    Post subject: Environment variables not getting updated with the vaules Reply with quote

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
View user's profile Send private message
smdavies99
PostPosted: Fri Feb 17, 2012 5:01 am    Post subject: Reply with quote

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
View user's profile Send private message
mqjeff
PostPosted: Fri Feb 17, 2012 5:25 am    Post subject: Reply with quote

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
View user's profile Send private message
indu30111986
PostPosted: Fri Feb 17, 2012 5:53 am    Post subject: i have taken the user trace Reply with quote

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
View user's profile Send private message
Vitor
PostPosted: Fri Feb 17, 2012 5:55 am    Post subject: Reply with quote

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
View user's profile Send private message
Vitor
PostPosted: Fri Feb 17, 2012 5:59 am    Post subject: Re: i have taken the user trace Reply with quote

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
View user's profile Send private message
indu30111986
PostPosted: Fri Feb 17, 2012 6:53 am    Post subject: Reply with quote

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
View user's profile Send private message
murdeep
PostPosted: Fri Feb 17, 2012 8:32 am    Post subject: Reply with quote

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
View user's profile Send private message
smdavies99
PostPosted: Fri Feb 17, 2012 11:08 am    Post subject: Reply with quote

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
View user's profile Send private message
indu30111986
PostPosted: Fri Feb 17, 2012 11:19 pm    Post subject: Reply with quote

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
View user's profile Send private message
Display posts from previous:   
Post new topic  Reply to topic Goto page 1, 2  Next Page 1 of 2

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Problem in using wildcard variables in message broker ESQL.
Jump to:  



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
Protected by Anti-Spam ACP
 
 


Theme by Dustin Baccetti
Powered by phpBB © 2001, 2002 phpBB Group

Copyright © MQSeries.net. All rights reserved.