In-Database query using Exasol: generic and mysterious error

[johnemery]

Hello,

I am encountering a strange database error that I hope somebody can provide assistance on.

I have many workflows that connect to an Exasol database. I recently got a new client laptop and downloaded Alteryx Designer version 2023.2.1.7 and downloaded the Exasol ODBC version 7.1.20.

Refer to the attached image. I have two queries that use the same ODBC connection. The first query works perfectly in both the In-Database and standard Input tools. The second query, however, fails on the Input Data and Data Stream Out tool. Interestingly, the query does work, and results can be seen via the Browse In-DB tool.

The error message reads: Error SQLExecute: [EXASOL][EXASolution driver]Error executing statement.

Further, when I run these queries in a database tool (DBeaver and dbVisualizer) and KNIME Analytics Platform, I have no errors.

If you need any more information from me, please let me know.

Thanks,

John

[DataNath · Accepted solution]

Hey @johnemery, do these 2 queries contain any checks/conditions with special XML characters like < and >? I had this recently where I was receiving XML parse errors when querying Snowflake, which would only happen with the In-DB tools and would work fine if I placed the same query in an Input Data tool with the same ODBC connection. If so, escaping the characters remedied the issue i.e. using 'A &lt;= B' instead of 'A <= B'. Full list of escape chars here: https://stackoverflow.com/questions/1091945/what-characters-do-i-need-to-escape-in-xml-documents - there's also a function in Alteryx, EscapeXMLMetacharacters(), if you're planning/need to build the query using workflow elements.

[johnemery]

Unreal, that was it.

The offending query had a where condition with a value that contained an ampersand (&). Upon removing that condition, it worked fine. Escaping the & with &amp; also allowed the queries to execute--and pass into Alteryx--as desired.

Thank you for providing a solution to this problem that was really stumping me.