DapperLib / DapperLib/Dapper

ODBC Connection using IN statement throws "Must declare the scalar variable "@status1"."

Open
#840 2 comments 2 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Dominant language
C#
Stars
18.4k
Forks
3.7k
Avg merge
5h 8m
Merged PRs (30d)
1

Description

Using Dapper 1.50.2, .Net 4.6.2

Relevant code as follows:

        public IEnumerable<MyOutputObject> GetOrderItems(bool getAll) {
            object queryParams;
            if(getAll) {
                queryParams = new {
                    status = new [] { 1, 2, 3, 9 }
                };
            } else {
                queryParams = new {
                    status = new [] { 1, 2 }
                };
            }

            using(IDbConnection db = new OdbcConnection(_connString)) {
                const string sql = @"
                    SELECT
                        o.Col1, o.Col2, o.Col3
                    FROM MyTable o
                    WHERE o.Status IN @status";

                return db.Query<MyOutputObject>(sql, queryParams).ToArray();
            }
        }

Since adding the "IN" statement I now get the following error:

ERROR [42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Must declare the scalar variable "@status1".

Connection string is as follows:

Driver={SQL Server Native Client 11.0};Server=localhost;Database=MyDatabase;Trusted_Connection=Yes;

Unfortunately I need to use an OdbcConnection (rather than a SqlConnection) as I'm switching the driver over to a Progress database when deployed (I don't have access to this DB on my dev machine, nor do I have the licenses to install Progress locally). The method works fine without the IN statement.

I've tried passing an anonymous object directly into the Query() call, as per the docs, but I get the same result:

return db.Query<SBSOrderItem>(sql, new {status = new[] { 1, 2 }}).ToArray();

For completeness here's the Progress connection string, though it's not particularly relevant here as I've not tested it yet:

DRIVER={Progress OpenEdge 11.6 Driver};HOST=another_server;PORT=12150;DB=MyDatabase;UID=username;PWD=password;DIL=READ COMMITTED

Contributor guide

No contributing guide indexed for this repository

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start with the shown GetOrderItems method and the Query call using OdbcConnection, then reproduce the failure with the SQL Server Native Client connection string and the IN @status query. Inspect how Dapper expands the array parameters for ODBC; done means the query executes without the undeclared @status1 error while preserving the reported array filtering.

Written by the indexing model from the issue text.

Assessment

Tech stack
csharp, sql
Domain
database
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.