| Author |
Message
|
| kiran_mvr |
 Posted: Mon May 09, 2005 5:25 am Post subject: problem with Oracle Database |
 |
|
Apprentice
Joined: 12 Dec 2004
Posts: 35
|
Hai
iam having some problem with getting a value from single column in Oracle database.
Here DIRECTORYPATH is the column name from which iam trying to get the value.TARGET is the table name.
CODE:
Environment.Variables=PASSTHRU('THE(SELECT ITEM T.DIRECTORYPATH FROM Database.TARGET AS T WHERE T.RECEIVER=RECEIVER)');
is this correct statement???
_________________
Kiran_manny |
|
| Back to top |
|
 |
| jefflowrey |
| Posted: Mon May 09, 2005 5:57 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
I think Oracle doesn't know the "THE" keyword.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| Devasis.Sahu |
| Posted: Mon May 09, 2005 6:01 pm Post subject: Oracle problem |
|
|
Apprentice
Joined: 22 Feb 2003
Posts: 43
Location: India
|
Hello,
You can also give a try by removing the keyword "database" .....PASSTHRU doesnot support the keyword "database"... use the DSN name instead of the keyword.
Further, look at this post. Though it was on DB2, you could give it a try.
http://www.mqseries.net/phpBB2/viewtopic.php?t=21845
Regds |
|
| Back to top |
|
|
| Ian |
| Posted: Tue May 10, 2005 5:22 am Post subject: |
|
|
Disciple
Joined: 22 Nov 2002
Posts: 152
Location: London, UK
|
Firstly, you have confused two different types of syntax here.
The 'THE' and 'ITEM' are WBIMB native ESQL syntax and form part of ESQL to query a database table.
They are not used with the PASSTHRU statement.
PASSTHRU is used to write ESQL statements that
- Bypass the WebSphere Business Integration Message Broker Parser
- Go straight to the configured backend database
- Execute a coded statement
PASSTHRU allows you to use database syntax not normally supported by ESQL, for example the TO_CHAR function in Oracle.
Secondly, you should code your ESQL to cater for multiple rows returned from an SQL SELECT statement.
Environment.Variables.ResultSet[]=PASSTHRU('SELECT...');
_________________
Regards, Ian |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Tue May 10, 2005 5:27 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
| Ian wrote: |
Secondly, you should code your ESQL to cater for multiple rows returned from an SQL SELECT statement.
Environment.Variables.ResultSet[]=PASSTHRU('SELECT...'); |
I'm going to go the other way and suggest
| Code: |
| Set Environment.Variables.DirPath = THE(SELECT ITEM T.DIRECTORYPATH FROM Database.TARGET AS T WHERE T.RECEIVER=RECEIVER); |
There's nothing that seems to require PASSTHRU in this.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| Ian |
| Posted: Tue May 10, 2005 8:02 am Post subject: |
|
|
Disciple
Joined: 22 Nov 2002
Posts: 152
Location: London, UK
|
I agree with this last suggestion as it is the more elegant solution (for this specific problem).
My second point had been more along the lines that when you require the use of an SQL SELECT statement within a PASSTHRU function then you MUST code it for a returned result set (ie, using the square braces).
_________________
Regards, Ian |
|
| Back to top |
|
|
| kiran_mvr |
| Posted: Tue May 10, 2005 8:47 am Post subject: Oracle Database Problem Solved |
|
|
Apprentice
Joined: 12 Dec 2004
Posts: 35
|
Hai all,
My problem was solved.Actually i am trying to get a column value(DIRECTORYPATH) dynamically with the help of RECEIVER value.
Here for this logic the below code worked.
CODE:
SET Environment.DirPath=THE(SELECT T.DIRECTORYPATH FROM Database.TARGET AS T WHERE T.RECEIVER=RECEIVER);
bye
Kiran 
_________________
Kiran_manny |
|
| Back to top |
|
|
|
|
