Using a MySQL variable in a data query throwing me an error

Ignition
1

I am doing a data query for a tree component. With this query, I am making a column for the treeItem number, which I accomplished just by ordering correctly and it seems to work fine, my column treeNum corresponds directly with the newValues on property change (I need this because I have other columns holding ID’s so I can look up directly when something is selected instead of doing another query to get the ID).

Here’s the issue, I am using MySQL and to replicate the ROW_NUMBER() function of other databases, I had to use the method here.

The query runs perfectly fine in MySQL workbench but when I copy and paste it to the designer, it tells me I have an error near may SELECT Statement line 3, the line where I do (@row_number:=@row_number+1). What is going on and how can I fix this?

The full query for reference:

SET @row_number = -1;

SELECT 
(@row_number:=@row_number + 1) as itemNum, t.*
FROM
((SELECT 
    '' AS `path`,
        `listcustomers`.`name` AS `text`,
        `listcustomers`.`active` AS `active`,
        IF(`listcustomers`.`active`, 'tree/CustomerActive.png', 'tree/CustomerInactive.png') AS `icon`,
        IF(`listcustomers`.`active`, 'tree/CustomerActive.png', 'tree/CustomerInactive.png') AS `selectedIcon`,
        'color(255,255,255,255)' AS `background`,
        '' AS `border`,
        '' AS `tootltip`,
        '' AS `selectedText`,
        '' AS `selectedTooltip`,
        '' AS `selectedBorder`,
        'color(0,0,0,255)' AS `foreground`,
        'color(250,214,138,255)' AS `selectedBackground`,
        'color(0,0,0,255)' AS `selectedForeground`,
        `listcustomers`.`idx` AS `customerId`,
        NULL AS `locationId`,
        NULL AS `areaId`,
        NULL AS `assetId`,
        NULL AS `projectId`
FROM
    `listcustomers`) UNION (SELECT 
    `listcustomers`.`name` AS `path`,
        `listlocations`.`name` AS `text`,
        `listcustomers`.`active` AS `active`,
        IF(`listlocations`.`active`, 'tree/LocationActive.png', 'tree/LocationInactive.png') AS `icon`,
        IF(`listlocations`.`active`, 'tree/LocationActive.png', 'tree/LocationInactive.png') AS `selectedIcon`,
        'color(255,255,255,255)' AS `background`,
        '' AS `border`,
        '' AS `tootltip`,
        '' AS `selectedText`,
        '' AS `selectedTooltip`,
        '' AS `selectedBorder`,
        'color(0,0,0,255)' AS `foreground`,
        'color(250,214,138,255)' AS `selectedBackground`,
        'color(0,0,0,255)' AS `selectedForeground`,
        `listcustomers`.`idx` AS `customerId`,
        `listlocations`.`idx` AS `locationId`,
        NULL AS `areaId`,
        NULL AS `assetId`,
        NULL AS `projectId`
FROM
    `listlocations`
JOIN `listcustomers` ON `listcustomers`.`idx` = `listlocations`.`parentCustomerId`) UNION (SELECT 
    CONCAT_WS('/', `listcustomers`.`name`, `listlocations`.`name`),
        `listareas`.`name` AS `text`,
        `listareas`.`active` AS `active`,
        IF(`listareas`.`active`, 'tree/AreaActive.png', 'tree/AreaInactive.png') AS `icon`,
        IF(`listareas`.`active`, 'tree/AreaActive.png', 'tree/AreaInactive.png') AS `selectedIcon`,
        'color(255,255,255,255)' AS `background`,
        '' AS `border`,
        '' AS `tootltip`,
        '' AS `selectedText`,
        '' AS `selectedTooltip`,
        '' AS `selectedBorder`,
        'color(0,0,0,255)' AS `foreground`,
        'color(250,214,138,255)' AS `selectedBackground`,
        'color(0,0,0,255)' AS `selectedForeground`,
        `listcustomers`.`idx` AS `customerId`,
        `listlocations`.`idx` AS `locationId`,
        `listareas`.`idx` AS `areaId`,
        NULL AS `assetId`,
        NULL AS `projectId`
FROM
    `listareas`
JOIN `listlocations` ON `listlocations`.`idx` = `listareas`.`parentLocationId`
JOIN `listcustomers` ON `listcustomers`.`idx` = `listlocations`.`parentCustomerId`) UNION (SELECT 
    CONCAT_WS('/', `listcustomers`.`name`, `listlocations`.`name`, `listareas`.`name`) AS `path`,
        `listassets`.`name` AS `text`,
        `listassets`.`active` AS `active`,
        IF(`listassets`.`active`, 'tree/assetActive.png', 'tree/assetInactive.png') AS `icon`,
        IF(`listassets`.`active`, 'tree/assetActive.png', 'tree/assetInactive.png') AS `selectedIcon`,
        'color(255,255,255,255)' AS `background`,
        '' AS `border`,
        '' AS `tootltip`,
        '' AS `selectedText`,
        '' AS `selectedTooltip`,
        '' AS `selectedBorder`,
        'color(0,0,0,255)' AS `foreground`,
        'color(250,214,138,255)' AS `selectedBackground`,
        'color(0,0,0,255)' AS `selectedForeground`,
        `listcustomers`.`idx` AS `customerId`,
        `listlocations`.`idx` AS `locationId`,
        `listareas`.`idx` AS `areaId`,
        `listassets`.`idx` AS `assetId`,
        NULL AS `projectId`
FROM
    `listassets`
JOIN `listareas` ON `listareas`.`idx` = `listassets`.`parentAreaId`
JOIN `listlocations` ON `listlocations`.`idx` = `listareas`.`parentLocationId`
JOIN `listcustomers` ON `listcustomers`.`idx` = `listlocations`.`parentCustomerId`) UNION (SELECT 
    CONCAT_WS('/', `listcustomers`.`name`, `listlocations`.`name`, `listareas`.`name`, `listassets`.`name`) AS `path`,
        `listprojects`.`name` AS `text`,
        `listprojects`.`active` AS `active`,
        NULL AS `icon`,
        NULL AS `selectedIcon`,
        'color(255,255,255,255)' AS `background`,
        '' AS `border`,
        '' AS `tootltip`,
        '' AS `selectedText`,
        '' AS `selectedTooltip`,
        '' AS `selectedBorder`,
        'color(0,0,0,255)' AS `foreground`,
        'color(250,214,138,255)' AS `selectedBackground`,
        'color(0,0,0,255)' AS `selectedForeground`,
        `listcustomers`.`idx` AS `customerId`,
        `listlocations`.`idx` AS `locationId`,
        `listareas`.`idx` AS `areaId`,
        `listassets`.`idx` AS `assetId`,
        `listprojects`.`idx` AS `projectId`
FROM
    `listprojects`
JOIN `listassets` ON `listassets`.`idx` = `listprojects`.`parentAssetId`
JOIN `listareas` ON `listareas`.`idx` = `listassets`.`parentAreaId`
JOIN `listlocations` ON `listlocations`.`idx` = `listareas`.`parentLocationId`
JOIN `listcustomers` ON `listcustomers`.`idx` = `listlocations`.`parentCustomerId`)) AS t ORDER BY t.`path`, t.`text`;

SOLUTION: So Ignition does NOT like MySQL variables. I just threw this into a stored procedure and called from there. Just in case anyone runs into the same issue as me in the future.

2

Ignition (and JDBC) doesn’t like any database’s variables. Because they are all vendor specific scripts, not standard SQL. JDBC expects you to execute one and only one statement per call.

3 Likes
3

Other parts of the code in this project just made more sense. Thanks for the insight.

4

If you add allowMultiQueries = true to the Extra Configuration Properties in the advanced database connection properties, you should be able to use MySQL variables in the query. The query, however, can only return one dataset.

https://dev.mysql.com/doc/connectors/en/connector-j-reference-configuration-properties.html

1 Like