Android Question B4xTable Search Fails If Text Field is NULL

[rgarnett1955]

Hi,

I have a text comments field in my sqLite database tables. The contents of the field are optional and so the fields are NULL unless they have at least a string of zero length in them i.e. ""

If you wish to use the search feature of b4xTables and you make the comments field searchable and high-lightable if records have a null in at least one of the searched fields nulls the program crashes in the b4xtable module in the setTextToCell function.

** Activity (main) Resume **
Error occurred on line: 506 (B4XTable)
java.lang.NullPointerException: Attempt to invoke virtual method 'java.lang.String java.lang.String.toLowerCase()' on a null object reference
    at b4a.gardenBuddyOld.b4xtable._settexttocell(b4xtable.java:3193)
    at b4a.gardenBuddyOld.b4xtable$ResumableSub_ImplUpdateDataFromQuery.resume(b4xtable.java:2885)
    at anywheresoftware.b4a.shell.DebugResumableSub$DelegatableResumableSub.resumeAsUserSub(DebugResumableSub.java:48)
    at java.lang.reflect.Method.invoke(Native Method)
    at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:732)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:351)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:255)
    at java.lang.reflect.Method.invoke(Native Method)
    at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:144)
    at anywheresoftware.b4a.BA.raiseEvent(BA.java:193)
    at anywheresoftware.b4a.shell.DebugResumableSub$DelegatableResumableSub.resume(DebugResumableSub.java:43)
    at anywheresoftware.b4a.keywords.Common$13.run(Common.java:1704)
    at android.os.Handler.handleCallback(Handler.java:739)
    at android.os.Handler.dispatchMessage(Handler.java:95)
    at android.os.Looper.loop(Looper.java:148)
    at android.app.ActivityThread.main(ActivityThread.java:5417)
    at java.lang.reflect.Method.invoke(Native Method)
    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:726)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:616)
** Activity (main) Pause, UserClosed = true **

Private Sub SetTextToCell (Text As String, lbl As B4XView, Searchable As Boolean)
    If Searchable = False or HighlightSearchResults = False Then
        lbl.Text = Text
    Else
    #if B4J
        Dim parent As B4XView = lbl.Parent
        If parent.GetView(parent.NumberOfViews - 1).Tag = TextPaneTag Then
            parent.GetView(parent.NumberOfViews - 1).RemoveViewFromParent   
        End If
    #End If
        If FilterText = "" Then
            lbl.Text = Text
        Else
            Dim x As Int = Text.ToLowerCase.IndexOf(FilterText)
            If x = -1 Or (PrefixSearch And x > 0) Then
                lbl.Text = Text
                Return
            End If
        
        #if B4A or B4i
            Dim cs As CSBuilder
            cs.Initialize.Append(Text.SubString2(0, x)).Color(HighlightTextColor).Append(Text.SubString2(x, x + FilterText.Length)).Pop
            cs.Append(Text.SubString(x + FilterText.Length))
            #if B4A
            lbl.Text = cs
            #else if B4i
            Dim l As Label = lbl
            l.AttributedText = cs
            #End If
        End If
        #Else
            TextFlow.Reset
            If x > 0 Then
                TextFlow.Append(Text.SubString2(0, x)).SetColor(TextColor).SetFont(LabelsFont)
            End If
            TextFlow.Append(Text.SubString2(x, x + FilterText.Length)).SetColor(HighlightTextColor).SetFont(LabelsFont)
            If x + FilterText.Length < Text.Length Then
                TextFlow.Append(Text.SubString(x + FilterText.Length)).SetColor(TextColor).SetFont(LabelsFont)
            End If
            Dim TextPane As B4XView = TextFlow.CreateTextFlow
            TextPane.Tag = TextPaneTag
            lbl.Text = ""
            parent.AddView(TextPane, 0, parent.Height / 2 - 12, parent.Width, parent.Height / 2)
        End If
        #end if
    End If
End Sub

I used an obvious work-around which was to make default the comments fields to "" rather than let sqLite make them nulls, but it would be better if the search function skipped over nulls and ignored them.

I wouldn't say this is a bug just a feature.

Best regards
Rob

 

[Erel]

Moved to the questions forum.

The values in B4XTable should be nulls. Bad things will happen. You need to convert nulls to whichever value you like before you set the data.

 

[rgarnett1955]

Hi Erel,

Sorry, I don't agree.

In some apps I want the data to display the as Nulls which the table does transaparently. Yes, I could do the conversion myself i.e. (if NULL then ""), but that is inefficient. Much better for the search engine to handle nulls so that us overworked developers don't have to to fiddle around with null exception string handling that would be more easily done in the search engine. The NULL is natures way of telling you there is nothing in the field - zero length. ALL of the databases use NULL for empty fields and a lot of tables are filled from DB's with NULLS in them.

You say that using nulls makes bad things happen. Quite correct. In this case the bad thing was the search engine gave me errors which meant that I had to unzip the library, because without the library what does the error info tell you? So I wasted an hour figuring it out. The bad thing that happened was I wasted my time. If the search finds a null it crashes the java engine. The java engine therefore understands NULLS intimately, they aren't a mystery to it, so why can't "user friendly" null handling be included in the search engine. There is an way of doing an "if null" function after all.

If StringArgs = Null Then

If the Null handling is done once in the search engine then it avoids the issue of having to repeat the test every time it is an issue in code.

The library could be modified easily to handle nulls so why not?

Another interesting point is; what if I wish to search for all NULL entries. I might wish to do this because I might find my db is missing data. I.e. the user hasn't put anything in and so I have null fields. Null fields are not the same as fields with white space. It seems obvious logical to me that as a user my search engine will not crash on NULL contentes but simply ignore them.

But as with all developers if something goes wrong that you didn't foresee, blame the user. That's what I always do: Rarely, does this approach gets me "off the hook" in most case I have to fix the problem and eat humble pie.

Best regards
Rob

 

[Erel]

Sorry, I don't agree.

There is nothing to agree or disagree here. By design B4XTable doesn't support nulls. You posted it as a bug report and it is not a bug. If you like you can make a feature request.

 

[Mahares]

If the Null handling is done once in the search engine then it avoids the issue of having to repeat the test every time it is an issue in code.

One way to avoid NULL in your B4Xtable is when you create you sql statement before you add the data to the B4XTable is to use the ifnull function, example:

strQuery="SELECT Country, Population, Capital, ifnull(Comment,'Missing') as MyComment FROM mytable"

 

[rgarnett1955]

There is nothing to agree or disagree here. By design B4XTable doesn't support nulls. You posted it as a bug report and it is not a bug. If you like you can make a feature request.

Yes.

I think I did say that it wasn't a bug, but maybe a feature.

You say that the B4XTable doesn't support Nulls in its fields however my b4xTable, empty comments text column cells had the word "null" displayed which I thought was rather good.

Ahh good thinking I thought.

I will make a feature request.

Best regards
Rob

 

[rgarnett1955]

One way to avoid NULL in your B4Xtable is when you create you sql statement before you add the data to the B4XTable is to use the ifnull function, example:

strQuery="SELECT Country, Population, Capital, ifnull(Comment,'Missing') as MyComment FROM mytable"

Hi Hahare,

I do realize that, but then I would have to do that for every query I write, or muck around with a sub-query or some other method. The b4XTable does handle nulls. I like the idea of nulls being handled without crashing the program. I like the fact that null entries propagate through. If I don't want to display nulls then I would handle that at display time. As I said a NULL string isn't an error it means something and it is not the same as white space.

I was going to leave the null in the code, so the user would see it and know it was an empty field. Now if I want to avoid crashing the search engine I have to put some text in the field and then the search engine will not skip that entry when it searches. It seems to me a bit silly to replace the empty field with a string saying "null" because then the search engine may include it, when it's actually supposed to be empty.

One of the things all developers have to put up with is data exceptions causing programs to crash. It's not always obvious what has caused the crash which requires the programmer to use valuable time finding the problem, then devising a robust solution to handle the issue. In this case the table handles empty fields in a very logical way and in away that doesn't preclude modifying it to do what the programmer wants. i.e. replace "null" with "" or "No Data" or something.

Of course there is no right or wrong answer here, I am just explaining my particular preference.



Best regards
Rob