| Author |
Message
|
| sanu_mit |
 Posted: Wed Jan 25, 2006 3:23 am Post subject: MB v6 - How to access multiple Databases |
 |
|
Apprentice
Joined: 03 Jul 2003
Posts: 25
Location: Kolkata
|
While browsing through the new features of MB v6, I found the following statement under paragraph "ESQL enhancements":
Access to multiple databases from the same Compute, JavaCompute, Database, or Filter node
But while going through the properties of the Compute/Database node, I found that the DSN property accepts only one DSN value. Now, to my knowledge, a given DSN can point to a single Database instance only (If it is possible to connect to multiple databases through a single DSN, please share the method to achieve that). So, does anyone have any idea or knowledge as to how to connect to multiple databases through the aforementioned nodes?
Any light on this is appreciated.
Regards,
Sanu |
|
| Back to top |
|
 |
| mgk |
| Posted: Wed Jan 25, 2006 3:29 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Hi,
If you look in the docs at each of the database access functions / statements (INSERT, UPDATE, DELETE, SELECT, PASSTHRU, CALL) you will see that they have all been updated to allow a DSN (and schema) clause. This allows you to choose a DSN at runtime (maybe based on an incoming message) for each database operation, and even a different DSN each time the same operation is executed if necessary.
The main restriction with this capability is that each DSN accessed in the same node, must be of the same type (ORACLE, DB2 etc).
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: Fri Feb 24, 2006 9:58 am Post subject: |
|
|
Acolyte
Joined: 19 Feb 2005
Posts: 61
Location: Gudivada,India
|
The following ESQL Code is resulting in a SQL Exception. Any thoughts?
| Code: |
DECLARE dataSourceName NAME 'SAMPLE';
PASSTHRU 'SELECT T.* FROM PSCHEMA.SALES AS T' TO Database.dataSourceName |
| Quote: |
Warning:
Severity Description Resource In Folder LocationCreation Time 1 Unresolvable database table reference "Database.dataSourceName". |
| Quote: |
Exception:
(0x01000000):DatabaseException = (
(0x03000000):File = 'F:\build\S000_P\src\DataFlowEngine\ImbOdbc.cpp'
(0x03000000):Line = 232
(0x03000000):Function = 'ImbOdbcHandle::checkRcInner'
(0x03000000):Type = ''
(0x03000000):Name = ''
(0x03000000):Label = ''
(0x03000000):Catalog = 'BIPv600'
(0x03000000):Severity = 3
(0x03000000):Number = 2321
(0x03000000):Text = 'Root SQL exception'
(0x01000000):Insert = (
(0x03000000):Type = 2
(0x03000000):Text = '-1'
)
(0x01000000):DatabaseException = (
(0x03000000):File = 'F:\build\S000_P\src\DataFlowEngine\ImbOdbc.cpp'
(0x03000000):Line = 360
(0x03000000):Function = 'ImbOdbcHandle::checkRcInner'
(0x03000000):Type = ''
(0x03000000):Name = ''
(0x03000000):Label = ''
(0x03000000):Catalog = 'BIPv600'
(0x03000000):Severity = 3
(0x03000000):Number = 2322
(0x03000000):Text = 'Child SQL exception'
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = 'IM002'
)
(0x01000000):Insert = (
(0x03000000):Type = 2
(0x03000000):Text = '0'
)
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = '[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified'
)
) |
Any suggestions on how we can overcome this. I verified that DataSourceName exists and the code works fine If I use Passthru without the "TO" clause. |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Fri Feb 24, 2006 10:02 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
I would think you would have to write it as either
| Code: |
| PASSTHRU 'SELECT T.* FROM PSCHEMA.SALES AS T' TO Database.SAMPLE |
or
| Code: |
| PASSTHRU 'SELECT T.* FROM PSCHEMA.SALES AS T' TO Database.{dataSourceName} |
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| shrek |
| Posted: Fri Feb 24, 2006 10:42 am Post subject: |
|
|
Acolyte
Joined: 19 Feb 2005
Posts: 61
Location: Gudivada,India
|
Neither one of them seems to be working. when I tried the below code, it did not result in any error but I'm not seeing any data in my output tree.
| Quote: |
| <Test><Data/></Test> |
| Code: |
DECLARE dataSourceName NAME 'Database.SAMPLE';
SET OutputRoot.XML.Test.Data = (SELECT T.SALES_DATE FROM dataSourceName.PSCHEMA.SALES as T); |
Something is wrong but I'm not sure what though. |
|
| Back to top |
|
|
| mgk |
| Posted: Sat Feb 25, 2006 3:25 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
You have to the use the { } syntax that Jeff showed you in his post. Also you need the Database correlation name, also shown in Jeffs post. Look up Dynamic Field References which is what the { } are to see why this is needed.
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 |
|
|
| jefflowrey |
| Posted: Sat Feb 25, 2006 4:55 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
| mgk wrote: |
| You have to the use the { } syntax that Jeff showed you in his post. Also you need the Database correlation name, also shown in Jeffs post. Look up Dynamic Field References which is what the { } are to see why this is needed. |
Then why does the documentation on the NAME clause in the DECLARE statement indicate otherwise?
| Quote: |
NAME
Use NAME to define an alias (another name) by which a variable can be known.
Example 1
-- The following statement gives Schema1 an alias of 'Joe'.
DECLARE Schema1 NAME 'Joe';
-- The following statement produces a field called 'Joe'.
SET OutputRoot.XML.Data.Schema1 = 42;
-- The following statement inserts a value into a table called Table1
-- in the schema called 'Joe'.
INSERT INTO Database.Schema1.Table1 (Answer) VALUES 42;
Example 2
DECLARE Schema1 EXTERNAL NAME;
CREATE FIRSTCHILD OF OutputRoot.XML.TestCase.Schema1 Domain('XML')
NAME 'Node1' VALUE '1';
-- If Schema1 has been given the value 'red', the result would be:
<xml version="1.0"?>
<TestCase>
<red>
<Node1>1</Node1>
</red> |
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| mgk |
| Posted: Sat Feb 25, 2006 1:28 pm Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Opps, I did not see the NAME clause on the declare.  The { } are only needed if the NAME clause is NOT used.
Sorry for any confusion.
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 |
|
|
| wschutz |
| Posted: Sun Feb 26, 2006 8:18 am Post subject: |
|
|
Jedi Knight
Joined: 02 Jun 2005
Posts: 3316
Location: IBM (retired)
|
| Code: |
DECLARE dataSourceName NAME 'Database.MYDB2';
DECLARE dbName NAME 'MYDB2';
-- CALL CopyMessageHeaders();
CALL CopyEntireMessage();
-- Data1 gets data:
SET OutputRoot.XML.Test.Data1[] = (SELECT T.col2 FROM Database.MYDB2.wschutz.tab1 as T);
-- Data2 doesn't get data:
SET OutputRoot.XML.Test.Data2 = (SELECT T.col2 FROM dataSourceName.wschutz.tab1 as T);
-- Data3 gets data:
SET OutputRoot.XML.Test.Data3[] = (SELECT T.col2 FROM Database.dbName.wschutz.tab1 as T); |
_________________
-wayne |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Sun Feb 26, 2006 12:07 pm Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
| wschutz wrote: |
| Code: |
-- Data2 doesn't get data:
SET OutputRoot.XML.Test.Data2 = (SELECT T.col2 FROM dataSourceName.wschutz.tab1 as T); |
|
Just for consistency... what does
| Code: |
| SET OutputRoot.XML.Test.Data2[] = (SELECT T.col2 FROM dataSourceName.wschutz.tab1 as T); |
get?
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| wschutz |
| Posted: Sun Feb 26, 2006 1:14 pm Post subject: |
|
|
Jedi Knight
Joined: 02 Jun 2005
Posts: 3316
Location: IBM (retired)
|
| Quote: |
BIP2496E: (.simple_Compute.Main, 9.8) : Illegal data type for target. A non-list field reference is required.
|
_________________
-wayne |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Sun Feb 26, 2006 2:28 pm Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Now that's ... very ... interesting.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| shrek |
| Posted: Mon Feb 27, 2006 8:59 am Post subject: |
|
|
Acolyte
Joined: 19 Feb 2005
Posts: 61
Location: Gudivada,India
|
Is there something wrong with the syntax or am i seeing it the other way? why is it we need to specify the keywork "Database" again in the "SET" statement and why do we need "MyDB2"/"dbName" mentioned again?
Can someone please explain. Appreciate it. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Mon Feb 27, 2006 9:06 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Looks like the syntax does not like the keyword 'Database' in the NAME part.
This might as well have to do with the capability to execute a select on the tree.
Enjoy
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Mon Feb 27, 2006 9:13 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
| fjb_saper wrote: |
Looks like the syntax does not like the keyword 'Database' in the NAME part.
This might as well have to do with the capability to execute a select on the tree. |
Maybe mgk will be nice enough to confirm, but it could be that NAME has the same restrictions that {} where it can only be used for individual pieces - so you can't say
| Code: |
| Set OutputRoot.XML.Field1 = InputRoot.XML.{"Body.Text.Field1"} |
but
| Code: |
| Set OutputRoot.XML.Field1 = InputRoot.XML.{"Body"}.{"Text"}.{"Field1"}; |
would work. Or did that restriction get lifted in v6?
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
|
|
