One parameter with multiple arguments - Selection and SelectionArgs

Question

I have encountered this problem:

Caused by: java.lang.IllegalArgumentException: Cannot bind argument at index 3 because the index is out of range. The statement has 1 parameters.

As a result of:

String where = "id_1 = 50 AND id_2 = ?";
//The String[] is ideally of dynamic length and not necessarily 3 in size.
String[] whereArgs = {"60", "61", "62"};

cursor.setSelection(where);
cursor.setSelectionArgs(whereArgs);

I am simply using it the wrong way. I have realized this already. But I would think it makes it evident what I am trying to accomplish.

My question: Is there a way for me to insert an array of varying length and arguments into a single parameter? Any best practices that I have missed. Have I simply baked myself into a bad situation?

The SQL statement I could be looking for:

WHERE id_1 = 50 AND ((id_2 = 60) OR (id_2 = 61) OR (id_2 = 62))

The only way I can think of to fix my problem is to create a string and build upon it in a loop the length of String[] adding OR (id_2 = xx) with every iteration. It just doesn't sound like a very good solution to me.

Thanks for your time!

What about MySQL `IN()`? `"... AND id_2 IN ( ? )"` and build `whereArgs` so it's a string of comma separated values. — marekful, Jun 04 '15 at 06:22
@marekful I have not tried that, no. I will give that one a shot. — Dennis, Jun 04 '15 at 06:25
That seems to be a flexible enough way in this case as you can easily join the array elements into a CSV list. — marekful, Jun 04 '15 at 06:27
http://stackoverflow.com/questions/178479/preparedstatement-in-clause-alternatives — ykaganovich, Jun 04 '15 at 06:33
@marekful That worked like a charm. Thanks! If you write an answer of it I can mark it. — Dennis, Jun 04 '15 at 06:41

score 1 · Answer 1 · answered Jun 04 '15 at 06:29

In general, constructing the WHERE clause dynamically is the correct solution. Use StringBuilder.append() rather than + to save some string construction overhead.

In some cases, you may also want just issue multiple statements. So instead of

select from table where id_1 = 50 AND (id_2 = ? OR id_2 = ? OR id_2 = ?);

You could do a Batch of

select from table where id_1 = 50 AND (id_2 = ?);
select from table where id_1 = 50 AND (id_2 = ?);
select from table where id_1 = 50 AND (id_2 = ?);

score 0 · Answer 2 · edited Jun 04 '15 at 07:05

0

Try with this code:

String where = "id_1 = 50 AND (id_2 = ? OR id_2 = ? OR id_2 = ?)";

String[] whereArgs = {"60", "61", "62"};

edited Jun 04 '15 at 07:05

enrico.bacis

30,497
10
86
115

answered Jun 04 '15 at 06:20

Arnab Dhar

239
2
15

Hi. I don't think that takes into consideration that my String[] can be of varying length. – Dennis Jun 04 '15 at 06:22

One parameter with multiple arguments - Selection and SelectionArgs

2 Answers2