D dlfallen Active Member Licensed User Longtime User Oct 31, 2013 #1 When I display a table in Sqlite (using DBUtils, for example) records containing a null value in a field will display as "null". Is there any option to display a blank field instead?
When I display a table in Sqlite (using DBUtils, for example) records containing a null value in a field will display as "null". Is there any option to display a blank field instead?
Erel B4X founder Staff member Licensed User Longtime User Nov 1, 2013 #2 There are several ways to deal with null values. I think that the simplest solution is to use isnull in your query. Something like: Code: Copy "SELECT ifnull(col1, ''), ifnull(col2, '')..." http://www.sqlite.org/lang_corefunc.html#ifnull This will convert null values to an empty string. Upvote 0
There are several ways to deal with null values. I think that the simplest solution is to use isnull in your query. Something like: Code: Copy "SELECT ifnull(col1, ''), ifnull(col2, '')..." http://www.sqlite.org/lang_corefunc.html#ifnull This will convert null values to an empty string.
D dlfallen Active Member Licensed User Longtime User Nov 1, 2013 #3 Yes, that cleans the table up nicely -- but the column header now reads ifnull(col1,'') instead of just col1. Nice table, but ugly column titles. Upvote 0
Yes, that cleans the table up nicely -- but the column header now reads ifnull(col1,'') instead of just col1. Nice table, but ugly column titles.
D DouglasNYoung Active Member Licensed User Longtime User Nov 1, 2013 #4 Obviously, next alternatives are to: a) rename the column titles by Select ifnull(col1, '' ") as Column1 , etc.... or b) do the ifnull comparison/replacement where you ouput the results. Douglas Upvote 0
Obviously, next alternatives are to: a) rename the column titles by Select ifnull(col1, '' ") as Column1 , etc.... or b) do the ifnull comparison/replacement where you ouput the results. Douglas
mc73 Well-Known Member Licensed User Longtime User Nov 1, 2013 #5 Erel, why not simply adding an if to reading with dbUtils? After all, an empty string is what user expects to view if a null appears. Upvote 0
Erel, why not simply adding an if to reading with dbUtils? After all, an empty string is what user expects to view if a null appears.
eps Expert Licensed User Longtime User Nov 1, 2013 #6 mc73 said: Erel, why not simply adding an if to reading with dbUtils? After all, an empty string is what user expects to view if a null appears. Click to expand... Er no..! This is the way all DBs work, changing this behaviour could lead to other problems. If the DB is under user control you should indicate that they should nulls should be empty strings. Something like this : http://stackoverflow.com/questions/6598881/how-to-make-a-column-of-text-empty-instead-of-null Although you may have to recreate the table from scratch which could be a pita. Upvote 0
mc73 said: Erel, why not simply adding an if to reading with dbUtils? After all, an empty string is what user expects to view if a null appears. Click to expand... Er no..! This is the way all DBs work, changing this behaviour could lead to other problems. If the DB is under user control you should indicate that they should nulls should be empty strings. Something like this : http://stackoverflow.com/questions/6598881/how-to-make-a-column-of-text-empty-instead-of-null Although you may have to recreate the table from scratch which could be a pita.
mc73 Well-Known Member Licensed User Longtime User Nov 1, 2013 #7 He eps said: Er no..! This is the way all DBs work, changing this behaviour could lead to other problems. If the DB is under user control you should indicate that they should nulls should be empty strings. Something like this : http://stackoverflow.com/questions/6598881/how-to-make-a-column-of-text-empty-instead-of-null Although you may have to recreate the table from scratch which could be a pita. Click to expand... He can just add a flag for whether returning nulls or empty strings... Upvote 0
He eps said: Er no..! This is the way all DBs work, changing this behaviour could lead to other problems. If the DB is under user control you should indicate that they should nulls should be empty strings. Something like this : http://stackoverflow.com/questions/6598881/how-to-make-a-column-of-text-empty-instead-of-null Although you may have to recreate the table from scratch which could be a pita. Click to expand... He can just add a flag for whether returning nulls or empty strings...
eps Expert Licensed User Longtime User Nov 4, 2013 #8 mc73 said: He He can just add a flag for whether returning nulls or empty strings... Click to expand... or just return nulls or a string... Upvote 0
mc73 said: He He can just add a flag for whether returning nulls or empty strings... Click to expand... or just return nulls or a string...
D dlfallen Active Member Licensed User Longtime User Nov 12, 2013 #9 OK, the answer was quite simple, really. My program contained these two lines of code (taken from SQLite Viewer): Code: Copy h = DBUtils.ExecuteHtml(Main.SQL, Query , Null, RecLimit, False) WebView1.LoadHtml(h) I just inserted a replacement function in between these two lines, so now it reads: Code: Copy h = DBUtils.ExecuteHtml(Main.SQL, Query , Null, RecLimit, False) h = h.Replace(Null,"") WebView1.LoadHtml(h) And Bob's your Uncle! Upvote 0
OK, the answer was quite simple, really. My program contained these two lines of code (taken from SQLite Viewer): Code: Copy h = DBUtils.ExecuteHtml(Main.SQL, Query , Null, RecLimit, False) WebView1.LoadHtml(h) I just inserted a replacement function in between these two lines, so now it reads: Code: Copy h = DBUtils.ExecuteHtml(Main.SQL, Query , Null, RecLimit, False) h = h.Replace(Null,"") WebView1.LoadHtml(h) And Bob's your Uncle!
