Error with executeQuery()

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.

JDBC requirement.

Basically any query that results in a change to the Database is an Update Query.

To expand on this a little bit...

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:

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.

@PGriffith 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:

See this topic for one technique:

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