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

Discussion in 'Android Questions' started by little3399, Jan 27, 2015.

  1. little3399

    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
     
  2. sorex

    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

    Code:
    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')
     
  3. Erel

    Erel Administrator Staff Member Licensed User

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

    You can pass arrays to BLOB fields (SQL tutorial)
     
  4. eurojam

    eurojam Well-Known Member Licensed User

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

    Stefan
     
  5. Mahares

    Mahares Well Known Member Licensed User

    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?
    Code:
    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)
     
  6. Erel

    Erel Administrator Staff Member Licensed User

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

    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.
     
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice