Just started to experiment with B4XTable, using the provided example project as provided by Erel in the first post regarding B4XTable.
I added some extra options to the search facility, edited the Subs:
Public Sub BuildQuery
Private Sub ImplUpdateDataFromQuery
All works perfectly fine, except when using the length function, which for some reason always produces zero.
All the above logs give the expected result, so I have no idea what the problem could be.
If I use the same SQL (so with the same data and with the SQLite length function) on a regular DB (so, not the in-memory database used
by B4XTable) then that gives me the expected result.
Any idea what the problem could here?
RBS
RBS
I added some extra options to the search facility, edited the Subs:
Public Sub BuildQuery
Private Sub ImplUpdateDataFromQuery
B4X:
Public Sub BuildQuery (WithPageLimit As Boolean) As Object()
Dim c As Int
Dim n As Int
Dim SortColumn As String
Dim strFind As String
Dim sb As StringBuilder
sb.Initialize
sb.Append("SELECT rowid")
For Each col As B4XTableColumn In VisibleColumns
If col.ColumnType = COLUMN_TYPE_VOID Then Continue
sb.Append(",").Append(col.SQLID)
If col.InternalSortMode <> "" Then
SortColumn = " ORDER BY " & col.SQLID & " " & col.InternalSortMode
End If
Next
sb.Append(" FROM ").Append(SQLTableName).Append(" ")
Dim args As List
args.Initialize
Dim TotalCount As Int
If iSearchConditions = 0 Then
TotalCount = CountAll
mCurrentCount = CountAll
miCountOfPages = Ceil(mCurrentCount / mRowsPerPage)
Else
For Each col As B4XTableColumn In VisibleColumns
If col.Searchable Then
For n = 0 To arrCheckConditions(c) - 1
strFind = arrConditions(c, n).strSearchText
Log("BuildQuery, strFind: " & strFind)
If args.Size = 0 Then
sb.Append(" WHERE ")
Else
If arrConditions(c, n).bSQL_OR Then
'this used to be all OR
sb.Append(" OR ")
Else
sb.Append(" AND ")
End If
End If
If col.ColumnType = COLUMN_TYPE_INTEGER Or col.ColumnType = COLUMN_TYPE_DOUBLE Then
If arrConditions(c, n).strOperator.Length = 0 Then
sb.Append(col.SQLID).Append(" = ? ")
Else
sb.Append(col.SQLID).Append(" ").Append(arrConditions(c, n).strOperator).Append(" ? ")
End If
args.Add(strFind)
Else
If arrConditions(c, n).strOperator.StartsWith("length") Then
'this doesn't work for some reason as it always produces zero for the length
Select Case arrConditions(c, n).strOperator
Case "length ="
sb.Append("length(").Append(col.SQLID).Append(") = ? ")
Case "length >"
sb.Append("length(").Append(col.SQLID).Append(") > ? ")
Case "length <"
sb.Append("length(").Append(col.SQLID).Append(") < ? ")
Case "length <>"
sb.Append("length(").Append(col.SQLID).Append(") <> ? ")
End Select
args.Add(strFind) 'adding the length to look at
Else
If arrConditions(c,n).bWildCard Then
If arrConditions(c,n).bCaseSensitive Then
sb.Append(col.SQLID).Append(" GlOB ? ")
If arrConditions(c,n).bPrefixSearch Then
args.Add(strFind & "*")
Else
args.Add("*" & strFind & "*")
End If
Else
sb.Append(col.SQLID).Append(" LIKE ? ")
If arrConditions(c,n).bPrefixSearch Then
args.Add(strFind & "%")
Else
args.Add("%" & strFind & "%")
End If
End If
Else
If arrConditions(c, n).bCaseSensitive Then
If arrConditions(c, n).strOperator.Length = 0 Then
sb.Append(col.SQLID).Append(" = ? ")
Else
sb.Append(col.SQLID).Append(" ").Append(arrConditions(c, n).strOperator).Append(" ? ")
End If
args.Add(strFind)
Else
If arrConditions(c, n).strOperator.Length = 0 Then
sb.Append("lower(").Append(col.SQLID).Append(") = ? ")
Else
sb.Append("lower(").Append(col.SQLID).Append(") ").Append(arrConditions(c, n).strOperator).Append(" ? ")
End If
args.Add(strFind.ToLowerCase)
End If
End If
End If
End If
Next
End If
c = c + 1
Next
TotalCount = -1
End If
If SortColumn <> "" Then sb.Append(SortColumn)
If WithPageLimit Then
Dim limit As Int = mRowsPerPage
If TotalCount < 0 Then limit = limit + 1
sb.Append($" LIMIT ${mFirstRowIndex}, ${limit}"$) 'LIMIT offset, row_count
End If
#if B4A
Dim aargs(args.Size) As String
For i = 0 To args.Size - 1
aargs(i) = args.Get(i)
Next
#else
Dim aargs As List = args
#End If
Return Array(sb.ToString, aargs, TotalCount)
End Sub
Private Sub ImplUpdateDataFromQuery (Query As String, Args As Object, TotalCount As Int, DontSleep As Boolean)
Dim i As Int
Dim iCol As Int
Dim arrArgs() As String = Args
SQLIndex = SQLIndex + 1
If mRowsPerPage = 0 Then Return
Dim MyIndex As Int = SQLIndex
If DontSleep = False Then
Sleep(40)
End If
If MyIndex <> SQLIndex Then
Return
End If
VisibleRowIds.Clear
Dim rs As ResultSet = sql1.ExecQuery2(Query, arrArgs)
Log("ImplUpdateDataFromQuery, rs.RowCount: " & rs.RowCount)
Dim success As Boolean = True
If success Then
Dim RowNumber As Int = 1
Do While RowNumber <= mRowsPerPage And rs.NextRow
VisibleRowIds.Add(rs.GetLong("rowid"))
iCol = 0
For Each c As B4XTableColumn In VisibleColumns
Dim lbl As B4XView = GetLabelFromColumn(c, RowNumber)
Select c.ColumnType
Case COLUMN_TYPE_TEXT
SetTextToCell(rs.GetString(c.SQLID), lbl, c.Searchable, iCol) 'SetTextToCell was edited to give the extra iCol argument
Case COLUMN_TYPE_INTEGER
c.Formatter.FormatLabel(rs.GetInt(c.SQLID), lbl)
Case COLUMN_TYPE_DOUBLE
c.Formatter.FormatLabel(rs.GetDouble(c.SQLID), lbl)
Case COLUMN_TYPE_DATE
lbl.Text = DateTime.Date(rs.GetLong(c.SQLID))
End Select
iCol = iCol + 1
Next
RowNumber = RowNumber + 1
Loop
rs.Close
For i = RowNumber To mRowsPerPage
iCol = 0
For Each c As B4XTableColumn In VisibleColumns
#if B4J
SetTextToCell("", GetLabelFromColumn(c, i), c.Searchable, iCol)
#else
GetLabelFromColumn(c, i).Text = ""
#End If
iCol = iCol + 1
Next
VisibleRowIds.Add(0)
Next
mLastRowIndex = mFirstRowIndex - 2 + RowNumber
If iSearchConditions > 0 Then
mCurrentCount = TotalCount
miCountOfPages = Ceil(mCurrentCount / mRowsPerPage)
End If
mCurrentPage = Ceil(mFirstRowIndex / mRowsPerPage) + 1
Else
Log(LastException)
End If
'this is to get the total number of rows obtained in the search and pass this to the B4XPage
'-------------------------------------------------------------------------------------------
If Query.ToLowerCase.Contains(" order by ") = False Then
If bRunningSearch And Query.ToLowerCase.Contains(" where ") Then
Dim strWhereClause As String = GetWhereClauseFromQuery(Query)
Log("ImplUpdateDataFromQuery, SQL: " & "select count(*) from data " & strWhereClause)
Log("ImplUpdateDataFromQuery, arrArgs(0): " & arrArgs(0))
Dim SenderFilter As Object = sql1.ExecQueryAsync("SQL", "select count(*) from data " & strWhereClause, arrArgs)
Wait For (SenderFilter) SQL1_QueryComplete (success As Boolean, rs2 As ResultSet)
End If
End If
bRunningSearch = False
If xui.SubExists(mCallBack, mEventName & "_DataUpdated", 0) Then
CallSub(mCallBack, mEventName & "_DataUpdated")
End If
End SubAll works perfectly fine, except when using the length function, which for some reason always produces zero.
All the above logs give the expected result, so I have no idea what the problem could be.
If I use the same SQL (so with the same data and with the SQLite length function) on a regular DB (so, not the in-memory database used
by B4XTable) then that gives me the expected result.
Any idea what the problem could here?
RBS
RBS
