Input Data Tool with ODBC driver asks for result schema before executing a query

[Adalbert]

Hi!

I'm using Input Data Tool with SingleStore and want to execute an anonymous code block.

I inserted it into the "Table or Query" field.

From ODBC traces, I'm seeing that Alteryx runs SQLNumResultCols after calling SQLPrepare but before calling SQLExecute.

Anonymous code blocks can contain Control flow statements and thus the result schema can't be determined before the query is actually executed. As a result, I'm getting "No columns returned" error.

The same problem happens when I'm trying to call stored procedure in "Table or Query" field.

[mceleavey]

Hi @Adalbert ,

Why are you doing any of those things?

What are you trying to achieve?

M.

[Adalbert]

Hi,

I want to do some computations before selecting data.

Adalbert

[danilang]

Hi @Adalbert 

As you've found through your ODBC traces, the target of the query has to return a fixed schema.  Stored procs have to return a single cursor as well with no record count info.   If you can convert your code to a stored proc, use an OleDB connection to call it.

Dan

[Adalbert]

Hi Dan, 

SingleStore doesn't have an OleDB driver.

Are there some workarounds with ODBC?

Adalbert

[danilang]

Hi @Adalbert 

I've never had any luck querying a stored proc using an ODBC driver.  You might be able to hack your way around it by having the stored proc in the presql that overwrites a table and then have a simple select from that table in the main call.  It can't be a temp table, since I don't think the presql and main sql run in the same session.

Dan

[Adalbert]

I was able to get data from the temporary table that is created in the "Pre SQL Statements". Looks like presql and main sql run in the same session. Thanks for your response!

[Adalbert]

The sad part is that I can't run anonymous code block in the presql as it splits the statement by semicolon.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Anonymous-code-blocks-in-quot-Pre-SQL-...

Creating a separate stored procedure for each workflow is not the best approach. I would like to control everything from Alteryx and don't do any changes directly in the database