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

[little3399]

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]

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

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')

 

[Erel]

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

Don't use it. It is much better to use ExecuteQuery2.

You can pass arrays to BLOB fields (SQL tutorial)

 

[eurojam]

Thanks Erel, it is always worth to read this question thread!

Stefan

 

[Mahares]

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?

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)

 

[Erel]

In the case of many items then it is indeed makes sense to build the string instead.

 

[sorex]

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.