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

little3399

Active Member
Licensed User
Longtime 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
 
Sort by date Sort by votes
S

sorex

Expert
Licensed User
Longtime 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')
 
Upvote 0
M

Mahares

Expert
Licensed User
Longtime User
Erel said:
Don't use it. It is much better to use ExecuteQuery2.
Click to expand...

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)
 
Upvote 0
S

sorex

Expert
Licensed User
Longtime 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.
 
Upvote 0
You must log in or register to reply here.

Similar Threads

  • Article
B4A Library [B4X] [XUI] AS SelectionList - Single or multiple selection
Replies
19
Views
6K
Alexander Stolte
A
  • Question
Android Question Where is TextView in B4A?
Replies
11
Views
4K
AnandGupta
  • Article
Android Code Snippet [algorithm] Digital Low-Pass Filter
Replies
4
Views
6K
max123
  • Question
Android Question Pass more than 3 arguments from B4A to WebView Javascript function
Replies
12
Views
6K
max123
  • Locked
  • Article
Android Code Snippet SaveAs - Let the user select a target folder + list of other related methods
Replies
16
Views
35K
Erel
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…