# MySQL Statement is not working **URL:** \ **Category:** Ignition\ **Tags:** ignition81\ **Created:** [April 10, 2023, 10:50am UTC](https://forum.inductiveautomation.com/t/mysql-statement-is-not-working/72252 "2023-04-10T10:50:18Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![ajit.kv2004](https://avatars.discourse-cdn.com/v4/letter/a/958977/32.png) [@ajit.kv2004](https://forum.inductiveautomation.com/u/ajit.kv2004)\ **Post date:** [April 10, 2023, 10:50am UTC](https://forum.inductiveautomation.com/t/mysql-statement-is-not-working/72252/1 "2023-04-10T10:50:18Z")
I am trying to convert the MSSQL Query to MySQL Query to be used as named query. MSSQL Query Declare @Number int Declare @count\_new int set @Number = (select top 1 id as Number FROM projectname\_cred order by id desc) set @count\_new = @Number + 1 SELECT @count\_new as id, 'All unresolved Cred' as username union all select id, username FROM projectname\_cred MySQL Query: select @Number :=0; select @count\_new :=0; set @Number = (select id as Number FROM projectname\_cred order by id desc limit 1); set @count\_new = @Number + 1; SELECT @count\_new as id, 'All unresolved Cred' as username union all select id, username FROM projectname\_cred The above query is working with MySQL Workbench but its not working with ignition 8.1.21 under named query section. while executing it in named query testing window its showing error message as "java.sql.SQLSyntaxErrorException: 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 @count\_new :=0; set @Number = (select id as Number FROM projectname\_cred ' at line 2" Can someone help here to fix the issue. ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [April 10, 2023, 11:00am UTC](https://forum.inductiveautomation.com/t/mysql-statement-is-not-working/72252/2 "2023-04-10T11:00:44Z")
Tip: select your code blocks and press the \ code formatting button to preserve indentation and apply syntax highlighting. You can use the pencil icon button to edit your post. ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [April 10, 2023, 11:07am UTC](https://forum.inductiveautomation.com/t/mysql-statement-is-not-working/72252/3 "2023-04-10T11:07:24Z")
> [@ajit.kv2004](#): > > set @Number = ...`` I don't think that you can use SLQ variables in named queries. You need to use query parameters. There are two types, _Value_ and _Querystring_. Make sure that you understand the risks of using querystrings. [https://docs.inductiveautomation.com/display/DOC81/Named+Query+Parameters](https://docs.inductiveautomation.com/display/DOC81/Named+Query+Parameters) ---
**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\ **Post date:** [April 10, 2023, 11:32am UTC](https://forum.inductiveautomation.com/t/mysql-statement-is-not-working/72252/4 "2023-04-10T11:32:19Z")
IIRC, the MySQL JDBC driver does not allow multiple queries by default. EDIT: Try adding _allowMultiQueries=true_ to your connection properties. [https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-connp-props-security.html](https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-connp-props-security.html) EDIT2: Although you may be able to do this in a single query: ```sql SELECT id + 1 as id, 'All unresolved Cred' as username FROM (SELECT id FROM projectname_cred ORDER BY id DESC LIMIT 1) UNION ALL SELECT id, username FROM projectname_cred ``` ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [April 10, 2023, 11:56am UTC](https://forum.inductiveautomation.com/t/mysql-statement-is-not-working/72252/5 "2023-04-10T11:56:19Z")
In general, JDBC doesn't support SQL _scripts_, just **single** SQL _statements_. If you want SQL that is widely compatible, don't use DECLARE, or BEGIN/END blocks, et cetera. Some JDBC drivers support scripts, and others do not. Don't use them, and don't assume something that works in a workbench will work in Ignition. ---
**Author:** ![ajit.kv2004](https://avatars.discourse-cdn.com/v4/letter/a/958977/32.png) [@ajit.kv2004](https://forum.inductiveautomation.com/u/ajit.kv2004)\ **Post date:** [April 10, 2023, 12:37pm UTC](https://forum.inductiveautomation.com/t/mysql-statement-is-not-working/72252/6 "2023-04-10T12:37:09Z")
Tried the second option using it with single query but getting following error message. "GatewayException: java.sql.SQLSyntaxErrorException: Every derived table must have its own alias caused by Exception: java.sql.SQLSyntaxErrorException: Every derived table must have its own alias" ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [April 10, 2023, 12:42pm UTC](https://forum.inductiveautomation.com/t/mysql-statement-is-not-working/72252/7 "2023-04-10T12:42:05Z")
> Every derived table must have its own alias Give it what it asks for! ```auto SELECT id + 1 as id, 'All unresolved Cred' as username FROM (SELECT id FROM projectname_cred ORDER BY id DESC LIMIT 1) myAlias UNION ALL SELECT id, username FROM projectname_cred ```