Named query: SELECT works but UPDATE doesn't

Ignition
1

I have two named queries set up in Ignition for MariaDB:

Query type: Query
SELECT * FROM devices

This works.

Query type: Update Query
UPDATE devices
SET description = 'Beautiful'
WHERE id = 9

This works in phpMyAdmin front end running on the Ignition server PC but testing with the Named Queries editor results in

ava.sql.SQLException: Could not set parameter at position 1 (values was 'description') Query - conn:771(M) - "

Any ideas?

2

Is the Named Query literally just that provided String, or is it something like

UPDATE devices SET "{col}" = ':val' WHERE id = :id

Quick online search would suggest removing the double quotes around ":col". If your Named Query is just a query which has no parameters configured/expected/received, then something else is going on.

3

JDBC (or other interfaces for any DB I know) does not permit parameterizing query structure, just values. Column names are structure. So ":col" would in the above would never be acceptable, with or without the double quotes. Parameterized values are not quoted, so ':val' will not work either.

2 Likes
4

I might be misunderstanding the original question (and I definitely had the syntax wrong - yikes), but with Named Queries, you can specify a QueryString Type and provide a query like

SELECT beverages.{col} as data from beverages where id=1

where col is passed in as a String.

But if it was

beverages."{col}"

then there would be an issue.

5

Huh. If you are going to allow structure substitutions, you really should allow the double-quoted form – that’s the SQL standard for identifier quoting. FWIW, it seems to defeat the whole purpose of Named Queries.

6

Query Strings fall under the category of "a tool we give you that we highly discourage using" :laughing:

1 Like
7

@Transistor: Could you post a screenshot of the NamedQuery from the Designer so that we can rule out formatting?

8

Thanks, all. I left this overnight and when I returned in the morning it worked! I’m running on a trial licence on a development machine. The query works from the Named Query test window whether the 2-hour trial has expired or not. This has me rather puzzled.

The test query in my original post is the literal SQL query. Screen grab below.

Ignition%20queryDevicesUpdate
Ignition%20queryDevicesUpdate702×496 34.2 KB

Now I’m wondering if I somehow upset the MariaDB / mySQL privileges while debugging this. I’ll do some more research and testing.

1 Like