# Named Query - MySQL SET Variable Not Working

**URL:** <https://forum.inductiveautomation.com/t/named-query-mysql-set-variable-not-working/21895>\
**Category:** Ignition\
**Created:** [January 15, 2019, 7:45pm UTC](https://forum.inductiveautomation.com/t/named-query-mysql-set-variable-not-working/21895 "2019-01-15T19:45:38Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![hodgins.e](https://avatars.discourse-cdn.com/v4/letter/h/977dab/32.png) [@hodgins.e](https://forum.inductiveautomation.com/u/hodgins.e)\
**Post date:** [January 15, 2019, 7:45pm UTC](https://forum.inductiveautomation.com/t/named-query-mysql-set-variable-not-working/21895/1 "2019-01-15T19:45:38Z")

</div>

Is it not possible **SET** a variable in a SQL query? This does not seem to work but works fine on my server directly.

```
SELECT @foo :=1;
SELECT @foo

```

or

```
SET @foo = 1;
SELECT @foo

```

![image](https://us1.discourse-cdn.com/inductiveautomation/original/2X/9/90342bef4763d41f133c1390fb1132d81cde8a04.png)

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [January 15, 2019, 8:19pm UTC](https://forum.inductiveautomation.com/t/named-query-mysql-set-variable-not-working/21895/2 "2019-01-15T20:19:13Z")

</div>

MySQL JDBC drivers can only perform one “transaction” at once. I once tried to run an update query then a delete from query on the same table in one named query, but it would not work. I had to break them up into two separate named queries. I tried this and it works `select @foo := 1, @foo2 :=3` It looks like that if you want to run something like what you had, you will probably will have to set it up as a stored procedure in the DB then call it from ignition.

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [January 15, 2019, 8:23pm UTC](https://forum.inductiveautomation.com/t/named-query-mysql-set-variable-not-working/21895/3 "2019-01-15T20:23:30Z")

</div>

> [@Using a MySQL variable in a data query throwing me an error](https://forum.inductiveautomation.com/t/using-a-mysql-variable-in-a-data-query-throwing-me-an-error/21826/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.

A similar issue was posted not too long ago with the following better explanation.

---

<div class="post-metadata">

**Author:** ![hodgins.e](https://avatars.discourse-cdn.com/v4/letter/h/977dab/32.png) [@hodgins.e](https://forum.inductiveautomation.com/u/hodgins.e)\
**Post date:** [January 15, 2019, 8:26pm UTC](https://forum.inductiveautomation.com/t/named-query-mysql-set-variable-not-working/21895/4 "2019-01-15T20:26:12Z")

</div>

Thanks a lot, figured it was a JDBC driver issue.

---

<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:39am UTC](https://forum.inductiveautomation.com/t/named-query-mysql-set-variable-not-working/21895/5 "2019-01-17T10:39:42Z")

</div>

> [@Using a MySQL variable in a data query throwing me an error](http://forum.inductiveautomation.com/t/using-a-mysql-variable-in-a-data-query-throwing-me-an-error/21826/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](https://dev.mysql.com/doc/connectors/en/connector-j-reference-configuration-properties.html)