Transaction Group SQL Expression Evaluation Error

Ignition
1

I am having a bit of an issue. I’m not sure if it is a bug or I am fundamentally doing something wrong.

In Transaction Groups, I have created a new Block Group. In that group I create a new Expression Item, with Data Type string and Expression Type SQL Query. I test a few scenarios:

SELECT ‘Hello’ (Evaluates OK)
SELECT ‘2018-09-01 12:06:55’ (Evaluates OK)
SELECT {integerTag} (Evaluates OK)
SELECT {stringTag} (Evaluation ERROR)
SELECT {dateTag} (Evaluation ERROR)

Any help would be much appreciated.

2

Try putting the string and date tags in single quotes

SELECT '{stringTag}'
SELECT '{dateTag}'
3

I don’t get the usage. Why are you using SQL expressions to take tags and turn them into items that will end up in the group’s inserts? Just supply the tag itself as the transaction group item. No funky data type conversions required, and no extra round trips to the DB prior to the INSERT.

4

Thank you both for your replies:

@pturmel
I have simplified the setup to highlight where the problem is so it may be recreated easy enough for possible testing of others. All other information would be irrelevant as the purpose was to show that the SQL Query would evaluate an integer tag but not a string or date tag.

If it helps you understand for future purposes though, I was attempting to grab an Id field of another table where two other fields match. One of those fields being a date field but I was getting an evaluation error

SELECT Id
FROM SomeOtherTable
WHERE JobNumber = {[~]_PlantPLC_/Job_Number}
AND FinishTime = {[~]_PlantPLC_/Finish_Time}

@schenk
That did the trick!
Anyone know why this is the case? It doesn’t matter what Data Type the tag is or the Group item’s Data Type for this to work either.

5

Curly-brace evaluation in any SQL expression in Ignition simply performs a conversion of that value with its default string format and inserts it in the SQL directly, and then parsed back to a raw type in the DB. This is fine for integers and usually fine for numbers in general, but is not fine for strings and any other datatype. For some DB platforms, the standard java conversion of date to string is non-conformant, and won't work even when quoted. Such cases, and any exotic data type, will need intermediate string formatting and possible also quote escaping via intermediate items. Also note that single and double quotes are interchangeable in python and in Ignition's expression language, but are not interchangeable in SQL. Double quotes are special, and it varies by DB brand.
The data type conversion problem is solved with parameters to named queries, so long as the JDBC driver understands the supplied java datatype (all the Ignition types are supported). And in other places in Ignition, with the "Prep" versions of the system.db.* functions. In non-SQL expression items (Ignition's expression language), data types of brace expansions are preserved into function calls and into return values.
You can also avoid these problems by using a stored procedure transaction group instead of a standard or block group, and have the DB perform the necessary lookups in one round trip. This will dramatically improve execution time when the DB is not on the same box. (Which it generally should not, IMNSHO.)

1 Like