Solved
MySQL Connector: "Allow User Variables" - what does it do?
Best answer by russell-jerseypost
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 Lookup Connector which uses XPATH variables in the Custom SQL WHERE clause.
<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" />
This topic has been closed for replies.
Hi
Allow User Variables allows you to utilize your user-defined variables in queries. 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:
I can use user-defined variables to select only the first record, like so:
The same logic applies to our MySQL driver. When the Allow User Variables is set to False, you will get this error (or a similar one):
When the Allow User Variables is set to True, we mimic the MySQL Workbench behavior:
Kindly refer to this documentation to learn more about User-Defined Variables: https://dev.mysql.com/doc/refman/8.0/en/user-variables.html
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.
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 Lookup Connector which uses XPATH variables in the Custom SQL WHERE clause.
<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" />
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
