Valid SQL statements in Ignition (JDBC)

Ignition
1

Hi,

From another topic I got this quote:

SQL statements in Ignition are run through JDBC driver(s), which generally speaking do not support more complex queries/statements

Is there a place where I can see which statements are valid and their syntax?

I'm not very familiar with SQL and I just need to create a simple query that picks between 2 different queries based on a condition.
I found some different IF..ELSE SQL statements, but I could'nt get them working.
Is an IF..ELSE statement supoprted?
If yes how does the syntax look?

Thanks

2

Standard SQL uses “CASE … END” with various forms of IF clauses within. That isn’t really a JDBC issue, but the syntax flavor of your connected DB. The issue in Ignition is the identification of UPDATE/INSERT operations versus SELECT operations. Queries that look like neither often don’t work everywhere. Queries submitted via scripting functions that supply that information are more tolerant. (Like system.db.runPrepQuery() versus system.db.runPrepUpdate()).

3

@pturmel
Thanks.
I’ve tried this kind of SQL statement now, but it’s not working:

select value from valueTable where [key] =
     case
          when {[.]UDTDATA/AREA} = '1' then concat( 'SP1.' , {[.]UDTDATA/SITE} , '.REF' )
          when {[.]UDTDATA/AREA} = '2' then concat( 'SP2.' , {[.]UDTDATA/SITE} , '.REF' )
     end

Edit: I should mention that this is code in a query tag inside a UDT.

4

You will probably have to troubleshoot it via trial and error.

F.e. try that query in the “Database Query Browser” tool (with the tag values replaced), check what it returns. If it doesn’t work, try to remove or simplify parts of the query until it works (f.e. remove the concats, remove the case, …). If it works in the dataabase browser, there’s probably an issue with your tag configuration (references to the other tags, return type that wasn’t expected, …)

5

If UDTDATA/AREA and/or UDTDATA/SITE are strings, you would need single quotes in your SQL.

6

@pturmel
That worked! Thanks Phil :+1: