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

Discussion in 'Code Snippets' started by Mahares, Jan 28, 2015.

  1. Mahares

    Mahares Well Known Member Licensed User

    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:
    Code:
    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:
    Code:
    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)
    Code:
    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
     
    Last edited: Jan 28, 2015
    FrankBerra, iz0ndg, rwblinn and 3 others like this.
  2. LucaMs

    LucaMs Expert Licensed User

    Thanks, @Mahares.

    I think you would have had to post it as:

    Repeat - String function

    and post the queries as examples.
     
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