ODBC Parameterization issue (Basic .NET)

[DitchT]

Hey all,

I have some rather basic C# code that I'm running against the newest DataBricks ODBC driver, attempting to insert parameterized queries.

I see the option to disable parameterized queries in the documentation. UseNativeQuery=false, FastSQLPrepare=false. SparkThriftServer, OAuth with passthrough token, SSL=true, but nothing else configured.

I can query this and other tables just fine and I can insert with SQL literals, but parameterization seems broken.

Any feedback would be appreciated
Forgive the formatting, but it's pretty much basic ODBC use with parameterized queries.

Thanks a lot!

-------------------------------------

const string sql = "INSERT INTO test_odbc_identity (name, value) VALUES (?, ?);";

    using var connection = new OdbcConnection(connectionString);

    connection.Open();

    using var command = new OdbcCommand(sql, connection);

    command.Parameters.Add(new OdbcParameter

    {

        OdbcType = OdbcType.VarChar,

        Value = "Alpha"

    });

    command.Parameters.Add(new OdbcParameter

    {

        OdbcType = OdbcType.Int,

        Value = 10

    });

    var rows = command.ExecuteNonQuery();
-----------------------------

The stacktrace isn't so helpful on my side.

Top-level message: ERROR [42P02] [Databricks][Hardy] (80) Syntax or semantic analysis error thrown in server while executing query. Error message from server: org.apache.hive.service.cli.HiveSQLException: Error running query: [UNBOUND_SQL_PARAMETER] org.apache.spark.sql.catalyst.ExtendedAnalysisException: [UNBOUND_SQL_PARAMETER] Found the unbound parameter: _53. Please, fix `args` and provide a mapping of the parameter to either a SQL literal or collection constructor functions such as `map()`, `array()`, `struct()`. SQLSTATE: 42P02; line 1 pos 53
at org.apache.spark.sql.hive.thriftserver.HiveThriftServerErrors$.runningQueryError(HiveThriftServerErrors.scala:49)
at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation.$anonfun$execute$1(SparkExecuteStatementOperation.scala:1063)
at scala.runtime.java8.JFunction0$mcV$sp.apply(JFunction0$mcV$sp.scala:18)
at com.databricks.unity.UCSEphemeralState$Handle.runWith(UCSEphemeralState.scala:51)
at com.databricks.unity.HandleImpl.runWith(UCSHandle.scala:104)
at org.apache.spark.[Databricks][Hardy] (80) Syntax or semantic analysis error thrown in server while executing query. Error message from server: org.apache.hive.service.cli.HiveSQLException: Error running query: [UNBOUND_SQL_PARAMETER] org.apache.spark.sql.catalyst.ExtendedAnalysisException: [UNBOUND_SQL_PARAMETER] Found the unbound parameter: _53. Please, fix `args` and provide a mapping of the parameter to either a SQL literal or collection constructor functions such as `map()`, `array()`, `struct()`. SQLSTATE: 42P02; line 1 pos 53
at org.apache.spark.sql.hive.thriftserver.HiveThriftServerErrors$.runningQueryError(HiveThriftServerErrors.scala:49)
at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation.$anonfun$execute$1(SparkExecuteStatementOperation.scala:1063)
at scala.runtime.java8.JFunction0$mcV$sp.apply(JFunction0$mcV$sp.scala:18)
at com.databricks.unity.UCSEphemeralState$Handle.runWith(UCSEphemeralState.scala:51)
at com.databricks.unity.HandleImpl.runWith(UCSHandle.scala:104)
at org.apache.spark.
--- ODBC Error Detail ---
SQLState : 42P02
NativeError : 80
Message : [Databricks][Hardy] (80) Syntax or semantic analysis error thrown in server while executing query. Error message from server: org.apache.hive.service.cli.HiveSQLException: Error running query: [UNBOUND_SQL_PARAMETER] org.apache.spark.sql.catalyst.ExtendedAnalysisException: [UNBOUND_SQL_PARAMETER] Found the unbound parameter: _53. Please, fix `args` and provide a mapping of the parameter to either a SQL literal or collection constructor functions such as `map()`, `array()`, `struct()`. SQLSTATE: 42P02; line 1 pos 53
at org.apache.spark.sql.hive.thriftserver.HiveThriftServerErrors$.runningQueryError(HiveThriftServerErrors.scala:49)
at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation.$anonfun$execute$1(SparkExecuteStatementOperation.scala:1063)
at scala.runtime.java8.JFunction0$mcV$sp.apply(JFunction0$mcV$sp.scala:18)
at com.databricks.unity.UCSEphemeralState$Handle.runWith(UCSEphemeralState.scala:51)
at com.databricks.unity.HandleImpl.runWith(UCSHandle.scala:104)
at org.apache.spark.[Databricks][Hardy] (80) Syntax or semantic analysis error thrown in server while executing query. Error message from server: org.apache.hive.service.cli.HiveSQLException: Error running query: [UNBOUND_SQL_PARAMETER] org.apache.spark.sql.catalyst.ExtendedAnalysisException: [UNBOUND_SQL_PARAMETER] Found the unbound parameter: _53. Please, fix `args` and provide a mapping of the parameter to either a SQL literal or collection constructor functions such as `map()`, `array()`, `struct()`. SQLSTATE: 42P02; line 1 pos 53
at org.apache.spark.sql.hive.thriftserver.HiveThriftServerErrors$.runningQueryError(HiveThriftServerErrors.scala:49)
at org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation.$anonfun$execute$1(SparkExecuteStatementOperation.scala:1063)
at scala.runtime.java8.JFunction0$mcV$sp.apply(JFunction0$mcV$sp.scala:18)
at com.databricks.unity.UCSEphemeralState$Handle.runWith(UCSEphemeralState.scala:51)
at com.databricks.unity.HandleImpl.runWith(UCSHandle.scala:104)
at org.apache.spark.
Source : Databricks ODBC Driver

[DitchT]

I should probably add that I've tried to downgrade to the last Simba driver and that doesn't support transactions which are kinda required in my scenario. Thanks again!

[emma_s · Accepted solution]

Hi, 

I haven't come across this issue myself but according to some internal resources I think the following fix may work.

This is a known issue introduced in ODBC driver version 2.8.0. The root cause is that the default for EnableNativeParameterizedQuery was changed from 1 to 0 in that release (to protect Power BI users). Without it, the driver's client-side SQL parser tries to rewrite parameterized queries but fails on DML statements like INSERT — it sends unresolved internal parameter names (_53, _67, etc.) to the server, which causes the UNBOUND_SQL_PARAMETER error.

SELECT queries work because the driver's ANSI SQL-92 parser handles simple SELECT parameterization, but INSERT/DML and complex SQL (CTEs, CASE WHEN, etc.) are not reliably handled by the client-side parser.

The fix is to add these two settings to your connection string:

UseNativeQuery=1;EnableNativeParameterizedQuery=1;

For C# OdbcConnection, these must be in the connection string itself, not just the DSN configuration. So your connection string should look something like:

Driver={Databricks};Host=...;Port=443;HTTPPath=...;AuthMech=11;Auth_Flow=0;Auth_AccessToken=...;SSL=1;UseNativeQuery=1;EnableNativeParameterizedQuery=1;

FastSQLPrepare is unrelated to this issue and won't help here.

If you're on an older driver version, I'd also recommend updating to 2.9.1+ as there have been additional fixes for parameterized query handling.

I haven't been able to test this so if it works please mark as accepted solution to help others.

[DitchT]

@emma_s Thank you so much! This resolves the issue for me.