Search code examples
db2dapper

DB2 - Select query using Dapper with String Array parameter will not return correct results

I have an issue with Select query using: DB2, Dapper, IN clause. I am trying to pass an array of strings to the query. The query itself works fine but the query picks up ONLY the first value from the array i.e. (sudo code as I will use this in C# code)

Select col1, col1, col3 from schema.tblName 
Where col1 = ? AND col2 IN (?)

Now, a little more context regarding the setup, there is a difference between querying using Dapper in MSSQL and DB2. My DB2 is set to only take position-based parameters, so ? in place of parameters (I have no control over this) and I cannot use Named Parameters.

In C# I've tried something like this:

var parameters = new {"1", new[]{"1000","2000"}};
var results = conn.Query<dynamic>(aboveSql, paramers);

All this works fine, compiles, and runs OK. But the query will NOT return values for the second String Array value (this is the IN clause of the Select statement). It will only return results for the first value of the String array.

I have done some extensive research tried to apply different techniques, followed below link as well https://huorswords.github.io/dapper/parameters/sql/in/2019/07/16/dapper-avoid-parentheses-IN-clause.html (bear in mind above link is for MSSQL and NOT DB2).

Tried changing the query, remove brackets no go.

Tried changing the type of parameter from String[]{} to List or IEnumerable - still no go. I checked Dapper documentation - all they say IN clause is supported.

Anybody has an idea how to use IN clause with the Array parameter in DB2 and could suggest a solution, I would really appreciate it.

Thank you

Solution

  • According to this page, you can use pseudo-positional parameters.

    It would look something like this:

    string sql = "SELECT col1, col1, col3 from schema.tblName WHERE col1 = ?param1? AND col2 IN ?param2?";
var results = conn.Query<dynamic>(sql, new {param1 = "1", param2 = new[]{"1000","2000"}});

    The ?param1? will be substituted by ? and the param1 member will be inserted as a positional parameter, param2 likewise.

    EDIT: Removed parentheses around the IN expression. They ought to be inserted by Dapper.