# Set variable in sql query binding **URL:** \ **Category:** Ignition\ **Created:** [November 21, 2013, 9:20pm UTC](https://forum.inductiveautomation.com/t/set-variable-in-sql-query-binding/7609 "2013-11-21T21:20:22Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![cacordova](https://avatars.discourse-cdn.com/v4/letter/c/bbce88/32.png) [@cacordova](https://forum.inductiveautomation.com/u/cacordova)\ **Post date:** [November 21, 2013, 9:20pm UTC](https://forum.inductiveautomation.com/t/set-variable-in-sql-query-binding/7609/1 "2013-11-21T21:20:22Z")
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? ---
**Author:** ![adamaustin](https://avatars.discourse-cdn.com/v4/letter/a/5f8ce5/32.png) [@adamaustin](https://forum.inductiveautomation.com/u/adamaustin)\ **Post date:** [November 21, 2013, 9:37pm UTC](https://forum.inductiveautomation.com/t/set-variable-in-sql-query-binding/7609/2 "2013-11-21T21:37:27Z")
I have noticed that Ignition gets a little “finicky” if your SQL code doesn’t start with the SELECT statement. ---
**Author:** ![cacordova](https://avatars.discourse-cdn.com/v4/letter/c/bbce88/32.png) [@cacordova](https://forum.inductiveautomation.com/u/cacordova)\ **Post date:** [November 21, 2013, 10:34pm UTC](https://forum.inductiveautomation.com/t/set-variable-in-sql-query-binding/7609/3 "2013-11-21T22:34:42Z")
Do you know how I can fix this? ---
**Author:** ![anna](https://avatars.discourse-cdn.com/v4/letter/a/779978/32.png) [@anna](https://forum.inductiveautomation.com/u/anna)\ **Post date:** [November 21, 2013, 10:44pm UTC](https://forum.inductiveautomation.com/t/set-variable-in-sql-query-binding/7609/4 "2013-11-21T22:44:40Z")
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: ```auto SELECT {Your Property Name} AS LOCATION .... ``` ---
**Author:** ![adamaustin](https://avatars.discourse-cdn.com/v4/letter/a/5f8ce5/32.png) [@adamaustin](https://forum.inductiveautomation.com/u/adamaustin)\ **Post date:** [November 21, 2013, 10:59pm UTC](https://forum.inductiveautomation.com/t/set-variable-in-sql-query-binding/7609/5 "2013-11-21T22:59:15Z")
…and if you are using scripting use a prepared statement: ```auto sSQL = "SELECT ? as LOCATION...[snip]" result = system.db.runPrepQuery(sSQL, ['2/12 Influent Wells - EW-12-05-180-M']) ``` ---
**Author:** ![nmudge](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nmudge/32/3394_2.png) [@nmudge](https://forum.inductiveautomation.com/u/nmudge)\ **Post date:** [November 21, 2013, 11:00pm UTC](https://forum.inductiveautomation.com/t/set-variable-in-sql-query-binding/7609/6 "2013-11-21T23:00:53Z")
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: ```auto CALL myStoredProcedure() ``` cacordova can wrap up his multiple commands in a stored procedure and then call the procedure in SQL query bindings. ---
**Author:** ![cacordova](https://avatars.discourse-cdn.com/v4/letter/c/bbce88/32.png) [@cacordova](https://forum.inductiveautomation.com/u/cacordova)\ **Post date:** [November 21, 2013, 11:18pm UTC](https://forum.inductiveautomation.com/t/set-variable-in-sql-query-binding/7609/7 "2013-11-21T23:18:41Z")
[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: ```auto 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!