| Author |
Message
|
| vadivel |
 Posted: Tue Mar 31, 2009 12:16 am Post subject: Dynamic DSN Name |
 |
|
Voyager
Joined: 05 Aug 2002
Posts: 79
Location: US
|
Hi,
I just started using WMB 6 and I'm doing a POC on the usage of dynamic DSN in ESQL. I have a message flow with a compute node from where I would like to query a table, but I want to dynamically change the DSN name used by the query based on the input message.
I saw a few posts that had some examples but most of them were using a variable of type NAME to store the DSN value and that makes it a constant.
Please let me know how to change the DSN name dynamically on runtime based on the input message?
Thanks in advance!!!
Vadivel |
|
| Back to top |
|
 |
| elvis_gn |
| Posted: Tue Mar 31, 2009 12:32 am Post subject: |
|
|
Padawan
Joined: 08 Oct 2004
Posts: 1905
Location: Dubai
|
Hi vadivel,
The variable NAME that other posts mentioned was most probably populated dynamically, after deciding which database to use.
Store the possible DSN names in some config database table, and read from there after deciding on the message type. You would still use a variable to affix the DSN into the query.
You could also have subflows or different compute/database nodes for each database UNLESS the DSN list is very big.
Regards. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Tue Mar 31, 2009 2:33 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
And remember to check up on limitations. One of them was that the DSN used had to be of the same type as the one declared on the node... no cross pollinating (oracle only or db2 only etc...) 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| vadivel |
| Posted: Fri Apr 03, 2009 2:32 am Post subject: |
|
|
Voyager
Joined: 05 Aug 2002
Posts: 79
Location: US
|
Hi,
Here is my sample ESQL code
| Code: |
DECLARE DSN NAME InputRoot.XMLNSC.Input.DSN;
DECLARE QUERY CHARACTER;
SET TableName = InputRoot.XMLNSC.Input.TableName;
SET QUERY = 'SELECT R.* FROM '|| TableName ||'AS R';
SET OutputRoot.XML.Data.Row[] = PASSTHRU(QUERY TO Database.DSN)
|
When I execute the flow, I get the following exeception.
RecoverableException BIP2498E: ('.TEST_DBFLOW_Compute.Main', '7.20') : An error occurred when navigating to path element '4' of the field reference at the given location.
Further messages are generated that provide details of the error.
Correct the syntax of your ESQL expression in node ''.TEST_DBFLOW_Compute.Main'', around line and column ''7.20'', then redeploy the message flow.
RecoverableException BIP2328E: A value of SQL datatype ''NULL'' encountered when datatype ''CHARACTER'' expected.
The value of SQL datatype 'NULL' was encountered, but a value of SQL datatype 'CHARACTER' was expected.
This message will be accompanied with others which will give the higher level context for this error. Look at these other messages to determine the root source of the problem.
As far as I understand the variable of type NAME should be intialised with a constant!! So not sure how to assign values dynamically.
Thanks, |
|
| Back to top |
|
|
| WMBDEV1 |
| Posted: Fri Apr 03, 2009 4:45 am Post subject: |
|
|
Sentinel
Joined: 05 Mar 2009
Posts: 888
Location: UK
|
| Have you checked all your variables have been initialised as expected and none of them are being set to null? I recommed running a user trace to confirm this. |
|
| Back to top |
|
|
| vairavan |
| Posted: Sun Apr 05, 2009 6:58 pm Post subject: |
|
|
Apprentice
Joined: 22 Apr 2008
Posts: 42
|
Looks like, either your DSN or TableName is coming as null from your Input root. And also, It says syntax error. Got the doubt, how could you able to compile your flows with this error?
As WMBDEV1 says, using trace will help you out of this null value issue. |
|
| Back to top |
|
|
| vadivel |
| Posted: Mon Apr 06, 2009 4:36 am Post subject: |
|
|
Voyager
Joined: 05 Aug 2002
Posts: 79
Location: US
|
Neither the DSN or TableName is null!!!! I assigned the value to a variable of type CHARACTER in the esql and got the correct output. And the error I had pasted earlier is from user trace!!!
| Quote: |
A value of SQL datatype ''NULL'' encountered when datatype ''CHARACTER'' expected
|
I dont understand this error!! and I'm pretty sure the input values are NOT NULL
Thanks |
|
| Back to top |
|
|
| WMBDEV1 |
| Posted: Mon Apr 06, 2009 5:11 am Post subject: |
|
|
Sentinel
Joined: 05 Mar 2009
Posts: 888
Location: UK
|
| vadivel wrote: |
Neither the DSN or TableName is null!!!! ...... And the error I had pasted earlier is from user trace!!!
|
But the snippet from the user trace did not help me determine this fact, and in the next line from your response, you are only "pretty sure".....
| Quote: |
I dont understand this error!! and I'm pretty sure the input values are NOT NULL
|
Im just trying to tick off the easy stuff first! |
|
| Back to top |
|
|
| mqjeff |
| Posted: Mon Apr 06, 2009 5:37 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
Also, you seem to be trying to use ESQL syntax with PASSTHRU, which expects SQL syntax.
Again,this may not be the problem, but let's try the easy things first.
Just do
| Code: |
| SET OutputRoot.XML.Data.Row[] = SELECT R.* FROM Database.DSN.{TableName} AS R; |
|
|
| Back to top |
|
|
|
|
