| Author |
Message
|
| nr525 |
 Posted: Wed Dec 15, 2004 9:28 am Post subject: SELECT using IN (?,?,...) with variable number of parameters |
 |
|
Novice
Joined: 16 May 2002
Posts: 15
Location: UK
|
Hi,
I'm trying to select a number of rows from the database dependant on message content using WHERE somecolumn IN ('','',''....) . The number of values to be matched varies per message. So I have something like;
Environment.Variables.Names.Name[1] = 'evt1'
Environment.Variables.Names.Name[2] = 'evt2'
......
...
.
With the number of occurances varying per message. So I'd like to do some like;
-- Determine how many names = numNames
-- build a string of question marks dependant on numNames
-- build the passthru statement
DECLARE passthruStr CHARACTER;
SET passthruStr = 'SELECT A.NAME AS Name,
A.AUDITED AS isAudited
FROM STP_DB.NAMES AS A
WHERE
A.NAME IN ('
|| questionMarks
|| ')';
-- Execute
SET Environment.Variables.NamesReturned[] =
PASSTHRU(passthruStr,
Environment.Variables.Names.Name[]);
It doesn't work, essentially only the first value 'evt1' is being set into the statement resulting in a SQL exception due to an incorrect number of parameters being set ([IBM][CLI Driver] CLI0100E Wrong number of parameters. SQLSTATE=07001). All others are present but are not being set
Any ideas? The manual indicates I should be able to do this
-----
SET OutputRoot.XML.Result.Data[]
= PASSTHRU('SELECT AccountNum FROM user2.AccountData
WHERE AccountId IN (? , ? , ?)',
InputBody.Data.Account.Id[]);
-----
I was wondering if it makes any difference that I'm using the environment and not values from the InputBody?
Also note that this code exists as a 'global' procedure in a different package to where its being called from.
Thanks in advance. |
|
| Back to top |
|
 |
| kirani |
| Posted: Wed Dec 15, 2004 10:15 am Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
I think your number of questionMarks and the number of elements in EnvironmentTree should match.
_________________
Kiran
IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries
|
|
| Back to top |
|
|
| nr525 |
| Posted: Thu Dec 16, 2004 1:41 am Post subject: |
|
|
Novice
Joined: 16 May 2002
Posts: 15
Location: UK
|
Thanks for the response and yes your right the number of question marks should match the number of elements in the environment tree.
Problem is that the number of question marks is built based on the number of elements and this is working, to the point that if I have two elements in the tree there are also two question marks.
Debugging this however I see that only the first element in the tree is being set into the first parameter, the second (third and so on) in the tree is not set and hence the missmatch between number of parameters expected and those set. |
|
| Back to top |
|
|
| mgk |
| Posted: Thu Dec 16, 2004 3:33 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Hi,
Please post your full ESQL, and I will take a quick look. What you are trying to do should work
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 |
|
|
| nr525 |
| Posted: Thu Dec 16, 2004 5:06 am Post subject: |
|
|
Novice
Joined: 16 May 2002
Posts: 15
Location: UK
|
Thanks for your time on this one.
I cut down the code to this test which still fails in the same way.
-------------------------------------------------------------------------------------
DECLARE envReference REFERENCE TO Environment;
DECLARE annoReference REFERENCE TO Environment.Variables.annotations;
SET auditsSize = CARDINALITY(annoReference.AUDITS[]);
DECLARE counter INT 1;
-- build a list of names to retrieve
WHILE (counter <= auditsSize) DO
SET envReference.Variables.Names.name[counter] = annoReference.AUDITS[counter].name;
SET counter = counter + 1;
END WHILE;
CALL getEvents(envReference); -- envReference.Variables.NamesReturned[] is populated now
-----------------------------------------------------------------------------------
CREATE PROCEDURE getNames(INOUT envReference REFERENCE) BEGIN
-- Determine how many names
DECLARE numEvts INT;
SET numEvts = CARDINALITY(envReference.Variables.Names.name[]);
-- build the ?, ?... string with the same number of these
DECLARE questionMarks CHARACTER;
DECLARE evts CHARACTER;
DECLARE counter INT 1;
WHILE (counter <= numEvts) DO
IF (counter = 1) THEN
SET questionMarks = '?';
ELSE
SET questionMarks = questionMarks || ' , ' || '?';
END IF;
SET counter = counter + 1;
END WHILE;
-- build the passthru statement and set the variables
DECLARE passthruStr CHARACTER;
SET passthruStr = 'SELECT NameDb.NAME AS name,
NameDb.AUDITED AS isAudited
FROM DB.NAME AS NameDb
WHERE
NameDb.NAME IN ('
|| questionMarks
|| ')
AND NameDb.VALID_FROM <= ?
AND NameDb.VALID_TO >= ?
AND NameDb.STATUS <> ?';
SET envReference.Variables.NamesReturned[] = PASSTHRU(passthruStr,
envReference.Variables.Names.name[],
CURRENT_DATE,
CURRENT_DATE,
'P');
RETURN;
END;
---------------------------------------------------------------------------------
Thanks again. |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Thu Dec 16, 2004 5:58 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
| nr525 wrote: |
SET envReference.Variables.NamesReturned[] = PASSTHRU(passthruStr,
envReference.Variables.Names.name[],
CURRENT_DATE,
CURRENT_DATE,
'P'); |
Using envReference.Variables.Names.name[] doesn't do what you think it does. It will pass a SINGLE parameter, not a list of parameters.
You will have to try something else. Consider a similar loop that will build a string of the form "envReference.Variables.Names.name[1],envReference.Variables.Names.name[2]" and using {}.
I don't know that it will work, though.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| nr525 |
| Posted: Thu Dec 16, 2004 7:37 am Post subject: |
|
|
Novice
Joined: 16 May 2002
Posts: 15
Location: UK
|
Well yes thats certainly whats happening but interestingly the documentation for passthru implies the opposite;
-----
SET OutputRoot.XML.Result.Data[]
= PASSTHRU('SELECT AccountNum FROM user2.AccountData
WHERE AccountId IN (? , ? , ?)',
InputBody.Data.Account.Id[]);
----- |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Thu Dec 16, 2004 8:13 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
That's definitely odd.
Try moving your [] to the end of your list of parameters. It may be a case where the interpretation only happens "the right way" if it's the last parameter.
Worse, it might only happen "the right way" if it's the ONLY parameter.
Even worse, it might be a bug in the documentation.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| EddieA |
| Posted: Thu Dec 16, 2004 10:06 am Post subject: |
|
|
Jedi
Joined: 28 Jun 2001
Posts: 2453
Location: Los Angeles
|
| Quote: |
| it might be a bug in the documentation |
Naw. That could never happen. 
Cheers,
_________________
Eddie Atherton
IBM Certified Solution Developer - WebSphere Message Broker V6.1
IBM Certified Solution Developer - WebSphere Message Broker V7.0 |
|
| Back to top |
|
|
| mgk |
| Posted: Fri Dec 17, 2004 1:41 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
I have just checked, and although the documentation is correct it is not as descriptive as it could be. PASSTHRU will take either a SINGLE list [] paramater (as shown in the docs example) OR a comma seperated list of scalar (non list) parameters. It does NOT handle a mix of lists and non lists. The fact that you seem to be able to pass it a list and it takes only the first item is a bug, it should really return an error about using a list where a scalar is required. You may like to raise a PMR about this...
You could probably get the behaviour you need by adding the extra parameters at the end of the list (as extra 'name' items in your case). In this way it will bind them in the normal way.
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 |
|
|
| nr525 |
| Posted: Fri Dec 17, 2004 5:00 am Post subject: |
|
|
Novice
Joined: 16 May 2002
Posts: 15
Location: UK
|
Thanks for your time, it does work with just a single list so that will do just fine for now.
Is there any particular reason it won't handle the combination of list and comma separated scalar parameters?
thanks... |
|
| Back to top |
|
|
|
|
