Android Question pass an array to a SELECT … WHERE … IN(?)

little3399

Active Member
Licensed User
Hi all

Is there a way to "pass an array to a SELECT … WHERE … IN" in sqlite ? such as

"select col1,col2,col3 from table where col4 in (?) " ,,, how to do if in b4x ? need Help
 

sorex

Expert
Licensed User
good question.

I prefer the concatenating method (queryExecute instead of queryExecute2) due to debugging reasons.

mysql="select col1,col2,col3 from table where col4 in ('"&myarray(0)&"','"&myarray(1)&"','"&myarray(2)&"') "

when it ain't a fixed sized array or you prefer flexibility you could use

B4X:
Dim myarray() As String
myarray = Array As String ("test","test2","test3")
Dim inrange As String
For x=0 To myarray.Length-1
inrange=inrange & ",'" & myarray(x)& "'"
Next
inrange=inrange.SubString(1)

Dim mysql As String="select col1,col2,col3 from table where col4 in ("&inrange&")"
Log (mysql)
output : select col1,col2,col3 from table where col4 in ('test','test2','test3')
 

Mahares

Well Known Member
Licensed User
Don't use it. It is much better to use ExecuteQuery2.
Do you prefer using the below code? If so, how do you handle it when the IN predicate has 50 or more items. How do you keep up with the number of question marks (one for each item, which means 50 question marks) without missing one or more?
B4X:
Dim MyArray() As String = Array As String ("test","test2","test3")
Dim MySql As String="SELECT col1,col2,col3 FROM table WHERE col4 IN (?,?,?)"
Cursor1= SQL1.ExecQuery2(MySql,MyArray)
 

sorex

Expert
Licensed User
I still think it better to do it like that for debugging purposes.

with the question marks method you don't have a clue where it goes wrong when you made a typo or other fault so you need to build up the string seperatly to figure it out.
 
Top