[UNBOUND_SQL_PARAMETER] Error when querying Databricks warehouse using parameterized queries

[mehulkasliwal]

Version list

Golang - 1.21 github.com/databricks/databricks-sql-go - v1.5.3

Code

Secrets truncated. Example taken from https://github.com/databricks/databricks-sql-go/blob/main/examples/parameters/main.go

package databricks

import (
    "banking/logger"
    "context"
    "database/sql"

    dbsql "github.com/databricks/databricks-sql-go"
)

func QueryDatabricks() {
    connector, err := dbsql.NewConnector(
        dbsql.WithServerHostname(""),
        dbsql.WithHTTPPath(""),
        dbsql.WithPort(443),
        dbsql.WithAccessToken(""),
    )
    if err != nil {
        logger.Errorf("ERR_CREATING_DATABRICKS_CONNECTOR: %+v", err)
        return
    }

    sqlDB := sql.OpenDB(connector)
    if err := sqlDB.Ping(); err != nil {
        logger.Errorf("ERR_PINGING_DATABRICKS: %+v", err)
        return
    }

    ctx := context.Background()
    var p_bool bool
    var p_int int
    var p_double float64
    var p_float float32
    var p_date string
    err = sqlDB.QueryRowContext(ctx, `SELECT
    :p_bool AS col_bool,
    :p_int AS col_int,
    :p_double AS col_double,
    :p_float AS col_float,
    :p_date AS col_date`,
        dbsql.Parameter{Name: "p_bool", Value: true},
        dbsql.Parameter{Name: "p_int", Value: int(1234)},
        dbsql.Parameter{Name: "p_double", Type: dbsql.SqlDouble, Value: "3.14"},
        dbsql.Parameter{Name: "p_float", Type: dbsql.SqlFloat, Value: "3.14"},
        dbsql.Parameter{Name: "p_date", Type: dbsql.SqlDate, Value: "2017-07-23 00:00:00"}).Scan(&p_bool, &p_int, &p_double, &p_float, &p_date)

    if err != nil {
        logger.Errorf("ERR_QUERYING_DATABRICKS: %+v", err)
        return
    }
}

Error

{"level":"error","connId":"7a450800-ad5e-439c-8ddf-1e5dee4ad409","corrId":"","queryId":"a7fc96f8-7401-4649-9a26-b191ee1c7382","time":"2024-03-21T12:14:56+05:30","message":"[UNBOUND_SQL_PARAMETER] Found the unbound parameter: p_bool. Please, fix `args` and provide a mapping of the parameter to a SQL literal.; line 2 pos 1"}
{"level":"error","connId":"7a450800-ad5e-439c-8ddf-1e5dee4ad409","corrId":"","queryId":"a7fc96f8-7401-4649-9a26-b191ee1c7382","error":"unexpected operation state ERROR_STATE: [UNBOUND_SQL_PARAMETER] Found the unbound parameter: p_bool. Please, fix `args` and provide a mapping of the parameter to a SQL literal.; line 2 pos 1","time":"2024-03-21T12:14:56+05:30","message":"databricks: failed to run query"}```

[yunbodeng-db]

If you are running against your own cluster, please check dbr version >= 13.3

[mehulkasliwal]

@yunbodeng-db I'm running it via SQL warehouse. Is it still applicable there?

[yunbodeng-db]

If you are running against your own cluster, please check dbr version >= 13.3

I got a report internally too. I think it is the same issue. The endpoint is sql/protocolv1/o/123456789/xxxx-xxxxxx-xxxxxxby. I did query info of this cluster and it's indeed 13.3.x-scala2.12. I am checking with others to see why this would not work for you. BTW, I ran the same code against our own staging environment and it worked. It is an AWS workspace though.

[mehulkasliwal]

This issue is resolved - apparently the connection values were pointing to the cluster instead of warehouse.

@yunbodeng-db A follow up question - Is there a way to use the "IN" operator with parameterized queries? Something like "SELECT * FROM table WHERE Id IN :ids" where ids is an array of strings

[yunbodeng-db]

Complex data types like array currently are not supported.