Boolean and DataReader

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
19 messages Options
Reply | Threaded
Open this post in threaded view
|

Boolean and DataReader

Johnny
Sorry, I have again a question about reading a sqlite db from c#.
Suppose you have a simple Boolean column in a table.
I want to use a .net DataReader (connected layer).
My question is:
Why I get a cast exception when calling the getBoolean method (solution A)?
Casting the DataReader to Boolean (solution B) works perfectly.
A) Boolean my_bool = DR.getBoolean(...
B) Boolean my_bool = (Boolean)DR[...
Obviously I can choose B but I would prefer the A programming style.
Thank you
Reply | Threaded
Open this post in threaded view
|

Re: Boolean and DataReader

Gerry Snyder-4
On 1/29/2014 1:08 PM, Johnny wrote:

> Sorry, I have again a question about reading a sqlite db from c#.
> Suppose you have a simple Boolean column in a table.
> I want to use a .net DataReader (connected layer).
> My question is:
> Why I get a cast exception when calling the getBoolean method (solution A)?
> Casting the DataReader to Boolean (solution B) works perfectly.
> A) Boolean my_bool = DR.getBoolean(...
> B) Boolean my_bool = (Boolean)DR[...
> Obviously I can choose B but I would prefer the A programming style.
>
SQLite does not have a Boolean data type, so it stores the values as an
integer or a string.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Boolean and DataReader

Johnny
From the db standpoint your answer is perfect and you also replied very quickly, so thank you!
Just of curiosity, who is the developer team (or the support team) of sqlite software products like sqlite admin or .net data driver? I mean - from the client connection layer, how can you miss the opportunity to get standard and consistent behaviour ? SqliteAdmin has the Boolean datatype and the .net DataReader support the Boolean casting... In any case, thanks again and best regards :)
Reply | Threaded
Open this post in threaded view
|

Re: Boolean and DataReader

Johnny
In reply to this post by Gerry Snyder-4
For the sake of clarity.
Now I'm using the sql browser to create a table column of type numeric Boolean.
Now also my c# DataReader method getBoolean is working fine..
It correctly interprets the type Boolean defined on the sqlite db.
Problem solved, I think :-)
Reply | Threaded
Open this post in threaded view
|

Re: Boolean and DataReader

Simon Slavin-3

On 29 Jan 2014, at 10:19pm, Johnny <[hidden email]> wrote:

> Now I'm using the sql browser to create a table column of type numeric
> Boolean.

No you're not.  See section 1.1 of

<http://www.sqlite.org/datatype3.html>

If some application presents a boolean type to you and then it's being done inside that application, and any part of it that isn't working is the fault of that application, not SQLite.

To check this out for yourself, inside your application do a

SELECT mycolname, typeof(mycolname) FROM myTable

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Boolean and DataReader

Johnny
Simon,
as I wrote before the native type is numeric.
You're right about typeof showing that type, of course.
But inside sqlite the dump command reads Boolean.
I also confirm that the application is working,
in particular the getBoolean does not throw any exception and
Properly reads the value.
Tell me in case you see anything wrong.
Thanks again.
Reply | Threaded
Open this post in threaded view
|

Re: Boolean and DataReader

Johnny
I just want to keep track of the issue as it is and of the complete solution.
Problem was due to a wrong sqlite initialization of the Boolean as a text instead of integer
when I used the boolean checkbox of the EditData tab in SQLite Administrator software.
Fixing that and making DR.GetBoolean work is easy then :-)
Thanks @Simon for suggesting the typeof(...) check
Reply | Threaded
Open this post in threaded view
|

Re: Boolean and DataReader

Stephen Chrzanowski
Just to make it crystal clear, to a developer actually calling the SQLite
functions (I'm talking about [ PrepareSQL_v2 ] and such, not [ select *
from table ]), SQLite is TYPELESS which means there is no data type that is
kept track of for any field.  This means that even if you define a column
as NUMERIC, you're still able to put 'ABC' and get a successful insert.

So a statement like this:
create table Test1 (Field1, Field2);

is going to be treated the same as
create table Test1 (Field1 numeric, Field2 char);

is going to be treated the same as
create table Test1 (Field1 char, Field2 UberwonderfulTypeCast);

At the Wrapper level, it doesn't matter what kind of type you define for a
field.  SQLite will essentially ignore the type and allow an insert of any
type.  You could put a BLOB in a BYTE type field and it'd happily take it
with an insert.

Now, at the point where the SQLite code is telling the OS to write data
out, it internally knows the type of data you're putting out.  So it knows
that you're writing a number, a string, or a blob, and that is JUST for
handling data efficiently, and you'll never have to concern yourself about
that bit of data (Unless your one of the SQLite dev'rs) but above that
layer, your wrapper is going to be making the final decision on what data
you're pulling out is.  So if you were trying to pull a boolean value out,
and you were getting unexpected results when the field value was 1, 0, or
-1, then your wrapper is internally calling a check on the field type and
validating the expected return and failing if NUMERIC wasn't defined.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Boolean and DataReader

Johnny
I'm not one of the sqlite dev'rs for sure,
but I immagine that the .net driver has been done with their cooperation
In any case the funny thing is that
(in a particular situation: after a "wrong" insert of "1" instead of 1, fault of sqlite admin)
my Wrapper level -
can I say that the ADO .net System.Data.SQLite.dll is my Wrapper level? -
is making an "inconsistent" decision, not a "final" one:
GetType returns System.Boolean (why if sqlite typeof is saying text ?!?)
GetBoolean on the other side throws an exception ...
Reply | Threaded
Open this post in threaded view
|

Re: Boolean and DataReader

Stephen Chrzanowski
I don't use .NET anything, so I can't speak on it.  However, with my
wrapper in Delphi, if I store a value as 1 or '1', returning a .AsInteger
will succeed.  The wrapper is smart enough to decide if it is actually a
number.  I don't recall if it'll throw an exception if the value in the
database is returned as a float (1.1) or if it will just return 1, either
by rounding, floor, or ceil it.

I also have run into boolean issues with my wrapper as well though.  When I
was designing the database in a different application, I was filling the
database with, what I thought at the time, boolean values.  However, the
SQL IDE I was using at the time treated TRUE values as 1, where as my
wrapper was expecting -1 as a TRUE.  So since the consistency was FALSE = 0
for both, I modified the wrapper (Since I had the source code) to return
FALSE if the result was zero, and true otherwise.  The code previously was
doing something strange with the comparison.  It was a few years ago when I
encountered this issue, so I don't remember the details exactly.

What I might suggest you do is instead of checking GetBoolean (Since it is
extremely picky apparently) is use GetInteger != 0.  If the result is ZERO
this will return FALSE. (1 != 0 = TRUE; 0 != 0 = FALSE)  If the result is
anything else, you'll return TRUE.


On Thu, Jan 30, 2014 at 10:38 AM, Johnny <[hidden email]> wrote:

> I'm not one of the sqlite dev'rs for sure,
> but I immagine that the .net driver has been done with their cooperation
> In any case the funny thing is that
> (in a particular situation: after a "wrong" insert of "1" instead of 1,
> fault of sqlite admin)
> my Wrapper level -
> can I say that the ADO .net System.Data.SQLite.dll is my Wrapper level? -
> is making an "inconsistent" decision, not a "final" one:
> GetType returns System.Boolean (why if sqlite typeof is saying text ?!?)
> GetBoolean on the other side throws an exception ...
>
>
>
>
> --
> View this message in context:
> http://sqlite.1065341.n5.nabble.com/Boolean-and-DataReader-tp73521p73542.html
> Sent from the SQLite mailing list archive at Nabble.com.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Boolean and DataReader

James K. Lowden
On Thu, 30 Jan 2014 10:49:39 -0500
Stephen Chrzanowski <[hidden email]> wrote:

> What I might suggest you do is instead of checking GetBoolean (Since
> it is extremely picky apparently) is use GetInteger != 0.  If the
> result is ZERO this will return FALSE. (1 != 0 = TRUE; 0 != 0 =
> FALSE)  If the result is anything else, you'll return TRUE.

or define a constraint

        colname boolean check (colname in ('Y', 'N'))

Of course you can use 0 and 1, but there's no technical advantage, and
very often 'Y' and 'N' turn out to be handy for reporting purposes.  

--jkl

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Boolean and DataReader

Igor Tandetnik-2
On 1/30/2014 7:20 PM, James K. Lowden wrote:
> or define a constraint
>
> colname boolean check (colname in ('Y', 'N'))
>
> Of course you can use 0 and 1, but there's no technical advantage

There is a tiny advantage. Values 0 and 1 are special-cased in SQLite's
file format, and occupy only one byte each. 'Y' and 'N' require two
bytes to represent.
--
Igor Tandetnik

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Boolean and DataReader

Keith Medcalf
In reply to this post by Johnny

Have you declared the column with integer affinity (before inserting the data)?

>-----Original Message-----
>From: [hidden email] [mailto:sqlite-users-
>[hidden email]] On Behalf Of Johnny
>Sent: Thursday, 30 January, 2014 08:38
>To: [hidden email]
>Subject: Re: [sqlite] Boolean and DataReader
>
>I'm not one of the sqlite dev'rs for sure,
>but I immagine that the .net driver has been done with their cooperation
>In any case the funny thing is that
>(in a particular situation: after a "wrong" insert of "1" instead of 1,
>fault of sqlite admin)
>my Wrapper level -
>can I say that the ADO .net System.Data.SQLite.dll is my Wrapper level? -
>is making an "inconsistent" decision, not a "final" one:
>GetType returns System.Boolean (why if sqlite typeof is saying text ?!?)
>GetBoolean on the other side throws an exception ...
>
>
>
>
>--
>View this message in context:
>http://sqlite.1065341.n5.nabble.com/Boolean-and-DataReader-
>tp73521p73542.html
>Sent from the SQLite mailing list archive at Nabble.com.
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Boolean and DataReader

Johnny
Good point, in terms of integer affinity I think that a Boolean_INT is the way to go.
I have posted a bugfix request on sqliteadmin forum but I don't think it is supported any longer...
It is using a non standard Y/N :(
On c# side GetBoolean is the best approach as opposite to the bool casting.
I think that the problem is closed: sqlite is light, but maybe a bit too essential...
Reply | Threaded
Open this post in threaded view
|

Re: Boolean and DataReader

Johnny
But sqlite is typeless... so the affinity does not change much... :-(
A constraint check in (0,1) is needed for a reliable boolean type
Reply | Threaded
Open this post in threaded view
|

Re: Boolean and DataReader

Keith Medcalf

Indeed it does.  If you insert something that smells like an integer in a column with integer affinity, it will be stored as an integer (rather than text, for example).

>-----Original Message-----
>From: [hidden email] [mailto:sqlite-users-
>[hidden email]] On Behalf Of Johnny
>Sent: Friday, 31 January, 2014 13:41
>To: [hidden email]
>Subject: Re: [sqlite] Boolean and DataReader
>
>But sqlite is typeless... so the affinity does not change much... :-(
>A constraint check in (0,1) is needed for a reliable boolean type
>
>
>
>--
>View this message in context:
>http://sqlite.1065341.n5.nabble.com/Boolean-and-DataReader-
>tp73521p73600.html
>Sent from the SQLite mailing list archive at Nabble.com.
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Boolean and DataReader

Johnny
Dear Keith,
the point is the following
you define a sqlite field as BOOLEAN
at that poit the sqlite ADO.NET maps it to System.Boolean
and it takes into account of an integer affinity
You put a wrong "Y" in, if there is no check constraint,
sqlite is typeless and accept it.
It does not smell like an integer:
GetBoolean - implemented in sqlite DataReader -
correctly throw an exception But
- and this is a Bug of sqlite ado net -
the GetValue and GetFieldType are misleading as
False and System.Boolean
while they should throw the same exception
and return System.String.

Reply | Threaded
Open this post in threaded view
|

Re: Boolean and DataReader

KlaasV
In reply to this post by Johnny
|Igor Tandetnik wrote:
   | On 1/30/2014 7:20 PM, James K. Lowden wrote:
   |or define a constraint
   |
   | colname boolean check (colname in ('Y', 'N'))
   |
   |Of course you can use 0 and 1, but there's no technical advantage

|There is a tiny advantage. Values 0 and 1 are special-cased in SQLite's file format, and occupy only one byte each. 'Y' and 'N' require two bytes to represent.

Since SQLite uses at least 8 bits there is enough space for three values: False, True, Undefined. One has to define standard values.
For example -1 for false, 0 for undefined, all others for true. YMMV. Using a letter has the advantage of being more explicit.  

Kind regards,
Klaas `Z4us` V

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Klaas "Z4us" V, MetaDBA at InnocentIsArt.EU
Reply | Threaded
Open this post in threaded view
|

Re: Boolean and DataReader

Igor Tandetnik-2
On 2/1/2014 12:26 PM, Klaas V wrote:

> |Igor Tandetnik wrote:
>     | On 1/30/2014 7:20 PM, James K. Lowden wrote:
>     |or define a constraint
>     |
>     | colname boolean check (colname in ('Y', 'N'))
>     |
>     |Of course you can use 0 and 1, but there's no technical advantage
>
> |There is a tiny advantage. Values 0 and 1 are special-cased in SQLite's file format, and occupy only one byte each. 'Y' and 'N' require two bytes to represent.
>
> Since SQLite uses at least 8 bits there is enough space for three values: False, True, Undefined. One has to define standard values.
> For example -1 for false, 0 for undefined, all others for true. YMMV. Using a letter has the advantage of being more explicit.

See http://www.sqlite.org/fileformat.html#record_format . For each cell,
there's at least one byte indicating the data type (possibly more for
strings and blobs, since the length is also encoded into the type),
followed by the actual data. Values NULL, integer 0 and integer 1 are
special - they are encoded into the type, which is not followed by any
data bytes. Values 'Y' and 'N' are not special - each will consume one
byte for the type, and one byte to store the actual character.

The special case for 0 and 1 exists precisely for the purpose of
efficiently representing boolean values.
--
Igor Tandetnik

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users