SQLite & Array Values Match

General
1

Hi, I would need to retrieve all matches in a SQLite Table for multiple string values passed by an Array.

I looked for any official documentation as well for a forum discussion about this but I found nothing that can help me.

The example that’s closer to what I need to achieve is provided by the following Prepare Statement IN Clause, even if it won’t solve the matter as values numbers are pre-established and I cannot forecast how many values will be passed by my Array.

Var sql As String

sql = "SELECT * FROM Player WHERE Team IN (?, ?);"

Var rs As RowSet

rs = db.SelectSQL(sql, "Seagulls", "Pigeons")

I’m not sure an array match search can really be done while Making DB Connection but if it’s possible I would like to know before I finally opt for a more articulated and farraginous procedure to achieve it.

I thank you all in advance for any suggestion about.

2

This the “twisted” solution it yet works to get the result but as you can see this is not the clean and simple solution I wish.

Yes, I’m using a Single Column Listbox.

// All of that is implemented into the TextChange Event Function

// TypeLenght - Retrieve the current Typed Text And Use its Current Lenght for Comparisons

Dim SearchValue As String = SearchInput.Text

Dim TypeLenght As Integer = SearchValue.Len

// Pair Input With Parent - Array Property - " WordsList " that holds all Cell.Values since Window opens

Dim MatchWords() As String

If Not SearchValue.IsEmpty And SearchValue <> " " Then
  
  For i As Integer = 0 To WordsList.Ubound
    
    // Look For Match Based On Current Search Input Type
    
    Dim WordsMatchValue As String = WordsList(i).Left(TypeLenght)
    
    // If Match
    
    If WordsMatchValue = SearchValue Then
      
      MatchWords.Add WordsList(i)
      
    Else
      
      // Hey Body! User Is looking for nothing in particular!
      // Restore Default - Just Retrieve All Table Results For Column x
      
      OpenDatabase()
      
    End If
    
  Next

// Retrieve All The Results - Then - Filter Them
  
  OpenDatabase()
  
  // Filter Search Result - My Really Daring Solution
  
  For a As Integer = 0 To  MatchWords.Ubound
    
    For i As Integer = WordsTable.LastRowIndex DownTo 0
      
      // Look For Match Based On Current Search Input Type
      
      Dim WordsMatchValue As String = WordsTable.CellValueAt(i,0).Left(TypeLenght)
      
      If WordsMatchValue <> SearchValue Then
        
        WordsTable.RemoveRow(i)
        
      End If
      
    Next
    
  Next
  
Else
  
  // Hey Body! User Is looking for nothing in particular!
  // Restore Default - Just Retrieve All Table Results For Column x
  
  OpenDatabase()
  
End If
3

You can build your IN clause like so

dim inClause as string
if SearchValues.Ubound >= 0 then
   inClause = "(?"
   for i as integer = 1 to SearchValues.Ubound
      inClause = inClause + ", ?"
   next
   inClause = inClause + ")"
   sql = "SELECT * FROM Player WHERE Team IN " + inClause  // the ; at the end is unnecessary
   rs = db.SelectSQL(sql, SearchValues)  // you can pass an array
end
1 Like
4

Thank you Tim, it seems we got a code artist contribution here.

I will test your solution to give you back a response about, meanwhile… Do you think my method could get at least some kind of unexpected advantage such as “less stressful” skill by using a minor number of SQLite connections per time?

  • Sorry, stupid question as connections per time remains the same.

I will let you know, thank you again.

5

Your approach might be faster, but if you stick with the database approach, Tim’s recommendation is the “right” way to do it. (I’d use an array instead of appending to a string, but that’s a minor quibble.)

However, do not implement this in TextChange unless you despise your users. Every keystroke will force an update and slow down typing. Instead, start or reset a Timer with a 500-750 ms period and put this code in that Timer’s Action event. This will allow the user to type a few characters at a time, usually their complete thought, before the code executes.

1 Like
6

Thank You Kem for your suggestions, I think Tim’s solution is very elegant and I’m going to test it today.

I understand your point here, anyway the solution you are offering does not fully fit my needs since I would like the user to get an immediate skimming of the database just based on each new typed letter, could be the case in which the user got two words starting for “S” and so typing the next letter make him/her to get faster the desired result.

Anyway I thank you for your consideration about potential case of overloads.