Calling a function with parameters via Spark ODBC driver

[CHorton]

Hi All,

I am having an issue with calling a Databricks SQL user defined function with parameters from my client application using the Spark ODBC driver.

I have been able to execute a straight SQL statement using parameters e.g. 

SELECT * FROM Customer WHERE CustomerId = ?

But if I try to execute a statement that contains a parameterised function it will not work e.g.

SELECT * FROM GetCustomerData(?)

I always get an [UNBOUND_SQL_PARAMETER] error.  I suspect that Databricks needs named parameter binding, but ODBC doesn't support that, so I am a bit stuck.

Has anyone come across this issue before, or have a solution?

Thanks,

Chris.

[mukul1409]

hi @CHorton 
This is a known limitation when calling Databricks SQL user defined functions with parameters through the Spark ODBC driver. Positional parameters using question marks are supported in standard SQL statements, but they are not supported when used inside function calls, which is why the unbound SQL parameter error occurs. Databricks SQL currently expects parameters to be resolved before function evaluation and does not support parameter binding for UDF arguments through ODBC. A common workaround is to avoid parameterized function calls and instead rewrite the logic inline in the SQL query or use a view or table valued function that does not require runtime parameters. Another option is to construct the SQL string dynamically in the client application with the parameter value substituted, taking care to handle escaping safely.