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

**URL:** <https://forum.inductiveautomation.com/t/using-a-mysql-variable-in-a-data-query-throwing-me-an-error/21826>\
**Category:** Ignition\
**Tags:** database\
**Created:** [January 11, 2019, 4:39pm UTC](https://forum.inductiveautomation.com/t/using-a-mysql-variable-in-a-data-query-throwing-me-an-error/21826 "2019-01-11T16:39:22Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [January 11, 2019, 4:39pm UTC](https://forum.inductiveautomation.com/t/using-a-mysql-variable-in-a-data-query-throwing-me-an-error/21826/1 "2019-01-11T16:39:22Z")

</div>

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](http://www.mysqltutorial.org/mysql-row_number/).

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.

---

<div class="post-metadata">

**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:** [January 11, 2019, 5:45pm UTC](https://forum.inductiveautomation.com/t/using-a-mysql-variable-in-a-data-query-throwing-me-an-error/21826/2 "2019-01-11T17:45:15Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [January 11, 2019, 5:49pm UTC](https://forum.inductiveautomation.com/t/using-a-mysql-variable-in-a-data-query-throwing-me-an-error/21826/3 "2019-01-11T17:49:17Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![mcgheeiv](https://avatars.discourse-cdn.com/v4/letter/m/71c47a/32.png) [@mcgheeiv](https://forum.inductiveautomation.com/u/mcgheeiv)\
**Post date:** [January 17, 2019, 10:37am UTC](https://forum.inductiveautomation.com/t/using-a-mysql-variable-in-a-data-query-throwing-me-an-error/21826/4 "2019-01-17T10:37:41Z")

</div>

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](https://dev.mysql.com/doc/connectors/en/connector-j-reference-configuration-properties.html)