Multiple WHERE IN statements and ODBC

[ArturoCRodriguez]

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.

[fkmbkk2]

Hi,

Are you able to solve this ?

I am using OleDb with and having similar issues (IN clause with another simple condition)

DELETE sysUserRole WHERE sysUserId = ?UserId? AND NOT sysRoleId IN ?RoleId?

I am getting error like this.. [Stack Trace ]: System.Data.OleDb.OleDbException (0x80040E10): No value given for one or more required parameters.

Hope someone can help...

[ArturoCRodriguez]

No, I'm not :(

[fkmbkk2]

Is there any way we can speed up this bug fix ? This is quite a simple requirement..

[victor-samson]

Hitting the same/similar issue. Using one pseudo-positional parameter for a WHERE IN works fine.

Introducing any further pseudo-positional parameters:

ERROR [07001] [DataDirect][ODBC Progress OpenEdge Wire Protocol driver]Value has not been specified for parameter 4.
ERROR [07001] [DataDirect][ODBC Progress OpenEdge Wire Protocol driver]Value has not been specified for parameter 5.

Using version 1.50.4.0. Might see if an update is any help.

[fkmbkk2]

Is there anyway can get this fix ? Kind of very limiting...

[stonestecnologia]

only managed to solve it this way : var sql = "INSERT INTO DBO.JOGOS_INSCRICOES (ANO) VALUES (?)"; OdbcCommand cmd = new OdbcCommand(sql, _dcDapper.Conn); cmd.Parameters.Add("Ano", OdbcType.Int).Value = 2024; cmd.ExecuteNonQuery();

or read this : https://www.c-sharpcorner.com/article/executing-sql-queries-and-sql-stored-procedure-with-odbc/