Android Question Increase search ability SQLite (Solved)

Shelby

Well-Known Member
Licensed User
Below is my b4xMainPage module for my SQLite app
My b4xmain module:
#Region Shared Files
#CustomBuildAction: folders ready, %WINDIR%\System32\Robocopy.exe,"..\..\Shared Files" "..\Files"
'Ctrl + click to sync files: ide://run?file=%WINDIR%\System32\Robocopy.exe&args=..\..\Shared+Files&args=..\Files&FilesSync=True
#End Region

'Ctrl + click to export as zip: ide://run?File=%B4X%\Zipper.jar&Args=The_Shelby_Code.zip

Sub Class_Globals
    Private Root As B4XView
    Private xui As XUI
 
    Public sql As SQL
 
    Public DBFileDir As String
    Public DBFileName = "dbircV11.db" As String
    Public Shared As String
    Public ImageFileName As String
 
    Private xtblItems As xTableLite
    Private ZoomImageView1 As ZoomImageView
    Private btnQuit As Button
#If B4A
    Public rp As RuntimePermissions
#End If
    Private lblTitle As B4XView
    Private lblSearch As B4XView           'first use of word: 'search'
    Private lblCancel As B4XView
    Private edtSearch As B4XView
    Private xTableSearch As xTableLite
    Private writing As Boolean            'Boolean to wait for search while writing
    Private timerSearch As Timer    'The timer that will check when is possible to start Search
    Private SearchPattern As String        'Variable to Hold the pattern to search for
    Private lstSearch As List            'List contains copy of DB to fasten the search (I suppose)
    Private ime As IME                    'Library to handle the keyboard and hide it when no longer needed
End Sub

Public Sub Initialize
'    B4XPages.GetManager.LogEvents = True
End Sub

'This event will be called once, before the page becomes visible.
Private Sub B4XPage_Created (Root1 As B4XView)
    Root = Root1
    Root.LoadLayout("Layout")
 
#If B4A
    Shared = rp.GetSafeDirDefaultExternal("")
#Else If B4J
    xui.SetDataFolder("SS2021B4X")
    Shared = xui.DefaultFolder
#Else
    Shared = xui.DefaultFolder
#End If
    File.MakeDir(Shared, "/Dbs/")
'    File.MakeDir(Shared, "/Images")
    DBFileDir = Shared & "/Dbs/"

'    ImagesDir = Shared & "/Images/"
'    ImagesDir = File.DirAssets    'you could eventually leave the images in the File.DirAssets folder.

    File.Delete(DBFileDir, DBFileName)    'can be used to reset the default database.
 
    Log(DBFileDir)
'    Log(ImagesDir)
    If File.Exists(DBFileDir, DBFileName) = False Then 'SSS When False:Lets browser update
        Log("db file does not exist")
        File.Copy(File.DirAssets, DBFileName, DBFileDir, DBFileName)
        Log("db copied")
    Else
        Log("db file does exist")
    End If
 
'    If File.Exists(ImagesDir, "R702.4.2.png") = False Then
'        File.Copy(File.DirAssets, "R702.4.2.png", ImagesDir, "R702.4.2.png")    'ignore
'    End If                         (SSS all green here by Klaus)                   
 
    B4XPages.SetTitle(Me, "SS2021B4X")
    If sql.IsInitialized = False Then
        Try
            #If B4J
            sql.InitializeSQLite(DBFileDir, DBFileName,True)
            #Else
            sql.Initialize(DBFileDir, DBFileName,True)
            #End If
        Catch
            Log("DB Not Initialized")
            'CkUser
        End Try
    Else
    
    End If
 
    Dim resultset As ResultSet = sql.ExecQuery("SELECT name from sqlite_master where type='table'")
    Do While resultset.NextRow
        Log(resultset.Getstring("name"))
    Loop
    resultset.Close
 
    'xtblItems.LoadSQLiteDB(Starter.sql,"SELECT DescriptionSubjectLookup, TableNumber as [Table], PageIRC As [IRC Page], PgPDF As [PDF Page] FROM TableList221",True)
    'Above edited out with Klaus recommendation in forum 5.20.21 SSS
    xtblItems.SingleLine = False
'    xtblItems.LoadSQLiteDB2(sql,"SELECT DescriptionSubjectLookup, TableNumber as [Table], PageIRC As [IRC Page], PgPDF As [PDF Page] FROM TableList221", False, Array As String("T", "T", "T", "I"))


    'I made a small change to the xTableLite class of Klaus, just to have the app wait for the table to be fully loaded before doing other things
    'Because of this I call the Table loading with a Wait For
    Wait For (xtblItems.LoadSQLiteDB2(sql,"SELECT DescriptionSubjectLookup, TableNumber as [Table], PageIRC As [IRC Page], PgPDF As [PDF Page] FROM TableList221", False, Array As String("T", "T", "T", "I"))) complete (Comp As Object)
 
    'Here we populate the List that will help us for the Search Function
    lstSearch.Initialize
    For x = 0 To xtblItems.Size - 1
        Dim item() As String = xtblItems.GetValues(x)
        lstSearch.Add(item)
    Next
 
 


    xtblItems.SetColumnsWidths(Array As Int(250dip, 150dip, 90dip, 75dip))
 
    'Setting the Table for Search Result to appear as the original one
    xTableSearch.SingleLine = False
    xTableSearch.SetHeader(Array As String ("DescriptionSubjectLookup", "Table", "IRC Page", "PDF Page"))
    xTableSearch.SetColumnsWidths(Array As Int(250dip, 150dip, 90dip, 75dip))
 
    timerSearch.Initialize("Search", 1000)    'Timer set to wait for 1 second after  writing before start Search.
    ime.Initialize("")                        'Initialize the library to hide the keyboard later
End Sub

Sub btnQuit_Click
    If btnQuit.Text = "Back" Then
        ZoomImageView1.mBase.Visible = False
        btnQuit.Text = "Exit"
    Else
        sql.Close
#If B4i
        xui.MsgboxAsync("You cannot exit with this button", "Warning")
#Else
        ExitApplication
#End If
    End If
End Sub

Private Sub xtblItems_CellClick(col As Int, row As Int)
    ImageFileName = xtblItems.GetValue(1, row) & ".png"
    If File.Exists(File.DirAssets, ImageFileName) Then
        ShowImage
    Else
        xui.MsgboxAsync("Image  " & ImageFileName & "  does not exist.", "Warning")
    End If
End Sub

Private Sub ShowImage
    btnQuit.Text = "Back"
    ZoomImageView1.SetBitmap(xui.LoadBitmap(File.DirAssets, ImageFileName))
    ZoomImageView1.mBase.Visible = True
End Sub


Private Sub lblSearch_Click
    'Hide the Title bar and make available the EditText for Search, and the icon to close the search function.
    'It even hide the original table and show the one for search results.
    lblTitle.Visible = False
    lblSearch.Visible = False
    lblCancel.Visible = True
    edtSearch.Visible = True
    edtSearch.Text = ""
    xtblItems.Visible = False
    xTableSearch.Visible = True
    xTableSearch.ClearAll
End Sub

Private Sub lblCancel_Click
    'Hide everything related to the Search function and show again the Title Bar and the icon for Search
    edtSearch.Visible = False
    lblCancel.Visible = False
    lblSearch.Visible = True
    lblTitle.Visible = True
    xTableSearch.Visible = False
    xTableSearch.ClearAll
    xtblItems.Visible = True
    ime.HideKeyboard
End Sub

Private Sub edtSearch_TextChanged (Old As String, New As String)
    If New = "" Then Return            'If EditText is empty no search will be done
    SearchPattern = New                'Copy the editext content as the pattern to search for
    writing = True                    'Variable to set that we are writing something and to avoid immediate unneeded search
    xTableSearch.ClearAll            'Clear the search results table
    timerSearch.Enabled = True        'Start the timer to check when possible to begin the search
End Sub

Private Sub Search_Tick
    If writing Then            'If we are writing no search will start
        writing = False
        Return
    End If
    timerSearch.Enabled = False                    'Stop the timer because a search will start
    For x = 0 To lstSearch.Size - 1                'Call the check of every single item simultaneously using a ResumableSub
        FastSearch(x)        'Just call a single item check
        Sleep(0)
    Next
End Sub

Private Sub FastSearch (what As Int)
    Dim myitem() As String = lstSearch.Get(what)                'Get the item to analyze from the List
    Sleep(0)        'Just not to let the app to hang up
    For y = 0 To 3
        If myitem(y).ToUpperCase.Contains(SearchPattern.ToUpperCase) Then        'Check all of the four Table fields. If the pattern is found then add it to the search table results
            xTableSearch.AddRow(Array As String (myitem(0), myitem(1), myitem(2), myitem(3)))    'Add the found item
            Sleep(0)
        End If
    Next
End Sub

Private Sub xTableSearch_CellClick(col As Int, row As Int)
    ime.HideKeyboard
    ImageFileName = xTableSearch.GetValue(1, row) & ".png"
    If File.Exists(File.DirAssets, ImageFileName) Then
        ShowImage
    Else
        xui.MsgboxAsync("Image  " & ImageFileName & "  does not exist.", "Warning")
    End If
End Sub
. My search ability is limited. If I use more than one keyword I only get results if the exact occurrence is found in my db. I'd like for all and any records to be brought up no matter what arrangement the keywords might be typed into the search.
Thanks for any help
 
Last edited:
Solution
Try this...

B4X:
Private Sub FastSearch (what As Int)
    'Get the item to analyze from the List
    Dim myitem() As String = lstSearch.Get(what)
    'Just not to let the app to hang up
    Sleep(0)   

    'concat data want to search
    Dim mydata As String = myitem(0) & myitem(1) & myitem(2) & myitem(3)

    'prepare any word want to search, (assume single space for each word)
    Dim word() = Regex.Split(" ", SearchPattern.ToUpperCase)
    
    'search for any match word on the data
    Dim found As Boolean
    For y = 0 To word.Length - 1
        If mydata.ToUpperCase.Contains(word(y).ToUpperCase) Then
            found = True
            Exit
        End If
    Next
    
    'if founded, add to to table
    If found=True Then...

Shelby

Well-Known Member
Licensed User
Private Sub FastSearch (what As Int) 'Get the item to analyze from the List Dim myitem() As String = lstSearch.Get(what) 'Just not to let the app to hang up Sleep(0) 'concat data want to search Dim mydata As String = myitem(0) & myitem(1) & myitem(2) & myitem(3) 'prepare any word want to search, (assume single space for each word) Dim word() = Regex.Split(" ", SearchPattern.ToUpperCase) 'search for any match word on the data Dim found As Boolean For y = 0 To word.Length - 1 If mydata.ToUpperCase.Contains(word(y).ToUpperCase) Then found = True Exit End If Next 'if founded, add to to table If found=True Then xTableSearch.AddRow(Array As String (myitem(0), myitem(1), myitem(2), myitem(3))) 'Add the found item Sleep(0) End If End Sub
Do you think I can add this to my B4X module without any other adjustments? Trying it now just by adding it to my module right under my Class_globals sub. Wow, so far no squiggly lines suggesting I'm stupid. running now....it's asking: (what As Int) Even I know it's integer....
 
Upvote 0

Shelby

Well-Known Member
Licensed User
rraswisac, It turns out I had something similar at the end of my module, namely:
Sub xTableSeaerch_cellclick....:
Private Sub xTableSearch_CellClick(col As Int, row As Int)
    ime.HideKeyboard
    ImageFileName = xTableSearch.GetValue(1, row) & ".png"
    If File.Exists(File.DirAssets, ImageFileName) Then
        ShowImage
    Else
        xui.MsgboxAsync("Image  " & ImageFileName & "  does not exist.", "Warning")
    End If
End Sub
 
Upvote 0

Shelby

Well-Known Member
Licensed User
True, hens do construct items of interest. I love their structures. Whoops! I missed the sexual context, so.... ha ha! Belated
 
Upvote 0

rraswisak

Active Member
Licensed User
Do you think I can add this to my B4X module without any other adjustments? Trying it now just by adding it to my module right under my Class_globals sub. Wow, so far no squiggly lines suggesting I'm stupid. running now....it's asking: (what As Int) Even I know it's integer....
The code is modified version of your original FastSearch Sub in first post. The changes will search every words on search text and will find in all column of the table
 
Upvote 0

Shelby

Well-Known Member
Licensed User
Thanks Rraswisak, we'll see if I can make it work. Your description sounds like exactly what I want.
 
Upvote 0

Shelby

Well-Known Member
Licensed User
Try this...

B4X:
Private Sub FastSearch (what As Int)
    'Get the item to analyze from the List
    Dim myitem() As String = lstSearch.Get(what)
    'Just not to let the app to hang up
    Sleep(0) 

    'concat data want to search
    Dim mydata As String = myitem(0) & myitem(1) & myitem(2) & myitem(3)

    'prepare any word want to search, (assume single space for each word)
    Dim word() = Regex.Split(" ", SearchPattern.ToUpperCase)
  
    'search for any match word on the data
    Dim found As Boolean
    For y = 0 To word.Length - 1
        If mydata.ToUpperCase.Contains(word(y).ToUpperCase) Then
            found = True
            Exit
        End If
    Next
  
    'if founded, add to to table
    If found=True Then
        xTableSearch.AddRow(Array As String (myitem(0), myitem(1), myitem(2), myitem(3)))    'Add the found item
        Sleep(0)
    End If
End Sub
Your code above says what as int, maybe I should make it "col As int"
 
Upvote 0

Shelby

Well-Known Member
Licensed User
Wow, when I commented out my fast search sub and substituted your sub, it seems to be totally revised to where I want it. In other words, I searched pipe and dog; it ignored the irrelevant word dog. Thank you so much, but I'll let you know if there is a glitch. Yippee!! When I entered pipe dog wood in the search, Eureka!! it ignored the dog and gave me records with the words pipe or wood just as I have wanted all along. I'll search your info and see if I can give you some Euros or Franks or play money.
Shelbunny
By the way, I didn't change your wording (what As Int)
 
Last edited:
Upvote 0

Shelby

Well-Known Member
Licensed User
Maybe you're suggesting that in the first line of the sub I change (what as Int) to (what as index) ? My understanding of types and parameters is largely inadequate. Or Dim myitem() as Index? I'll be studying your sub compared to the sub I had. Also I just subscribed on your youtube channel.
 
Last edited:
Upvote 0

aeric

Expert
Licensed User
Longtime User
I believe my solution is working but it is not the actual code that you can take and use it out of the box.
In forum, we not always get the exact answer and use it like a product.
Members will give you idea and you need to learn how to use the idea by doing experiments. From there you will learn how things work.
We should not just blindly take what it is. Try. Until give up.
The search query is working with filter and translate to SQL become “WHERE” keyword. Then you have the option to use exact search or wildcard search. Exact matching is dealing with “=“ while wildcard is dealing with “LIKE” and “%” whether it is put in “before” or “after” the search keyword. You need to learn some basic SQL to understand how it works. When dealing with multiple search keywords, then we need to use more “LIKE” inside the query. While you can rely on B4X to do the loop, SQL library can do the work more efficiently in my opinion.

I recommend you to learn on SQL if you are developing database app.
 
Upvote 0

Shelby

Well-Known Member
Licensed User
Your kindness of explaining things to me is touching. Thanks Aeric. I see that the word 'like' is occurring only one time in my B4XMainPage module and it was there before Rraswisak gave me the solution which answers my requests to allow an unlimited number of keywords (relevant or irrelevant to my records).
 
Last edited:
Upvote 0

Shelby

Well-Known Member
Licensed User
Leave the data type as is, the parameter name i suggested is 'row' or 'index' rather than 'what', so it would be (row as int) or (index as int)
Thanks again, I did notice that the compiling is accomplished without naming the type. Maybe I'll experiment changing those parameters as you show here. My IDE does show a squiggly line under and say 'Variable declaration type is missing. String type will be used'; but it's referring to the strings Regex.Split and SearchPattern. Still it compiles fine.
 
Last edited:
Upvote 0

emexes

Expert
Licensed User
I did notice that the compiling is accomplished without naming the type. Maybe I'll experiment changing those parameters as you show here.

This might give you a handle on what's going on under the hood. No coffee required, but no warranty supplied either. 🙃

This Sub to convert a typed-in search string (possibly multiple words) to the "WHERE ..." part of a SQL query:

B4X:
Sub ShelbyWhere(SearchWords As String) As String
  
    Log("SearchWords = """ & SearchWords & """")
  
    Dim Words() As String = Regex.Split(" ", SearchWords.Trim)

    Dim sb As StringBuilder
    sb.Initialize
  
    For I = 0 To Words.Length - 1
        Dim ThisWord As String = Words(I).Trim
        Logg("Word " & I & " = """ & ThisWord & """")
      
        If ThisWord.Length <> 0 Then
            ThisWord = ThisWord.Replace("'", "_")    'change apostrophe to match-any-character
            ThisWord = ThisWord.Replace("%", "_")    'change percentage to match-any-character
            ThisWord = ThisWord.Replace("*", "%")    'change commonly-known * wildcard to SQL wildcard %
            ThisWord = ThisWord.Replace("?", "_")    'change commonly-known ? wildcard to SQL wildcard _
          
            ThisWord = "%" & ThisWord & "%"          'add leading and trailing wildcards to do ".Contains" match
            ThisWord = ThisWord.Replace("%%", "%")   'to be sure, to be sure
          
            If sb.Length <> 0 Then
                sb.Append(" OR ")
            End If
          
            sb.Append("DescriptionSubjectLookup LIKE '" & ThisWord & "'")
        End If                     
    Next
  
    If sb.Length = 0 Then
        Return ""
    Else
        Return "WHERE " & sb.ToString
    End If
  
End Sub

This Sub to search your database and display all records matching the "WHERE ..." generated above:

B4X:
Sub ShelbyShow(WhereClause As String)
  
    Dim sql As SQL
    sql.Initialize(File.DirInternal, "dbircv11.db", False)
  
    Dim FullQuery As String = ("SELECT DescriptionSubjectLookup, TableNumber, rowid FROM TableList221 " & WhereClause).Trim
    Log("WhereClause = " & WhereClause)
    Log("FullQuery = " & FullQuery)
  
    Dim csr As Cursor = sql.ExecQuery(FullQuery)
  
    Log("RowCount = " & csr.RowCount)
    Log("ColumnCount = " & csr.ColumnCount)
  
    For I = 0 To csr.RowCount - 1
        If I < 20 Then
            csr.Position = I
            Log(I & TAB & csr.GetString2(2) & TAB & csr.GetString2(1) & TAB & csr.GetString2(0))
        Else
            Log("and another " & (csr.RowCount - I) & " rows...")
            Exit
        End If
    Next
  
    sql.Close
  
End Sub

Test it with:

B4X:
ShelbyShow(ShelbyWhere(""))
ShelbyShow(ShelbyWhere("index"))
ShelbyShow(ShelbyWhere("brace"))
ShelbyShow(ShelbyWhere("build"))
ShelbyShow(ShelbyWhere("freez"))
ShelbyShow(ShelbyWhere("roosters"))
ShelbyShow(ShelbyWhere("chickens"))
ShelbyShow(ShelbyWhere("seis"))
ShelbyShow(ShelbyWhere("seis roosters"))
ShelbyShow(ShelbyWhere("chickens seis roosters"))

and with luck you'll get something like this:

Log output:
Logger connected to:  HMD Global Nokia C01 Plus
--------- beginning of system
--------- beginning of main
Call B4XPages.GetManager.LogEvents = True to enable logging B4XPages events.
SearchWords = ""
Word 0 = ""
WhereClause =
FullQuery = SELECT DescriptionSubjectLookup, TableNumber, rowid FROM TableList221
RowCount = 449
ColumnCount = 3
0    1    R403.3(2)pg1    A Tues 12/06/22  Air-Freezing Index For U.S. Locations (State, County, index) Alabama-Indiana pg1
1    3    R702.4.2    Backer Board Materials  (Material, Standard)
2    4    R404.1.2(2)    Foundation Concrete Basement Walls Vertical Reinforcement 6 Inch Thickness (Height, Backfill, Bar, Spacing, Soil)
3    5    R404.1.2(1)    Foundation Concrete Basement Walls Horizontal Reinforcement (Height, Location)
4    6    R602.10.1.3    Braced Wall Line Spacing (Application, Condition, Building Type, Spacing)
5    7    R602.10.5    Braced Wall Panel Minimum Length (Method, Minimum Length, Contributing Length)
6    8    R602.10.5.2    Braced Wall Panel -Partial Credit- Less Than 4' Length (Length, Wall Height)
7    9    R602.10.6.1    Braced Wall Panels - Minimum Hold-Down Forces (Seismic Category & Wind Speed, Supporting/Story, Hold-Down Force)
8    10    R602.10.6.4    Braced Wall Tension Strap Resisting Wind Pressure Capacity (Stud Size & Grade, Pony Height, Opening, Tension Strap Capacity)
9    11    R602.10.3(3)pg1    Bracing Wall Based On Seismic Design( Wall Dimensions, Methods, Panels) pg1
10    12    R602.10.4pg1    Bracing Wall Methods (Method, Thickness, Materials, Figure, Fasteners, Spacing) pg1
11    13    R602.10.3(1)pg1    Bracing Walls Requirements Based On Wind Speed (Exposure Category, Minimum Panels) pg1
12    14    R602.10.6.5    Bracing Wall Requirements Brick Veneer-Wood Structural Panels (Seismic Category, Story,  Wall Line Length, Hold-Down Force)
13    15    R602.10.3(4)    Bracing Wall Seismic Adjustment Factors (Basis, Story, Condition, Methods)
14    16    R602.12.4    Bracing Wall Minimum Number Of Units Requirement- Sides Of Rectangle (Wind Speed, Story, Ridge Height, Number Of Units)
15    17    R602.10.3(2)    Bracing Wall Wind Adjustment Factors (Item Number, Adjustment, Stories For Support, Condition, Methods)
16    18    R502.3.3(1)    Cantilever Spans-Supporting Wall & Roof, Exterior Balcony (Member & Spacing, Maximum Cantilever Span [Snow, Roof Width])
17    19    R802.5.1(1)pg1    Ceiling Joist Spans, Uninhabitable Attics-Without Storage, Live Load 10 (Spacing, Species, Dead Load, Joist Size) pg 1
18    20    R804.3.1.1(1)    Ceiling Joist Steel Spans Live Load 10-No Attic Storage (Member, Allowable Span, Bracing)
19    22    N1101.7.2(2)    Climate Zone Definitions International  (Zone Number,Thermal Criteria)
and another 429 rows...
SearchWords = "index"
Word 0 = "index"
WhereClause = WHERE DescriptionSubjectLookup LIKE '%index%'
FullQuery = SELECT DescriptionSubjectLookup, TableNumber, rowid FROM TableList221 WHERE DescriptionSubjectLookup LIKE '%index%'
RowCount = 7
ColumnCount = 3
0    1    R403.3(2)pg1    A Tues 12/06/22  Air-Freezing Index For U.S. Locations (State, County, index) Alabama-Indiana pg1
1    71    N1106.4    Energy Maximum Rating Index (Climate Zone, Rating Iindex)
2    94    R403.3(1)    Footings- Frost-Protected Depth & Insulation (Freeze Index, Vertical & Horizontal R-value)
3    487    R403.3(2)pg2    Air-Freezing Index For U.S. Locations (State, County, index) Iowa-Minnesota pg2
4    488    R403.3(2)pg3    Air-Freezing Index For U.S. Locations (State, County, index) Mississippi-North Carolina pg3
5    489    R403.3(2)pg4    Air-Freezing Index For U.S. Locations (State, County, index) North Dakota-Utah pg4
6    490    R403.3(2)pg5    Air-Freezing Index For U.S. Locations (State, County, index) Vermont-Wyoming pg5
SearchWords = "brace"
Word 0 = "brace"
WhereClause = WHERE DescriptionSubjectLookup LIKE '%brace%'
FullQuery = SELECT DescriptionSubjectLookup, TableNumber, rowid FROM TableList221 WHERE DescriptionSubjectLookup LIKE '%brace%'
RowCount = 5
ColumnCount = 3
0    6    R602.10.1.3    Braced Wall Line Spacing (Application, Condition, Building Type, Spacing)
1    7    R602.10.5    Braced Wall Panel Minimum Length (Method, Minimum Length, Contributing Length)
2    8    R602.10.5.2    Braced Wall Panel -Partial Credit- Less Than 4' Length (Length, Wall Height)
3    9    R602.10.6.1    Braced Wall Panels - Minimum Hold-Down Forces (Seismic Category & Wind Speed, Supporting/Story, Hold-Down Force)
4    10    R602.10.6.4    Braced Wall Tension Strap Resisting Wind Pressure Capacity (Stud Size & Grade, Pony Height, Opening, Tension Strap Capacity)
SearchWords = "build"
Word 0 = "build"
WhereClause = WHERE DescriptionSubjectLookup LIKE '%build%'
FullQuery = SELECT DescriptionSubjectLookup, TableNumber, rowid FROM TableList221 WHERE DescriptionSubjectLookup LIKE '%build%'
RowCount = 19
ColumnCount = 3
0    6    R602.10.1.3    Braced Wall Line Spacing (Application, Condition, Building Type, Spacing)
1    70    N1105.5.2(1)pg1    Energy Efficiency Design Specifications For Building Components (Component, Standard) pg1
2    123    R301.5    Live Loads-Minimum Uniformly Distributed (Use In Building Areas, Live Load)
3    186    R610.5(1)    Wall SIP Minimum Thickness Supports SIP Or Light-Frame Roof (Building Width, Wind, Snow Load)
4    187    R610.5(2)    Wall SIP Minimum Thickness Supports SIP Or Light-Frame 1 Story & Roof (Building Width, Wind, Snow load)
5    200    R603.3.2(2)    Stud Steel Sizing Roof & Ceiling 28' (Width Of Building, Wind, Category, Stud Thickness, Snow)
6    214    R603.3.2(1)    Wall  Fastening Schedule [Steel] (Building Element, Number Of Fasteners, Spacing)
7    257    G2413.4(19)    Pipe-Propane Polyethylene Plastic 11PSI Drop .5 PSI [Capacity Per Sizing Regulator to Building] (Use, Length, Diameter, Capacity Btu/Hr.)
8    258    G2413.4(21)    Pipe-Propane Polyethylene Plastic 11 PSI Drop .5 PSI [Capacity Per Sizing Regulator to Building] (Length, Diameter, Capacity Cu.Ft./Hr.)
9    297    P3002.1(2)    Sanitary Drainage & Vent Pipe Underground Building Standard (Pipe Type, Standard)
10    298    P3002.2    Sanitary Building Sewer Pipe Standard (Material, Standard)
11    303    P3005.4.2    Sanitary Fixture Units Allowed Connected Building Drain, Branches Or Sewer (Pipe Diameter, Slope Per Foot)
12    376    R505.3.1(2)    Floor Fastening Schedule (Building Elements, Number & size Fasteners, Spacing)
13    388    R603.3.2(4)    Stud Steel Sizing Roof & Ceiling 32' (Width Of Building, Wind, Category, Stud Thickness, Snow)
14    389    R603.3.2(5)    Stud Steel Sizing Roof & Ceiling 36' (Width Of Building, Wind, Category, Stud Thickness, Snow)
15    390    R603.3.2(6)    Stud Steel Sizing Roof & Ceiling 40' (Width Of Building, Wind, Category, Stud Thickness, Snow)
16    391    R603.3.2(3)    Stud Steel Sizing Roof & Ceiling (Width Of Building, Wind, Category, Stud Thickness, Snow)
17    463    N1105.5.2(1)pg2    Energy Efficiency Design Specifications For Building Components (Component, Standard) pg2
18    464    N1105.5.2(1)pg3    Energy Efficiency Design Specifications For Building Components (Component, Standard) pg3
SearchWords = "freez"
Word 0 = "freez"
WhereClause = WHERE DescriptionSubjectLookup LIKE '%freez%'
FullQuery = SELECT DescriptionSubjectLookup, TableNumber, rowid FROM TableList221 WHERE DescriptionSubjectLookup LIKE '%freez%'
RowCount = 7
ColumnCount = 3
0    1    R403.3(2)pg1    A Tues 12/06/22  Air-Freezing Index For U.S. Locations (State, County, index) Alabama-Indiana pg1
1    24    R301.2(1)    Climatic Design Criteria (Wind Design, Damage from, Ice, Flood, Freezing)
2    94    R403.3(1)    Footings- Frost-Protected Depth & Insulation (Freeze Index, Vertical & Horizontal R-value)
3    487    R403.3(2)pg2    Air-Freezing Index For U.S. Locations (State, County, index) Iowa-Minnesota pg2
4    488    R403.3(2)pg3    Air-Freezing Index For U.S. Locations (State, County, index) Mississippi-North Carolina pg3
5    489    R403.3(2)pg4    Air-Freezing Index For U.S. Locations (State, County, index) North Dakota-Utah pg4
6    490    R403.3(2)pg5    Air-Freezing Index For U.S. Locations (State, County, index) Vermont-Wyoming pg5
SearchWords = "roosters"
Word 0 = "roosters"
WhereClause = WHERE DescriptionSubjectLookup LIKE '%roosters%'
FullQuery = SELECT DescriptionSubjectLookup, TableNumber, rowid FROM TableList221 WHERE DescriptionSubjectLookup LIKE '%roosters%'
RowCount = 0
ColumnCount = 3
SearchWords = "chickens"
Word 0 = "chickens"
WhereClause = WHERE DescriptionSubjectLookup LIKE '%chickens%'
FullQuery = SELECT DescriptionSubjectLookup, TableNumber, rowid FROM TableList221 WHERE DescriptionSubjectLookup LIKE '%chickens%'
RowCount = 0
ColumnCount = 3
SearchWords = "seis"
Word 0 = "seis"
WhereClause = WHERE DescriptionSubjectLookup LIKE '%seis%'
FullQuery = SELECT DescriptionSubjectLookup, TableNumber, rowid FROM TableList221 WHERE DescriptionSubjectLookup LIKE '%seis%'
RowCount = 12
ColumnCount = 3
0    9    R602.10.6.1    Braced Wall Panels - Minimum Hold-Down Forces (Seismic Category & Wind Speed, Supporting/Story, Hold-Down Force)
1    11    R602.10.3(3)pg1    Bracing Wall Based On Seismic Design( Wall Dimensions, Methods, Panels) pg1
2    14    R602.10.6.5    Bracing Wall Requirements Brick Veneer-Wood Structural Panels (Seismic Category, Story,  Wall Line Length, Hold-Down Force)
3    15    R602.10.3(4)    Bracing Wall Seismic Adjustment Factors (Basis, Story, Condition, Methods)
4    126    R606.12.4.2    Masonry Stacked Bonded Seismic D2, Lateral Resisting (Thickness, Bar Size Spaced 16" o.c.)
5    127    R606.12.4.1    Masonry Stacked Bonded Wall In Seismic D2,Non Lateral Resisting (Thickness, Bar Size Spaced 24" o.c.)
6    132    R606.12.2.1    Masonry Wall Min Length Ext. (Seismic, Top Story, Light-Frame, Masonry 2nd Story,Roof)
7    134    R606.12.3.2    Masonry Wall Reinforcement Seismic (Thickness, Sum-Rebar-Areas, Bar-Size)
8    179    R301.2.2.1.1    Seismic Design Category Determination (Calculated Speeds, Seismic Design Category)
9    210    R703.8(1)    Veneer Stone Or Masonry Seismic Limitations & Requirements (Seismic Design Category- A,B,C  Number Of Stories, Height, Thickness, Weight)
10    386    R602.10.3(3)pg2    Bracing Wall Based On Seismic Design( Wall Dimensions, Methods, Panels) pg2
11    422    R703.8(2)    Veneer Stone Or Masonry Seismic Limitations & Requirements (Seismic Design Category- D0,D1,D2  Number Of Stories, Height, Thickness, Weight)
SearchWords = "seis roosters"
Word 0 = "seis"
Word 1 = "roosters"
WhereClause = WHERE DescriptionSubjectLookup LIKE '%seis%' OR DescriptionSubjectLookup LIKE '%roosters%'
FullQuery = SELECT DescriptionSubjectLookup, TableNumber, rowid FROM TableList221 WHERE DescriptionSubjectLookup LIKE '%seis%' OR DescriptionSubjectLookup LIKE '%roosters%'
RowCount = 12
ColumnCount = 3
0    9    R602.10.6.1    Braced Wall Panels - Minimum Hold-Down Forces (Seismic Category & Wind Speed, Supporting/Story, Hold-Down Force)
1    11    R602.10.3(3)pg1    Bracing Wall Based On Seismic Design( Wall Dimensions, Methods, Panels) pg1
2    14    R602.10.6.5    Bracing Wall Requirements Brick Veneer-Wood Structural Panels (Seismic Category, Story,  Wall Line Length, Hold-Down Force)
3    15    R602.10.3(4)    Bracing Wall Seismic Adjustment Factors (Basis, Story, Condition, Methods)
4    126    R606.12.4.2    Masonry Stacked Bonded Seismic D2, Lateral Resisting (Thickness, Bar Size Spaced 16" o.c.)
5    127    R606.12.4.1    Masonry Stacked Bonded Wall In Seismic D2,Non Lateral Resisting (Thickness, Bar Size Spaced 24" o.c.)
6    132    R606.12.2.1    Masonry Wall Min Length Ext. (Seismic, Top Story, Light-Frame, Masonry 2nd Story,Roof)
7    134    R606.12.3.2    Masonry Wall Reinforcement Seismic (Thickness, Sum-Rebar-Areas, Bar-Size)
8    179    R301.2.2.1.1    Seismic Design Category Determination (Calculated Speeds, Seismic Design Category)
9    210    R703.8(1)    Veneer Stone Or Masonry Seismic Limitations & Requirements (Seismic Design Category- A,B,C  Number Of Stories, Height, Thickness, Weight)
10    386    R602.10.3(3)pg2    Bracing Wall Based On Seismic Design( Wall Dimensions, Methods, Panels) pg2
11    422    R703.8(2)    Veneer Stone Or Masonry Seismic Limitations & Requirements (Seismic Design Category- D0,D1,D2  Number Of Stories, Height, Thickness, Weight)
SearchWords = "chickens seis roosters"
Word 0 = "chickens"
Word 1 = "seis"
Word 2 = "roosters"
WhereClause = WHERE DescriptionSubjectLookup LIKE '%chickens%' OR DescriptionSubjectLookup LIKE '%seis%' OR DescriptionSubjectLookup LIKE '%roosters%'
FullQuery = SELECT DescriptionSubjectLookup, TableNumber, rowid FROM TableList221 WHERE DescriptionSubjectLookup LIKE '%chickens%' OR DescriptionSubjectLookup LIKE '%seis%' OR DescriptionSubjectLookup LIKE '%roosters%'
RowCount = 12
ColumnCount = 3
0    9    R602.10.6.1    Braced Wall Panels - Minimum Hold-Down Forces (Seismic Category & Wind Speed, Supporting/Story, Hold-Down Force)
1    11    R602.10.3(3)pg1    Bracing Wall Based On Seismic Design( Wall Dimensions, Methods, Panels) pg1
2    14    R602.10.6.5    Bracing Wall Requirements Brick Veneer-Wood Structural Panels (Seismic Category, Story,  Wall Line Length, Hold-Down Force)
3    15    R602.10.3(4)    Bracing Wall Seismic Adjustment Factors (Basis, Story, Condition, Methods)
4    126    R606.12.4.2    Masonry Stacked Bonded Seismic D2, Lateral Resisting (Thickness, Bar Size Spaced 16" o.c.)
5    127    R606.12.4.1    Masonry Stacked Bonded Wall In Seismic D2,Non Lateral Resisting (Thickness, Bar Size Spaced 24" o.c.)
6    132    R606.12.2.1    Masonry Wall Min Length Ext. (Seismic, Top Story, Light-Frame, Masonry 2nd Story,Roof)
7    134    R606.12.3.2    Masonry Wall Reinforcement Seismic (Thickness, Sum-Rebar-Areas, Bar-Size)
8    179    R301.2.2.1.1    Seismic Design Category Determination (Calculated Speeds, Seismic Design Category)
9    210    R703.8(1)    Veneer Stone Or Masonry Seismic Limitations & Requirements (Seismic Design Category- A,B,C  Number Of Stories, Height, Thickness, Weight)
10    386    R602.10.3(3)pg2    Bracing Wall Based On Seismic Design( Wall Dimensions, Methods, Panels) pg2
11    422    R703.8(2)    Veneer Stone Or Masonry Seismic Limitations & Requirements (Seismic Design Category- D0,D1,D2  Number Of Stories, Height, Thickness, Weight)
 
Upvote 0

Shelby

Well-Known Member
Licensed User
This might give you a handle on what's going on under the hood. No coffee required, but no warranty supplied either. 🙃

This Sub to convert a typed-in search string (possibly multiple words) to the "WHERE ..." part of a SQL query:

B4X:
Sub ShelbyWhere(SearchWords As String) As String
 
    Log("SearchWords = """ & SearchWords & """")
 
    Dim Words() As String = Regex.Split(" ", SearchWords.Trim)

    Dim sb As StringBuilder
    sb.Initialize
 
    For I = 0 To Words.Length - 1
        Dim ThisWord As String = Words(I).Trim
        Logg("Word " & I & " = """ & ThisWord & """")
     
        If ThisWord.Length <> 0 Then
            ThisWord = ThisWord.Replace("'", "_")    'change apostrophe to match-any-character
            ThisWord = ThisWord.Replace("%", "_")    'change percentage to match-any-character
            ThisWord = ThisWord.Replace("*", "%")    'change commonly-known * wildcard to SQL wildcard %
            ThisWord = ThisWord.Replace("?", "_")    'change commonly-known ? wildcard to SQL wildcard _
         
            ThisWord = "%" & ThisWord & "%"          'add leading and trailing wildcards to do ".Contains" match
            ThisWord = ThisWord.Replace("%%", "%")   'to be sure, to be sure
         
            If sb.Length <> 0 Then
                sb.Append(" OR ")
            End If
         
            sb.Append("DescriptionSubjectLookup LIKE '" & ThisWord & "'")
        End If                    
    Next
 
    If sb.Length = 0 Then
        Return ""
    Else
        Return "WHERE " & sb.ToString
    End If
 
End Sub

This Sub to search your database and display all records matching the "WHERE ..." generated above:

B4X:
Sub ShelbyShow(WhereClause As String)
 
    Dim sql As SQL
    sql.Initialize(File.DirInternal, "dbircv11.db", False)
 
    Dim FullQuery As String = ("SELECT DescriptionSubjectLookup, TableNumber, rowid FROM TableList221 " & WhereClause).Trim
    Log("WhereClause = " & WhereClause)
    Log("FullQuery = " & FullQuery)
 
    Dim csr As Cursor = sql.ExecQuery(FullQuery)
 
    Log("RowCount = " & csr.RowCount)
    Log("ColumnCount = " & csr.ColumnCount)
 
    For I = 0 To csr.RowCount - 1
        If I < 20 Then
            csr.Position = I
            Log(I & TAB & csr.GetString2(2) & TAB & csr.GetString2(1) & TAB & csr.GetString2(0))
        Else
            Log("and another " & (csr.RowCount - I) & " rows...")
            Exit
        End If
    Next
 
    sql.Close
 
End Sub

Test it with:

B4X:
ShelbyShow(ShelbyWhere(""))
ShelbyShow(ShelbyWhere("index"))
ShelbyShow(ShelbyWhere("brace"))
ShelbyShow(ShelbyWhere("build"))
ShelbyShow(ShelbyWhere("freez"))
ShelbyShow(ShelbyWhere("roosters"))
ShelbyShow(ShelbyWhere("chickens"))
ShelbyShow(ShelbyWhere("seis"))
ShelbyShow(ShelbyWhere("seis roosters"))
ShelbyShow(ShelbyWhere("chickens seis roosters"))

and with luck you'll get something like this:

Log output:
Logger connected to:  HMD Global Nokia C01 Plus
--------- beginning of system
--------- beginning of main
Call B4XPages.GetManager.LogEvents = True to enable logging B4XPages events.
SearchWords = ""
Word 0 = ""
WhereClause =
FullQuery = SELECT DescriptionSubjectLookup, TableNumber, rowid FROM TableList221
RowCount = 449
ColumnCount = 3
0    1    R403.3(2)pg1    A Tues 12/06/22  Air-Freezing Index For U.S. Locations (State, County, index) Alabama-Indiana pg1
1    3    R702.4.2    Backer Board Materials  (Material, Standard)
2    4    R404.1.2(2)    Foundation Concrete Basement Walls Vertical Reinforcement 6 Inch Thickness (Height, Backfill, Bar, Spacing, Soil)
3    5    R404.1.2(1)    Foundation Concrete Basement Walls Horizontal Reinforcement (Height, Location)
4    6    R602.10.1.3    Braced Wall Line Spacing (Application, Condition, Building Type, Spacing)
5    7    R602.10.5    Braced Wall Panel Minimum Length (Method, Minimum Length, Contributing Length)
6    8    R602.10.5.2    Braced Wall Panel -Partial Credit- Less Than 4' Length (Length, Wall Height)
7    9    R602.10.6.1    Braced Wall Panels - Minimum Hold-Down Forces (Seismic Category & Wind Speed, Supporting/Story, Hold-Down Force)
8    10    R602.10.6.4    Braced Wall Tension Strap Resisting Wind Pressure Capacity (Stud Size & Grade, Pony Height, Opening, Tension Strap Capacity)
9    11    R602.10.3(3)pg1    Bracing Wall Based On Seismic Design( Wall Dimensions, Methods, Panels) pg1
10    12    R602.10.4pg1    Bracing Wall Methods (Method, Thickness, Materials, Figure, Fasteners, Spacing) pg1
11    13    R602.10.3(1)pg1    Bracing Walls Requirements Based On Wind Speed (Exposure Category, Minimum Panels) pg1
12    14    R602.10.6.5    Bracing Wall Requirements Brick Veneer-Wood Structural Panels (Seismic Category, Story,  Wall Line Length, Hold-Down Force)
13    15    R602.10.3(4)    Bracing Wall Seismic Adjustment Factors (Basis, Story, Condition, Methods)
14    16    R602.12.4    Bracing Wall Minimum Number Of Units Requirement- Sides Of Rectangle (Wind Speed, Story, Ridge Height, Number Of Units)
15    17    R602.10.3(2)    Bracing Wall Wind Adjustment Factors (Item Number, Adjustment, Stories For Support, Condition, Methods)
16    18    R502.3.3(1)    Cantilever Spans-Supporting Wall & Roof, Exterior Balcony (Member & Spacing, Maximum Cantilever Span [Snow, Roof Width])
17    19    R802.5.1(1)pg1    Ceiling Joist Spans, Uninhabitable Attics-Without Storage, Live Load 10 (Spacing, Species, Dead Load, Joist Size) pg 1
18    20    R804.3.1.1(1)    Ceiling Joist Steel Spans Live Load 10-No Attic Storage (Member, Allowable Span, Bracing)
19    22    N1101.7.2(2)    Climate Zone Definitions International  (Zone Number,Thermal Criteria)
and another 429 rows...
SearchWords = "index"
Word 0 = "index"
WhereClause = WHERE DescriptionSubjectLookup LIKE '%index%'
FullQuery = SELECT DescriptionSubjectLookup, TableNumber, rowid FROM TableList221 WHERE DescriptionSubjectLookup LIKE '%index%'
RowCount = 7
ColumnCount = 3
0    1    R403.3(2)pg1    A Tues 12/06/22  Air-Freezing Index For U.S. Locations (State, County, index) Alabama-Indiana pg1
1    71    N1106.4    Energy Maximum Rating Index (Climate Zone, Rating Iindex)
2    94    R403.3(1)    Footings- Frost-Protected Depth & Insulation (Freeze Index, Vertical & Horizontal R-value)
3    487    R403.3(2)pg2    Air-Freezing Index For U.S. Locations (State, County, index) Iowa-Minnesota pg2
4    488    R403.3(2)pg3    Air-Freezing Index For U.S. Locations (State, County, index) Mississippi-North Carolina pg3
5    489    R403.3(2)pg4    Air-Freezing Index For U.S. Locations (State, County, index) North Dakota-Utah pg4
6    490    R403.3(2)pg5    Air-Freezing Index For U.S. Locations (State, County, index) Vermont-Wyoming pg5
SearchWords = "brace"
Word 0 = "brace"
WhereClause = WHERE DescriptionSubjectLookup LIKE '%brace%'
FullQuery = SELECT DescriptionSubjectLookup, TableNumber, rowid FROM TableList221 WHERE DescriptionSubjectLookup LIKE '%brace%'
RowCount = 5
ColumnCount = 3
0    6    R602.10.1.3    Braced Wall Line Spacing (Application, Condition, Building Type, Spacing)
1    7    R602.10.5    Braced Wall Panel Minimum Length (Method, Minimum Length, Contributing Length)
2    8    R602.10.5.2    Braced Wall Panel -Partial Credit- Less Than 4' Length (Length, Wall Height)
3    9    R602.10.6.1    Braced Wall Panels - Minimum Hold-Down Forces (Seismic Category & Wind Speed, Supporting/Story, Hold-Down Force)
4    10    R602.10.6.4    Braced Wall Tension Strap Resisting Wind Pressure Capacity (Stud Size & Grade, Pony Height, Opening, Tension Strap Capacity)
SearchWords = "build"
Word 0 = "build"
WhereClause = WHERE DescriptionSubjectLookup LIKE '%build%'
FullQuery = SELECT DescriptionSubjectLookup, TableNumber, rowid FROM TableList221 WHERE DescriptionSubjectLookup LIKE '%build%'
RowCount = 19
ColumnCount = 3
0    6    R602.10.1.3    Braced Wall Line Spacing (Application, Condition, Building Type, Spacing)
1    70    N1105.5.2(1)pg1    Energy Efficiency Design Specifications For Building Components (Component, Standard) pg1
2    123    R301.5    Live Loads-Minimum Uniformly Distributed (Use In Building Areas, Live Load)
3    186    R610.5(1)    Wall SIP Minimum Thickness Supports SIP Or Light-Frame Roof (Building Width, Wind, Snow Load)
4    187    R610.5(2)    Wall SIP Minimum Thickness Supports SIP Or Light-Frame 1 Story & Roof (Building Width, Wind, Snow load)
5    200    R603.3.2(2)    Stud Steel Sizing Roof & Ceiling 28' (Width Of Building, Wind, Category, Stud Thickness, Snow)
6    214    R603.3.2(1)    Wall  Fastening Schedule [Steel] (Building Element, Number Of Fasteners, Spacing)
7    257    G2413.4(19)    Pipe-Propane Polyethylene Plastic 11PSI Drop .5 PSI [Capacity Per Sizing Regulator to Building] (Use, Length, Diameter, Capacity Btu/Hr.)
8    258    G2413.4(21)    Pipe-Propane Polyethylene Plastic 11 PSI Drop .5 PSI [Capacity Per Sizing Regulator to Building] (Length, Diameter, Capacity Cu.Ft./Hr.)
9    297    P3002.1(2)    Sanitary Drainage & Vent Pipe Underground Building Standard (Pipe Type, Standard)
10    298    P3002.2    Sanitary Building Sewer Pipe Standard (Material, Standard)
11    303    P3005.4.2    Sanitary Fixture Units Allowed Connected Building Drain, Branches Or Sewer (Pipe Diameter, Slope Per Foot)
12    376    R505.3.1(2)    Floor Fastening Schedule (Building Elements, Number & size Fasteners, Spacing)
13    388    R603.3.2(4)    Stud Steel Sizing Roof & Ceiling 32' (Width Of Building, Wind, Category, Stud Thickness, Snow)
14    389    R603.3.2(5)    Stud Steel Sizing Roof & Ceiling 36' (Width Of Building, Wind, Category, Stud Thickness, Snow)
15    390    R603.3.2(6)    Stud Steel Sizing Roof & Ceiling 40' (Width Of Building, Wind, Category, Stud Thickness, Snow)
16    391    R603.3.2(3)    Stud Steel Sizing Roof & Ceiling (Width Of Building, Wind, Category, Stud Thickness, Snow)
17    463    N1105.5.2(1)pg2    Energy Efficiency Design Specifications For Building Components (Component, Standard) pg2
18    464    N1105.5.2(1)pg3    Energy Efficiency Design Specifications For Building Components (Component, Standard) pg3
SearchWords = "freez"
Word 0 = "freez"
WhereClause = WHERE DescriptionSubjectLookup LIKE '%freez%'
FullQuery = SELECT DescriptionSubjectLookup, TableNumber, rowid FROM TableList221 WHERE DescriptionSubjectLookup LIKE '%freez%'
RowCount = 7
ColumnCount = 3
0    1    R403.3(2)pg1    A Tues 12/06/22  Air-Freezing Index For U.S. Locations (State, County, index) Alabama-Indiana pg1
1    24    R301.2(1)    Climatic Design Criteria (Wind Design, Damage from, Ice, Flood, Freezing)
2    94    R403.3(1)    Footings- Frost-Protected Depth & Insulation (Freeze Index, Vertical & Horizontal R-value)
3    487    R403.3(2)pg2    Air-Freezing Index For U.S. Locations (State, County, index) Iowa-Minnesota pg2
4    488    R403.3(2)pg3    Air-Freezing Index For U.S. Locations (State, County, index) Mississippi-North Carolina pg3
5    489    R403.3(2)pg4    Air-Freezing Index For U.S. Locations (State, County, index) North Dakota-Utah pg4
6    490    R403.3(2)pg5    Air-Freezing Index For U.S. Locations (State, County, index) Vermont-Wyoming pg5
SearchWords = "roosters"
Word 0 = "roosters"
WhereClause = WHERE DescriptionSubjectLookup LIKE '%roosters%'
FullQuery = SELECT DescriptionSubjectLookup, TableNumber, rowid FROM TableList221 WHERE DescriptionSubjectLookup LIKE '%roosters%'
RowCount = 0
ColumnCount = 3
SearchWords = "chickens"
Word 0 = "chickens"
WhereClause = WHERE DescriptionSubjectLookup LIKE '%chickens%'
FullQuery = SELECT DescriptionSubjectLookup, TableNumber, rowid FROM TableList221 WHERE DescriptionSubjectLookup LIKE '%chickens%'
RowCount = 0
ColumnCount = 3
SearchWords = "seis"
Word 0 = "seis"
WhereClause = WHERE DescriptionSubjectLookup LIKE '%seis%'
FullQuery = SELECT DescriptionSubjectLookup, TableNumber, rowid FROM TableList221 WHERE DescriptionSubjectLookup LIKE '%seis%'
RowCount = 12
ColumnCount = 3
0    9    R602.10.6.1    Braced Wall Panels - Minimum Hold-Down Forces (Seismic Category & Wind Speed, Supporting/Story, Hold-Down Force)
1    11    R602.10.3(3)pg1    Bracing Wall Based On Seismic Design( Wall Dimensions, Methods, Panels) pg1
2    14    R602.10.6.5    Bracing Wall Requirements Brick Veneer-Wood Structural Panels (Seismic Category, Story,  Wall Line Length, Hold-Down Force)
3    15    R602.10.3(4)    Bracing Wall Seismic Adjustment Factors (Basis, Story, Condition, Methods)
4    126    R606.12.4.2    Masonry Stacked Bonded Seismic D2, Lateral Resisting (Thickness, Bar Size Spaced 16" o.c.)
5    127    R606.12.4.1    Masonry Stacked Bonded Wall In Seismic D2,Non Lateral Resisting (Thickness, Bar Size Spaced 24" o.c.)
6    132    R606.12.2.1    Masonry Wall Min Length Ext. (Seismic, Top Story, Light-Frame, Masonry 2nd Story,Roof)
7    134    R606.12.3.2    Masonry Wall Reinforcement Seismic (Thickness, Sum-Rebar-Areas, Bar-Size)
8    179    R301.2.2.1.1    Seismic Design Category Determination (Calculated Speeds, Seismic Design Category)
9    210    R703.8(1)    Veneer Stone Or Masonry Seismic Limitations & Requirements (Seismic Design Category- A,B,C  Number Of Stories, Height, Thickness, Weight)
10    386    R602.10.3(3)pg2    Bracing Wall Based On Seismic Design( Wall Dimensions, Methods, Panels) pg2
11    422    R703.8(2)    Veneer Stone Or Masonry Seismic Limitations & Requirements (Seismic Design Category- D0,D1,D2  Number Of Stories, Height, Thickness, Weight)
SearchWords = "seis roosters"
Word 0 = "seis"
Word 1 = "roosters"
WhereClause = WHERE DescriptionSubjectLookup LIKE '%seis%' OR DescriptionSubjectLookup LIKE '%roosters%'
FullQuery = SELECT DescriptionSubjectLookup, TableNumber, rowid FROM TableList221 WHERE DescriptionSubjectLookup LIKE '%seis%' OR DescriptionSubjectLookup LIKE '%roosters%'
RowCount = 12
ColumnCount = 3
0    9    R602.10.6.1    Braced Wall Panels - Minimum Hold-Down Forces (Seismic Category & Wind Speed, Supporting/Story, Hold-Down Force)
1    11    R602.10.3(3)pg1    Bracing Wall Based On Seismic Design( Wall Dimensions, Methods, Panels) pg1
2    14    R602.10.6.5    Bracing Wall Requirements Brick Veneer-Wood Structural Panels (Seismic Category, Story,  Wall Line Length, Hold-Down Force)
3    15    R602.10.3(4)    Bracing Wall Seismic Adjustment Factors (Basis, Story, Condition, Methods)
4    126    R606.12.4.2    Masonry Stacked Bonded Seismic D2, Lateral Resisting (Thickness, Bar Size Spaced 16" o.c.)
5    127    R606.12.4.1    Masonry Stacked Bonded Wall In Seismic D2,Non Lateral Resisting (Thickness, Bar Size Spaced 24" o.c.)
6    132    R606.12.2.1    Masonry Wall Min Length Ext. (Seismic, Top Story, Light-Frame, Masonry 2nd Story,Roof)
7    134    R606.12.3.2    Masonry Wall Reinforcement Seismic (Thickness, Sum-Rebar-Areas, Bar-Size)
8    179    R301.2.2.1.1    Seismic Design Category Determination (Calculated Speeds, Seismic Design Category)
9    210    R703.8(1)    Veneer Stone Or Masonry Seismic Limitations & Requirements (Seismic Design Category- A,B,C  Number Of Stories, Height, Thickness, Weight)
10    386    R602.10.3(3)pg2    Bracing Wall Based On Seismic Design( Wall Dimensions, Methods, Panels) pg2
11    422    R703.8(2)    Veneer Stone Or Masonry Seismic Limitations & Requirements (Seismic Design Category- D0,D1,D2  Number Of Stories, Height, Thickness, Weight)
SearchWords = "chickens seis roosters"
Word 0 = "chickens"
Word 1 = "seis"
Word 2 = "roosters"
WhereClause = WHERE DescriptionSubjectLookup LIKE '%chickens%' OR DescriptionSubjectLookup LIKE '%seis%' OR DescriptionSubjectLookup LIKE '%roosters%'
FullQuery = SELECT DescriptionSubjectLookup, TableNumber, rowid FROM TableList221 WHERE DescriptionSubjectLookup LIKE '%chickens%' OR DescriptionSubjectLookup LIKE '%seis%' OR DescriptionSubjectLookup LIKE '%roosters%'
RowCount = 12
ColumnCount = 3
0    9    R602.10.6.1    Braced Wall Panels - Minimum Hold-Down Forces (Seismic Category & Wind Speed, Supporting/Story, Hold-Down Force)
1    11    R602.10.3(3)pg1    Bracing Wall Based On Seismic Design( Wall Dimensions, Methods, Panels) pg1
2    14    R602.10.6.5    Bracing Wall Requirements Brick Veneer-Wood Structural Panels (Seismic Category, Story,  Wall Line Length, Hold-Down Force)
3    15    R602.10.3(4)    Bracing Wall Seismic Adjustment Factors (Basis, Story, Condition, Methods)
4    126    R606.12.4.2    Masonry Stacked Bonded Seismic D2, Lateral Resisting (Thickness, Bar Size Spaced 16" o.c.)
5    127    R606.12.4.1    Masonry Stacked Bonded Wall In Seismic D2,Non Lateral Resisting (Thickness, Bar Size Spaced 24" o.c.)
6    132    R606.12.2.1    Masonry Wall Min Length Ext. (Seismic, Top Story, Light-Frame, Masonry 2nd Story,Roof)
7    134    R606.12.3.2    Masonry Wall Reinforcement Seismic (Thickness, Sum-Rebar-Areas, Bar-Size)
8    179    R301.2.2.1.1    Seismic Design Category Determination (Calculated Speeds, Seismic Design Category)
9    210    R703.8(1)    Veneer Stone Or Masonry Seismic Limitations & Requirements (Seismic Design Category- A,B,C  Number Of Stories, Height, Thickness, Weight)
10    386    R602.10.3(3)pg2    Bracing Wall Based On Seismic Design( Wall Dimensions, Methods, Panels) pg2
11    422    R703.8(2)    Veneer Stone Or Masonry Seismic Limitations & Requirements (Seismic Design Category- D0,D1,D2  Number Of Stories, Height, Thickness, Weight)
Thanks, there was a time when I thought I needed a new column with the simple index numbers 1 through 4 hundred whatever. Mostly I thought it would make my manipulation of the db images easier. I still have some editing to do but I'll probably give up on that simple index column. Thanks for the after dinner snack. (i.e. food for thought).
 
Upvote 0

emexes

Expert
Licensed User
This might give you a handle on what's going on under the hood. No coffee required, but no warranty supplied either. 🙃

TBH all the while I was doing that, there was a little voice at the back of my head saying "it's under 100kB, why not just load it into a string array?"

Also, I've had an idea for a magic search, but I'm out of time to try it out today.
 
Upvote 0

aeric

Expert
Licensed User
Longtime User
I tried to make a random guess of the requirement. Here is a demo using B4XTable and B4XFloatTextField. (B4J and B4A) It supports up to 2 search keywords.

View attachment 136849
If anyone find this example interesting then you can replace the function as following to support unlimited search keywords.

B4X:
Private Sub FTFSearch_TextChanged (Old As String, New As String)
    Dim Data As List
    Data.Initialize
  
    If New.Trim = "" Then
        Dim strSQL As String = $"SELECT Item_Code, Item_Name, Item_Price, Item_Type FROM MD_Items"$
        Dim RS1 As ResultSet = DB.ExecQuery(strSQL)
    Else
        Dim search As List
        search.Initialize
      
        Dim words() As String = Regex.Split(" ", New)
        Dim strSQL As String = $"SELECT Item_Code, Item_Name, Item_Price, Item_Type FROM MD_Items WHERE"$
        For Each word In words
            If search.Size > 0 Then strSQL = strSQL & " OR"
            strSQL = strSQL & " Item_Name LIKE ?"
            search.Add("%" & word & "%")
        Next
        'Log( strSQL )
        'Log( search )
        #If B4A
        For i = 0 To words.Length - 1
            words(i) = search.Get(i)
        Next
        Dim RS1 As ResultSet = DB.ExecQuery2(strSQL, words)
        #Else If B4J
        Dim RS1 As ResultSet = DB.ExecQuery2(strSQL, search)
        #End If
    End If
  
    Do While RS1.NextRow
        Dim row1(4) As Object
        row1(0) = RS1.GetString("Item_Code")
        row1(1) = RS1.GetString("Item_Name")
        row1(2) = RS1.GetDouble("Item_Price")
        row1(3) = RS1.GetString("Item_Type")
        Data.Add(row1)
    Loop
    RS1.Close
    B4XTable1.SetData(Data)
    B4XTable1.Refresh
End Sub

Edit: B4A does not support List for second parameter in ExecQuery2.
 
Last edited:
Upvote 0

emexes

Expert
Licensed User
Mostly I thought it would make my manipulation of the db images easier. I still have some editing to do but I'll probably give up on that simple index column.

It probably would make things easier, and it already exists, but it's hidden by default. Every table has a column called rowid (unless you've decreed otherwise).

Try SELECT rowid, * FROM TableList221

or for a less-cluttered view, SELECT rowid FROM TableList221
 
Upvote 0

Shelby

Well-Known Member
Licensed User
It probably would make things easier, and it already exists, but it's hidden by default. Every table has a column called rowid (unless you've spurned that default).

Try SELECT rowid, * FROM TableList221

or for a less-cluttered view, SELECT rowid FROM TableList221
Wow, cool; I do think I read somewhere that it exists but I never tried to find if I could expose it or as you point out, use it.
Thanks again
Off to bed with a head cold developing in the last few hours.
Goodnight
 
Last edited:
Upvote 0
Top