Android Question Converting null values in Sqllite db or list to ""

[sacad]

Hi I need to take data from database and convert it to excel but when I have any null values savecsv will give a null pointer exception. Currently I make use of the dbutils.executememorytable function to obtain the data from database but want to replace any null values in list to nothing after data was obtained. Is this possible? What would be the best approach to follow? I do not want to display but convert. Please help

 

[Mahares]

One way to do it is to convert the column or columns that have NULL to blanks in your table then, export to to text or Excel. Please make a backup copy of your database before doing this if you are going to try it, as I do not have a database with NULLs to test.

txt="UPDATE tblChemicals SET chemical= ? WHERE chemical= ?"
SQL1.ExecNonQuery2(txt, Array As Object("",Null))

 

[Reviewnow]

The easy way to do this is just change your select statement

create table t1(a int, b  Text, c int);
insert into t1 values(1,a,0);
insert into t1 values(2,b,1);
insert into t1 values(3,c,0);
insert into t1 values(4,d,1);
insert into t1 values(5,null,0);
insert into t1 values(6,null,1);
insert into t1 values(7,null,null);

if its a text field do this
SELECT a, case when b is null then '' else b end as b, c FROM t1
or
SELECT a, case when b is null then 'N/A' else b end as b, c FROM t1

create table t1(a int, b int, c int);
insert into t1 values(1,0,0);
insert into t1 values(2,0,1);
insert into t1 values(3,1,0);
insert into t1 values(4,1,1);
insert into t1 values(5,null,0);
insert into t1 values(6,null,1);
insert into t1 values(7,null,null);

if its an int field
SELECT a, case when b is null then 0 else b end as b, c FROM t1

 

[Mahares]

Reviewnow says: The easy way to do this is just change your select statement

Your approach is definitely sound if you do not want to update the fields. By the way, even if b is INTEGER, it can still accept a string. Here is the complete B4A code based on Reviewnow post:

SQL1.BeginTransaction
    txt="DROP TABLE IF EXISTS t1"
    SQL1.ExecNonQuery(txt)
     
'    txt="create table t1(a Int, b  Text, c Int)"  'this works too
    txt="create table t1(a Int, b  Int, c Int)"
    SQL1.ExecNonQuery(txt)
    txt="insert into t1 values(1,'a',0)"
    SQL1.ExecNonQuery(txt)
    txt="insert into t1 values(2,'b',1)"
    SQL1.ExecNonQuery(txt)
    txt="insert into t1 values(3,'c',0)"
    SQL1.ExecNonQuery(txt)
    txt="insert into t1 values(4,'d',1)"
    SQL1.ExecNonQuery(txt)
    txt="insert into t1 values(5,Null,0)"
    SQL1.ExecNonQuery(txt)
    txt="insert into t1 values(6,Null,1)"
    SQL1.ExecNonQuery(txt)
    txt="insert into t1 values(7,Null,Null)"
    SQL1.ExecNonQuery(txt)
SQL1.TransactionSuccessful
SQL1.EndTransaction

txt="SELECT a, case when b is null then '' else b end as b, " _
& "case when c is null then 'Reviewnow' else c end as c FROM t1"
Cursor1=SQL1.ExecQuery(txt)
Cursor1.Position=6  
Msgbox(Cursor1.GetString("a") & "  " & Cursor1.GetString("b") & "  " & Cursor1.GetString("c"),"")

 

[sacad]

Thank you so much will give it a try...

 

[Erel]

Another option is to use the isnull SQLite keyword: http://www.b4x.com/android/forum/threads/display-of-sqlite-null-values.34087/#post-199594