System.Data.SQLite

This very well could be my problem but..

string blah = reader.GetString(3);
blah will equal 'True'

bool blah = reader.GetBoolean(3);
blah will equal 'false'

This is with version 1.0.25.0

Is the column declared as a boolean type? What does Datareader.GetType return?

If I can get a response on this and have enough information to determine if its a bug or not, I can squeeze any fixes necessary into the next update ...

Robert

 

Sorry for the delay. I'm still not convinced its a true bug - it seems like it would've been caught in your tests.

DataReader.GetType() is going to return the type of the datareader object.....so I'm not clear on what you are asking.
Changing my original code (posted below) to just use a datareader.GetValue (4) returns 'false'.

The table schema is:

CREATE TABLE assoc (
    guid TEXT,
    name TEXT,
    desc TEXT,
    parent TEXT,
    active BOOLEAN  
    );

[edit: I also tried 'active BOOL' without luck]

sqlite> select * from assoc;
bd72360a-d12a-4b30-9c0e-fd251fab59d3|Bob||00000000-0000-0000-0000-000000000000|True

Code:

        private void ReadGroups(SQLiteCommand cmd, Hashtable table)
        {            
            SQLiteDataReader reader = null;
            try
            {
                cmd.CommandText = "SELECT * FROM assoc;";
                reader = cmd.ExecuteReader();

                Debug.Assert(reader.FieldCount == 5,
                    "Wrong number of assoc columns");
                while (reader.Read())
                {
                    //string a = reader.GetString (4);

                    bool a = reader.GetBoolean (4); // Returns 'false'

                    //bool active;
                    //if (a == "False")
                    //    active = false;
                    //else
                    //    active = true;

                    Group group = new Group(reader.GetGuid(0), reader.GetString(1),
                        reader.GetString(2), reader.GetGuid(3), true); // DEBUG, change for "real" code

                    group.State = ObjectState.Existing; // group is in-sync with db
                    table.Add(group.Id, group);
                }
            }
            catch (Exception except)
            {
                throw new Exception(except.Message + "\n[ReadGroups]");
            }
            finally
            {
                reader.Close();
            }
        }

sqlite> select * from assoc;
bd72360a-d12a-4b30-9c0e-fd251fab59d3|Bob||00000000-0000-0000-0000-000000000000|True

That right there told me exactly what went wrong.

SQLite allows you to put any kind of data in any kind of column.  The column Active is declared as boolean, but you've inserted a text string into the column who's value is the word "true".

Technically speaking, the SQLiteDataReader *should* have thrown an error when you called GetBoolean(), because that function is not supposed to try and convert a string value to a boolean value.  However, it sees that the column is boolean, and calls the core C function sqlite3_column_int() which is returning 0 because the column is not an int, and I am translating that to false.

To fix the problem, you need to import the data into SQLite and use 1 for true and 0 for false in that column instead of the text values "true" and "false".

Robert

 

 Here is how I built my query:

if (this.State == ObjectState.NewFangled) {
    sql.Append ("INSERT INTO assoc VALUES ('" + Id + "', '");
    sql.Append (Name + "', '" + Desc + "', '");
    sql.Append (Parent + "', '" + IsActive + "');");
   }

Where IsActive returns a bool. And results in:
{INSERT INTO assoc VALUES ('25107f80-b517-4610-9d3e-791a9a237cbc', 'TestGroup', '', '00000000-0000-0000-0000-000000000000', 'True');}

Which I guess is the problem. Guess I will need to parse out my "IsActive" value and append the string correctly. Makes sense... :-(
Thanks for the time and a great product!

Hi!

I too stumbled on this at first, it appears I stored "True" as a string in the db. When looking in the db with the commandline tool, I think booleans are represented by either 1 or 0 (as true or false). So, if it says "True" in the db, it is probably a string, and the GetBoolean interprets this string as a false value...
(That is my guess anyway. ;-) )

Since I discovered this, I try to use SQLiteParameters for all my sql commands. That way, any type is correctly translated to SQLite without me needing to worry.

Regards
Carl