What is wrong here? StringValue vs BooleanValue

  1. ‹ Older
  2. 7 months ago

    Eli O

    is not verified 19 Apr 2017 Europe (Berlin, Germany)
    Edited 7 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.

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

  4. Greg O

    20 Apr 2017 Xojo Inc Somewhere near Raleigh, NC

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

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

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

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

  8. Dave S

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

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

  10. Dave S

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

  11. Kem T

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

    As near as I can tell, no.

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

  13. Dave S

    20 Apr 2017 San Diego, California USA
    Edited 7 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.....

  14. Dave S

    20 Apr 2017 Answer San Diego, California USA
    Edited 7 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

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

  16. Dave S

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

    Here is a Method that will help in some situations..
    If you had a statement such as "SELECT * FROM myTABLE"
    and myTABLE has BOOLEAN fields that may or may not contain data other than 0,1,True,False
    this will return an expanded field list, detecting and casting "boolean" fields correctly

    If ONLY works for "SELECT *" at this time, but could easily be upgraded to accept a comma list of fields

    Public Function selectAstrix(srcDB as SQLiteDatabase,tablename as string) as string
      Dim rs As RecordSet
      Dim v() As String
      Dim sql As String
      dim name as string
      dim kind as string
      // if tablename has a "." then it is schema.tablename
      v=Split(tablename,".")
      sql="PRAGMA "
      If v.Ubound>0 Then sql=sql+v(0)+"."
      sql=sql+"table_info('"+v(v.Ubound)+"')"
      // pragma [<schema>.]table_info('<tablename>')
      rs=srcDB.SQLSelect(SQL)
      Redim v(-1)
      If srcDB.Error Then
        sendMSG "Error:"+ srcDB.ErrorMessage
        v.append "*"
        Return "* FROM "+tablename
      Else
        While Not rs.eof
          name=rs.field("name").StringValue
          kind=rs.field("type").StringValue
          If kind<>"BOOLEAN" Then 
            v.append(name)
          Else
            v.append("CAST("+name+" as Text) as "+name)
          end if
          rs.MoveNext
        Wend
      End If
      sql=Join(v,",")+" FROM "+tablename  // NOTE it does NOT prepend SELECT (on purpose)
      Return sql
      
    End Function

    One problem with this solution... that datatype of BOOLEAN is replaced with TEXT, so you no longer "really" know it was boolean to start with.

  17. Kem T

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

    Yes, that works in VS too.

  18. Kem T

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

    Having said that, I'd leave that up to the user to do if that's what they really want.

  19. I'd almost be inclined to think that NativeValue should give you the unaltered values from the DB and that StringValue and BooleanValue should transform it "appropriately" (which is what they seem to be doing)
    That too doesn't seem to work that way but its plausible it could be a bug or maybe a feature request

  20. Dave S

    20 Apr 2017 San Diego, California USA

    @Norman P I'd almost be inclined to think that NativeValue should give you the unaltered values from the DB and that StringValue and BooleanValue should transform it "appropriately" (which is what they seem to be doing)
    That too doesn't seem to work that way but its plausible it could be a bug or maybe a feature request

    I thought NativeValue might have been the answer, but it usually doesn't return anything at all regardless of fieldtype or contents

  21. Eli O

    is not verified 20 Apr 2017 Europe (Berlin, Germany)

    @Norman P I'd almost be inclined to think that NativeValue should give you the unaltered values from the DB and that StringValue and BooleanValue should transform it "appropriately" (which is what they seem to be doing)

    In my opinion StringValue should return "ABC" (as should Value and NativeValue). Or then the docs should state that the results are not conforming to SQLite.

    Here is what I was able to find out:

    CREATE Table test (
       fld1 BOOLEAN,
     }

    If you do

    Dim rst As RecordSet = db.SQLSelect("SELECT typeof(fld1) FROM test")

    ... you get a type affinity of nil.

    If you then do an insert with INSERT INTO test VALUES('ABC') and query it with

    Dim rst As RecordSet = db.SQLSelect("SELECT typeof(fld1) FROM test")

    ... you get a type affinity of text.

    My assumption is that the Xojo driver checks the column type with pragma table_info('test'), which returns "boolean". FieldSchema is probably based on that as it indeed returns 12 (= Boolean).

    But on each row for each field the type affinity should be queried to set the Value property of DatabaseField. So in my opinion these should be the results returned by DatabaseField to be consistent with the SQLite docs:

    BooleanValue –> False
    StringValue –> "ABC"
    Value –> "ABC"
    NativeValue –> "ABC"

or Sign Up to reply!