Working with boolean values in an SQLite database

Question

I read that SQLite databases support a boolean value. Here's my query to create the table:

private static final String CREATE_TABLE_PROMEMORIE_RSI = "CREATE TABLE IF NOT EXIXSTS "
            + promemorieRSI.PROMEMORIE_RSI_TABLE + " ("
            + promemorieRSI.ID + " integer primary key autoincrement, "
            + promemorieRSI.GIORNO + " integer, "
            + promemorieRSI.MESE + " integer, "
            + promemorieRSI.ANNO + " integer, "
            + promemorieRSI.NOT_RIP_RSI + " boolean, "
            + promemorieRSI.RIP_VAL_RSI + " integer, "
            + promemorieRSI.UNIT_RIP_VAL_RSI + " text, "
            + promemorieRSI.NOT_FISSA_RSI + " boolean, "
            + promemorieRSI.ETICHETTA_RSI + " text, "
            + promemorieRSI.REQUEST_CODE_RSI + " integer);";

As you can see, I set the not_rip_rsi and not_fissa_rsi columns to be of boolean values. I read that the database stores the true and false values as, respectively, 1 and 0 integer values, but now I can't understand this:

When I add a record to the database, what value should I use for these columns?

For better explanation, here's my insert method:

public void inserisciPromemoriaRSI(Integer giorno, Integer mese, Integer anno, Boolean not_rip, Integer rip_val, String unit_rip_val, Boolean not_fissa, String etichetta, Integer request_code){
        ContentValues val = new ContentValues();
        val.put(promemorieRSI.GIORNO, giorno);
        val.put(promemorieRSI.MESE, mese);
        val.put(promemorieRSI.ANNO, anno);
        val.put(promemorieRSI.NOT_RIP_RSI, not_rip);
        val.put(promemorieRSI.RIP_VAL_RSI, rip_val);
        val.put(promemorieRSI.UNIT_RIP_VAL_RSI, unit_rip_val);
        val.put(promemorieRSI.NOT_FISSA_RSI, not_fissa);
        val.put(promemorieRSI.ETICHETTA_RSI, etichetta);
        val.put(promemorieRSI.REQUEST_CODE_RSI, request_code);
        mDb.insert(promemorieRSI.PROMEMORIE_RSI_TABLE, null, val);
    }

As you can see, to add the record I use a boolean value for these columns. Even if I set the value of the entries of those columns to be of type boolean, when I add a record, should I use an integer value of 1 or 0 instead of a boolean value, or will the database automatically store true as 1 and false as 0? Will I have to use a cursor.getInt(column_index) == 1 to get the boolean value? Am I making a mistake in my code? If so, how do I solve this problem. Any suggestions?

What is the language of your code? PS Have you googled this yet? — philipxy, Jul 07 '16 at 19:33
Possible duplicate of [Store boolean value in SQLite](http://stackoverflow.com/questions/843780/store-boolean-value-in-sqlite) — philipxy, Jul 07 '16 at 19:40
Already saw questions about this argument, but couldn't answer to my question — DISSENNATORE, Jul 07 '16 at 22:27

score 2 · Accepted Answer · edited May 23 '17 at 12:08

2

A boolean doesn't exists in SQLite. Instead, you store the boolean value in the database as an INTEGER in SQLite. The values that you can use are 0 for false and 1 for true. When you go and select the INTEGER from the database, to change it to a boolean, you use:

boolean isTrue = cursor.getInt(columnNo) > 0;

If you want a value of false, then:

boolean isFalse = cursor.getInt(columnNo) < 1;

Have a look at this post and this post for further information.

edited May 23 '17 at 12:08

Community

1
1

answered Jul 07 '16 at 21:03

Razor

1,778
4
19
36

I've already seen those posts, but I can't understand at all.. If I set the value of those columns to be of type `boolean` , when I insert a record have I to pass an `integer` value (`1` or `0`) or a `boolean`? – DISSENNATORE Jul 07 '16 at 22:38
You create the columns in your table to be of type `INTEGER` and pass a value of `1` or `0`. `boolean`s don't exists in SQLite and are stored as integers. – Razor Jul 07 '16 at 22:45
Let me see if I understood.. In the query to make the table I **must** change the `boolean` type to `integer` (Is the same if upcased or not, right?), but I **can** pass a value of `boolean` type on the insert method and the database will **_automatically_** convert it to an `integer` (`0` or `1`)(And so I would only have to change the query to make the table) – DISSENNATORE Jul 07 '16 at 23:01
1

Almost right. You must pass an `integer`, either `0` for `false` or `1` for `true`. If you pass a `boolean`, you app might crash.The data type `boolean` doesn't exists in SQLite. – Razor Jul 07 '16 at 23:05
1

Thank you very much :)
I have to make a big change to my reminder-app.. It's hard coding if your knowledge is very basic, but I like it – DISSENNATORE Jul 07 '16 at 23:18
this would unnecessarily increase the size of db – 10101010 Sep 09 '17 at 07:55
+10101010 Yeah, I agree. I don't think SQLite has a BIT data type like MSSQL does – Razor Sep 09 '17 at 08:42

Working with boolean values in an SQLite database

1 Answers1