How to Add a Boolean Column in Android SQlite

Question

I have created a table for my ContentProvider using the following line :

static final String CREATE_DB_TABLE = 
  " CREATE TABLE " + CONTACTS_TABLE_NAME +
  " (_id INTEGER PRIMARY KEY AUTOINCREMENT, " + 
  " pid TEXT NOT NULL, " +
  " name TEXT NOT NULL,"+
  "number TEXT NOT NULL);";

It has 4 columns. Now i want to add a column with a boolean value of true/false. How can i add append/change this statement if i have to add a boolean column named "status".

SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true). Check this [Post](http://stackoverflow.com/questions/843780/store-boolean-value-in-sqlite) — M D, Jul 07 '14 at 07:10
plz take a loot at this post,,,m waiting for answer for last 3 days, n i didn't even get a single comment...http://stackoverflow.com/questions/24733444/how-to-use-two-cursors-and-cursorjoiner-in-loadermanager-in-android — BST Kaal, Jul 17 '14 at 10:18

Phantômaxx · Accepted Answer · 2016-12-01T10:07:36.647

69

You could use something like this:

Create your table:

static final String CREATE_DB_TABLE = 
    "CREATE TABLE " + CONTACTS_TABLE_NAME " + 
    " (_id INTEGER PRIMARY KEY AUTOINCREMENT," + 
    "..." + " flag INTEGER DEFAULT 0)";

retrieve your value as:

Boolean flag = (cursor.getInt(cursor.getColumnIndex("flag")) == 1);

edited Dec 01 '16 at 10:07

answered Jul 07 '14 at 08:07

Phantômaxx

37,901
21
84
115

Unfortunately, I know nothing about `CursorJoiner` and `LoaderManager`, since I don't use them. My suggestion is... can't you **JOIN** (or you wanted to do a **UNION**?) the two tables directly in a single query? – Phantômaxx Jul 17 '14 at 10:28
`CursorJoiner` actually joins two cursors. But, I need to call it via LoaderManager coz it queries the ContentProvider automatically. I need to reflect the changes at run time, and i cant afford to query again and again. – BST Kaal Jul 17 '14 at 10:31
`joins two cursors` what about a single query that **UNION** (s) two SELECTs in a `single Cursor`? After all, you need only one cursor. So you'll get the same result with: **1** less Cursor and **0** CursorJoiner... => `SELECT ... UNION SELECT ...` (make sure that the fields are the same in both SELECTs. If a field is missing, add some `null AS myNonExistingField`) – Phantômaxx Jul 17 '14 at 10:37
In Select Statement, i need to mention the table, and unfortunately Phone Contacts doesn't allow to be as a table...as for as i know... – BST Kaal Jul 17 '14 at 10:42
I see. But I'm convinced there's a way to query the contacts directly. After all, it's a SQLite table... – Phantômaxx Jul 17 '14 at 10:52
May be, but at the moment i dnt know how – BST Kaal Jul 17 '14 at 10:53
... use Google intensively? ;) – Phantômaxx Jul 17 '14 at 10:56
OK, if you can wait a couple of days more (or 3), I'll try to study a solution to this issue. – Phantômaxx Jul 17 '14 at 11:01
Hay @Frank N. Stein, can u plz take a look at http://stackoverflow.com/questions/24863038/avoid-swiperefresh-while-fastscrolling-in-android – BST Kaal Jul 22 '14 at 06:34
Sorry, I don't have any clue with `fastScroll` and `SwipeRefresh`. – Phantômaxx Jul 22 '14 at 07:13
1

thanks, this method will also check, if the number odd or even, for any number – Noor Hossain Oct 31 '20 at 15:10

score 19 · Answer 2 · edited Jul 25 '16 at 13:40

19

As mentioned by M D,

SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true).

Use the following ALTER statement -

ALTER TABLE CREATE_DB_TABLE ADD status boolean NOT NULL default 0;

Then you can use Cursor to get the required value and then convert to actual boolean -

flag=cursor.getString(0).equals("1")

You can use this flag to display on user screen.

Reference: http://www.sqlite.org/datatype3.html

edited Jul 25 '16 at 13:40

ilgazer

110
9

answered Jul 07 '14 at 07:20

sjain

23,126
28
107
185

2

Well I would probably use `return c.getShort(index) == 1;` instead. – Chris.Jenkins Nov 20 '15 at 10:17

score 4 · Answer 3 · answered Jan 17 '18 at 16:07

4

unfortunately android sqlite does not support Boolean type and you should use Integer instead of it

answered Jan 17 '18 at 16:07

Abolfazl Miadian

3,099
2
19
15

score 3 · Answer 4 · answered Jul 07 '14 at 07:26

public enum Status{
        TRUE,FALSE;
}
static final String CREATE_DB_TABLE = 
  " CREATE TABLE " + CONTACTS_TABLE_NAME +
  " (_id INTEGER PRIMARY KEY AUTOINCREMENT, " +......
  " booleanColumn INTEGER DEFAULT 0);";

//while inserting

pass Status.TRUE.ordinal() values to database and

//while retrieving

cross check with the ordinal value for status

score 1 · Answer 5 · edited Jul 25 '16 at 12:54

1

Sqlite does not provide any class to store boolean values.

You can use 0 for false and 1 for true. You will then have to convert these values after retrieving them from your database.

edited Jul 25 '16 at 12:54

Makach

7,435
6
28
37

answered Jul 07 '14 at 08:49

Pankaj Arora

10,224
2
37
59

How to Add a Boolean Column in Android SQlite

5 Answers5