How to dynamically execute SQL in SAP IQ, to consume in Crystal Reports

Question

Goal:

I have a stored procedure in SAP IQ that uses execute() to execute a dynamic SQL statement into a temp table, and then it selects from the table as the output.

Problem:

The proc returns the appropriate dataset just fine in my SQL client, but when I try to execute it in Crystal Reports, it does not return any columns, and instead returns a column named "expression". I believe this is Crystal's way of indicating that it couldn't interpret the result set.

Question:

What is the correct way to dynamically execute a query in IQ? Particularly in terms of executing the proc via Crystal Reports. I'm used to working in SAP ASE, and this is a procedure that I'm attempting to move from ASE to IQ.

Reproducible example:

/* Example proc that create a temp table with a dynamic value */

create or replace proc test_exec (
    @num_value int
)
as
begin
    -- Dynamically create the temp table with the requested column value
    execute('select ' || @num_value || ' as column_1 into #temp')

    -- Return the result
    select column_1 from #temp
end

Output from the SQL client (correct):

exec test_exec 123

column_1
123

Output in Crystal Reports (does not return column_1):

Answer 1

This is likely due to a difference in the driver used to connect. The query tool is using a built in JDBC driver, while Crystal is using a different driver for its ODBC connection.

I cannot tinker the ODBC settings on this work computer, so I can't experiment with different settings to give a definitive answer of the cause.