built a named query to create a test table in database: create table testUsers ( id int auto_increment primary key, firstName varchar(255) not null, lastName varchar(255) not null, email varchar(255) not null unique, isAdmin boolean, canEdit boolean, canRead boolean, canConfig boolean, isNotifified boolean ); running the query from the Testing tab returned this error: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: java.sql.SQLException: Can not issue data manipulation statements with executeQuery(). now before you get excited, this will eventually become broken out into a bitwise permission table and a general user table. that aside, my query is fine (i ran it on the db in Heidi to be sure), so why does it throw an error? the irony is that i'm following the IU tutorial on Creating Tables... i've just modified my query to reflect something useful to me and closed my query properly.

Change your named query to be an "update" type.

okaayyy... RTFM = nothing about create falling into the Update Query category found. why differentiate 'query types'? the query itself has everything you need and returns what you ask for: entire dataset or single datum. IU does indicate the correct type; i must have glazed over that, so i apologize. i am very familiar with SQL so it never occurred to me that queries would ever be differentiated like this.

[image] ian.sebryk: i am very familiar with SQL so it never occurred to me that queries would ever be differentiated like this. JDBC requirement.

[image] ian.sebryk: nothing about create falling into the Update Query category found Basically any query that results in a change to the Database is an Update Query.

[image] pturmel: JDBC requirement. To expand on this a little bit... [image] ian.sebryk: the query itself has everything you need and returns what you ask for: entire dataset or single datum. Java, the language, has no idea what a SQL query is, nor what type it is. So you're essentially giving a random string to a random method on a random object. Java has no ability to do multiple return types, nor sealed classes or generics (20 years ago when the JDBC spec was written, at least). So the only way to disambiguate and get a programmatically useful return type from the method call was to add two different method types: https://docs.oracle.com/en/java/javase/11/docs/api/java.sql/java/sql/Statement.html#executeQuery(java.lang.String) https://docs.oracle.com/en/java/javase/11/docs/api/java.sql/java/sql/Statement.html#executeUpdate(java.lang.String) It would be nice if this could be caught by Ignition and displayed as a more friendly error... but it's just a standard Java SQLException, so the only way for Ignition to distinguish it from "your SQL was wrong" or any of a huge variety of other possible SQL errors is by checking the string error message of the code...which is also subject to localization by the JVM. Thus, the current state of affairs, where you get a technically correct, but nearly useless error message unless you know all this extra context.

@paul-griffith THAT was a very useful reply. i am NOT familiar with Java and to that end the JDBC. makes sense now. and thanks twice for taking the time to educate. i like to know WHY. now i get to ask a question how to import 10000 records QUICKLY... look for it soon. :smiley:

[image] ian.sebryk: how to import 10000 records QUICKLY See this topic for one technique: [image] Using Gateway scripts to move data from one database to another Ignition This, handed to system.db.runPrepUpdate(), will insert three rows at once: INSERT INTO some_table (some_column1, some_column2, some_column3) VALUES (?, ?, ?), (?, ?, ?), (?, ?, ?) You would provide the nine arguments in a flat list. You would construct the SQL dynamically with the right number of inserted row placeholders and the right number of JDBC substitution arguments. Just be careful not to exceed the limit on ? placeholders. Some JDBC drivers choke at around 2000.

ooh-la! sweet! :100: :partying_face: thanks @pturmel! reading that now

Error with executeQuery()

Ignition

pturmel July 5, 2023, 6:52pm 8

See this topic for one technique: