|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
|
|
ESQL PASSTHRU PARAMETERS supplied in variable |
« View previous topic :: View next topic » |
Author |
Message
|
inMo |
Posted: Fri Mar 17, 2017 10:12 am Post subject: ESQL PASSTHRU PARAMETERS supplied in variable |
|
|
Master
Joined: 27 Jun 2009 Posts: 216 Location: NY
|
Need some help with ESQL PASSTHRU. Have a requirement to build the SQL statement in a node prior to the passthru and assign it to a variable. The variable will be used in a later node like:
ROW[] = PASSTHRU (SQL_STATEMENT_VARIBALE);
Would like to use parameters in a manner similar to the following (tried a number of syntax variations based on this generalized approach)
Code: |
SELECT t1.*, t2.*
FROM TABLE1 t1, TABLE2 t2
WHERE t1.ID= ?
AND t2.ID= ?
AND t2.COLUMN2= ?
AND t2.COLUMN3= ?
VALUES('VALUE1', 'VALUE2', 'VALUE3', 'VALUE4')
|
However, when building the string and assigning it to a variable, the passthru statement always results in an error of
Code: |
BIP2322E: Database error: SQL State ''07001''; Native Error Code '0'; Error Text ''[IBM][ODBC Oracle Wire Protocol driver]Value has not been specified for parameter 1.''. : /build/slot1/S900_P/src/DataFlowEngine/MessageServices/ImbOdbc.cpp: 3850: ImbOdbcStatement::checkRcInner: :
BIP2322E: Database error: SQL State ''07001''; Native Error Code '0'; Error Text ''[IBM][ODBC Oracle Wire Protocol driver]Value has not been specified for parameter 2.''. : /build/slot1/S900_P/src/DataFlowEngine/MessageServices/ImbOdbc.cpp: 3850: ImbOdbcStatement::checkRcInner: :
BIP2322E: Database error: SQL State ''07001''; Native Error Code '0'; Error Text ''[IBM][ODBC Oracle Wire Protocol driver]Value has not been specified for parameter 3.''. : /build/slot1/S900_P/src/DataFlowEngine/MessageServices/ImbOdbc.cpp: 3850: ImbOdbcStatement::checkRcInner: :
BIP2322E: Database error: SQL State ''07001''; Native Error Code '0'; Error Text ''[IBM][ODBC Oracle Wire Protocol driver]Value has not been specified for parameter 4.''. : /build/slot1/S900_P/src/DataFlowEngine/MessageServices/ImbOdbc.cpp: 3850: ImbOdbcStatement::checkRcInner: :
|
Is there a trick to using the parameters in combination with a variable? |
|
Back to top |
|
|
smdavies99 |
Posted: Fri Mar 17, 2017 11:22 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003 Posts: 6076 Location: Somewhere over the Rainbow this side of Never-never land.
|
you need to format a Character Variable with the complete SQL command.
Yes you can add the params to the call and used the '?' to substitute the value but I have had inconsistent resulrs that way. So I just create the whole command in a CHAR variable and pass that to the PASSTHRU function.
IT can be a PITA but at least you can check the syntax of the SQL Command in say SQLDeveloper first.
Something like this
Code: |
declare I1 INTEGER 100;
declare cSQL CHAR;
set cSQL = 'select P1, P2, P3 from MyTable where IND1= ''' || Cast(I1 as char) || ''' ';
set Environment.RES.R1[] = PASSTHRU(cSQL);
|
_________________ WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
Back to top |
|
|
inMo |
Posted: Fri Mar 17, 2017 12:51 pm Post subject: |
|
|
Master
Joined: 27 Jun 2009 Posts: 216 Location: NY
|
Thank you for the response.
The approach you've outlined will absolutely work. I'm trying to get the parameters to work as the documentation suggests it is more performance friendly.
https://www.ibm.com/support/knowledgecenter/en/SSMKHH_9.0.0/com.ibm.etools.mft.doc/ak05890_.htm
Quote: |
use parameter markers whenever possible because this reduces the number of different statements that need to be prepared and stored in the database and the broker. |
I don't understand why PASSTHRU works if given the fully constructed statement in a character variable, but using the fully constructed statement with parameters and values clause is causing it to break.
Any additional thoughts are appreciated. Perhaps I'm missing a simpler way to view this. |
|
Back to top |
|
|
rekarm01 |
Posted: Fri Mar 17, 2017 1:07 pm Post subject: Re: ESQL PASSTHRU PARAMETERS supplied in variable |
|
|
Grand Master
Joined: 25 Jun 2008 Posts: 1415
|
inMo wrote: |
The variable will be used in a later node like:
Code: |
ROW[] = PASSTHRU (SQL_STATEMENT_VARIABLE); |
|
Does the SQL_STATEMENT_VARIABLE include the VALUES clause? It probably shouldn't:
Code: |
ROW[] = PASSTHRU (SQL_STATEMENT_VARIABLE VALUES('VALUE1', 'VALUE2', 'VALUE3', 'VALUE4')); |
|
|
Back to top |
|
|
adubya |
Posted: Fri Mar 17, 2017 1:14 pm Post subject: |
|
|
Partisan
Joined: 25 Aug 2011 Posts: 377 Location: GU12, UK
|
Your SQL is specifying placeholder values using the "?" representation but you're not passing the corresponding variables in the PASSTHRU function. So the ESQL error is expected. When you specify "?" placeholder then IIB will expect a PASSTHRU VALUES argument to be supplied, the "VALUES" part of the SQL statement isn't the place to specify values which you're expecting to be used for the ? placeholders.
You need to use the PASSTHRU <SQL> VALUES <params> version of the PASSTHRU function.
<SQL> = your SQL, which has embedded "?" placeholders for variable values.
<params> is a LIST of actual values which will be used for each of the "?" placeholders.
On IIB engagements I ceate a utility "execSQL" procedure which takes a SQL string and a REFERENCE to a LIST of parameter values + options specifying how errors should be handled. I use the PASSTHRU <SQL> VALUES <params> approach to achieve this. _________________ Independent Middleware Consultant
andy@knownentity.com |
|
Back to top |
|
|
|
|
|
|
Page 1 of 1 |
|
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
|
|
|
|