Set variable in sql query binding

cacordova · November 21, 2013, 9:20pm

Hi, I’m just wondering why on phpmyadmin this code is working fine, but when i copied this code on my table under sql query binding its not working.

[code]SET @location = ‘2/12 Influent Wells - EW-12-05-180-M’;

SELECT @location AS LOCATION,
ROUND(sum(CASE WHEN t2.tagid = ‘2874’ THEN t2.floatvalue ELSE 0 END), 2) AS Meter_Reading,
ROUND(sum(CASE WHEN t2.tagid = ‘2734’ THEN t2.floatvalue ELSE 0 END), 2) AS GPM,
ROUND(sum(CASE WHEN t2.tagid = ‘2735’ THEN t2.floatvalue ELSE 0 END), 2) AS PSI,
ROUND(sum(CASE WHEN t2.tagid = ‘2736’ THEN t2.floatvalue ELSE 0 END), 2) AS DTW

FROM sqlt_data_2_2013_11 t2

INNER JOIN sqlth_te t1
ON t1.id = t2.tagid

WHERE t2.t_stamp = (SELECT MAX(t2.t_stamp)
FROM sqlt_data_2_2013_11 t2
WHERE t1.id = t2.tagid)[/code]

this is the error:

[quote]java.lang.Exception: Error running query:
SQLQuery(query=SET @location = ‘2/12 Influent Wells - EW-12-05-180-M’;

SELECT @location AS LOCATION,
ROUND(sum(CASE WHEN t2.tagid = ‘2874’ THEN t2.floatvalue ELSE 0 END), 2) AS Meter_Reading,
ROUND(sum(CASE WHEN t2.tagid = ‘2734’ THEN t2.floatvalue ELSE 0 END), 2) AS GPM,
ROUND(sum(CASE WHEN t2.tagid = ‘2735’ THEN t2.floatvalue ELSE 0 END), 2) AS PSI,
ROUND(sum(CASE WHEN t2.tagid = ‘2736’ THEN t2.floatvalue ELSE 0 END), 2) AS DTW

FROM sqlt_data_2_2013_11 t2

INNER JOIN sqlth_te t1
ON t1.id = t2.tagid

WHERE t2.t_stamp = (SELECT MAX(t2.t_stamp)
FROM sqlt_data_2_2013_11 t2
WHERE t1.id = t2.tagid)[, database=AES_FO)@5000ms
On: Main Window.Root Container.Table.data
at com.inductiveautomation.factorypmi.application.gateway.QueryManager$QueryExecutor.run(QueryManager.java:316)
at java.lang.Thread.run(Unknown Source)
Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘SELECT @location AS LOCATION,
ROUND(sum(CASE WHEN t2.tagid = ‘2874’ THEN t2.’ at line 3
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.newGatewayException(GatewayInterface.java:313)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:287)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:244)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.runLimitQuery(GatewayInterface.java:712)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.runQuery(GatewayInterface.java:676)
at com.inductiveautomation.factorypmi.application.gateway.SQLQuery.execute(SQLQuery.java:48)
at com.inductiveautomation.factorypmi.application.gateway.QueryManager$Query.execute(QueryManager.java:690)
at com.inductiveautomation.factorypmi.application.gateway.QueryManager$Query.access$500(QueryManager.java:663)
at com.inductiveautomation.factorypmi.application.gateway.QueryManager$QueryExecutor.run(QueryManager.java:314)
… 1 more
Caused by: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘SELECT @location AS LOCATION,
ROUND(sum(CASE WHEN t2.tagid = ‘2874’ THEN t2.’ at line 3
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2934)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1616)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1708)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3249)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3178)
at com.mysql.jdbc.Statement.executeQuery(Statement.java:1203)
at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
at com.inductiveautomation.ignition.gateway.datasource.DelegatingStatement.executeQuery(DelegatingStatement.java:63)
at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper$SRStatement.executeQuery(SRConnectionWrapper.java:655)
at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.RunQuery.run(RunQuery.java:98)
at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.AbstractDBAction.invoke(AbstractDBAction.java:76)
at com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:373)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at com.inductiveautomation.ignition.gateway.bootstrap.MapServlet.service(MapServlet.java:85)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:861)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:606)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
at java.lang.Thread.run(null)

Ignition v7.6.2 (b2368)
Java: Sun Microsystems Inc. 1.6.0_45
[/quote]

Am i missing something?

adamaustin · November 21, 2013, 9:37pm

I have noticed that Ignition gets a little “finicky” if your SQL code doesn’t start with the SELECT statement.

cacordova · November 21, 2013, 10:34pm

Do you know how I can fix this?

anna · November 21, 2013, 10:44pm

cacordova, You cannot use stored procedures in a SQL query binding. Instead of declaring and using a variable, just store your value in a custom property and then reference that property in the query - something like this:

SELECT {Your Property Name} AS LOCATION ....

adamaustin · November 21, 2013, 10:59pm

…and if you are using scripting use a prepared statement:

sSQL = "SELECT ? as LOCATION...[snip]"
result = system.db.runPrepQuery(sSQL, ['2/12 Influent Wells - EW-12-05-180-M'])

nmudge · November 21, 2013, 11:00pm

He wasn’t using a stored procedure. Just trying to execute multiple SQL commands.

Stored procedures can be called from within SQL query bindings.

For example:

CALL myStoredProcedure()

cacordova can wrap up his multiple commands in a stored procedure and then call the procedure in SQL query bindings.

cacordova · November 21, 2013, 11:18pm

[quote=“nmudge”]He wasn’t using a stored procedure. Just trying to execute multiple SQL commands.

Stored procedures can be called from within SQL query bindings.

For example:

CALL myStoredProcedure()

cacordova can wrap up his multiple commands in a stored procedure and then call the procedure in SQL query bindings.[/quote]

I’m a she. lol

But anyway, thank you, i think i will just do it like this. Thanks!