| Author |
Message
|
| wolstek |
 Posted: Mon Oct 22, 2001 6:00 am Post subject: |
 |
|
Acolyte
Joined: 25 Jun 2001
Posts: 52
Location: Bristol, UK
|
Can someone explain the benefit, (if there is one) to do say
SET OutputRoot.XML.Data.DB[] = PASSTHRU('SELECT FLD1, FLD2 FROM user1.MYTABLE WHERE KEYFLD = ?', InputRoot.XML.Data.keyvalue);
rather than
SET OutputRoot.XML.Data.DB[] = SELECT FLD1, FLD2 FROM user1.MYTABLE WHERE KEYFLD = InputRoot.XML.Data.keyvalue;
I was hoping that I could use the passthru to get around the fact that you can only specify one output datasource per database node (and none for compute node), but it seems you still need the datasource eventhough the PASSTHRU seems to infer that you go straight to the databse bypassing the MQSI broker parser...
If you have many tables to update, it would nice to do it in one and not many nodes. |
|
| Back to top |
|
 |
| Outdesign |
| Posted: Mon Oct 22, 2001 8:09 am Post subject: |
|
|
Apprentice
Joined: 16 Sep 2001
Posts: 38
Location: Hampshire, UK
|
(1)
In your example there is no benefit to writing the code either way.
(2)
You can only specify one DataSource per node.
This DataSource is used to establish a connection to a database.
You can access any table within this database.
For example :
--------------------------------------------------------------------------
ComputeNode1 [Data Source Name = TESTDB; Table Name = TABLE1]
SET OutputRoot.XML.Data.DB1[] = (SELECT column1 FROM Database.TABLE1 ...);
SET OutputRoot.XML.Data.DB2[] = (SELECT column1 FROM Database.TABLE2 ...);
SET OutputRoot.XML.Data.DB3[] = (SELECT column1 FROM Database.TABLE3 ...);
--------------------------------------------------------------------------
PASSTHRU bypasses the MQSI ESQL parser, and passes the SQL directly to the database
through the database connection established by the DataSource.
You would use this to write database specific SQL.
This applies to both the Compute and Database nodes.
|
|
| Back to top |
|
|
| Armin |
| Posted: Mon Oct 22, 2001 10:45 pm Post subject: |
|
|
Novice
Joined: 24 Jul 2001
Posts: 15
Location: Germany
|
PASSTHRU is also necessary if you want to use order or group by clauses in your select.
Armin |
|
| Back to top |
|
|
| sceriani |
| Posted: Wed Oct 24, 2001 5:41 am Post subject: |
|
|
Newbie
Joined: 23 Oct 2001
Posts: 2
|
Hi, i am very new to MQ Series and MQSI. I have done a lot of reading, but i am a little confused on PASSTHRU. I am getting nothing in return for the following statement:
SET OutputRoot.XML.Message.Record[] = PASSTHRU('Select SUM(b.FLD1 - b.FLD2) as SUPPLY, sum(b.FLD3) as DEMAND from TBL b;');
Now when i put this same SQL into Oracle this works fine, but in MQSI it returns nothing. Can anyone see what i am missing? I don't have the '?' markes, but i don't think i would need them because i don't have a where clause. Maybe i am just not understanding this right. Any help would be appreciated.
Thanks! |
|
| Back to top |
|
|
| Armin |
| Posted: Wed Oct 24, 2001 11:09 am Post subject: |
|
|
Novice
Joined: 24 Jul 2001
Posts: 15
Location: Germany
|
I think you have to use "from TBL as b" assuming that the name of your Database Table is TBL.
Like:
SET OutputRoot.XML.Message.Record[] = PASSTHRU('Select SUM(b.FLD1 - b.FLD2) as SUPPLY, sum(b.FLD3) as DEMAND from TBL as b;');
Hope this solves your problem.
Armin |
|
| Back to top |
|
|
| Outdesign |
| Posted: Thu Oct 25, 2001 4:43 am Post subject: |
|
|
Apprentice
Joined: 16 Sep 2001
Posts: 38
Location: Hampshire, UK
|
Armin,
QUOTE:
I think you have to use "from TBL as b" assuming that the name of your Database Table is TBL.
This is not correct because the target DBMS is Oracle where the correct syntax is
" FROM my_table alias ".
In other words, with Oracle the "AS" option is invalid.
Sceriani,
Your ESQL statement looks valid.
SET OutputRoot.XML.Message.Record[] =
PASSTHRU('Select SUM(b.FLD1 - b.FLD2) as SUPPLY, sum(b.FLD3) as DEMAND from TBL b;');
You need to clarify is your comment : "but in MQSI it returns nothing"
Are there any error messages in your NT Event Log, Syslog or User Trace ?
Possibly, MQSI cannot connect to the table 'TBL' ?
FYI :
In MQSIv2 the userid and password used to access a user databases is determined by the
mqsicreatebroker command :
mqsicreatebroker <brokername> -i <ServiceUserID> -a <ServicePassword>
-q <QueueManagerName>
-n <DataSourceName> -u <DataSourceUserID> -p <DataSourcePassword>
1. User database access is controlled via the values that you specify for the
DataSourceUserID and DataSourcePassword parameters on the mqsicreatebroker command.
2. If you allow these parameters to default, user database access is controlled via
the values that you specify for the ServiceUserID and ServicePassword values on the
mqsicreatebroker command.
[ This Message was edited by: Outdesign on 2001-10-25 05:48 ] |
|
| Back to top |
|
|
| sceriani |
| Posted: Thu Oct 25, 2001 5:24 am Post subject: |
|
|
Newbie
Joined: 23 Oct 2001
Posts: 2
|
| Thanks for the replies. After looking more closly, it looks like i am having a security issue. I thought i had gotton all the security i needed, but it don't think i have my ODBC set up properly on our UNIX box. I will try this. |
|
| Back to top |
|
|
|
|
