Android Question Parameter Query With IN keyword

jahswant

Well-Known Member
Licensed User
Longtime User
I use this query. How to send this data in a Parameterized SQL Query ?
B4X: 
SELECT * FROM MXI mxi WHERE mxi.item_id in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20)

Current :
B4X: 
SELECT * FROM MXI mxi WHERE mxi.item_id in ?
 
Last edited:
Solution
M
jahswant said:
How to send this data in a Parameterized SQL Query ?
Click to expand...
Here is the complete code if you want to spruce it up with string builder and parameterized query:
B4X: 
Dim MyList As List
    MyList.Initialize
    MyList.AddAll(Array As Int(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20))
    Dim sb As StringBuilder
    sb.Initialize   
    sb.Append( " IN(")
    For i= 0 To MyList.size -1
        Dim f As String = "?"
        If i = MyList.Size-1 Then
            sb.Append($"${f})"$)
        Else
            sb.Append($"${f},"$)
        End If
    Next
    rs= SQL.ExecQuery2("SELECT * FROM MXI mxi WHERE mxi.item_id " & sb.ToString, MyList)

EDITED LATER without a full test: You may have to have an array like this to execute the query...
Sort by date Sort by votes
M

Mahares

Expert
Licensed User
Longtime User
jahswant said:
SELECT * FROM MXI mxi WHERE mxi.item_id in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20)
Click to expand...

B4X: 
rs= sql1.ExecQuery2("SELECT col1 FROM table1 WHERE col3 IN (?, ?,?, etc)", Array As Int(22,21,89,etc))
You can also put the values ina list and use that for the array part. Also, you can fancy it a little better with stringBuilder
Bonne Nuit
 
Upvote 0
M

Mahares

Expert
Licensed User
Longtime User
jahswant said:
How to send this data in a Parameterized SQL Query ?
Click to expand...
Here is the complete code if you want to spruce it up with string builder and parameterized query:
B4X: 
Dim MyList As List
    MyList.Initialize
    MyList.AddAll(Array As Int(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20))
    Dim sb As StringBuilder
    sb.Initialize   
    sb.Append( " IN(")
    For i= 0 To MyList.size -1
        Dim f As String = "?"
        If i = MyList.Size-1 Then
            sb.Append($"${f})"$)
        Else
            sb.Append($"${f},"$)
        End If
    Next
    rs= SQL.ExecQuery2("SELECT * FROM MXI mxi WHERE mxi.item_id " & sb.ToString, MyList)

EDITED LATER without a full test: You may have to have an array like this to execute the query:
B4X: 
Dim s() As String = Array As String(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20)
And use:
B4X: 
  rs= SQL.ExecQuery2("SELECT * FROM MXI mxi WHERE mxi.item_id " & sb.ToString, s)
 
Last edited:
Upvote 1
Solution
You must log in or register to reply here.
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…