DapperLib / DapperLib/Dapper

Multiple WHERE IN statements and ODBC

Open
#1,145 6 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

I'm using Dapper to make Select statments against a DB2 database (with ODBC drive). When I include one "WHERE IN" clause it works correctly:

    //Works!
    var p = new DynamicParameters();
    var list = new float[] { 0.35f, 0.36f, 0.37f };            
    p.Add("ids", list);          
    string query = $"select * from {_table} where FIELD1 in ?ids?";
    return WithConnection(c => c.Query<Entity>(query, p));

However, if I include another WHERE IN clause, then I don't get the expected result:

   //Unexpected result (I should get 2 elements but returns empty)    
    var p = new DynamicParameters();
    var list = new float[] { 0.35f, 0.36f, 0.37f };  
    var list2 = new int[] { 2423634, 2423635, 2423636, 2423637, 2417456 };  
    p.Add("ids", list); 
    p.Add("ids2", list2);         
    string query = $"select * from {_table} where FIELD1 in ?ids? and FIELD2 in ?ids2?";
    return WithConnection(c => c.Query<Entity>(query, p));

Moreover, if I use a WHERE IN clause and a simple condition, I get an error:

        //Error Error: [SQL0302] Data conversion or data mapping error. 
        // Cause . . . . . :   Error type 6 has occurred. Error types and 
        // their meanings are: ... 6 -- Numeric data that is not valid. ...
        var p = new DynamicParameters();
        var list = new float[] { 0.35f, 0.36f, 0.37f };
        int id = 2423634;
        p.Add("ids", list);
        p.Add("id", id);
        string query = $"select * from {_table} where FIELD1 in ?ids? and FIELD2 = ?id?";
        return WithConnection(c => c.Query<Entity>(query, p));

If I use the condition without the WHERE IN it works correctly.

Am I doing anything wrong or could it be a bug in Dapper? (I think there was an similar bug when using WHERE IN clauses solved in 1.50.2 version).

Thank you in advance.

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

No source file or test is identified. Reproduce the three DB2-over-ODBC queries from the issue, comparing one expanded WHERE IN parameter with multiple IN parameters and a scalar condition; done means the expected rows are returned and the SQL0302 conversion error no longer occurs.

Written by the indexing model from the issue text.

Assessment

Tech stack
csharp, sql
Domain
database
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 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.