| Author |
Message
|
| MQMD |
 Posted: Mon Jun 11, 2007 12:33 am Post subject: PASSTHRU without TO clause but with Values Clause |
 |
|
Apprentice
Joined: 03 May 2007
Posts: 45
|
Is it possible to write a PASSTHRU statment without TO clause but that includes Values Clause.I am getting the syntax issue while trying to do so
Has anybody tried it before. |
|
| Back to top |
|
 |
| mgk |
| Posted: Mon Jun 11, 2007 12:38 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
If you want to use VALUES you have to use TO (as you can see if you look at the syntax diagram). However, you can use a comma seperated list of expressions (values) instead of the VALUES clause if you don't want to use the TO. This will give you equivalent functionality.
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 |
|
|
| MQMD |
| Posted: Mon Jun 11, 2007 2:05 am Post subject: |
|
|
Apprentice
Joined: 03 May 2007
Posts: 45
|
|
| Back to top |
|
|
| jefflowrey |
| Posted: Mon Jun 11, 2007 11:02 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
| mgk wrote: |
| If you want to use VALUES you have to use TO (as you can see if you look at the syntax diagram) |
I'm not gonna argue with you that your statement is true... but that's not what the syntax diagram shows - at least to my eyes.
The railroad diagram shows that both the TO clause and the VALUES clause are optional, and independantly optional of each other. Maybe the dotted decimal form implies differently, but if so then one of the forms is wrong (because they should be equivalent.
http://publib.boulder.ibm.com/infocenter/wmbhelp/v6r0m0/index.jsp?topic=/com.ibm.etools.mft.doc/ak05100_.htm
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| mgk |
| Posted: Tue Jun 12, 2007 9:44 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Hi Jeff,
You got me this time 
I checked the syntax diagram and the code to make sure, and yes you can use the VALUES clause with or without the TO clause, as long as you use the new style PASSTHRU that does not have the brackets:
PASSTHRU <expression> VALUE( <expr> , ... ) ; //THIS IS OK
The above should work, and would be a defect if it did not. However, the following should fail, as it uses brackets which denotes the old style syntax:
PASSTHRU( <expression VALUES( <expr> , ... ) ) ; //SYNTAX ERROR
So the question is, MQMD, can you retry this and post the error and the ESQL you were using if you still have a problem please.
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 |
|
|
| MQMD |
| Posted: Wed Jun 13, 2007 3:22 am Post subject: |
|
|
Apprentice
Joined: 03 May 2007
Posts: 45
|
I was using the expression
PASSTHRU( <expression VALUES( <expr> , ... ) ) ; //SYNTAX ERROR
But expression below solved the problem.
PASSTHRU( <expression containing ? >,<expression> ) ; //SYNTAX worked. |
|
| Back to top |
|
|
| j.f.sorge |
| Posted: Fri Jan 16, 2009 7:35 am Post subject: |
|
|
Master
Joined: 27 Feb 2008
Posts: 218
|
We have the same problem here when we want to use a SELECT in the expression. There is no way to get the return values of the statement when you are using something like
| Code: |
| PASSTHRU <expression> VALUE( <expr> , ... ) ; |
If you use
| Code: |
| PASSTHRU( <expression containing ? >,<expression> ) ; |
it works but help center says that this code is for backwards compatibility only.
_________________
IBM Certified Solution Designer - WebSphere MQ V6.0
IBM Certified Solution Developer - WebSphere Message Broker V6.0
IBM Certified Solution Developer - WebSphere Message Broker V6.1
IBM Certified Solution Developer - WebSphere Message Broker V7.0 |
|
| Back to top |
|
|
| mgk |
| Posted: Sat Jan 17, 2009 2:02 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Hi,
I think there is some confusion between the PASSTHRU statement, which cannot return a result set from a select and the PASSTHRU function which can. The comments I made above were refering to the PASSTHRU statement.
However, this example is on the help page you linked to, and is not 'deprecated' in any way and should help explain:
| Code: |
SET OutputRoot.XMLNSC.Data.SelectResult.Row[] =
PASSTHRU('SELECT R.* FROM Schema1.Table1 AS R WHERE R.Name = ? OR R.Name =
? ORDER BY Name'
TO Database.DSN1
VALUES ('Name1', 'Name4')); |
Does this help?
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions.
Last edited by mgk on Tue Jan 20, 2009 4:14 am; edited 1 time in total |
|
| Back to top |
|
|
| j.f.sorge |
| Posted: Tue Jan 20, 2009 3:37 am Post subject: |
|
|
Master
Joined: 27 Feb 2008
Posts: 218
|
The problem is that you have to know the DataSourceName in the ESQL code when you use the PASSTHRU function (as we want to do).
As we are working with variables which are set at deploy time, we could create an external which gets the same value as the field on compute node. So it will be replaced with the same value and we could use the DSN in the PASSTHRU function.
Is there any chance to get the DSN (which has been set on the ComputeNode) within the ESQL code?
_________________
IBM Certified Solution Designer - WebSphere MQ V6.0
IBM Certified Solution Developer - WebSphere Message Broker V6.0
IBM Certified Solution Developer - WebSphere Message Broker V6.1
IBM Certified Solution Developer - WebSphere Message Broker V7.0 |
|
| Back to top |
|
|
| mgk |
| Posted: Tue Jan 20, 2009 4:23 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Hello.
Firstly you can get the datasource on the node as a Broker Property like this:
| Code: |
DECLARE myDSN CHARACTER;
SET myDSN = DataSource; |
You can also dynamically set the datasource. The value you use as the DSN can come from a User Defined Property (UDPs [help topic ak01015]) which can then be set when you deploy, you can use a value in a message etc. Once you have the value you want to use, you can give it to the PASSHTRU (or other ESQL DB access method like SELECT or INSERT etc) like this:
...TO Database.{myDSN}...
where myDSN is an ESQL variable.
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 |
|
|
| j.f.sorge |
| Posted: Tue Jan 20, 2009 5:53 am Post subject: |
|
|
Master
Joined: 27 Feb 2008
Posts: 218
|
| mgk wrote: |
| Code: |
DECLARE myDSN CHARACTER;
SET myDSN = SQL.DataSource; |
|
Thanks a lot! We will use this code, as we do not want to define a UDP separately.
_________________
IBM Certified Solution Designer - WebSphere MQ V6.0
IBM Certified Solution Developer - WebSphere Message Broker V6.0
IBM Certified Solution Developer - WebSphere Message Broker V6.1
IBM Certified Solution Developer - WebSphere Message Broker V7.0 |
|
| Back to top |
|
|
| mgk |
| Posted: Tue Jan 20, 2009 5:56 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Hello.
| Quote: |
| Thanks a lot! We will use this code, as we do not want to define a UDP separately. |
I'm glad you're happy  , but I don't quite understand something  . If you just do not specify the TO clause at all the DSN on the node will automatically be used. Therefore, why are you trying to specify it explicitly?
Cheers,
_________________
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 |
|
|
| j.f.sorge |
| Posted: Tue Jan 20, 2009 6:00 am Post subject: |
|
|
Master
Joined: 27 Feb 2008
Posts: 218
|
| mgk wrote: |
| .If you just do not specify the TO clause at all the DSN on the node will automatically be used. Therefore, why are you trying to specify it explicitly? |
ESQL editors says that there is a syntax error when you do not specify the TO when using the PASSTHRU function.
_________________
IBM Certified Solution Designer - WebSphere MQ V6.0
IBM Certified Solution Developer - WebSphere Message Broker V6.0
IBM Certified Solution Developer - WebSphere Message Broker V6.1
IBM Certified Solution Developer - WebSphere Message Broker V7.0 |
|
| Back to top |
|
|
| mgk |
| Posted: Tue Jan 20, 2009 9:11 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Can you post the code that is giving you the error?
_________________
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 |
|
|
| j.f.sorge |
| Posted: Wed Jan 21, 2009 8:11 am Post subject: |
|
|
Master
Joined: 27 Feb 2008
Posts: 218
|
The following Code works
| Code: |
| SET row_results = PASSTHRU(ch_sqlStatement TO Database.{SQL.DataSource} VALUES(ref_request.ns:KontoId)) |
whereas
| Code: |
| SET row_results = PASSTHRU(ch_sqlStatement VALUES(ref_request.ns:KontoId)) |
is not valid.
(I tried to post this code around a hundred times and always got an 503 because of the semicolons... ) 
_________________
IBM Certified Solution Designer - WebSphere MQ V6.0
IBM Certified Solution Developer - WebSphere Message Broker V6.0
IBM Certified Solution Developer - WebSphere Message Broker V6.1
IBM Certified Solution Developer - WebSphere Message Broker V7.0 |
|
| Back to top |
|
|
|
|
