Are comments in Named Queries known to not work correctly?

Ignition
1

I am trying to get some basic named queries up and running, and during my tribulations I have discovered that if I add comments (-- prefix, postgresql), I get errors like

org.postgresql.util.PSQLException: The column index is out of range: 4, number of columns: 3.

These are entirely caused by the existence of comments inline with the query. Additionally, having whole queries commented out below the active query causes the error:

org.postgresql.util.PSQLException: Multiple ResultSets were returned by the query.

It took me awhile to realize that comments were breaking at least the testing capabilities of Ignition for its own Named Queries.

Am I doing something fundamentally wrong here?

For example, the following query works if I remove all of the commented lines below the SELECT slope FROM slope line, but throws the most previously aforementioned error as is:

WITH data AS (
  SELECT
    EXTRACT(EPOCH from NOW()) - EXTRACT(EPOCH FROM tag_historian.t_stamp) AS x,
    tag_historian.mfs1_pressure AS y
  FROM
    tag_historian
  WHERE
  	tag_historian.t_stamp <= NOW() AND tag_historian.t_stamp >= (NOW() - 10 * INTERVAL '1 second')
),
sums AS (
  SELECT
    SUM(x) AS sum_x,
    SUM(y) AS sum_y,
    SUM(x * y) AS sum_xy,
    SUM(x * x) AS sum_xx,
    COUNT(*) AS n
  FROM
    data
),
slope AS (
  SELECT
    (n * sum_xy - sum_x * sum_y) / (n * sum_xx - sum_x * sum_x) AS slope
  FROM
    sums
)
SELECT slope FROM slope;
--SELECT    	
--	tag_historian.mfs1_pressure AS y,
--	TO_TIMESTAMP('2023-09-19 11:30:00', 'YYYY-MM-DD HH:MI:SS') - NOW() as x
--FROM
--	tag_historian
--WHERE
--	tag_historian.t_stamp <= NOW() AND tag_historian.t_stamp >= (NOW() - 10 * INTERVAL '1 second')
--LIMIT 1;
2

Hmm, this sounds familiar. What version of Ignition are you using?

3

8.1.31. Also, in case it is relevant, our gateway has crashed 3 times since I have been fiddling in SQL. There is no apparent logged cause. I just get sharp reboots.

4

Not sure about the crashes, but yes it looks like comments are unsupported.

Someone tried to fix it once, but it broke a bunch of legitimate queries and it got rolled back.

5

Here is a concrete example of the inline commenting breaking my query:

WITH data AS (
  SELECT
  	TO_TIMESTAMP( :timestamp, 'YYYY-MM-DD HH24:MI:SS') as ts,
    TO_TIMESTAMP( :timestamp, 'YYYY-MM-DD HH24:MI:SS') - tag_historian.t_stamp AS x,
    CAST(tag_historian.pt03_pressure as FLOAT) AS y
  FROM
    tag_historian
  WHERE
  	tag_historian.t_stamp >= (TO_TIMESTAMP( :timestamp, 'YYYY-MM-DD HH24:MI:SS') - 10 * INTERVAL '1 second') AND tag_historian.t_stamp <= TO_TIMESTAMP( :timestamp, 'YYYY-MM-DD HH24:MI:SS')
)
SELECT ts,x,y FROM data;

This query doesn't return anything, so I am trying to debug my timestamp manipulation. I do this in editor (using the comment hotkey while highlighting two lines):

WITH data AS (
  SELECT
  	TO_TIMESTAMP( :timestamp, 'YYYY-MM-DD HH24:MI:SS') as ts,
    TO_TIMESTAMP( :timestamp, 'YYYY-MM-DD HH24:MI:SS') - tag_historian.t_stamp AS x,
    CAST(tag_historian.pt03_pressure as FLOAT) AS y
  FROM
    tag_historian
--  WHERE
--  	tag_historian.t_stamp >= (TO_TIMESTAMP( :timestamp, 'YYYY-MM-DD HH24:MI:SS') - 10 * INTERVAL '1 second') AND tag_historian.t_stamp <= TO_TIMESTAMP( :timestamp, 'YYYY-MM-DD HH24:MI:SS')
)
SELECT ts,x,y FROM data;

When I run it in Testing, I get this error: org.postgresql.util.PSQLException: The column index is out of range: 3, number of columns: 2.

6

I see. That hurts the process development in-Designer substantially.

7

I think it's a JDBC limitation.

We would have to strip them out before passing the queries to the underlying JDBC driver, and it turns out the last attempt was too naive, and doing it without breaking valid queries would require us to implement a full SQL parser.

edit: maybe not JDBC, but the parameter substitution logic instead.

8

It definitely is related to my gateway crashing. I think trying to run a Testing query while one is running already may be the problem. But I do not see a way to cancel the active query in the even that I do something like forget "limit" and need to not pull the entire database.

9

You could try using the /* ... */ for commenting lines, OR, copy your code into Notepad++ for easy reference.

10

The /* */ style of commenting has the same effect. And while I obviously can use an alternative editor, I was trying to do as much as I could inside of the tools provided by the product.

11

This. Comments are part of SQL scripting, not SQL statements. Scripting is outside the scope of JDBC, and is only partially supported by some JDBC drivers.

This is complicated in named queries by Ignition's need to find parameter references and place question marks in the right places (value params), and/or perform string interpolation (queryString params). JDBC itself only accepts question-mark parameters.

Sorry.

12

You helped me solve this problem a while ago, probably why it sounds familiar!

1 Like
13

I was under the previous impression that postgresql supports comments inside of statements, using either -- or /* */. The documentation seems to support this, at least in version 8. The documentation says

A comment is removed from the input stream before further syntax analysis and is effectively replaced by whitespace.

Perhaps the JDBC skips this removal step?

14

No, the Ignition NQ preprocessor is the problem. You have colon-delimited parameters inside your comment that is throwing off the NQ parameter preprocessor.

Try including comments in a query run through system.db.runPrepQuery() instead of a named query. It'll probably go through. (Requires question marks and a parameter list instead of colon-delimited named parameters and parameter dictionary.)

2 Likes
15

Thank you for the tip, Ill probably be using this functionality after the number of difficulties I have encountered in the process of trying to build some Named Queries. Although neither support dynamic column names, I suppose Ill need to handle that in the query text generation step -- I was suprised to find out that the parameters in named queries do not support column naming, since that is the first step towards making dynamic queries.

16

Column names must be known at the time a query is planned, as it affects the possible solutions. Binary transport of parameters (to avoid SQL injection) is only possible for actual values on every DB native API I've ever worked with. (At a "C" level, including Oracle, PostgreSQL, and MySQL.) So too with JDBC.

2 Likes
17

Yes of course, but as this was a layer in front of the query planner, it could have had in-system dynamic column name handling. I didn't realize how little extra pre-processing it was using, but now it is more clear to me. Ill likely stick to mutatable text queries in scripts. I know that they're lower security but I am more concerned with a functional system than an overly secure one at the moment.

18

You can change a parameter type to QueryString, and insert with curly braces. So yes, NQs can do dynamic column selection. But you'd best make sure the column name supplied is valid, as you are responsible for preventing SQL injection for anything that isn't a binary (value) parameter.

1 Like
19

This is good to know, thank you. Ill fiddle with it a little.

20

On the gateway side, I tend to do this too. But not for anything that needs to participate in a binding. NQs are the right solution for anything involving UI bindings.