I am still polishing my stats reporting methods and found :
Women: 0
Men: 0
in the Sexe Column, where I get numbers, previously.
After making different changes in my SQLite command line, the idea to change from TitleCase to lowercase (instead of Titlecase) gave me the actual count !
My SQL command line is now:
SELECT COUNT (*) FROM Caritas WHERE Sexe='true';
When I removed COUNT and surrounding parenthesis (to *) I get a list of entries with lowercase, nothing with Titlecase.
A saved report done on October 24, 2016 have the correct # of persons (M/W).
The project version from the previous day have True / False !
Xojo 2015r1 / 2016r4.1
El Capitan .6
Developer state: astonished.
Are you saying that there was a behavior change between the two versions?
No and this is very strange.
I am working with Xojo 2015r1. When I found something strange that I want to report, I check if I have the same behavior using the last (current) Xojo release, actually 2016r4.1.
But, in this case, the very same Xojo version was working fine in last october, wrong today. This is based on a saved report done in last october and todays project compilation I checked last october project version and the sql string was the same (the one with Titlecased True / False).
So, for actual IDE, nothing to change (I think), for users, this is a For Your Info message.
BTW: even if I saw any “error” in the Xojo application, I never correct it, I never open the macOS application to remove or add, modify anything. I have too many things on my plate and few time to achieve these to spend time doing crazy things with the Xojo application(s).
How my report is possible is far beyond my understanding.
BTW: usually, I love case sensitive. What I dislike is to discover abrupt changes in the rules. I do not understand from where this change comes. A virus ?
SQL databases are generally case sensitive when it comes to ‘=’ comparison and string/text values
You have to ask them to behave in a NOT case sensitive way if you want ‘True’ to be treated the same as ‘true’
Thats where the LIKE clause can be useful - even without wild cards
It also usually means “NOT case sensitive” - although some dbs make you use a different keyword (ilike for instance)
You original query could, and maybe should, be written as
SELECT COUNT (*) FROM Caritas WHERE Sexe LIKE ‘true’;
BTW: reading the command line (above) appears very different from what I had in mind ;-
(at 1:00 in the night, this start to be a normal reading of the computer sentence )
Wait a minute. I have a simple Book database and one of the entries is Spider Robinson’s book Telempath. When I use a DB reader, either one I wrote in Xojo or the SQLite Database Browser, using the following SQL
SELECT title FROM Books WHERE title = 'telempath'
I get Telempath as the result. The actual DB entry has an uppercase ‘T’ so if SQLite is case sensitive why would that SQL yield that result? No place in the database have I set anything to nocase.
[quote=308099:@Dale Arends]Wait a minute. I have a simple Book database and one of the entries is Spider Robinson’s book Telempath. When I use a DB reader, either one I wrote in Xojo or the SQLite Database Browser, using the following SQL
SELECT title FROM Books WHERE title = 'telempath'
I get Telempath as the result. The actual DB entry has an uppercase ‘T’ so if SQLite is case sensitive why would that SQL yield that result? No place in the database have I set anything to nocase.[/quote]
because someplace either that option was set , or the query has a COLLATE NOCASE (could also be in CREATE TABLE)
SELECT * FROM Caritas WHERE UPPER(Sexe) = ‘TRUE’ //or UPPER(‘true’)
Gives the same results as LIKE, I forget which is faster. Were you using the apostrophes when you did count if no, it’s not text it’s a boolean ergo TRUe = true
Yep. It took some delving in but you are correct, the CREATE TABLE and the CREATE INDEX statements all have a COLLATE NOCASE in them. That’s what I get for taking on someone else’s project back then.
I falled into the same trap today (or was it late this night ?). Using Norman advice (WHERE Sexe LIKE 'true') wasthe solution. Even in larger command using ORDER BY with two (or more colums).
I used Norman advice and it works fine.
Nota: todays sqlite file was built from a text file (exported file with changes in two columns, not the involved one, but ). I may made some error somewhere. I have to check if my export to text method is correct on this Boolean (true / True / whatever) column.
Sqlite uses integer as boolean datatype.
For me it seems like you are using an text datatype ??? Don’t do that!
CREATE TABLE mybooltable (ID INTEGER PRIMARY KEY AUTOINCREMENT, boolvalue INTEGER)
You can then Select it with “SELECT * FROM mybooltable WHERE boolvalue = 0(false) OR 1(true)”
Or if you use Preparedstatements (and you should do that)
dim ps as SQLitePreparedStatement = db.Prepare(“SELECT * FROM mybooltable WHERE boolvalue = ?”)
ps.bindtype(0, ps.SQLITE_BOOLEAN)
dim rs as recordset = ps.sqlselect(true) or ps.sqlselect(false)
to convert your data you could do someting like that:
update table SET col = 0 WHERE LOWER(col) = true
update table SET col = 1 WHERE LOWER(col) = false
The LOWER() will set all cars in col to lowercase, so even “True”, “truE” OR “TRUE” will work
When I read my .sqlite file with a specialized tool, I saw true or false. That information is set in the application using a PopupMenu with Man or Woman (thus the Sexe column name).
Elsewhere, I use something like:
if rs.Field("Sexe").BooleanValue Then
to deal with the data base column contents (as adviced in Xojo SQLite documentation).
In January 6, I certainly do just what I do yesterday, a base export and do not check when I build a brand new .sqlite db. I do not had time yet to check what the text export holds as I am fighting with that information (true / false) in the data base: I pasted different first name / last name to replace the confidential ones (originals).
While I am here, I checked the export text and it is fine (I have true and false). Maybe the import process ?
No, Import seems to do the job correctly
I really have to make the whole process instead of checking my code (since I do not reach the trouble doing that).
Xojo is able to convert text-column to bool value. That is all.
Also, there are really much reasons why you don’t want to use text as a datatype for bool.
But at the moment i couldn’t find one to do that.
If you would use integer - as sqlite defines that - you wouldn’t have these problems.
You could run this querys to eliminate that.
Update table set cola = lower(cola) where lower(cola) = ‘true’ OR lower(cola) = ‘false’;
Update table set colb = lower(colb) where lower(colb) = ‘true’ OR lower(colb) = ‘false’
Select * from table WHERE (cola != ‘true’ AND cola != ‘false’) OR (colb != ‘true’ AND colb != ‘false’)
That will set all to lowercase and then show you if there is something else.
