| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
| MB v6 - How to access multiple Databases |
« View previous topic :: View next topic » |
| Author |
Message
|
| wschutz |
 Posted: Mon Feb 27, 2006 11:24 am Post subject: |
 |
|
Jedi Knight
Joined: 02 Jun 2005
Posts: 3316
Location: IBM (retired)
|
Well, this doesn't work either ...
| Code: |
DECLARE dbName NAME 'MYDB2';
DECLARE db NAME 'Database';
-- Data2 doesn't get data:
SET OutputRoot.XML.Test.Data2 = (SELECT T.col2 FROM db.dbName.wschutz.tab1 as T);
-- Data3 gets data:
SET OutputRoot.XML.Test.Data3[] = (SELECT T.col2 FROM Database.dbName.wschutz.tab1 as T); |
looks like the esql parser wants to see that "Database" keyword there ....
_________________
-wayne |
|
| Back to top |
|
 |
| mgk |
| Posted: Wed Mar 01, 2006 10:22 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Hi,
As jeff suspected a variable of type NAME can only contain one element name. This is shown in the code below, which also tries to demonstrate the usages of dynamic and multiple dsn references. Also a database reference MUST start with the keyword 'Database'.
| Code: |
SET OutputRoot.MQMD = InputRoot.MQMD;
DECLARE dsnName NAME 'gremlin3';
DECLARE dbSchemaName NAME 'dbo';
DECLARE dbTableName NAME 'assignids';
DECLARE testname NAME 'mgk.a.testing';
DECLARE db NAME 'Database';
SET OutputRoot.XML.Test.Status = 'Begin';
/* This gives an exception as we have only provided a one-part name (which is assumed to be a table name) and this table does not exist: */
/*SET OutputRoot.XML.Test.Data1[] = SELECT T.Detail FROM Database.dsnName as T;*/
/* Data1 gets expected data, using the DSN on the node: */
SET OutputRoot.XML.Test.Data1[] = SELECT T.Detail FROM Database.dbTableName as T;
/* Data2 gets expected data, using literal DSN name, dynamic schema name : */
SET OutputRoot.XML.Test.Data2[] = SELECT T.TYPE FROM Database.gremlinSQLSVR.dbSchemaName.assignids as T;
/*Data3 gets expected data, used dynamic DSN name: */
SET OutputRoot.XML.Test.Data3[] = SELECT T.ID FROM Database.dsnName.dbo.assignids as T;
/*Data4 gets expected data, using PASSTHRU and dynamic DSN name*/
SET OutputRoot.XML.Test.Data4[] = PASSTHRU( 'SELECT T.* FROM assignids AS T' TO Database.dsnName);
/*Shows that a NAME can only be contain one element name as the result is:*/
/*...<mgk.a.testing><result>42</result></mgk.a.testing>...*/
SET OutputRoot.XML.Test.testname.result = '42'; |
Furthermore, a dynamic DB reference for SELECT, INSERT etc can contain one, two or three elements.
Database.a.b.c = use: DSN a, SCHEMA b, TABLE c
Database.b.c = use: DSN 'node property' SCHEMA b, TABLE c
Database.c= use: DSN 'node property' SCHEMA 'default', TABLE c
Regards,
_________________
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 |
|
|
| shrek |
| Posted: Thu Mar 02, 2006 2:42 am Post subject: |
|
|
Acolyte
Joined: 19 Feb 2005
Posts: 61
Location: Gudivada,India
|
Before we go into details of this post, I would like to mention that I'm just trying to explore the new capability in Version6 and I highly appreciate the response that I'm getting from this forum. I'm confident that I'm getting close but may be missing some minor things
MGK: Thank you for providing the example and I'm sure that you have verified the code on your system but for some reason it is not working on mine. I just quoted your comments for ease of other developers who try to explore the same thing. I'm sorry for any inconvenience
Database: DB2 8.1 on Windows XP Profession
Version: WMB6.0
DatabaseName: TESTDB
ODBC NAME: TESTDB
| Code: |
DECLARE dsnName NAME 'TESTDB';
DECLARE dbSchemaName NAME 'EMPSCHEMA';
DECLARE dbTableName NAME 'EMPLOYEE';
DECLARE db NAME 'Database';
SET OutputRoot.XML.Test.Status = 'Begin'; |
| Quote: |
MGK mentioned:
/* This gives an exception as we have only provided a one-part name (which is assumed to be a table name) and this table does not exist: */
/*SET OutputRoot.XML.Test.Data1[] = SELECT T.Detail FROM Database.dsnName as T;*/ |
| Code: |
| I haven't tried this as I'm sure it would throw an exception |
| Quote: |
MGK Mentioned:
-- Data1 gets expected data, using the DSN on the node:
SET OutputRoot.XML.Test.Data1[] = SELECT T.EMPNAME FROM Database.dbTableName as T; |
| Code: |
Output From my Run:
/*
Comment1: Resulted in an exception as no schema is specified. Note this is on DB2 not on SQLServer/Oracle
Comment2: Including the schema name did brought back the result set
SET OutputRoot.XML.Test.Data1[] = SELECT T.EMPNAME FROM Database.dbSchemaName.dbTableName as T;
*/ |
| Quote: |
MGK mentioned:
/* Data2 gets expected data, using literal DSN name, dynamic schema name : No Datasource name on the node*/
SET OutputRoot.XML.Test.Data2[] = SELECT T.TYPE FROM Database.TESTDB.dbSchemaName.EMPLOYEE as T;
/*Data3 gets expected data, used dynamic DSN name: */
SET OutputRoot.XML.Test.Data3[] = SELECT T.EMPNAME FROM Database.dsnName.dbSchemaName.EMPLOYEE as T; |
| Code: |
Output From my Run:
/*
Comment: Both Data2 & Data3 gives me the below exception. Note: There is no DSN specified on the node
5296 RecoverableException BIP2230E: Error detected whilst processing a message in node 'TestDBConnectivity.Compute'.
The message broker detected an error whilst processing a message in node 'TestDBConnectivity.Compute'. An exception has been thrown to cut short the processing of the message.
See the following messages for details of the error.
5296 RecoverableException BIP2488E: ('.TestDBConnectivity_Compute.Main', '19.3') Error detected whilst executing the SQL statement ''''.
The message broker detected an error whilst executing the given statement. An exception has been thrown to cut short the SQL program.
See the following messages for details of the error.
5296 RecoverableException BIP2580E: ('.TestDBConnectivity_Compute.Main', '19.44') : No external data source specified for this node.
An attempt was made to access an external database table, but no database inputs have been specified for this Compute, Filter or Database node.
Add database inputs for each table that is accessed, and redeploy the message flow.
5296 UserTrace BIP2539I: Node 'TestDBConnectivity.Trace': Evaluating expression ''ExceptionList'' at ('', '1.3'). This resolved to ''ExceptionList''. The result was ''ROW... Root Element Type=16777216 NameSpace='' Name='Root' Value=NULL''.
*/ |
| Quote: |
I Included the schema name
/*Data4 gets expected data, using PASSTHRU and dynamic DSN name*/
SET OutputRoot.XML.Test.Data4[] = PASSTHRU ('SELECT T.* FROM dbSchemaName.dbTableName AS T' TO Database.dsnName);
SET OutputRoot.XML.Test.Data4[] = PASSTHRU ('SELECT T.* FROM EMPSCHEMA.EMPLOYEE AS T' TO Database.dsnName); |
| Code: |
Output From my Run:
/*
Comment: All the above 3 statements gives me the following syntax error
Syntax error. Valid options include: UNCOORDINATED VALUES TestDBConnectivity.esql line 28 March 2, 2006 5:15:23 AM
*/ |
| Quote: |
My Own Try:
/*Data4 gets expected data, using PASSTHRU and dynamic DSN name*/
PASSTHRU 'SELECT T.* FROM EMPSCHEMA.EMPLOYEE AS T' TO Database.dsnName; |
| Code: |
/*
Comment: This doesn't give me any syntax error but throws an exception
BIP2537I: Node 'TestDBConnectivity.Compute': Executing statement ''DECLARE dsnName NAME 'TESTDB';'' at ('.TestDBConnectivity_Compute.Main', '5.3').
BIP2537I: Node 'TestDBConnectivity.Compute': Executing statement ''DECLARE dbSchemaName NAME 'EMPSCHEMA';'' at ('.TestDBConnectivity_Compute.Main', '6.3').
BIP2537I: Node 'TestDBConnectivity.Compute': Executing statement ''DECLARE dbTableName NAME 'EMPLOYEE';'' at ('.TestDBConnectivity_Compute.Main', '7.3').
BIP2537I: Node 'TestDBConnectivity.Compute': Executing statement ''DECLARE db NAME 'Database';'' at ('.TestDBConnectivity_Compute.Main', '8.3').
BIP2537I: Node 'TestDBConnectivity.Compute': Executing statement ''SET OutputRoot.XML.Test.Status = 'Begin';'' at ('.TestDBConnectivity_Compute.Main', '10.3').
BIP2566I: Node 'TestDBConnectivity.Compute': Assigning value '''Begin''' to field / variable ''OutputRoot.XML.Test.Status''.
BIP2537I: Node 'TestDBConnectivity.Compute': Executing statement ''PASSTHRU('SELECT T.* FROM EMPSCHEMA.EMPLOYEE AS T')'' at ('.TestDBConnectivity_Compute.Main', '25.3').
BIP2537I: Node 'TestDBConnectivity.Compute': Executing statement ''IF SQLCODE <> 0 THEN... END IF;'' at ('.TestDBConnectivity_Compute.Main', '27.3').
BIP2540I: Node 'TestDBConnectivity.Compute': Finished evaluating expression ''SQLCODE'' at ('.TestDBConnectivity_Compute.Main', '27.7'). The result was ''-1''.
BIP2539I: Node 'TestDBConnectivity.Compute': Evaluating expression ''SQLCODE <> 0'' at ('.TestDBConnectivity_Compute.Main', '27.15'). This resolved to ''-1 <> 0''. The result was ''TRUE''.
BIP2537I: Node 'TestDBConnectivity.Compute': Executing statement ''THROW EXCEPTION VALUES( SQLERRORTEXT);'' at ('.TestDBConnectivity_Compute.Main', '28.4').
BIP2540I: Node 'TestDBConnectivity.Compute': Finished evaluating expression ''SQLERRORTEXT'' at ('.TestDBConnectivity_Compute.Main', '28.33'). The result was '''[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified'''.
*/ |
|
|
| Back to top |
|
|
| mgk |
| Posted: Thu Mar 02, 2006 4:06 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Hi,
There was a lot in your post, but I want to clarify a few things:
1: You MUST specify a DSN on the node before you can do ANY DB work in that node, even if you never actually use that DSN to retreive data.
2: The string given to PASSTHRU is given as-is (literally) to the DB with NO changes. This means that you cannot use field references inside the string. If you want to use PASSTHRU to perform dynamic DB work (eg on different tables) then you must dynamically construct the string (eg using the concatenation '||' operator).
3: Not really an issue here, but for completeness: All dynamic DSNs used in a given node must be compatible at the ODBC level with the DSN given on the node attribute or an exception will be thrown. What this means in practice is that all DBs must be either all DB2 or all SQLServer or all Oracle (meaning all of the same DB type) etc at the same (or very similar) versions. In general if you are not sure, try it, and if it works then they are ok to be used in the same node, if you get an exception they are not  .
| Quote: |
Comment1: Resulted in an exception as no schema is specified. Note this is on DB2 not on SQLServer/Oracle
Comment2: Including the schema name did brought back the result set |
This is probably because the schema in which the table resides is not the same as the brokers DB username (provided on the createBroker) which is used as the default schema name if one is not given. In this case you must specify the schema name.
| Quote: |
| Comment: Both Data2 & Data3 gives me the below exception. Note: There is no DSN specified on the node |
As I say above, you MUST provide a DSN on the node, or you will ALWAYS get this exception. Just because you have to provide one does not mean you have to use it!
Please check these things mentioned above, and retry your tests. If you still have any outstanding problems, please post them again
Regards,
_________________
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 |
|
|
| shrek |
| Posted: Tue Mar 07, 2006 10:20 pm Post subject: |
|
|
Acolyte
Joined: 19 Feb 2005
Posts: 61
Location: Gudivada,India
|
Passthru continues to give a Syntax Error.
Severity Description Resource In Folder Location Creation Time
2 Syntax error. Valid options include: || / = >= > <= < - <> + ; *
AND BETWEEN DAY HOUR IN IS LIKE MINUTE MONTH NOT
OR SECOND YEAR TestDBConnectivity.esql line 28 March 4, 2006 3:34:18 PM
SET OutputRoot.XML.Test.Data4[] = PASSTHRU( 'SELECT T.* FROM EMPSCHEMA.EMPLOYEE AS T' TO Database.dsnName);
Thank you. |
|
| Back to top |
|
|
| mgk |
| Posted: Thu Mar 09, 2006 1:39 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Hi,
You have found a defect in the Tooling. The syntax does work in the runtime, and that is where I tested it. I suggest you raise a PMR to obtain a tooling fix, as the syntax should. To give you a workaround, you can use an executable comment (V6 only) to bypass the syntax checker in the tooling and deploy this statement (as shown below), to verify that the runtime does indeed accept this syntax. However this is not a long term solution, and you still need to raise the PMR to obtain a proper fix.
| Code: |
| /*!{ SET OutputRoot.XML.Test.Data4[] = PASSTHRU( 'SELECT T.* FROM EMPSCHEMA.EMPLOYEE AS T' TO Database.dsnName); }!*/ |
Regards,
_________________
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 |
|
|
| shrek |
| Posted: Mon Mar 13, 2006 7:32 pm Post subject: |
|
|
Acolyte
Joined: 19 Feb 2005
Posts: 61
Location: Gudivada,India
|
First, I'm not sure how you got around creating the bar file without correcting the syntax issues and secondly where can we find documentation about "executable comments".
I tried the you mentioned below and it didn't get executed.
Thanks for all the support. |
|
| Back to top |
|
|
| mgk |
| Posted: Tue Mar 14, 2006 1:27 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Hi,
Executable Comments are documented under topic ak04870_ You can search for this or for "executable comment". Note that they are V6 only. They can only be used inside a function (Main or a user written function that main calls). They cannot be used in Schema or Module level code. They work because the tooling treats them as a comment and ignores them. So they get passed down to the runtime which treats them as normal ESQL code.
Regards,
_________________
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 |
|
|
| shrek |
| Posted: Sun Apr 09, 2006 5:57 am Post subject: |
|
|
Acolyte
Joined: 19 Feb 2005
Posts: 61
Location: Gudivada,India
|
Just back from Vacation...Using Executable Comments is also giving an error when invoked from Main() as well as UserWritten. I think PMR is the only way to go.
I'll apply CSD01 and will try it again before opening PMR.
BIP2401E: (.TestDBConnectivity_Compute.Main, 25.84) : Syntax error : expected ';' but found 'keyword To'.
The expected token was not found.
Correct the syntax of your expression and redeploy the message flow.
/*Data4 gets expected data, using PASSTHRU and dynamic DSN name*/
/*!{SET OM.Test.Data4[] = PASSTHRU ('SELECT T.* FROM EMPSCHMEA.EMPLOYEE AS T') TO Database.dsnName; }!*/ |
|
| Back to top |
|
|
| wschutz |
| Posted: Sun Apr 09, 2006 6:50 am Post subject: |
|
|
Jedi Knight
Joined: 02 Jun 2005
Posts: 3316
Location: IBM (retired)
|
are you sure the closeing paren ) isn't in the wrong place? mgk's example has it after the db name (and so does the infocentr)
_________________
-wayne |
|
| Back to top |
|
|
| shrek |
| Posted: Mon Apr 10, 2006 2:45 pm Post subject: |
|
|
Acolyte
Joined: 19 Feb 2005
Posts: 61
Location: Gudivada,India
|
| My Bad...It works. Thanks. |
|
| Back to top |
|
|
| moogoo |
| Posted: Wed Jun 21, 2006 6:02 am Post subject: |
|
|
Acolyte
Joined: 20 Sep 2002
Posts: 54
Location: US
|
Hi,
How would one handle a dynamic DSN name with a select statement that uses parameters? We have something like:
SET Environment.Function.Result[] = passthru('SELECT PKG.acctdetails(?) PACE FROM dual ', AcctNo);
I've tried a bunch of iterations including:
SET Environment.Function.Result[] = passthru('SELECT PKG.acctdetails(?) PACE FROM Database.dsnName.dual ', AcctNo);
SET Environment.Function.Result[] = passthru('SELECT PKG.acctdetails(?) PACE FROM Database.dsnName ', AcctNo);
but all do not seem to work. The two things above give the following error:
(0x03000000):Text = '[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified'
Any suggestions?
Thanks,
MG |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Wed Jun 21, 2006 2:32 pm Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
You don't use the Database qualifer when using Passthru.
Your SQL that you provide to PASSTHRU must be valid and legal SQL on the database itself, not valid and legal ESQL.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| moogoo |
| Posted: Fri Jun 23, 2006 6:02 am Post subject: |
|
|
Acolyte
Joined: 20 Sep 2002
Posts: 54
Location: US
|
Your absolutely right. My fault. After reading the posts a little more closely, I've modified my statement to the following:
SET Environment.Function.Result[] = passthru('SELECT PKG.acctdetails(?) PACE FROM dual' TO Database.dsnName , OutputRoot.XML.MSG.DATA.AcctNo);
However, I'm getting the "Uncoordinated Values" error that Shrek was also getting. Can anyone confirm that this is fixed with the 6.0.0.1 version of the toolkit? If so, I'll update immediately. But I've been having issues with the a data source that's been on the fritz so I've been trying to sort out different errors.
Thanks,
MG |
|
| Back to top |
|
|
| moogoo |
| Posted: Mon Jun 26, 2006 1:53 pm Post subject: |
|
|
Acolyte
Joined: 20 Sep 2002
Posts: 54
Location: US
|
Database problem was finally resolved. Can anyone advise whether the syntax I'm using is correct? There are two issues: 1) Uncoordinated Values error is still popping up (which I'm assuming will be resolved with the Toolkit update as per MGK) and 2) unable to deploy when using MGK's executable comments. It comes back with:
BIP2401E: (.STTP_PSCCI_PACE_Routing_MRM_V01R01_Compute.f_get_acct_details, 11.140) : Syntax error : expected ')' but found ','.
The expected token was not found.
Correct the syntax of your expression and redeploy the message flow.
This is using the syntax from my previous post:
SET Environment.Function.Result[] = passthru('SELECT PKG.acctdetails(?) PACE FROM dual' TO Database.dsnName , OutputRoot.XML.MSG.DATA.AcctNo);
I've also tried:
SET Environment.Function.Result[] = passthru('SELECT PKG.acctdetails(?) PACE FROM dual' , OutputRoot.XML.MSG.DATA.AcctNo TO Database.dsnName );
which also fails and gives the error:
BIP2401E: (.STTP_PSCCI_PACE_Routing_MRM_V01R01_Compute.f_get_acct_details, 11.180) : Syntax error : expected ')' but found 'keyword To'.
The expected token was not found.
Correct the syntax of your expression and redeploy the message flow.
I'm at a loss here. I've searched thru the manuals and other posts on this site and I have yet to see an example where someone is using both dynamic data sources with input parameters to a query.
Any suggestions would be greatly appreciated.
Thanks,
MG |
|
| Back to top |
|
|
|
|
|
|
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
|
|
|
|
