[quote=361879:@Dave S]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.
[/quote]
If you are referring to me you misunderstand my meaning.
I know SQLite has no boolean type. However the DatabaseField class when you use the generic RecordSet.Field(“column”) method is generic in the sense that it applies to all database types. So the DatabaseField class is not smart enough to know that a BooleanValue is not really a valid type in SQLite because it IS in other database types.
So what you end up with is a Variant-like experience where the DatabaseField makes its best guess of what the data is based on the type of output you want. If you say BooleanValue then it will try to convert the SQLite value into a boolean using whatever magic Xojo has come up with. Probably something like 0 is False, 1 is True, “true” is True, etc. However that can cause hidden surprises if SQLite ever changes, if the SQLiteDatabase class changes, if DatabaseField changes, or if you ever insert data into that field other than the values Xojo knows how to auto-convert. You end up in situations like above.
Auto however will raise an exception that it cannot cast the value to the type because it does not pretend to know how every database engine stores every data type that they may or may not support. So in the end it is a little more work on developer side but less subtle bugs on the built product.
Hence I actually prefer the way Auto does it because DatabaseField has all the same caveats as a Variant.
I agree with MOST of what you said there… but the “variant” part is behind the scenes…
If you access the field value using the
.booleanValue
.IntegerValue
.StringValue
etc.
and as such there is no way to apply “auto” since the developer doesn’t have any deeper access.
What I have done in a lot of cases is take the .StringValue and the datatype as defined in the CREATE Statement and make my own decisions on what to do.
[quote=361882:@Dave S]I agree with MOST of what you said there… but the “variant” part is behind the scenes…
If you access the field value using the
.booleanValue
.IntegerValue
.StringValue
etc.
and as such there is no way to apply “auto” since the developer doesn’t have any deeper access.
What I have done in a lot of cases is take the .StringValue and the datatype as defined in the CREATE Statement and make my own decisions on what to do.[/quote]
Yes I agree I think parsing StringValue yourself is honestly the safest way to go unless you know it is a numeric type.
I understand Auto is not available yet (see my thoughts on the new framework) but it is an area where I look forward to improvement.
[quote=361879:@Dave S]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[/quote]
Thank you again Dave.
I started learning Xojo a couple of months ago, I need to do some work with database and SQLite is enough for my project. So I started learning SQLite. I read that Xojo only send commands to SQLite engine, and there are some add-ons that can help database development like ARGen and SQLdeLite, but I’m too new to programming that I can’t figure how to use those tools.
So I created a SQLite database, select Boolean as one of the fields, after all I read I expected .StringValue 0/1 but didn’t work. Thanks to MsgBox I was able to see that the values stored in CellTag were false/true and I changed my If statement. It worked.
Now I know (thanks to you), that I was right to expect 0/1 and Xojo actually “see” that there is a “boolean datatype” and only report True if the value is 1, everything else is False, even when SQLite send the actual value.
I used boolean in SQLite because I don’t know if I’ll change to other database in the future.
Maybe this information is somewhere in the docs and I missed it.