System.Data.SQLite

How about existing Columns in an existing table marked BOOLEAN?  When I try to access data in the table for such a column with a datareader I get the following results (copied from a Watch list with a little editing):
dr["ManualBias"]    Value: false    Type: object {bool}
dr.GetBoolean(dr.GetOrdinal("ManualBias"))    'dr.GetBoolean(dr.GetOrdinal("ManualBias"))' threw an exception of type 'System.InvalidCastException'    Type: bool {System.InvalidCastException}

Provider version is the current one (0.36.1)

Another thing while I'm experiencing it:
Similar problem for fields of type NUMERIC(13,2):  If data contains a decimal point, for example 0.5 or 10.32, dr.GetDouble and dr.GetFloat don't have any problems returning the correct value.  However if the decimal point is missing, for example 1 or 10, I need to convert from dr.GetDecimal.  Any specific reason for this?

I'll have the decimal bug fixed in the next release.  The boolean issue, however, is interesting.  Boolean is already mapped as a known datatype.  Is the underlying data in the database a string or a number?

Robert

 

Due to existing data, it can be both.  Range of Values are 0, 'False', and 'True'.  The original schema definition for that column is actually BOOLEAN DEFAULT '0'.


Ah, the joys of using an existing database :)

I can add Convert.ToBoolean() code if the source is a string, but it will only work on the machine's current culture.  If your db is only going to be on English Windows your "true" and "false" strings will be fine.  Of course, my recommendation would be a global replace of any "true" or "false" strings in the db to their respective 0 or 1 bool values :)

Robert

 

rsimpson:

I can add Convert.ToBoolean() code if the source is a string, but it will only work on the machine's current culture. 

You could also use Convert.ToBoolean( booleanString, CultureInfo.InvariantCulture).

By the way, Convert.ToBoolean("true") works fine on my computer ("es-ES" culture). However Convert.ToBoolean("Verdadero") fails with FormatException.

I guess the question becomes ... should I do it?

Should I just toss out VerifyType completely and rewrite those get routines to just try and coerce anything out of the database into the requested type?

Robert

 

Sounds good to me :D

I'm happy either way really - the current implementation will force me to cleanup the current database using update statements.  However, if there was a need to share the database with an app that didn't care so much about data types, that might cause problems.

I think you should do it only if it makes sense, say if the requested type maps to the declared field type. For example, if you have a field declared as BOOLEAN and you write the following:

    bool fieldValue = reader.GetBoolean( ordinal );

SQLite provider should try to convert whatever value in the field to boolean.

Hello everybody,

I have the problem, that I have an exported Database from a MSSQL2005 Server, where the boolean values are 'Y' for true and 'N' for false.

For me it would be nice, if I can specify my true and false Values somewhere. Then it would be no problem if I have 0/1, True/False, Y/N, OK/NotOK as Values in the Database.

Just convert the Y/N to 0/1 (or whatever it may be male/female) after you import the data with a simple SQL statement.  It is the simplest solution.

Robert Simpson:

I guess the question becomes ... should I do it?

Should I just toss out VerifyType completely and rewrite those get routines to just try and coerce anything out of the database into the requested type?

Robert

 

I dont think you can ever be all things to all people.  maybe as an addition to the proposed 'strong types' connection string option you could introduce ways to specify how you're storing various values eg for bools y/n vs 1/0 vs true/false.  I think in this case, sensable conventions with sane configuration is the best route. 

 Hello

i am one of those people that have to migrate from Access ...
but i run into problems with primary keys that need to be
defined as PRIMARY KEY AUTOINCREMENT.
To have that AUTOINCREMENT i need to define the colums as
INTEGER not INT which is 64 bit.
Now the customers code uses (int) to cast that data (which is 32 bit), and of course
i get an runtime error that this conversion is not allowed.

Anyone haveing an suggestion beyond changeing all code to eg. Convert.ToInt32 ?

I guess that depends on what your code looks like.  If you're accessing the datareader directly, then just call GetInt32().  If you're using the array [ accessor, then you'll have to do the conversion yourself because it'll return an Int64.

 

 Thanks Robert

 i did convert all calls ....