bindvar for MSSQL

[yuphing-ong]

MSSQL bind vars doesn't seem to work properly without a workaround (will describe further down). The writeup at https://jmoiron.github.io/sqlx/ says

Named query support is implemented by parsing the query for the :param syntax and replacing it with the bindvar supported by the underlying database, then performing the mapping at execution, so it is usable on any database that sqlx supports

I tried Query, NamedQuery, and looks like ? is generated instead of @ for MSSQL, since the error is:
err=mssql: Incorrect syntax near '?'.

I also tried the example with sqlx.Named (since it's "named" the same as sql's Named) but that says it specifically uses the ? bind variable, and yes, it also threw the same incorrect syntax near '?' error.

[edit]

I also tried db.Rebind, but again, same problem:

	db, err := sqlx.Open(azuread.DriverName, connectString)
	query := "select * from test where region=:region"
	arg := map[string]interface{}{
		"region": region,
	}
	query, args, err := sqlx.Named(query, arg)
	query = db.Rebind(query)
	rows, err = db.Queryx(query, args...)

again same error: mssql: Incorrect syntax near '?'

NB just realised I am using an azure SQL Server instance, maybe this makes a difference? azuread is from "github.com/microsoft/go-mssqldb/azuread"

To workaround this, I had to do something like:

import 	(
	sqll "database/sql"
	"github.com/jmoiron/sqlx"
)	
...
	query := "select * from test where region=@region"
	rows, err = db.Queryx(query, sqll.Named("region", region))
...

Is there a better way to do this, or this is a known issue and correct solution?

[yuphing-ong]

I did some digging around and found that in bind.go:

var defaultBinds = map[int][]string{
	DOLLAR:   []string{"postgres", "pgx", "pq-timeouts", "cloudsqlpostgres", "ql", "nrpostgres", "cockroach"},
	QUESTION: []string{"mysql", "sqlite3", "nrmysql", "nrsqlite3"},
	NAMED:    []string{"oci8", "ora", "goracle", "godror"},
	AT:       []string{"sqlserver"},
}

But db.DriverName() returns "azuresql", which means it gets "UNKNOWN" instead from func BindType.

[yuphing-ong]

adding azuresql solved the problem, i.e. vi bind.go and:

AT: []string{"sqlserver", "azuresql"},

So now this code works:

	db, err := sqlx.Open(azuread.DriverName, connectString)
	query := "select * from test where region=:region"
	arg := map[string]interface{}{
		"region": region,
	}
	query, args, err := sqlx.Named(query, arg)
	query = db.Rebind(query)
	rows, err = db.Queryx(query, args...)

[yuphing-ong]

closing as #927 has been merged