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.
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.
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.
thanks