What is wrong here? StringValue vs BooleanValue

  1. 8 months ago

    Dave S

    19 Apr 2017 San Diego, California USA
    Edited 8 months ago

    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

    ABC :: false
    true :: true
    false :: false

    yet what I got was

    false :: false
    true :: true
    false :: false

    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

  2. Greg O

    19 Apr 2017 Xojo Inc Somewhere near Raleigh, NC

    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.

  3. http://sqlite.org/datatype3.html

  4. Dave S

    19 Apr 2017 San Diego, California USA
    Edited 8 months ago

    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> 

    it too returns "ABC" as I would expect

  5. Jeff T

    19 Apr 2017 Midlands of England, Europe

    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?

  6. Dave S

    19 Apr 2017 San Diego, California USA
    Edited 8 months ago

    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.

    D #5 abc@,ffffffxyz2017-dec-252017-jan-25 13:22:0022:19:05

    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

  7. Eli O

    is not verified 19 Apr 2017 Europe (Berlin, Germany)
    Edited 8 months ago

    @Greg OLone 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.

    As far as I understand the docs this is a wrong assumption:

    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.
    ...

    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.

  8. Dave S

    20 Apr 2017 San Diego, California USA

    @Eli O 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.

    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

  9. Greg O

    20 Apr 2017 Xojo Inc Somewhere near Raleigh, NC

    What do you get if you use Value instead of StringValue?

  10. Dave S

    20 Apr 2017 San Diego, California USA

    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...

  11. Greg O

    20 Apr 2017 Xojo Inc Somewhere near Raleigh, NC

    It might also be worth looking at the version of SQLite being used in all of these places.

  12. Kem T

    20 Apr 2017 Pre-Release Testers, Xojo Pro New York

    FWIW, Valentina Studio doesn't show me the "true" contents either, presenting as a checkmark (the way it shows booleans).

  13. Dave S

    20 Apr 2017 San Diego, California USA
    Edited 8 months ago
    		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?

  14. Kem T

    20 Apr 2017 Pre-Release Testers, Xojo Pro New York
    create table test (
      b boolean,
      d double,
      i integer
    );
    
    insert into test values ('abc', 'cde', 'hhh');
    
    select * from test;

    VS returns false, cde, hhh.

  15. Dave S

    20 Apr 2017 San Diego, California USA
    Edited 8 months ago

    Thanks...

    Last login: Wed Apr 19 21:28:17 on ttys000
    iMac-2:~ davesisemore$ /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

    Seems Xojo is internally messing with "Boolean" :(

    [Is VS written in Xojo as well?]

  16. Kem T

    20 Apr 2017 Pre-Release Testers, Xojo Pro New York

    As near as I can tell, no.

  17. Emile S

    20 Apr 2017 Europe (France, Strasbourg)

    What the SQLite sources says ?

    Is it someone who have modified the sources compile and use its new version to get the "correct" result ?

    Christian ?

  18. Dave S

    20 Apr 2017 San Diego, California USA
    Edited 8 months ago

    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.....

  19. Dave S

    20 Apr 2017 Answer San Diego, California USA
    Edited 8 months ago

    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

  20. Thomas T

    20 Apr 2017 Pre-Release Testers, Xojo Pro Europe (Germany, Munich)

    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.

  21. Newer ›

or Sign Up to reply!