Bind DB Tags to Stored Procedure result

Ignition
1

Hello,

I am trying to create a DB tag whose value is the result of a stored procedure. It keeps telling me that there is an evaluation error but nothing else. I tried removing the logic out of the stored procedure and placed it directly in the tag’s SQL expression but it didn’t like that either.

So I guess my question is can a tag’s value only be the result of a simple SELECT statement? I am using t-sql to declare variables, set them based on other OPC tags and then use them in SELECT statements and it seems like that is what’s causing this.

Thanks in advance,
Steve

2

How are you calling the stored procedure? What kind of result do you expect?

It is possible to call stored procedures, but you can’t use output variables and you can only get a scalar value back.

If you post some examples of the queries you’re trying to run we can see if anything jumps out.

Regards,

3

Here is a real brief example that isn’t even using a stored procedure. I have the following in my Expression/SQL Binding text box:

DECLARE @DayAdd int SET @DayAdd = 1 SELECT DATEADD(dd, @DayAdd, getdate()) 'Date'

If I remove the DECLARE and SET and just put the 1 in where the @DayAdd variable is, the DATEADD works.

Thanks,
Steve

4

Hi,

Ok, so I took that example and mocked it up, and noticed a few things:

  1. 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…
  2. 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…
  3. 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.

Regards,