# MySQL Connector: "Allow User Variables" - what does it do?

**URL:** <https://community.cdata.com/t/mysql-connector-allow-user-variables-what-does-it-do/644>\
**Category:** CData Arc\
**Tags:** question, mysql\
**Created:** [February 28, 2024, 2:28pm UTC](https://community.cdata.com/t/mysql-connector-allow-user-variables-what-does-it-do/644 "2024-02-28T14:28:41Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![russell-jerseypost](https://avatars.discourse-cdn.com/v4/letter/r/439d5e/32.png) [@russell-jerseypost](https://community.cdata.com/u/russell-jerseypost)\
**Post date:** [February 28, 2024, 2:28pm UTC](https://community.cdata.com/t/mysql-connector-allow-user-variables-what-does-it-do/644/1 "2024-02-28T14:28:41Z")

</div>

![d120e279-19da-4923-820f-52be5471aa59.png](https://us1.discourse-cdn.com/cdata/original/2X/1/12a83615e131198436387f960161b7340c1c6d8e.png)

---

<div class="post-metadata">

**Author:** ![Luçjano\_Capo](https://sea3.discourse-cdn.com/cdata/user_avatar/community.cdata.com/lu%C3%A7jano_capo/32/1638_2.png) [@Luçjano\_Capo](https://community.cdata.com/u/Lu%C3%A7jano_Capo)\
**Post date:** [February 28, 2024, 3:51pm UTC](https://community.cdata.com/t/mysql-connector-allow-user-variables-what-does-it-do/644/2 "2024-02-28T15:51:54Z")

</div>

Hi @russell-jerseypost ,

**Allow User Variables** allows you to utilize your user-defined variables in queries.&nbsp;When set to True, user variables (prefixed by an @) can be used in SQL queries. The default behavior is to treat identifiers prefixed with @ as command parameters.

If we take MySQL Workbench, for example, this is my current table:

 ![d09894c5-d8c8-42cb-98a2-56504f21f5f5.png](https://us1.discourse-cdn.com/cdata/original/1X/0b198fe7b088a508241a11165fec0e66f915b132.png)

I can use user-defined variables to select only the first record, like so:

 ![7d3c53bb-9460-49eb-b679-faeaf5074dec.png](https://us1.discourse-cdn.com/cdata/original/2X/d/dd275d6803925dbf8d0051d971deb90fa64823bf.png)

The same logic applies to our MySQL driver. When the&nbsp; **Allow User Variables** &nbsp;is set to False, you will get this error (or a similar one):

 ![febfdb40-23be-4849-bdd5-9b08b9bae610.png](https://us1.discourse-cdn.com/cdata/original/2X/1/161655cb380f494159dc9a6f40cccc6e7186e32d.png)

When the&nbsp; **Allow User Variables** &nbsp;is set to&nbsp; **True** , we mimic&nbsp;the MySQL Workbench behavior:

 ![0bf50f23-2758-4d65-a8a0-c7832d3bcc1a.png](https://us1.discourse-cdn.com/cdata/original/1X/683231633343352184362ec340d67d9c74734411.png)

Kindly refer to this documentation to learn more about User-Defined Variables:&nbsp;[https://dev.mysql.com/doc/refman/8.0/en/user-variables.html](https://dev.mysql.com/doc/refman/8.0/en/user-variables.html)

---

<div class="post-metadata">

**Author:** ![russell-jerseypost](https://avatars.discourse-cdn.com/v4/letter/r/439d5e/32.png) [@russell-jerseypost](https://community.cdata.com/u/russell-jerseypost)\
**Post date:** [March 9, 2024, 12:58pm UTC](https://community.cdata.com/t/mysql-connector-allow-user-variables-what-does-it-do/644/3 "2024-03-09T12:58:26Z")

</div>

Thanks.

Is there a way to set user variables from xpath data or message headers?

Basically I’m looking to build a dynamic SELECT query.

---

<div class="post-metadata">

**Author:** ![russell-jerseypost](https://avatars.discourse-cdn.com/v4/letter/r/439d5e/32.png) [@russell-jerseypost](https://community.cdata.com/u/russell-jerseypost)\
**Post date:** [March 12, 2024, 5:21pm UTC](https://community.cdata.com/t/mysql-connector-allow-user-variables-what-does-it-do/644/4 "2024-03-12T17:21:57Z")

</div>

So I have managed to do this with a combination of a Script connector which creates an XML message with start and end datetimes, and then using a MySQL&nbsp;Lookup Connector which uses XPATH variables in the Custom&nbsp;SQL WHERE clause.

```xml
<arc:set attr="output.filename" value="xmltriggerfile_[guid()].xml" /><arc:set attr="output.filepath" value="[filepath]" /><arc:set attr="output.data"><?xml version="1.0" encoding="UTF-8"?> <Schedule> <startDateTime>[_|now()|dateadd('day',-1, 'yyyy-MM-dd 00:00:00')]</startDateTime> <endDateTime>[_|now()|dateadd('day',-1, 'yyyy-MM-dd 23:59:59')]</endDateTime> <Results></Results></Schedule> </arc:set><arc:push item="output" />

```

 ![a9b85fbc-da61-47a1-9212-94556c08d0a6.png](https://us1.discourse-cdn.com/cdata/original/2X/2/2fbaa20d7d934d2e0009d15c5050b23471e5ec3c.png)