Ok, so I took that example and mocked it up, and noticed a few things:
- On an item set to “SQL Query” mode you get an error stating that “a result set was generated for an update query”. What’s going on here is that behind the scenes, Ignition has to decided whether the query is fundamentally a SELECT or a UPDATE/INSERT/DELETE. Since the first word isn’t “select”, it’s choosing wrong. So, “SQL Query” mode won’t work. BUT…
- You can use “expression mode” with the executeScalarQuery function. However, just pasting in the query won’t work, since multi-line strings aren’t supported in the expression language, so…
- You have to modify the query to be a single string with lines separated either by semi-colons or newlines.
So, to summarize, set your item to “Expression” expression mode, and use the following:
executeScalarQuery("DECLARE @DayAdd int;"+
"SET @DayAdd = 1;"+
"SELECT DATEADD(dd, @DayAdd, getdate()) 'Date'")
(See, I used concatenation to split it over multiple lines for readability, but it still boils down to a single string)
Hope this helps, let me know if you run into any difficulty with your more advanced queries. I’ll put a ticket in to allow multi-line strings, and perhaps a way to override how the sql query gets interpreted.