ODBC Connection using IN statement throws "Must declare the scalar variable "@status1"."
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
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- 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