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
– mao, Commented Nov 21, 2020 at 9:09
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
– kriss2, Commented Nov 22, 2020 at 0:44
@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
– kriss2, Commented Nov 23, 2020 at 20:15

Palle Due · Accepted Answer · 2020-11-24 07:55:32Z

1

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.

edited Nov 24, 2020 at 7:55

answered Nov 22, 2020 at 19:04

Palle Due

6,3224 gold badges22 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

kriss2 Over a year ago

Hi @Palle Due, thank you for the suggestion and the link. I have tested the possible fix. I'm getting errors when changing my query to suggested: An unexpected token , was found. Expected tokens may include: AT MICROSECONDS MICROSECOND SECONDS SECOND MINUTES MINUTE HOURS. SQLSTATE: 42601, SQLCODE: -104 (when I follow your suggestion - brackets around ?param2?) And The number of host variables in the EXECUTE or OPEN statement is not equal to the number of values required. SQLSTATE: 07001, SQLCODE: -313 (when I follow suggestion from the link - no brackets around ?param2?)

Palle Due Over a year ago

@Kriss2: Sorry about that. There definitely shouldn't be parentheses around the IN list. I've edited the answer.

kriss2 Over a year ago

don't worry about it. Your answer was actually spot on. The link you provided pointed me to this site: dapper.programmingpedia.net/en/tutorial/10/… Eventually, my query needed to look like this: string sql = "SELECT col1, col1, col3 from schema.tblName WHERE col1 = ? AND col2 IN ?param2?"; I tried to upvote your post but I am not allowed :(

eaglei22 Over a year ago

Thank you! This solved my issue, I couldn't find anywhere. I'm connecting to a db/2 database, and kept getting an error when using the normal "@Input" syntax for params.. couldn't figure it out, until I stumbled across this answer. Replacing "@Input" with ?Input? fixes it.

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related