Databricks Simba Spark ODBC .NET8 C# Driver Parameters in SQL Queries

Question

Hello,

I'm using Simba ODBC driver v2.8.0 in order to query data from my azure databrick sql warehouse into a .net 8 Asp.net Api App.

The ODBC driver works fine using plain text query but i need to parametrize the query. Searching around I found that it should be possible to use the '?' wildcard and add some OdbcParameter to the command to be replaced per position (no named parameter replacement is supported in ODBC Driver).

Here follows the code sample:

    using (OdbcConnection connection = (OdbcConnection)dbConnectionFactory.GetConnection(dbConfig.ConnectionString))
    {

        connection.Open();
        using (OdbcCommand cmd = connection.CreateCommand())
        {
            cmd.CommandText = $@"
SELECT * 
FROM MyTable
WHERE param1 = ?
    AND param2= ?
LIMIT {param.Limit} OFFSET {param.Offset}";

            cmd.Parameters.Add(new OdbcParameter("param1", OdbcType.Text)).Value = param1value;
            cmd.Parameters.Add(new OdbcParameter("param2", OdbcType.Int)).Value = param2value;
            OdbcDataReader reader = cmd.ExecuteReader();
            connection.Close();
        }
    }

but i get this error:

[UNBOUND_SQL_PARAMETER] Found the unbound parameter: _119. 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 3 pos 43

I would like to get LIMIT and OFFSET values to be passed as parameter as well, by the way those values are runtime generated and so they could not be affected by SQL injection for this reason i trust the simple string interpolation.
Thanks for the help

Answer 1

@Luigi Navarra - It seems like you are trying to use parameterized queries with Simba Spark ODBC driver v2.8.0 to query data from your Azure Databricks SQL warehouse into a .NET 8 ASP.NET API app. However, you are encountering an error message that says "Found the unbound parameter: _119. 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 3 pos 43".

This error message indicates that there is an unbound parameter in your query. To fix this error, you need to provide a mapping of the parameter to either a SQL literal or collection constructor functions such as map(), array(), struct().

Regarding your question about passing LIMIT and OFFSET values as parameters, it is not possible to pass them as parameters in a parameterized query. However, you can use string interpolation to generate the query dynamically at runtime. Please note that using string interpolation can expose your application to SQL injection attacks if you do not properly validate and sanitize user input.

It seems that the wildcard "?" does not work with OFFSET in the ODBC driver you are using. As you have mentioned, you can use string interpolation to generate the query dynamically at runtime and use a .NET library to validate data and prevent SQL injection attacks.

Using plain text strings, you can also use Dapper ORM over the ODBC driver. Dapper is a lightweight ORM that provides a simple way to map database results to objects. It can help you write cleaner and more maintainable code.

I'm glad that I could help. Let me know if you have any further questions or concerns.

I hope this helps! Let me know if you have any further questions.