Impala ODBC driver and python query with params

I tried but the problem remains the same.

Thanks for the tip

The simple query, without subqueries, with params works.

For example:

SELECT * FROM target_test.test2 where id = ?;

 About the support of params in the ODBC driver for impala I found this: https://community.cloudera.com/t5/Support-Questions/Unable-to-pass-parameter-to-Impala-from-SSRS/m-p/65620/highlight/false#M4261

Quickly the ODBC driver for impala supports the standard SQLBindParameter() API to subsitute ? in the query text with parameters.

but this seems not to work for complex queries.

I tried but not works. The complet test below:

import pyodbc

# connect
crsr = pyodbc.connect('DSN=impala1;', autocommit=True).cursor()

# unnamed parameters
params = ('C', 1000000000, '2023-01-01')

# short query
query = """SELECT *
        FROM test1.test1 d 
        INNER JOIN 
            (SELECT * 
            FROM test1.test2 c1
            WHERE dat_ini_val = (SELECT max(c2.dat_ini_val) 
                                FROM test1.test2 c2
                                WHERE c2.sta = ? 
                                AND c2.cod_cli=c1.cod_cli 
                                AND c1.cod_fsc=c2.cod_fsc)) c 
        ON d.cod_cli = c.cod_cli
        WHERE cast(D.cod_fsc as string) <> ''  
        AND d.prg_rig < ? AND d.dat_cre_flu >= ? AND d.TIP_FLU = 'F24';"""

try:
    res = crsr.execute(query, params).fetchall() #<-- this works
    print('Query simple DONE!!!') 
except pyodbc.Error as e:
    e.args = (e.args[1],)
    print(e)

# long query
query = """SELECT *
FROM test1.test1 d 
LEFT OUTER JOIN 
    (SELECT cli, fsc, nom_rag_soc, dat_ini_val, dat_ccz_inl, Dat_csz_efv, sta, prg_var, COD_CAU_CSZ_DL
     FROM 
        (SELECT c2.cod_cli AS cli, 
                TRIM(CAST(c2.cod_fsc AS STRING)) AS fsc,
                TRIM(CAST(c2.nom_rag_soc AS STRING)) AS nom_rag_soc, 
                c2.dat_ini_val, 
                c2.dat_ccz_inl, 
                c2.Dat_csz_efv,
                c2.sta, 
                c2.prg_var, 
                c2.COD_CAU_CSZ_DL
                ,(CASE WHEN c2.Dat_csz_efv = '1900-01-01 00:00:00' THEN '2999-01-01' ELSE c2.Dat_csz_efv END) AS dta_cessata
                ,ROW_NUMBER () OVER (PARTITION BY cod_fsc ORDER BY (case when c2.Dat_csz_efv='1900-01-01 00:00:00' then '2999-01-01' ELSE c2.Dat_csz_efv END) desc) rn
         FROM test1.test2 c2 
         WHERE c2.sta = ?
        ) A
    ) k 
ON TRIM(CAST(d.COD_fsc AS STRING)) = k.fsc
WHERE d.prg_rig < ? 
  AND d.dat_cre_flu >= ? 
  AND d.TIP_FLU = 'F24';"""


try:
    res = crsr.execute(query, params).fetchall() #<-- this does not work
    print('Query complex DONE!!!')
except pyodbc.Error as e:
    e.args = (e.args[1],)
    print(e)


query = """
WITH prova as (SELECT c2.cod_cli AS cli, 
                TRIM(CAST(c2.cod_fsc AS STRING)) AS fsc,
                TRIM(CAST(c2.nom_rag_soc AS STRING)) AS nom_rag_soc, 
                c2.dat_ini_val, 
                c2.dat_ccz_inl, 
                c2.Dat_csz_efv,
                c2.sta, 
                c2.prg_var, 
                c2.COD_CAU_CSZ_DL
                ,(CASE WHEN c2.Dat_csz_efv = '1900-01-01 00:00:00' THEN '2999-01-01' ELSE c2.Dat_csz_efv END) AS dta_cessata
                ,ROW_NUMBER () OVER (PARTITION BY cod_fsc ORDER BY (case when c2.Dat_csz_efv='1900-01-01 00:00:00' then '2999-01-01' ELSE c2.Dat_csz_efv END) desc) rn
         FROM test1.test2 c2 
         WHERE c2.sta = 'C'
)

SELECT *
FROM test1.test1 d 
LEFT OUTER JOIN 
    (SELECT cli, fsc, nom_rag_soc, dat_ini_val, dat_ccz_inl, Dat_csz_efv, sta, prg_var, COD_CAU_CSZ_DL
     FROM prova
    ) k 
ON TRIM(CAST(d.COD_fsc AS STRING)) = k.fsc
WHERE d.prg_rig < ? 
  AND d.dat_cre_flu >= ? 
  AND d.TIP_FLU = 'F24';
"""

params = (1000000000, '2023-01-01')

try:
    res = crsr.execute(query, params).fetchall() #<-- this does not work
    print('Query with DONE!!!')
except pyodbc.Error as e:
    e.args = (e.args[1],)
    print(e)

the simple query, with parameters, works, others do not

Sure, it’s the latest version available. We have opened a case to cloudera, but being Impala and python widely used together I am surprised not finding lots bug report about this.