I an looking at a database created with an app from about a year and a half ago and to which data has been added/modifed since then… And I’m finding that records touched more recently true and false stored vs 1 and 0…
The Xojo SQLite API USED TOstore 1’ and 0’s for boolean values … did it change over to ‘true’ and ‘false’ at some point?
The issue is that my SQL queries all use 1 and 0 and they don’t return the correct results if true and false are stored and a significant number of records were store that way rather than with 1 and 0!
Use prepared statements to query, and RecordSet.{your choice of field selection method}.BooleanValue and it won’t be an issue. The PreparedStatement will handle the true/false value going into the database, and .BooleanValue will handle them coming out.
Not knowing exactly what started this topic… the important thing to realize is that SQLite does not have a BOOLEAN datatype. as a matter of fact SQLite has NO real datatypes, but it does have Affinities… but that being said you can shove any kind of data into any SQLite datafield using SQL… the Xojo BooleanType attritube simply looks for TRUE/FALSE or 0/1 or more accuratly TRUE/1 (anything else would be False)
That’s kind of what it started about. The older versions of either RealStudio or Xojo was storing booleans as true / false, and is now storing them as 1 / 0 and this was causing SQL errors.
The prepared statements and .BooleanValue work quite well and are in use for a project that had just this very issue.
I fixed it that way before my first post… but I need to know how it got that way!
This one go the downsides of SQLite… I her dome some minor edits on the DB at times with the Firefox SQLite add-in, as well as Valentina studio… maybe one of those did it?
various SQLite 3rd party apps will adopt either True/False or 0/1 to represent a binary value … either/both/mixed is acceptable
and the BooleanValue of the Field function should return a proper boolean True/False regardless of with/both are used.
Where it becomes an issue is in raw SQL (and I believe this includes prepared statements, but I’d have to test that)
because SQLite doesn’t have a Boolean datatype (as mentioned above)… True/False are stored in the TEXT affinity, while 0/1 are stored in the INTEGER affinity
So…
SELECT FROM mytable WHERE field=TRUE
is an SQL error (not an Xojo error), because there is no FIELD named TRUE
SELECT FROM mytable WHERE field='TRUE'
will work ONLY if field contains ‘TRUE’ (or ‘true’ depending on COLLATE NOCASE)
but will return zero results for fields that contain ‘1’ as 1<>TRUE
so in ‘raw’ SQL YOU need to know how booleans are expressed, and make sure your statements compensate appropriately
aBooleanVariable=rs.field("myfield").booleanvalue
only works once you have selected the records
I anguished over this for days when designing my TADPOLE Sqlite manager, and there is a builtin Utility that analyzes the table and tells you which method (or mixed) is used in a specific table… or if there is NON-Boolean values in the field
I had some issues with SQLite and boolean. I put 0 or 1 on a field. I used this:
rs.Field("Inhabil").StringValue
and expected a 0 or a 1 in there, but when I used If me.CellTag(…) = 0 or If me.CellTag(…) = “0”, it didn’t work.
I had to use:
If me.CellTag(me.ListIndex,1) = false Then
to make it work.
I just changed .StringValue to .BooleanValue and = false to = 0. Thanks, this also works.
I guess that the Boolean Affinity tell SQLite/Xojo that 0 is False and 1 is True, and because I used rs.Field(“Inhabil”).StringValue instead of .BooleanValue, I got false/true instead of 0/1. Maybe if I created “Inhabil” as integer and only put 0/1 there then using .StringValue should result in 0 and 1 as strings.
There is no BOOLEAN Affinity… it uses the NUMERIC Affinity (except if you put TEXT there )
which really only applies if you “follow the rules”, because as stated before you can put anything you want in any field
what is SHOULD say is “Boolean values SHOULD BE stored as integers 0 (false) and 1 (true).”
because all these are valid statements for SQLite (other DB engines might balk (as they should))
INSERT INTO mytable (Inhabil) VALUES("now is the time for all good men");
INSERT INTO mytable (Inhabil) VALUES(417);
INSERT INTO mytable (Inhabil) VALUES(3.14);
INSERT INTO mytable (Inhabil) VALUES(0);
INSERT INTO mytable (Inhabil) VALUES(1);
INSERT INTO mytable (Inhabil) VALUES("TRUE");
INSERT INTO mytable (Inhabil) VALUES("FALSE")
I would create it as BOOLEAN … and
me.celltag(...)=rs.field("Inhabil").booleanvalue
should work just fine
but a lot will depend also on how the table was CREATED to begin with…
again… I would use “BOOLEAN” just to make the CREATE TABLE easier to understand the intent
Yes rs.Field(“Inhabil”).booleanvalue works just fine changing the If statement to = 0 instead of = false.
I used Boolean when I created the table with default value 0.
I understand that I can put anything on a SQLite database. I only put 0 and 1 in Inhabil.
What I don’t understand Is how/why if I use rs.Field(“Inhabil”).StringValue I get false/true and not 0/1.
I mean, as a newbie, I expected StringValue to pass 0 or 1 from the db. I have a lot to learn.
The SQLite Database has 0 and 1
.BooleanValue will return True or False (as correct boolean datatype)
.IntegerValue will return 0 and 1
.StringValue returns ‘True’ and ‘False’ as string values…
put a 2 into boolean field in SQLite, testing values 0, 1 and 2
.StringValue = false, true, false
.BooleanValue = False, True, False
.IntegerValue = 0, 1, 0
created a integer field in SQLite, put the same values (0, 1 and 2)
.StringValue = 0, 1, 2
.IntegerValue = 0, 1, 2
.BooleanValue = False, True, True
So even if SQLite boolean I can put any value, anything there that is not 1 is equal to False.
I was confused about SQLite that you can put anything, so even if I created a Table with Boolean field I was under the impression that it will work as an integer. That’s why I expected .StringValue as 0/1.
Now my guess is that Xojo interact with SQLite, knows it is a Boolean field and only report True if value is 1, everything else is False. Either that or I’m doing something wrong.
Edit: I put aaa instead of 2. With boolean I get False, with integer I get aaa (using rs.Field(…).StringValue)
[quote=361861:@Dave S]probably how Xojo decided to make it work…
The SQLite Database has 0 and 1
.BooleanValue will return True or False (as correct boolean datatype)
.IntegerValue will return 0 and 1
.StringValue returns ‘True’ and ‘False’ as string values…
makes sense to me[/quote]
Actually a perfect example of where Variant and its cousins (DatabaseField) can easily lead you astray. What happens when one result can be multiple types?
Auto is actually quite nice here. The major downside being it is a little more annoying to use at the moment.
Variant/Auto have NOTHING to do with how SQLite processes data…
What many people fail to realize is the SQLite is its own “engine”, and SQL is the language that engine understands
Xojo has created an interface to that SQLite Engine, and reinterpets what is stored.
If you take the examples that Alberto posted above, and use SQL statements (without letting Xojo reinterpet them) you will find that actual database contains 0, 1 ,2 the “boolean datatype” values are cast by the Xojo Engine, NOT the SQLite engine
)