SQL placeholder in WHERE IN issue, inserted strings fail.
- by Alastair Pitts
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?
