I realize that SQLite is NOT strongly typed… as a matter of fact for the most part you can put anything into an SQL field regardless of how the datatype on the table was orignially defined.
That being said… this is not working as I would “expect” [yes I know there is no error traps… its just a working example]
Dim rs As RecordSet
Dim workDB As New SQLiteDatabase
Dim s As String
If workDB.Connect=True Then
workDB.SQLExecute "CREATE table test ( b1 boolean)"
workDB.SQLExecute "INSERT INTO test VALUES('ABC')"
workDB.SQLExecute "INSERT INTO test VALUES('true')"
workDB.SQLExecute "INSERT INTO test VALUES('false')"
//
rs=workDB.SQLSelect("SELECT * FROM TEST")
//
While Not rs.eof
s=s+rs.field("b1").StringValue+" :: "+Str(rs.Field("b1").BooleanValue)+EndOfLine
rs.MoveNext
Wend
MsgBox s
End If
I would have expected the returned results to be
yet what I got was
I even did a test where I created and populated the table in another non-Xojo app, where that app did return what I expected.
but reading it with code simlar to above I got what I consider “wrong” results.
There is nothing that should be coercing “ABC” to be either true or false
any OTHER “datatype” seems to return the expected STRINGVALUE
SQLite.org says that fields specified as Boolean are actually stored as Integer. 0 = False, 1 = True. It does not surprise me that ABC equates to 0 in an integer field.
But here is my problem… EVERY other SQLite tool I have played with (including one also written in Xojo), returns ABC
and here is what running that same test directly from SQLite in OSX terminal mode does
Last login: Tue Apr 18 16:35:32 on console
$ /usr/bin/sqlite3
SQLite version 3.8.10.2 2015-05-20 18:17:19
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table test (b1 boolean);
sqlite> insert into test values('abc');
sqlite> select * from test;
abc
sqlite>
I think the interesting part will be when you take the database created by Xojo in your first post, then examine it using SQLite in OSX terminal mode.
Basically, is Xojo’s implementation of SQLite actually turning ABC into ‘1’ at the point of insertion?
whereas SQLLite in terminal just puts in what you ask for and interprets on the way out?
I have a table called stress_test that has a column defined as BOOLEAN
when Xojo executes “SELECT * FROM STRESS_TEST”
the 3 records return
FALSE
TRUE
FALSE
when I do the same statement from SQLite3 in OSX Terminal that same column returns
false
true
xyz
AND if you examine the RAW Sqlite Database with a Hex Editor you can plainly see that “xyz” is in the data.
which is in line with the other data in that record.
And if these were database/tables that my app was creating and using itself, I wouldn’t be worrying about. However this app will consume data from unknown sources (only criteria must be an SQLite database), and may create/update database/tables that will be consumed by other applications over which I have no control
As far as I understand the docs this is a wrong assumption:
[quote]Type Affinity
…
A column with NUMERIC affinity may contain values using all five storage classes. When text data is inserted into a NUMERIC column, the storage class of the text is converted to INTEGER or REAL (in order of preference) if such conversion is lossless and reversible. For conversions between TEXT and REAL storage classes, SQLite considers the conversion to be lossless and reversible if the first 15 significant decimal digits of the number are preserved. If the lossless conversion of TEXT to INTEGER or REAL is not possible then the value is stored using the TEXT storage class. No attempt is made to convert NULL or BLOB values.
…[/quote]
A column with type name BOOLEAN results in a NUMERIC type affinity, so the above should apply.
And therefore ‘ABC’ should be inserted in the table.
[quote=326922:@Eli Ott]A column with type name BOOLEAN results in a NUMERIC type affinity, so the above should apply.
And therefore ‘ABC’ should be inserted in the table.
[/quote]
That is MY understanding as well… and using SQLite directly via Terminal confirms that to be true.
Yet there MUST be someway to get the “ABC” via Xojo, as I have seen other Xojo oriented programs do it
same thing… I can find no way for Xojo to return the “raw” result that is actually in the SQLite database
This would be a non-issue if Sqlite was strongly typed like other DB Engines… but since it is not, I need to be able to deal with what ever the true contents are…
Dim rs As RecordSet
Dim workDB As New SQLiteDatabase
Dim s As String
If workDB.Connect=True Then
workDB.SQLExecute "CREATE table test ( b1 boolean)"
workDB.SQLExecute "INSERT INTO test VALUES('ABC')"
rs=workDB.SQLSelect("SELECT * FROM TEST")
s=s+"String Value :"+rs.field("b1").StringValue+EndOfLine
s=s+"Boolean Value : "+Str(rs.field("b1").BooleanValue)+EndOfLine
s=s+"Value : "+rs.field("b1").Value+EndOfLine
MsgBox s
End If
All return “false”, except Boolean which returns “False”
I am using Xojo 2016r4.1 which has Sqlite 3.14.1
I did the same test in Real Studio 2010 (changing to RealSqlDatabase) and it was Sqlite 3.7.3
and the OSX Terminal example above was 3.8
Since 3.7 and 3.14 in Xojo do one thing, and 3.8 from terminal does something else.
So… Kem… If you have a table as defined above… with “ABC” in a Boolean column, VS returns false as well?
I guess I’m just gonna have to make my app enforce type…
Kem… what does VS do with OTHER datatypes that contain mismatched data contents?
Last login: Wed Apr 19 21:28:17 on ttys000
iMac-2:~ daveS$ /usr/bin/sqlite3
SQLite version 3.8.10.2 2015-05-20 18:17:19
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table test (
...> b boolean,
...> d double,
...> i integer
...> );
sqlite>
sqlite> insert into test values ('abc', 'cde', 'hhh');
sqlite>
sqlite> select * from test;
abc|cde|hhh
sqlite>
pasting your example directly to SQLite3, returns again, the “expected” result
What is worse, it because SQLite techincally (outside of Xojo at least) will allow “anything” to be stored.
It makes a Query near impossible
SELECT * FROM myTABLE where b='True' or b='true' or b='TRUE' or b=1 or b>0 or what?
Seems “True”=true, 1=True, “False”=false, 0=false, anything else = false (EXCEPT in a SELECT statment because there is will an exact match to what ever is REALLY in the field…
SOLUTION … now I hope I can incorporate it tranparently into my app
select cast(b as text) from fred
I made a test file and loaded it with all kinds of gibberish in a boolean field.
StringValue return only True or False regardless of the true content
but using CAST returned the TRUE contents (ie. the gibberish I inserted to begin with)
Kem… could you try that in VS and see what comes out?
Would be nice if Xojo internally did this for non Text Declared fields when returning .StringValue
BTW, if you ever want to see what’s really stored in the sqlite file: My disk editor iBored, written in Xojo, undestands the sqlite file format and lets you poke at the pages. You have to be able to follow B*Tree nodes though, if you have a more complex setup. But with a simple single-table setup with only a handful of rows, you can see what each record contains rather quickly.
