| Author |
Message
|
| mindspace |
 Posted: Tue Jan 22, 2013 8:38 am Post subject: How to use Like clause with Values in passthru |
 |
|
Apprentice
Joined: 22 Jan 2013
Posts: 26
|
Hi
Currently we have an sql query where we are passing literals in passthru,, like below.
Declare key Character;( sample values will be like '12344+23243+%')
sqlstmt = SELECT XXX FROM Database.Schema.Tablename
WHERE coloumn LIKE ''' || character|| '''';
SET sqlResult = PASSTHRU(sqlStmt TO Database.dsnName);
Now requirement is to use parameters in above query so that at run time same SQL statement can be used with different data value.
I am aware of using of parameter markers in normal cases but never used them with LIKE
Please suggest me if use of parameter markers is possible in above query?
I tried couple of things but no use.. |
|
| Back to top |
|
 |
| lancelotlinc |
| Posted: Tue Jan 22, 2013 9:19 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
You do this like any other CHARACTER string in ESQL. Substitute values in building the string (ie. ' || ') as you would any other time. This is not difficult. You would have seen this in the training class. Have you attended the class?
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| mqjeff |
| Posted: Tue Jan 22, 2013 9:23 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
As well, whether or not you can use a parameter marker (a "?") in the PASSTHRU statement depends entirely on what the database behind the connection accepts, because passthru does exactly what it says it does.
It takes the SQL statement and passes it through to the database for execution. |
|
| Back to top |
|
|
| mindspace |
| Posted: Tue Jan 22, 2013 8:09 pm Post subject: |
|
|
Apprentice
Joined: 22 Jan 2013
Posts: 26
|
| lancelotlinc wrote: |
| You do this like any other CHARACTER string in ESQL. Substitute values in building the string (ie. ' || ') as you would any other time. This is not difficult. You would have seen this in the training class. Have you attended the class? |
I tried couple of things.. like below
SET sqlStmt = 'SELECT XXX FROM Database.Schema.Table name
WHERE XXX = ? VALUES LIKE ''' || Character|| '''';
SET sqlResult = PASSTHRU(sqlStmt TO Database.dsnName);
SET sqlStmt = 'SELECT XXX FROM Database.Schema.Table name
WHERE XXX LIKE ? VALUES ( ''' || character|| ''')';
SET sqlResult = PASSTHRU(sqlStmt TO Database.dsnName);
But nothing works.. please advice the right way of using parameter markers.. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Tue Jan 22, 2013 8:46 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
have you tried
| Code: |
| VALUES ({character variable name}) |
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| mindspace |
| Posted: Tue Jan 22, 2013 10:14 pm Post subject: |
|
|
Apprentice
Joined: 22 Jan 2013
Posts: 26
|
| fjb_saper wrote: |
have you tried
| Code: |
| VALUES ({character variable name}) |
|
Nope,.. this doesnt work... |
|
| Back to top |
|
|
| kash3338 |
| Posted: Tue Jan 22, 2013 10:58 pm Post subject: |
|
|
Shaman
Joined: 08 Feb 2009
Posts: 709
Location: Chennai, India
|
This code should work out,
| Code: |
DECLARE sqlStmt CHARACTER;
DECLARE pattern CHARACTER '<LIKE_Pattern>';
SET sqlStmt = 'SELECT XXX FROM Database.Schema.Table name
WHERE XXX LIKE ?';
SET sqlResult = PASSTHRU(sqlStmt, pattern);
|
|
|
| Back to top |
|
|
| adubya |
| Posted: Wed Jan 23, 2013 1:58 am Post subject: |
|
|
Partisan
Joined: 25 Aug 2011
Posts: 377
Location: GU12, UK
|
How about the following
| Code: |
DECLARE likeExpr CHAR 'like expression e.g. %TEST%';
SET sqlStmt = 'SELECT XXX FROM Schema.Table name WHERE XXX LIKE ?';
SET sqlResult = PASSTHRU(sqlStmt TO Database.dsnName VALUES (likeExpr)); |
|
|
| Back to top |
|
|
| mindspace |
| Posted: Wed Jan 23, 2013 6:51 am Post subject: |
|
|
Apprentice
Joined: 22 Jan 2013
Posts: 26
|
| adubya wrote: |
How about the following
| Code: |
DECLARE likeExpr CHAR 'like expression e.g. %TEST%';
SET sqlStmt = 'SELECT XXX FROM Schema.Table name WHERE XXX LIKE ?';
SET sqlResult = PASSTHRU(sqlStmt TO Database.dsnName VALUES (likeExpr)); |
|
Thanks! It works!! |
|
| Back to top |
|
|
| sandy vish |
| Posted: Wed Jan 23, 2013 6:51 am Post subject: |
|
|
Apprentice
Joined: 18 Nov 2012
Posts: 27
|
IF valueColumn is not null AND TRIM(valueColumn) <> '' THEN
DECLARE selectStatement CHAR;
SET selectStatement =
'select ' || valueColumn ||
' from ' || tableName ||
' where ' || keyColumn || ' = ?';
SET resultSet.values[] VALUE = PASSTHRU(selectStatement, keyValue); |
|
| Back to top |
|
|
|
|
