topic Does the ODBC driver for databricks support parameterized queries? in Warehousing & Analytics

Does the ODBC driver for databricks support parameterized queries?

DilyanDimitrov — Wed, 02 Oct 2024 10:06:14 GMT

Hi all,

I am using the official ODBC driver for databricks downloaded from here (the latest version) and trying to have a dotnet API (C#) fetching data from databricks via the driver. I am using the general Microsoft package for ODBC connection - System.Data.Odbc.

But it seems like when trying to execute a parameterized query I get the UNBOUND_SQL_PARAMETER error which says that the values for the params has not been passed.

Here is a snippet of my code:

using OdbcCommand command = connection.CreateCommand(); command.CommandText = Sql; command.Parameters.Add(new OdbcParameter("param1", param1)); command.Parameters.Add(new OdbcParameter("param2", param2)); using OdbcDataReader reader = command.ExecuteReader();

and in the query I have :param1 and :param2.

I am wondering if the ODBC driver supports passing params to queries?

Re: Does the ODBC driver for databricks support parameterized queries?

mjaptarro — Wed, 23 Oct 2024 23:56:05 GMT

I'm getting the same error as well. I've even tried including the Driver in the connection string (on Windows it is Simba Spark ODBC Driver, and Linux has a similar name).

I've tried changing the param name :param1, @param1, and just param1. They all result in the same error.

Unfortunately you can't read or debug the ODBC driver.

Re: Does the ODBC driver for databricks support parameterized queries?

mjaptarro — Wed, 23 Oct 2024 23:58:26 GMT

I'll also point out that all of the chatGpt/copilots tell us our code is written correctly. Even the Assist in Data Bricks gives the same answer. I've read the docs and am unable to figure out an alternate way.

Re: Does the ODBC driver for databricks support parameterized queries?

_Sumit_Kate — Wed, 30 Oct 2024 11:34:00 GMT

Instead of named parameters, the ODBC .NET Provider uses positional parameters that are marked with a question mark (?) in the syntax of the command text.

Please make sure that UseNativeQuery to false(0) in the odbc connection string as they have mentioned in the https://docs.databricks.com/en/_extras/documents/Simba-Apache-Spark-ODBC-Connector-Install-and-Configuration-Guide.pdf . that when this option is enabled(1) the parameterized query won't work.