| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| IIB9: DatabaseRoute Node, Special Characters |
« View previous topic :: View next topic » |
| Author |
Message
|
| akil |
 Posted: Wed Aug 20, 2014 1:28 am Post subject: IIB9: DatabaseRoute Node, Special Characters |
 |
|
Partisan
Joined: 27 May 2014
Posts: 338
Location: Mumbai
|
Hi
The documentation for the DatabaseRoute node says the following
| Quote: |
For example, in a table called Employee, a database column called LastName that is defined as char(10) with the value 'Smith', is returned as 'Smith ', therefore the filter expression must be:
$Employee_LastName = 'Smith '
|
It appears that the the XPath expressions follow a convention (tableName_columnName)
I have a MSSQL database, with table names such as HO-PROCESS, and USER_PROFILE (with a hyphen or underscore).
Can I still use the this node , given that the table & column names are what they are? if so, how do I this?
Regards
_________________
Regards |
|
| Back to top |
|
 |
| smdavies99 |
| Posted: Wed Aug 20, 2014 1:46 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
Have you tried it?
If so what was the result?
The reason I ask is that you are in the best position to try using the information from the documentation. You have a system with the tables and columns already setup and available, we don't (or may not have)
_________________
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 |
|
|
| akil |
| Posted: Wed Aug 20, 2014 2:05 am Post subject: |
|
|
Partisan
Joined: 27 May 2014
Posts: 338
Location: Mumbai
|
Hi
I am trying, the '[' is rejected as an invalid XPath expression,
Since it appears the broker creates XPath variables from table & column names, I guessed that there will be some pattern that's applied for column names that result in invalid XPath variables, I was hoping that someone could tell me , I've not found that in the documentation .
Is there a way to figure out the available XPath variable names given a set of table & column names in the database route node?
Following is the query shown in the database route node, I am trying to figure out how to access the table/column name combination in the filter expression table..
| Code: |
SELECT [HO-GROUP-STAGING].[IS-ERROR], [HO-GROUP-STAGING].ERROR_DESCRIPTION
FROM [HO-GROUP-STAGING]
WHERE [HO-GROUP-STAGING].BATCHID = ?
AND [HO-GROUP-STAGING].EXTERNALGROUPNO = ?
ORDER BY [HO-GROUP-STAGING].[IS-ERROR] ASC, [HO-GROUP-STAGING].ERROR_DESCRIPTION ASC
|
_________________
Regards |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Wed Aug 20, 2014 5:12 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Have you tried putting the offending names between double quotes rather than square brackets?
| Code: |
SELECT "HO-GROUP-STAGING"."IS-ERROR", "HO-GROUP-STAGING"."ERROR_DESCRIPTION"
FROM "HO-GROUP-STAGING"
WHERE "HO-GROUP-STAGING".BATCHID = ?
AND "HO-GROUP-STAGING".EXTERNALGROUPNO = ?
ORDER BY "HO-GROUP-STAGING"."IS-ERROR" ASC, "HO-GROUP-STAGING"."ERROR_DESCRIPTION" ASC |
Using a db table alias after the table name would have made that SQL command much easier to read. And I thought you could not use the order by clause in ESQL? Does that mean your select is a pass-through?
_________________
MQ & Broker admin
Last edited by fjb_saper on Wed Aug 20, 2014 3:17 pm; edited 1 time in total |
|
| Back to top |
|
|
| akil |
| Posted: Wed Aug 20, 2014 7:55 am Post subject: |
|
|
Partisan
Joined: 27 May 2014
Posts: 338
Location: Mumbai
|
Hi
I didn't hand write the SQL, I used the database route node, and added the table name and column names in the properties tab... That generated this SQL..
But when I try to add the filter expression , which is to route to different terminals, I get stuck, I am not sure how to construct the Xpath variable... The convention as per the documentation says use tablename_columnname, but the names aren't friendly as you see..
I tried quotes, did not work , I can't even trace the node ( because I don't know how to )... Is there a way? ( I can't change tnphe names, they are external to this project )
_________________
Regards |
|
| Back to top |
|
|
| akil |
| Posted: Wed Aug 20, 2014 10:06 pm Post subject: |
|
|
Partisan
Joined: 27 May 2014
Posts: 338
Location: Mumbai
|
Hi
Still no luck, following is the trace output .. the SQL query works with the offending column name as long as I put [] or "" around it, but I am unable to figure out what kind of variable name is being created for such a column ..
Searched through the documentation, could not find any clue,
| Quote: |
The JDBC connection URL is ''jdbc:sqlserver://10.211.55.4:1433;DatabaseName=contact admin;user=finesb;password=xxxxxxxx''.
No user action required.
2014-08-21 11:33:15.880627 24508 UserTrace BIP6258I: Executing a prepared SQL query with parameters against a database connected to by node: 'Broker 'IB9NODE'; Execution Group 'Q'; Message Flow 'openGroupLoanAccount.Main'; Node 'openGroupLoanAccount/openGroup.Database Route'; Node Type 'DatabaseRoute'.
The parameterized SQL query statement is ''SELECT H.[IS-ERROR] FROM [HO-GROUP-STAGING] H WHERE H.BATCHID = ? AND H.EXTERNALGROUPNO = ? ORDER BY H.[IS-ERROR] ASC''.
No user action required.
2014-08-21 11:33:15.880713 24508 UserTrace BIP6264I: Setting the value of a designated parameter within a prepared parameterized SQL query used by node: ''.
Setting value of type ''com.ibm.broker.plugin.MbElement'' for parameter at position ''1'' with a value of ''MbElement( type: 3000000 name: batchID value: 200 )''.
No user action required.
2014-08-21 11:33:15.880734 24508 UserTrace BIP6260I: Setting the value of a designated parameter within node: 'Broker 'IB9NODE'; Execution Group 'Q'; Message Flow 'openGroupLoanAccount.Main'; Node 'openGroupLoanAccount/openGroup.Database Route'; Node Type 'DatabaseRoute'.
Setting value of ''200'' into parameter.
No user action required.
2014-08-21 11:33:15.880802 24508 UserTrace BIP6264I: Setting the value of a designated parameter within a prepared parameterized SQL query used by node: ''.
Setting value of type ''com.ibm.broker.plugin.MbElement'' for parameter at position ''2'' with a value of ''MbElement( type: 3000000 name: externalID value: SW288838 )''.
No user action required.
2014-08-21 11:33:15.880814 24508 UserTrace BIP6260I: Setting the value of a designated parameter within node: 'Broker 'IB9NODE'; Execution Group 'Q'; Message Flow 'openGroupLoanAccount.Main'; Node 'openGroupLoanAccount/openGroup.Database Route'; Node Type 'DatabaseRoute'.
Setting value of ''SW288838'' into parameter.
No user action required.
2014-08-21 11:33:15.882436 24508 UserTrace BIP6254I: Evaluating the contents of a result set returned from a database query in node: 'Broker 'IB9NODE'; Execution Group 'Q'; Message Flow 'openGroupLoanAccount.Main'; Node 'openGroupLoanAccount/openGroup.Database Route'; Node Type 'DatabaseRoute'.
Extracting an SQL data type ''bit'' for qualified database column name ''H_[IS-ERROR]'' returned for row number ''0'' in the result set.
No user action required.
2014-08-21 11:33:15.882474 24508 UserTrace BIP6255I: Evaluating the contents of a result set returned from a database query in node: 'Broker 'IB9NODE'; Execution Group 'Q'; Message Flow 'openGroupLoanAccount.Main'; Node 'openGroupLoanAccount/openGroup.Database Route'; Node Type 'DatabaseRoute'.
Extracting a value of ''false'' for qualified database column name ''H_[IS-ERROR]''.
No user action required.
2014-08-21 11:33:15.882946 24508 Error BIP2628E: Exception condition detected on input node 'openGroupLoanAccount.Main.MQ Input'.
The input node 'openGroupLoanAccount.Main.MQ Input' detected an error whilst processing a message. The message flow has been rolled-back and, if the message was being processed in a unit of work, it will remain on the input queue to be processed again. Following messages will indicate the cause of this exception.
Check the error messages which follow to determine why the exception was generated, and take action as described by those messages.
2014-08-21 11:33:15.882957 24508 RecoverableException BIP2230E: Error detected whilst processing a message in node 'openGroupLoanAccount.Main.openGroupLoanAccount/openGroup.Database Route'.
The message broker detected an error whilst processing a message in node 'openGroupLoanAccount.Main.openGroupLoanAccount/openGroup.Database Route'. An exception has been thrown to cut short the processing of the message.
See the following messages for details of the error.
2014-08-21 11:33:15.882959 24508 RecoverableException BIP4388W: XPath: The variable ''H_IS-ERROR'' is not bound to a value.
The XPath processor encountered an unbound variable reference in the expression.
Bind a value to the variable reference before evaluating the expression.
|
How do I proceed?
_________________
Regards |
|
| Back to top |
|
|
| akil |
| Posted: Wed Aug 20, 2014 10:39 pm Post subject: |
|
|
Partisan
Joined: 27 May 2014
Posts: 338
Location: Mumbai
|
Hi
Found the way,
For writing the Query Elements, there are 2 options - [IS-ERROR] or "IS-ERROR". While these are the same as far as the JDBC is concerned, [IS-ERROR] is not a valid ESQL expression.
So I use "IS-ERROR" in the Query Element, and I used $H_"IS-ERROR" in the Filter Expression Table, and then it worked.
Phew 
_________________
Regards |
|
| 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
|
|
|
|
