MySQL Statement is not working

Ignition
1

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.

2

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.

3

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

4

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

EDIT2: Although you may be able to do this in a single query:

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
5

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.

1 Like
6

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"

7

Every derived table must have its own alias

Give it what it asks for!

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
3 Likes