Issue trying coercing Meter ID value from Tag into SQL query

Ignition
1

Hey, I have a table is using a SQL query to fill in data. I want to post values filter on a meter id.

SELECT [Meter id],
[pressbase],
[tempbase],
[sampletime]
FROM [eDB].[dbo].[efmconfig] WHERE [meter_id] = 'XXX' ORDER BY [sampletime] DESC

I've tried using an indirect tag on a Label, and Numeric Label (MeterID2)and then have the

WHERE [meter_id] = 'XXX'

query use it to filter.

WHERE [meter_id] = '{MeterID2.value}'

I see that the correct Meter ID number shows on both labels I've tried, but the SQL query displays nothing. If I use the Database Query Browser and hardcode the meter id, it works. Any ideas what i'm missing? I saw someone had a similar issue with a Multi valve state, and the answer was to use an indirect tag. However that doesn't seem to be working for me. Your help is appreciated. Thanks

2

When you string-embed a numeric into a raw SQL query, the generic string conversion will yield no leading zeroes or any other formatting. You are quoting the meter ID, so I presume it is a string in the DB. If so, use an intermediate custom property to format the number into a string in the expected way. If a numeric field in the DB, remove the quotes in your where clause to let the DB see it as a number.

3

You are quoting the meter ID, so I presume it is a string in the DB

Yes, varchar(255)

If so, use an intermediate custom property to format the number into a string in the expected way.

I'm not sure what this entails. Could you give a bit more details?