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

sacad · May 31, 2014

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 · May 31, 2014

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.

B4X:

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

Reviewnow · May 31, 2014

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

B4X:

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

B4X:

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 · May 31, 2014

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:

B4X:

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 · Jun 1, 2014

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

Erel · Jun 1, 2014

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

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

sacad

Member

Mahares

Expert

Reviewnow

Active Member

Mahares

Expert

sacad

Member

Erel

B4X founder

Similar Threads

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

sacad

Member

Mahares

Expert

Reviewnow

Active Member

Mahares

Expert

sacad

Member

Erel

B4X founder

Similar Threads

Privacy & Transparency

Privacy & Transparency