| Author |
Message
|
| schau31 |
 Posted: Thu Jan 22, 2015 3:30 am Post subject: Calling select statement from Graphical Map - WMB 8.0 |
 |
|
Novice
Joined: 22 Jan 2015
Posts: 10
|
Hello,
I'm quite new to WMB, and I'm fighting with a (simple) problem since a few days :
I have a mapping node in one of my flows and want to perform a simple 'select' request on a DB2 schema to replace one field of my incoming message before returning it.
At runtime, I receive following exception:
(0x03000000:NameValue):Text = 'com.ibm.db2.jcc.am.SqlSyntaxErrorException: [jcc][10145][10292][3.67.27] Invalid parameter 2 : Parameter index is out of range. ERRORCODE=-4461, SQLSTATE=42815
The thing is that I only pass one parameter to my query in may mapping node...
In the log file, I can see following (just before receiving the exception) :
(0x03000000:NameValue):Text = 'Static namespace declarations: let $dbselect := jdbc:select('SFP', 'select /*snstart*/MYSCHEMA./*tnstart*/MYTABLE/*tnend*/.MYTABLEID from /*snstart*/MYSCHEMA./*tnstart*/MYTABLE/*tnend*/ where /*snstart*/MYSCHEMA./*tnstart*/MYTABLE/*tnend*/.MYTABLEFIELD= ? ', ($Body0/SMaint/SMaintEvs/CustomSegment/TN), xs:int('0'), false(),'', xs:int('1') )' (CHARACTER)
I don't understand the end of the line : "...xs:int('0'), false(),'', xs:int('1')" but it seems to be the reason of the failure, no ?
Thank you for your help
Sebastien |
|
| Back to top |
|
 |
| schau31 |
| Posted: Thu Jan 22, 2015 6:24 am Post subject: |
|
|
Novice
Joined: 22 Jan 2015
Posts: 10
|
A few progress since my first post : to figure out what happened, I replaced the parameter used in the select query by a hard-coded value :
It resulted in something like : Select MYSCHEMA.MYTABLE.MYTABLEID where MYSCHEMA.MYTABLE.MYTABLEID = 'FOO', and it successfully retrieved a value from the DB, but of course it
was always the same value...
So now, back to my requirement : put a parameter in the query so that it behaves according to the content of incoming messages.
The point is that my incoming message has multiple cardinalities for some elements like this :
<Elements>
<Element>
<Field1>value11</Field1>
<Field2>value12</Field2>
</Element>
<Element>
<Field1>value21</Field1>
<Field2>value22</Field2>
</Element>
</Elements>
I would like to have something like this as the output of the mapping node :
<Elements>
<Element>
<Field1>myDBValue1</Field1>
<Field2>value12</Field2>
</Element>
<Element>
<Field1>myDBValue2</Field1>
<Field2>value22</Field2>
</Element>
</Elements>
For now, with my intermediate solution, I get something like (always the same returned value from the DB as I always perform the exact same request) :
<Elements>
<Element>
<Field1>myDBValue</Field1>
<Field2>value12</Field2>
</Element>
<Element>
<Field1>myDBValue</Field1>
<Field2>value22</Field2>
</Element>
</Elements>
To sum up, I would like the mapping node to perform a request in DB for each and every "Element" of my incoming request, to retrieve the right value to fill the output message.
Something that could be like : Select MYSCHEMA.MYTABLE.MYTABLEID where MYSCHEMA.MYTABLE.MYTABLEID = ($Body0/Elements/Element[index]/Field1)
So my questions are :
What could be the syntax/way to manage "index" in previous example ? In the graphical editor of the mapping node, I could only build something like '($Body0/Elements/Element/Field1)' so it seems to ignore the cardinality of the incoming elements...
How can I assign the result of the Select query to the right location (index) in the outcome of the node ?
Thanks to those who read this message !
Sebastien |
|
| Back to top |
|
|
| martinb |
| Posted: Thu Jan 22, 2015 8:44 am Post subject: |
|
|
Master
Joined: 09 Nov 2006
Posts: 210
Location: UK
|
Looks like you should be placing your Select transform within the nested mapping of a For each transform that is iterating over "Element".
Then the insert to your select Where clause is the singleton "Field1" from teh current iteration of "Element".
So wire For each between source / target Element, move into nested mapping and add the Select transform at that point |
|
| Back to top |
|
|
| schau31 |
| Posted: Fri Jan 23, 2015 8:01 am Post subject: |
|
|
Novice
Joined: 22 Jan 2015
Posts: 10
|
Hi martinb, and thanks for your help.
I tried to implement your solution, so added a "For each" transform on the Element, and in the nested map, tried to perform the right select query to my DB, but couldn't figure out the right syntax to use for the XPath part of the query : I obtain something like :
Select MYSCHEMA.MYTABLE.MYTABLEID where MYSCHEMA.MYTABLE.MYTABLEFIELDID = ?
And the ? part has to be a XPath expression. In the graphical editor, on the right side ("Available inputs for column values"), I can see :
$Element1 and below its children (Field1 & Field2), as well as $Element1-index, but I tried both syntaxes without any success...
Should I use somehow some brackets or is there a specific syntax to get the current occurrence in the XPath expression ?
(By the way, I use WMB V8.0)
Thanks again
Sebastien |
|
| Back to top |
|
|
| martinb |
| Posted: Sat Jan 24, 2015 4:28 am Post subject: |
|
|
Master
Joined: 09 Nov 2006
Posts: 210
Location: UK
|
Hi Sebastien
The Graphical Data Mapper allows you to build the Where clause though simple drag and drop.
So you should be constructing it by dragging the relevant table column, the "=" operator and then the relevant element from your input message which is in the "Available inputs", and from your description sounds like what you want is the "Field1" element. You should be able to expand the tree view in "Available inputs" and then drag "Field1" onto the Where clause drop site.
The resulting XPath added by the action will be something like
|
|
| Back to top |
|
|
| schau31 |
| Posted: Mon Jan 26, 2015 8:51 am Post subject: |
|
|
Novice
Joined: 22 Jan 2015
Posts: 10
|
Hi Martinb,
Thanks again for your help. The solution you suggested is what I intended to do, but it drove me crazy...
Let's try to clarify:
When I enter the nested map of the "for each" element, I am able to create a "move" transform between "Element" box on the left and "Element" box on the right.
If I run my flow like this, it works perfectly, it returns me a copy of my incoming message 
Next step:
Still in the nested map, I select "Select rows from a database" : it opens me the "New Database Select" popup window, in which I "build" the query as you mentioned :
On left side ("Choose the columns to include"), I select MYTABLE.MYTABLEID => It updates the right side of the popup with the proper table column so that I create the "where" clause :
I remove everything in the "SQL where clause" input area (by default, this area contains "1=1"), and drag the 3 part of the clause as you described : first the MYTABLE.MYFIELD column, then the "=" operator, and finally the Field1 (after expending the "Available inputs"). The result in the "SQL where clause" area looks like :
| Code: |
| MYTABLE.MYFIELD = ? |
And the XPath table underneath is updated with a single row : the place holder "?" is associated with the XPath expression
When I validate this window, it updates the nested map with a new "Select from DB" box on the left side. If I expand the "ResultSet" in this box, I can see "MYTABLEID" as expected...
Then I connect this "MYTABLEID" to the "Field1" on the right side (outgoing message), and it automatically creates a "Move tranform".
I have to fix 2 errors then before being able to run the flow :
I have to set a cardinality to indicate that I want to get the (for instance) first element of the resultSet.
I also have to "group in an override group" this second "Move" transform with the one above (at "Element" level)
At this point, I can't see any visible problem in the graphical editor, but if I try to run my flow, I get following error :
....
Text:CHARACTER:Caught BrokerXCIDynamicException
Insert
Type:INTEGER:5
Text:CHARACTER:IXJXE0797E: [ERR 0712][ERR XPST0008] No binding was found for the external variable 'Elements1'
Any complementary idea ?
Thank you again for your patience ! |
|
| Back to top |
|
|
| martinb |
| Posted: Mon Jan 26, 2015 9:18 am Post subject: |
|
|
Master
Joined: 09 Nov 2006
Posts: 210
Location: UK
|
Hi Sebastien
Sounds like you have correctly constructed the mapping.
However I suspect you are not at a recent fixpack level of the WMB product runtime code.
The issue you are describing is a match to:
IC95988: GRAPHICAL DATA MAP DATABASE TRANSFORM ERROR NO BINDING FOR VAR
This shipped in GDM 1.0.4.0 iFix2. The Broker Fixpack pages detail the level of GDM they ship, for example
-IBM Graphical Data Mapper version 1.0.4.0 IFix007 is included with WMB 8 Fix Pack 4, 8.0.0.4
- IBM Graphical Data Mapper Version 1.0.4.0 iFix008 is included with IIB 9 Fix Pack 2, 9.0.0.2.
Hope this gets you running |
|
| Back to top |
|
|
| schau31 |
| Posted: Tue Jan 27, 2015 12:14 am Post subject: |
|
|
Novice
Joined: 22 Jan 2015
Posts: 10
|
Hi Martinb
I had a look at the problem you mentionned in your last post : it seems that the defect "IC95988" has been fixed in the GDM V1.0.4.0 iFix3
(instead of V1.0.4.0 iFix2), but I could not figure out how to know which version of the broker ships this fix...
It seems that I'm using WMB 8.0.0.1 according to the result of the "mqsiservice -v" command hereafter:
| Code: |
BIPmsgs fr_FR
Console OEM CP=850, ICU CCSID=5348
Default codepage=ibm-5348_P100-1997, in ascii=ibm-5348_P100-1997
JAVA console codepage name=cp850
BIP8996I: Version : 8001
BIP8997I: Produit : WebSphere Message Broker
BIP8998I: Niveau CMVC : S800-L120815
BIP8999I: Type de génération : Production, 32 bit, x86_nt_4
|
Anyway, when I read the error description, it seems that it is a non-bloquing issue as there is a "local fix" suggestion, but the "local fix" and the "error description" seem inconsistent:
Error description:
...
The problem occurs when the order of the inputs on the nested transform becomes such that the Database operation is no-longer the last input.
Local fix:
The user can use the "Order" properties page on the nested transform containing the database transform to manually reorder the inputs so that the database transform is first.
And finally, even if I try to apply this workaround (use the "Order" page...), it does not solve anything to the initial problem...
So my questions of the day : Is it mandatory to apply the V1.0.4.0 iFix2 to make things working ? How can I apply such a fix to my current WMB installation ?
Thank you again
Sebastien |
|
| Back to top |
|
|
| schau31 |
| Posted: Tue Jan 27, 2015 1:34 am Post subject: |
|
|
Novice
Joined: 22 Jan 2015
Posts: 10
|
| By the way...If it is a defect in "Graphical Data Mapper" component, it sounds to be a toolkit problem, so should I fix the toolkit only or the message broker (as the problem pops up at runtime...) ? |
|
| Back to top |
|
|
| martinb |
| Posted: Tue Jan 27, 2015 2:27 am Post subject: |
|
|
Master
Joined: 09 Nov 2006
Posts: 210
Location: UK
|
The current fix level is WebSphere Message Broker V8.0 - Fix Pack 8.0.0.4
http://www-01.ibm.com/support/docview.wss?rs=171&uid=swg24037264
And that includes:-
"IBM Graphical Data Mapper version 1.0.4.0 IFix007 included with this Fix Pack,"
There are many important fixes in both Tooling and runtime since your FixPack 1 level, perhaps the local workaround requires a more recent level.
Ensure you did redeploy the map after applying the workaround. |
|
| Back to top |
|
|
| schau31 |
| Posted: Tue Jan 27, 2015 5:59 am Post subject: |
|
|
Novice
Joined: 22 Jan 2015
Posts: 10
|
OK, I'll give a try to the Fix Pack you suggest on my laptop, but the problem is that I won't be able to migrate the production environment so easily...
So I'm thinking about replacing the mapping node by a Compute one, or maybe a database one, but I couldn't figure out how to connect these nodes with the JDBCProvider I created on message broker runtime side...
What do you think about this option ? Is there a possibility to achieve the same with such nodes ?
Thanks
Sebastien |
|
| Back to top |
|
|
| schau31 |
| Posted: Thu Jan 29, 2015 11:00 pm Post subject: |
|
|
Novice
Joined: 22 Jan 2015
Posts: 10
|
Martinb, I finally tried to run my flow with a v8.0.0.4 broker, but it gave me the exact same result ("No binding was found for the external variable...").
I finally isolated my problem in a self-contained project to illustrate this. Is there a way so that I send you this project so that you can test it and tell me what's wrong ?
I really don't see how could I progress on this issue now... 
Thanks again for your help
Sebastien |
|
| Back to top |
|
|
| Vitor |
| Posted: Fri Jan 30, 2015 5:02 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| schau31 wrote: |
| I finally isolated my problem in a self-contained project to illustrate this. Is there a way so that I send you this project so that you can test it and tell me what's wrong ? |
It might be more effective (and better for the rest of us) if you raised a PMR that included this project so IBM are aware their fix might not work in all circumstances.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| martinb |
| Posted: Sat Jan 31, 2015 3:19 am Post subject: |
|
|
Master
Joined: 09 Nov 2006
Posts: 210
Location: UK
|
Hi Sebastien
Sorry to hear you are still facing this issue.
Raising a PMR and providing the project interchange recreate you have is the way to get this resolved.
Mention this post so that IBM support have the history. |
|
| Back to top |
|
|
|
|
