Android Question B4XTable search for empty values(SOLVED)

[RB Smissaert]

Just experimenting with the B4XTable.
I added some extra options to search column values along these lines:

        For Each col As B4XTableColumn In VisibleColumns
            If col.Searchable Then
                For n = 0 To arrCheckConditions(c) - 1
                    strFind = arrConditions(c, n).strSearchText
                    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(" " & arrConditions(c, n).strOperator & " ? ")
                        End If
                        args.Add(strFind)
                    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(" " & arrConditions(c, n).strOperator & " ? ")
                                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(") " & arrConditions(c, n).strOperator & " ? ")
                                End If
                                args.Add(strFind.ToLowerCase)
                            End If
                        End If
                    End If
                Next
            End If
            c = c + 1
        Next
        TotalCount = -1
    End If

This works all fine, but so far been unable to search for "empty" column values.
I understand that B4XTable doesn't handle SQL Null values (but had some tries with that as well), but even the SQL length function I have been unable to make work
to search for these values.
Any suggestions how this should be done?
I am trying to avoid searching the actual customlistview labels, so I would like to do this with SQL on the memory table.

RBS

 

[toby]

The following mysql query will return all records with columnName is null or empty:

 SELECT * FROM myTable WHERE ColumnName IS NULL OR ColumnName = ''

 

[RB Smissaert]

The following mysql query will return all records with columnName is null or empty:

 SELECT * FROM myTable WHERE ColumnName IS NULL OR ColumnName = ''

I don't think this would work in the B4XTable class.
RBS

 

[Mahares]

so far been unable to search for "empty" column values.

You can use: CreateDataview. As an example:

Private Sub Button2_Click  
    Dim Col2 As B4XTableColumn =  B4XTable1.GetColumn("Name")
    Dim Col3 As B4XTableColumn =  B4XTable1.GetColumn("Street")
    B4XTable1.CreateDataView($"${Col2.SQLID} LIKE  ''  OR  ${Col3.SQLID} LIKE  '' "$ )   'displays all records where either has an empty string
end sub

 

[RB Smissaert]

You can use: CreateDataview. As an example:

Private Sub Button2_Click 
    Dim Col2 As B4XTableColumn =  B4XTable1.GetColumn("Name")
    Dim Col3 As B4XTableColumn =  B4XTable1.GetColumn("Street")
    B4XTable1.CreateDataView($"${Col2.SQLID} LIKE  ''  OR  ${Col3.SQLID} LIKE  '' "$ )   'displays all records where either has an empty string
end sub

Yes, that is an option, but I would like to do this in SQL. I am not sure why exactly this is so problematic.
RBS

 

[Mahares]

Yes, that is an option, but I would like to do this in SQL.

Of course you can do it in SQL. Do it with a BuilQuery

Private Sub Button2_Click
    MyList.Initialize
    Dim o() As Object = B4XTable1.BuildQuery(False)
    Dim s As String ="SELECT * FROM data WHERE c1 LIKE ?"   '2nd column. You can use multiple cols with LIKE also
    o(0)=s
    Dim s2 As String =$""$
    o(1)=Array As String(s2)
    Dim rs As ResultSet = B4XTable1.sql1.ExecQuery2(o(0), o(1))
    Do While rs.NextRow
        Dim r() As Object= Array(rs.getint("c0"), rs.getstring("c1"),rs.GetString("c2"))
        MyList.Add(r)
    Loop
    B4XTable1.SetData(MyList)
    rs.Close
End Sub

 

[RB Smissaert]

Of course you can do it in SQL. Do it with a BuilQuery

Private Sub Button2_Click 
    MyList.Initialize
    Dim o() As Object = B4XTable1.BuildQuery(False)
    Dim s As String ="SELECT * FROM data WHERE c1 LIKE ?"   '2nd column. You can use multiple cols with LIKE also
    o(0)=s
    Dim s2 As String =$""$
    o(1)=Array As String(s2)
    Dim rs As ResultSet = B4XTable1.sql1.ExecQuery2(o(0), o(1))
    Do While rs.NextRow
        Dim r() As String = Array(rs.getint("c0"), rs.getstring("c1"),rs.GetString("c2"))
        MyList.Add(r)
        B4XTable1.SetData(MyList)
    Loop
    rs.Close
End Sub

I didn't explain well.
I am talking about the search as in the supplied example project posted in the first post from Erel about B4XTable.
The data comes an Excel file, through the SetData Sub with a list of 1d string arrays as the argument. That data is then searched via the search edittext at the top. It looks you are doing something different.
RBS

 

[RB Smissaert]

I didn't explain well.
I am talking about the search as in the supplied example project posted in the first post from Erel about B4XTable.
The data comes an Excel file, through the SetData Sub with a list of 1d string arrays as the argument. That data is then searched via the search edittext at the top. It looks you are doing something different.
RBS

This was a simple bug.
In the B4XPage code there is a Sub that adds all the search conditions to a Type and then to a B4XOrderedMap, to be passed to the search Sub in the B4XTable class:

Sub AddSearchConditionsB4XOrderedMap
    
    If lblColumnToSearch.Text.Length = 0 Then Return

    Dim oKey As Object = iSearchCondition
    Dim tBSC As tB4XTableSearchConditions
    tBSC.Initialize
    tBSC.strSearchText = edtSearchText.Text
    tBSC.iSearchColumn = B4XTableColumnID2ColumnInt(lblColumnToSearch.Text)
    tBSC.bCaseSensitive = chkCaseSensitive.Checked
    tBSC.bWildCard = chkWildcards.Checked
    tBSC.bPrefixSearch = chkPrefixSearch.Checked
    tBSC.bSQL_OR = chkSQL_OR.Checked
    tBSC.strOperator = lblOperator.Text
    B4XOMSearchConditions.Put(oKey, tBSC)
    
End Sub

This is how it should be as any set of search conditions will need a column where this search applies to.
Instead I had here:

    If edtSearchText.Text.Length = 0 Then Return

Which of course is no good if you are looking for an empty string.
All working fine now.

RBS