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 » ESQL PASSTHRU PARAMETERS supplied in variable

Post new topic  Reply to topic
 ESQL PASSTHRU PARAMETERS supplied in variable « View previous topic :: View next topic » 
Author Message
inMo
PostPosted: Fri Mar 17, 2017 10:12 am    Post subject: ESQL PASSTHRU PARAMETERS supplied in variable Reply with quote

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
View user's profile Send private message
smdavies99
PostPosted: Fri Mar 17, 2017 11:22 am    Post subject: Reply with quote

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
View user's profile Send private message
inMo
PostPosted: Fri Mar 17, 2017 12:51 pm    Post subject: Reply with quote

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
View user's profile Send private message
rekarm01
PostPosted: Fri Mar 17, 2017 1:07 pm    Post subject: Re: ESQL PASSTHRU PARAMETERS supplied in variable Reply with quote

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
View user's profile Send private message
adubya
PostPosted: Fri Mar 17, 2017 1:14 pm    Post subject: Reply with quote

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

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » ESQL PASSTHRU PARAMETERS supplied in variable
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.