Query with global temp tables errors out when querying against a DB2 database

[DimitarVanguelov]

Hi, I get the following error and stacktrace when I try running a DB2 query that uses global temp tables:
[ODBC] 24000: [IBM][CLI Driver] CLI0115E Invalid cursor state. SQLSTATE=24000

ERROR: ODBC.ODBCError("API.SQLFetchScroll(q.stmt, API.SQL_FETCH_NEXT, 0) failed; return code: -1 => SQL_ERROR")

Stacktrace: [1] macro expansion at C:\Users\[myuserid]\.julia\packages\ODBC\YEzHX\src\ODBC.jl:57 [inlined] [2] ODBC.Query(::ODBC.DSN, ::String) at C:\Users\[myuserid]\.julia\packages\ODBC\YEzHX\src\Query.jl:121 [3] #query#15(::Bool, ::Bool, ::Dict{Int64,Function}, ::Function, ::ODBC.DSN, ::String, ::Type{DataFrame}) at C:\Users\[myuserid]\.julia\packages\ODBC\YEzHX\src\Query.jl:390 [4] query at C:\Users\[myuserid]\.julia\packages\ODBC\YEzHX\src\Query.jl:385 [inlined] [5] query(::ODBC.DSN, ::String) at C:\Users\[myuserid]\.julia\packages\ODBC\YEzHX\src\Query.jl:376 [6] top-level scope at none:0

This is not crucial to my project, just thought I'd point it out.

Edit: using IBM DB2 ODBC DRIVER if that's any help.

[DimitarVanguelov]

Hi @quinnj , I'm closing this issue as I realized it's a non-issue -- my apologies.

In case anyone has similar struggles, I'm posting my solution here, which was inspired by this similar issue for an R package.

Basically, you can't have multiple SQL statements separated by a ; in the same ODBC.query statement. Each piece of the global temp table creation has to be executed in its own ODBC.execute! statement with the final query run with an ODBC.query statement. So in my case that looked something like this:

pre1 = """
declare global temporary table session.temp_table
(
      some_fields
)
in some_group
with replace not logged
on commit preserve rows partitioning key (some_field) using hashing;
"""

pre2 = """
insert into session.temp_table
select fields 
from some.table
"""

output_query = """
select fields 
from session.temp_table
"""

ODBC.execute!(conn, pre1)
ODBC.execute!(conn, pre2)
output = ODBC.query(conn, output_query)