Android Code Snippet How to Handle Multiple Parameters in a SQLite Query Using Question Marks

Subname: Repeat - String function as it applies to SQLite and beyond

Description: In a SQL statement involving ExecQuery2 or ExecNonQuery2, if the number of parameters is large, it is very easy to under or over estimate the number of question marks (?) required to replace the parameters, hence the query crashes. This code snippet uses a sub that takes care of the number of ? needed without entering a ? multiple times and therefore, eliminates the miscount.

SELECT STATEMENT:

Dim MyArray() As String = Array As String ("Vietnam","Tunisia","Brazil","France","Egypt","Sweden","Israel")
txt="SELECT *  FROM MyTable WHERE COUNTRY IN (" & Repeat("?",",",MyArray.Length) & ")"
Cursor1= SQL1.ExecQuery2(txt,MyArray)

INSERT STATEMENT:

Dim MyArray() As String = Array As String("Universe", "Earth", "1","Theory","Solar","Magnetic","Miles","Radius","Strata","Altitude")
SQL1.ExecNonQuery2("INSERT INTO MyTable VALUES (" & Repeat("?",",",MyArray.Length) & ")",MyArray)

Sub Repeat(Character As String, Separator As String, Number As Int) As String
  Dim Result =  "" As String
  For i = 0 To Number  -1
    Result  = Result & Character & Separator
  Next
  If Separator="" Then
    Return Result
  Else
    Return Result.Substring2(0, Result.Length-1)
  End If
End Sub

The sub itself can be applied independently of SQLite and using other than question marks:
Repeat("x","",75) This repeats the x 75 times
Repeat("B4A","|",50) This repeats B4A| 50 times

Tags: SQLite, Select, Insert, ExecQuery2, ExecNonQuery2

 

[LucaMs]

Thanks, @Mahares.

I think you would have had to post it as:

Repeat - String function

and post the queries as examples.