Query tag parameter UDT

Ignition
1

Hi everyone,

I have a Query Tag inside a UDT that reads data from SQL Server.
The query works fine when I use a fixed table name, but it fails when I pass the table name as a UDT parameter.

Example:

SELECT TOP(1)
    kgtotalizador
FROM
    {BASCULA}
WHERE
    fechainsert > CONVERT(datetime, '{[.]../../GLOBAL/FechaHoraDia}', 120)
ORDER BY
    id ASC

If I replace {BASCULA} with the actual table name (for example T1), the query works perfectly.
If I remove the WHERE clause, it also works fine.

So the issue only appears when:

  • The table name comes from a UDT parameter ({BASCULA}), and

  • The query includes a condition with a tag reference ({[.]../../GLOBAL/FechaHoraDia}).

The tag returns:

[null, Error_ExpressionEval("The statement did not return a result set.")]

It looks like the date condition causes the problem when combined with a table parameter.
Has anyone seen this behavior before or knows why Ignition fails in this case?

Thanks!

2

Have you tried putting {BASCULA} in quotes?

3

I don’t think direct parameterization of the table name is allowed in SQL like that. I don’t normally use query tags, but have you tried using QUOTENAME(OBJECT_NAME({BASCULA}))

4

I just tested this and it worked fine on 8.1.42

The only difference is that I used a parameter for the date and put it in quotes.

SELECT TOP(1) [TableID]
FROM {TableName}
WHERE [TimeBegin] >= '{DateParam}'
ORDER BY [TimeBegin] DESC

What type of tag are you reading the date from? and are you sure of the convert?

5

I vaguely recall a bug related to query bindings that have both a UDT parameter and a tag reference, but I can't recall the details.

Related notes:

  • Query tags cannot use proper/safe parameter substitutions (Prep query question marks, named query colon-delimited values). Curly braces are string substitutions without proper parameter handling.

  • String substitution is necessary for SQL structure elements, like table and column names.

  • String substitution is dangerous for SQL values, particularly when values are arbitrary and/or from external sources (like a PLC).

  • Single quotes around string substitutions are required when the value injected is not purely numeric.

Basically, you should not be using a query tag for this case, since it needs proper parameterization. Unfortunately, with a UDT, you cannot scalably use gateway tag events (subscribed to all FechaHoraDia), which you need to safely run queries. Consider using scripted queries with my Integration Toolkit's BulkScript tag actor, which helps solve this problem.