Android Question Increase search ability SQLite (Solved)

[Shelby]

Below is my b4xMainPage module for my SQLite app
[CODE lang="b4x" title="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

, 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

, 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

, 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[/CODE]
. 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

 

[Shelby]

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....

 

[Shelby]

rraswisac, It turns out I had something similar at the end of my module, namely:
[CODE lang="b4x" title="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[/CODE]

 

[Shelby]

True, hens do construct items of interest. I love their structures. Whoops! I missed the sexual context, so.... ha ha! Belated

 

[rraswisak]

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

 

[Shelby]

Thanks Rraswisak, we'll see if I can make it work. Your description sounds like exactly what I want.

 

[Shelby]

Try this...

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"

 

[Shelby]

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)

 

[rraswisak]

The fact that i didn't change either, its on your original code, but if you want to change the parameter name, it should be row or index since the value is index of list

 

[Shelby]

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.

 

[rraswisak]

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)

 

[aeric]

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.

 

[Shelby]

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).

 

[Shelby]

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.

 

[emexes]

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:

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:

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:

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:

[CODE lang="json" title="Log output" highlight="5,9,32,36,45,49,56,60,81,85,94,98,100,104,106,110,124,129,143,149,"]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)[/CODE]

 

[Shelby]

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:

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:

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:

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:

[CODE lang="json" title="Log output" highlight="5,9,32,36,45,49,56,60,81,85,94,98,100,104,106,110,124,129,143,149,"]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)[/CODE]

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).

 

[emexes]

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.

 

[aeric]

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.

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.

 

[emexes]

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

 

[Shelby]

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