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

Question

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

For Db2 CLI applications, each member of the IN list must have its own parameter marker in the query when Db2 compiles the query. As your query has a single parameter marker, only the first array entry gets bound. — mao
Hi @mao, first thank you for taking the time to read and respond to the post, much appreciated. As the values in the IN clause are dynamic, I will have to dynamically set positioned parameters, the question mark place holders. I will test this on Monday and update. It actaully makes sense since the code does not break or thows any errors. Just returns result for ONLY the first value in the IN clause. — kriss2
@mao, just to confirm. Your explanation, is definitely the reason the code compiles but takes ONLY the first parameter from the string array. I have hardcoded ... IN (?,?) - matching the number of values in the string array and got the correct result. Unfortunately, this would mean I have to find a way to dynamically change the query to include variable number of ? in the IN clause. — kriss2

Palle Due Palle Due · Accepted Answer · 2020-11-22T19:04:14

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.

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

1 Answers