ASG
IBM
Zystems
Cressida
Icon
Netflexity
 
  MQSeries.net
Search  Search       Tech Exchange      Education      Certifications      Library      Info Center      SupportPacs      LinkedIn  Search  Search                                                                   FAQ  FAQ   Usergroups  Usergroups
 
Register  ::  Log in Log in to check your private messages
 
RSS Feed - WebSphere MQ Support RSS Feed - Message Broker Support

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » PASSTHRU vs SQL

Post new topic  Reply to topic
 PASSTHRU vs SQL « View previous topic :: View next topic » 
Author Message
wolstek
PostPosted: Mon Oct 22, 2001 6:00 am    Post subject: Reply with quote

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
View user's profile Send private message
Outdesign
PostPosted: Mon Oct 22, 2001 8:09 am    Post subject: Reply with quote

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
View user's profile Send private message Visit poster's website
Armin
PostPosted: Mon Oct 22, 2001 10:45 pm    Post subject: Reply with quote

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
View user's profile Send private message
sceriani
PostPosted: Wed Oct 24, 2001 5:41 am    Post subject: Reply with quote

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
View user's profile Send private message
Armin
PostPosted: Wed Oct 24, 2001 11:09 am    Post subject: Reply with quote

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
View user's profile Send private message
Outdesign
PostPosted: Thu Oct 25, 2001 4:43 am    Post subject: Reply with quote

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
View user's profile Send private message Visit poster's website
sceriani
PostPosted: Thu Oct 25, 2001 5:24 am    Post subject: Reply with quote

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
View user's profile Send private message
Display posts from previous:   
Post new topic  Reply to topic Page 1 of 1

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » PASSTHRU vs SQL
Jump to:  



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
Protected by Anti-Spam ACP
 
 


Theme by Dustin Baccetti
Powered by phpBB © 2001, 2002 phpBB Group

Copyright © MQSeries.net. All rights reserved.