SQL placeholder in WHERE IN issue, inserted strings fail.

Question

As part of my jobs, I need to writes SQL queries to connect to our PI database. To generate a query, I need to pass an array of tags (essentially primary keys), but these have to be inserted as strings.

As this will be a modular query and used for multiple tags, a placeholder is being used.

The query relies upon the use of the WHERE IN statement, which is where the placeholder is, like below:

SELECT SUM(value * 5/1000) as "Hourly Flow [kL]" 
  FROM piarchive..pitotal 
 WHERE tag IN (?) 
   AND time between ? and ? 
   AND timestep = '1d' 
   AND calcbasis = 'Eventweighted' 
   AND value <> ''

The issue is the format in which the tags are to be passed in as. If I add them directly into the query (for testing), they go in the format (these are example numbers): '000000012','00000032','005050236','4560236' and the query looks like:

SELECT SUM(value * 5/1000) as "Hourly Flow [kL]" 
  FROM piarchive..pitotal 
 WHERE tag IN ('000000012','00000032','005050236','4560236') 
   AND time between ? and ? 
   AND timestep = '1d' 
   AND calcbasis = 'Eventweighted' 
   AND value <> '' 

...which works.

If I try and add the same tags into the placeholder, the query fails. If I only add 1 tag, with no quotes (using the placeholder), the query works.

Why is this happening? Is there anyway around it?

Answer 1

When you try to place all of the numbers into the placeholder, it's being treated as a single string, not as a comma-delimited list of items. So, unless you happen to have a value in your table that exactly matches that "string", it will never work.

The solutions are either to use a placeholder for each item you need to match (three items, three placeholders, for examples), or, don't use a placeholder and, instead, concatenate the entire SQL string and execute. (Of course, that's not recommended due to the possibility of SQL injection).

Answer 2

I've struck this problem before on our reporting solution where the user wanted to be able to enter up to 5 values in independent drop-down controls. These controls were populated with the possible values and a NULL value meaning "don't use" (The first control did not allow NULL since you have top select one possibility at least).

Our query has the snippet (translated to your situation):

WHERE tag IN (?,?,?,?,?)

Then when we set up the place-holders, we assigned the values from the drop-downs to their respective place-holder with one proviso: if the drop-down contained NULL, we populated the place-holder with the contents of the first drop-down.

That way, if you enter 7,3,1,8,9, you end up with:

WHERE tag IN (7,3,1,8,9)

If you enter 7,NULL,1,NULL,9, you get:

WHERE tag IN (7,7,1,7,9)

and so on. The DBMS we used is smart enough to collapse IN statements to their most efficient form before executing them so there was no real penalty to doing it this way. Your mileage may vary.

One problem with this kludge is that you have to set an upper bound to the number of possible entries in the IN clause but that's not a problem in our situation.

Answer 3

We questioned the vendor, and got this response, which allows us to pass a comma-separated group of tags.

select *
from pitotal
where tag IN (SELECT tag from pipoint WHERE INSTR(?, tag) <> 0) and time between 'y' and 't'

basically doing a bunch of string comparisons.

Hope this is useful to someone