Named query: SELECT works but UPDATE doesn't

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?

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.

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.

1 Like

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.

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.

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

1 Like

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

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.

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