B4J Tutorial Null in relation to assigning database values to variables

I don't know if this counts as a tutorial but it might help someone. Please advise of any errors or omissions and I'll fix them.

Sometimes the value in a field in a resultset will be null.
This can even be the case for database columns that are are defined NOT NULL by design, if the the resultset
is from a join operation.

Consider this example where the database field Company in the resultset RS is null :

Dim ob As Object = RS.getstring("Company")
Log(ob=Null)  'this logs True
Dim Company As String  = ob
Log(Company) 'this logs the string "null", you probably don't want this.
Log(Company.Length ) 'this logs 4
        
Company = RS.getstring("Company")
Log(Company=Null) 'this logs False
Log(Company)'this again logs the string "null"
Log(Company.Length )'this throws: (Exception) java.lang.Exception:  java.lang.NullPointerException

So in order to be able to test if null, first assign to an object.
Test that for null and then do whatever you need to do, afterwards :

Dim ob As Object = RS.getstring("Company")
if ob = null then
    Company = ""
else
    Company = ob
end if

For more information see https://www.b4x.com/android/forum/threads/b4x-b4xtable-load-data-from-sql-database.102520/#content

Update based on Erel's comment below.
If your sql query was

"Select Company from dbname"

and you want the empty string returned for rows where the column is null
then rewrite the query as

"Select IFNULL(Company, "") from dbname"

Then in code below Company will be assigned the empty string if the database field is null

Company = RS.getstring("Company")

IFNull is the SQLite function, Postgres has COALESCE, etc.

 

[Erel]

Your code is correct. I recommend casting nulls to other values such as empty strings, in the query itself.
The exact syntax depends on the SQL engine. With SQLite you can use IFNULL: https://sqlite.org/lang_corefunc.html#ifnull

 

[j_o_h_n]

Your code is correct. I recommend casting nulls to other values such as empty strings, in the query itself.
The exact syntax depends on the SQL engine. With SQLite you can use IFNULL: https://sqlite.org/lang_corefunc.html#ifnull

Cool, I didn't know about that. The equivalent in postgres is coalesce.

sqlite IFNULL() in postgres

What is the equivalent of SQLite's IFNULL() in Postgres? I have to following query (sqlite in Ruby): SELECT ifnull(max(code_id) + 1, 1) FROM configentries WHERE configtable_id = ... How should...

stackoverflow.com

 

[Harris]

So in order to be able to test if null, first assign to an object.
Test that for null and then do whatever you need to do, afterwards :

Dim ob As Object = RS.getstring("Company")
if ob = null then
Company = ""
else
Company = ob
end if

Yes. I learned this early on when retrieving string values from my tables. Darn good advice - however one wishes to handle it...
IsNumber is also handy when trying to evaluate a valid numeric value - in the same vein...

If and when all else fails in very complex situations, Try, Catch, End Try will save your app (and ass) from crashing unexpectedly ....

The point you make here is to be pro-active in coding, as apposed to re-active (later debugging when your users discover issues that are hard for you - or them to reproduce).
My motto is to code for exception - rather than perfection (what "should" be expected).

Great coders understand this. New coders need to learn and respect this. Older coders (if not already) need to adapt...

Thanks for (re)-stating this and making all of us more aware of our developer responsibilities...
The genius of the B4X platform makes this easy to comply...

 

[j_o_h_n]

Thank you Harris for your kind remarks.
I only looked into this after the top of a webpage was captioned null!
I agree with you about trying to be proactive rather than waiting for something to cause problems