golang/sqlserver positional parameters in select statements

Question

Can anyone provide a working example of a Query or QueryRow (not QueryContext) with a simple select and 2 (or more) positional parameters using the golang sqlserver driver?

There's some churn, apparently: https://github.com/denisenkom/go-mssqldb/issues/260

The only sample code given is for QueryContext which is hypercomplex for a simple cli data transition program. This is brain-dead simple with postgres or mysql but I'm dead in the water with sql server.

Using:

var checkQuery = "select SigCode from @LRU where LRUEmu=@ENVVAR"
...
rows, err := db.Query(checkQuery, sql.Named("LRU", string1), sql.Named("ENVVAR", string2))

yields:

application() rows.Query() failed:mssql: Must declare the table variable "@LRU".

Edit. Per @Flimzy below, retried with QueryContext:

rows, err := db.QueryContext(context.TODO(), checkQuery,sql.Named("LRU", string1), sql.Named("ENVVAR", string2))

Same error.

@MWillemse: Here is some sample code (pymssql) which uses a variable to specify the target table:

slice_cursor.execute(
    "select distinct Subsystem, Field from [%(dlog)s] "
    "where Subsystem not like 'STRING1' "
    "order by Subsystem, Field"
    % {
        'dlog' : datelog
    }
)

I do it all the time. In golang/pg as well, works like a charm.

@putu: I tried your suggestion but no joy. New error, though...

var checkQuery = "DECLARE @LRU VARCHAR(255), @ENVVAR VARCHAR(255); select SigCode from @LRU where LRUEmu=@ENVVAR;"

// ...

rows, err := db.Query(checkQuery, sql.Named("LRU", string1), sql.Named("ENVVAR", string2))

Yields:

mssql: The variable name '@LRU' has already been declared. Variable names must be unique within a query batch or stored procedure.

If string1 contains a table name it seems to me you need put the table name in the string before passing it to Sql Server. Sql server has let you know it was given a string where it expected a table variable. It is talking about @LRU you've put in the FROM clause of your query. — MWillemse
– MWillemse, Commented Aug 1, 2017 at 18:38
QueryContext is not hyper complex at all. It only takes one additional argument, and it can be effectively ignored by using context.TODO(). — Jonathan Hall
– Jonathan Hall, Commented Aug 1, 2017 at 18:49
@MWillemse - see the meager documentation for this at github.com/denisenkom/go-mssqldb — Omortis
– Omortis, Commented Aug 1, 2017 at 18:52
@Flimzy - heh - I thought that TODO line was a typo in the Go docs ;-). Tried it with QueryContext, still the same error as posted above. — Omortis
– Omortis, Commented Aug 1, 2017 at 18:55
How if you change the query to var checkQuery = "DECLARE @LRU VARCHAR(255), @ENVVAR VARCHAR(255); select SigCode from @LRU where LRUEmu=@ENVVAR;"? — putu
– putu, Commented Aug 2, 2017 at 1:45

MWillemse · Accepted Answer · 2017-08-02 22:58:22Z

1

I don't know golang but i do know sql server.

var checkQuery = "select SigCode from @LRU where LRUEmu=@ENVVAR"
rows, err := db.Query(checkQuery, sql.Named("LRU", string1), sql.Named("ENVVAR", string2))

These lines must be getting translated into SQL code and send to SQL server. The resulting SQL code probably looks something like this:

DECLARE @LRU   NVARCHAR(MAX) = '<contents of string 1>';
DECLARE @ENVAR NVARCHAR(MAX) = '<contents of string 2>';
select SigCode from @LRU where LRUEmu=@ENVVAR

When this is executed SQL Server will raise the error

Must declare the table variable "@LRU"

Which is caught and rethrown by your QueryContext.

The reason for SQL server to raise the error is an syntax error in the query. SQL server does not (and afaik neither do other rdbms) allow you to put table names in a variable and run a select against it.

So you either to this:

var checkQuery = "select SigCode from " + string1 + " where LRUEmu=@ENVVAR"
rows, err := db.Query(checkQuery, sql.Named("ENVVAR", string2))

or use dynamic sql like this:

var checkQuery = 
       "DECLARE @SQL NVARCHAR(MAX) = 
           'select SigCode from '+@LRU+' where LRUEmu=@ENVVAR';
        EXEC(@SQL);"
rows, err := db.Query(checkQuery, sql.Named("LRU", string1), sql.Named("ENVVAR", string2))

answered Aug 2, 2017 at 22:58

MWillemse

9805 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Omortis Over a year ago

I use variables as tables names all the time. See my edit above for a python sample in production code which uses pymssql.

Omortis Over a year ago

Also, I tried your very last last code suggestion above and '+' signs generate run-time errors, and without them I get the usual LRU undefined errors. The plus sign errors are probably driver issues, I've used that syntax before in SQLS Mgmt Studio...

MWillemse Over a year ago

That variable is resolved by the phython code before sending it off to the sql server. About the + sign, I assumed you'd know how to concatenate 3 strings together which I (again) assumed you'd understand was the purpose of that line of code. Anyway, please reread my awnser and try to understand what I'm saying about what is happing where when you execute those 2 lines of code. If possible start a debug session and step into db.Query method to see what it does with your query before sending it off to SQL Server.

Omortis Over a year ago

I can do it using plus signs but there's no real way to let the db driver sanitize the inputs that way. By using parametric insertion both the compiler and the db driver help to sanitize the inputs. I stopped assembling SQL strings in that manner 3 years (and 2 languages) ago. Simple brute force string assembly is not the way to go imo. If you look at the way this is done with other RDMBS drivers in python/perl/go parametric insertion is the way to go. And yes, I missed the plus signs. Sorry, pre-coffee.

Omortis Over a year ago

Again - this all works like a charm in python. I will stick with p3 for now and see if I can help with the go driver in some way. Ironically I was trying to use this little project as a way to introduce golang into our dev org...

Collectives™ on Stack Overflow

golang/sqlserver positional parameters in select statements

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related