save/load boolean seems failing on 2.1

  1. 2 weeks ago

    On r2.1, saving a booleanValue, to a SQLite table on disk. , seems that always is re-loaded as false
    for example: the saving line looks like:

    row.Column("multiplayer").BooleanValue = globals.multiplayer

    globals.multiplayer is true at saving time.

    When loading the line looks like

    globals.Multiplayer = rs.Column("Multiplayer").BooleanValue

    is always loaded as false.
    ===
    Reinstalled and tested on r2 where the code for saving was

    row.BooleanColumn("multiplayer") = globals.Multiplayer

    and the load

    globals.Multiplayer = rs.Column("Multiplayer").BooleanValue

    the boolean is loaded as true if saved as true.

    Any suggestion to go on with 2.1 and saving the day.

  2. Tobias B

    Nov 29 Pre-Release Testers, Xojo Pro Bern, Switzerland

    can you check what actually is written to the SQLite DB (e.g. using the commandline interface sqlite3)? The documentation states

    Boolean fields are strict about what they expect when using the BooleanValue function: The values "0" and "False" are treated as False and "1" and "True" are treated as True. The behavior of any other values is undefined when retrieved using BooleanValue. Use StringValue to retrieve the original values in the field.

  3. I may try, but never used the commandline interface sqlite3, could you give an idea?
    But with the Xojo debbuger the rowset shows all booleans as nil

  4. Tobias B

    Nov 29 Pre-Release Testers, Xojo Pro Bern, Switzerland

    @Enric H But with the Xojo debbuger the rowset shows all booleans as nil

    that would indicate that the problem occurs when writing the data. How did you accomplish this using a DatabaseRow?

    My test failed with another bug way earlier when writing using a DatabaseRow, the remaining seem to work fine:

    //intit
    Dim db As New SQLiteDatabase
    db.Connect
    db.ExecuteSQL("CREATE TABLE t (id INT PRIMARY KEY, b1 BOOLEAN, b2 BOOLEAN);")
    
    //insert
    Dim row As New DatabaseRow
    row.Column("b1").BooleanValue = True
    row.Column("b2").BooleanValue = False
    //oh my! the following gives 'near ")": syntax error'
    'db.AddRow("t", row) 
    db.ExecuteSQL("INSERT INTO t(b1, b2) VALUES('True', 'False');") //this does not test writing
    
    //read
    Dim rs As RowSet
    rs = db.SelectSQL("SELECT * FROM t;")
    //...using RowSet
    System.DebugLog rs.Column("b1").NativeValue + rs.Column("b2").NativeValue
    System.DebugLog Str(rs.Column("b1").BooleanValue) + Str(rs.Column("b2").BooleanValue)
    //...using DatabaseRow
    For Each r As DatabaseRow In rs
      System.DebugLog r.Column("b1").NativeValue + r.Column("b2").NativeValue
      System.DebugLog Str(r.Column("b1").BooleanValue) + Str(r.Column("b2").BooleanValue)
      Break //look in debugger
    Next r
    
    //edit
    rs.MoveToFirstRow
    rs.EditRow
    rs.Column("b1").BooleanValue = True
    rs.Column("b2").BooleanValue = False
    rs.SaveRow
    
    //read again
    rs = db.SelectSQL("SELECT * FROM t;")
    //...using RowSet
    System.DebugLog rs.Column("b1").NativeValue + rs.Column("b2").NativeValue
    System.DebugLog Str(rs.Column("b1").BooleanValue) + Str(rs.Column("b2").BooleanValue)
    //...using DatabaseRow
    For Each r As DatabaseRow In rs
      System.DebugLog r.Column("b1").NativeValue + r.Column("b2").NativeValue
      System.DebugLog Str(r.Column("b1").BooleanValue) + Str(r.Column("b2").BooleanValue)
      Break //look in debugger
    Next r
  5. I posted a Feedback and they already answered:

    This issue has been reproduced based on the information provided.
    A workaround might be to use an Integer field instead and use 0 to represent False and 1 to represent True.
    This is pretty much how SQLite implements its Boolean data type.

    So, the solution for now, is changing all booleans to integers.

  6. Tobias B

    Nov 29 Pre-Release Testers, Xojo Pro Bern, Switzerland

    @Enric H I posted a Feedback and they already answered:

    thanks. If you file issues discussed in the Forum in Feedback, please also post a link to it: Feedback Case #58470

    @Tobias B My test failed with another bug way earlier when writing using a DatabaseRow

    this bug was already found by @Dale A and filed as Feedback Case #58403

  7. Alberto D

    Nov 29 Pre-Release Testers, Xojo Pro
    Edited 2 weeks ago

    Change

    row.Column("multiplayer").BooleanValue = globals.multiplayer

    to

    row.Column("multiplayer").IntegerValue = if(globals.multiplayer,1,0)

    Edit: from my tests, pulling the information from the Database with BooleanValue works ok

  8. Edited 2 weeks ago

    @Alberto D Change

    row.Column("multiplayer").BooleanValue = globals.multiplayer

    to

    row.Column("multiplayer").IntegerValue = if(globals.multiplayer,1,0)

    Edit: from my tests, pulling the information from the Database with BooleanValue works ok

    Thanks Alberto, this morning I realize that this was the workaround but I decided to take day off.

  9. By the way, Alberto,

    Edit: from my tests, pulling the information from the Database with BooleanValue works ok

    I tested and it not works on me.

    when saving must change

    // row.Column("IsDead").BooleanValue= u.isDead

    to

    row.Column("IsDead").IntegerValue = if(u.isDead,1,0)

    when loading change

    // u.isDead = rs.Column("IsDead").BooleanValue

    to

    u.isDead = if (rs.Column("IsDead").IntegerValue=1,true,false)

    x Tables x booleans in each table

  10. Alberto D

    Nov 29 Pre-Release Testers, Xojo Pro

    Did you get an error or always False too?

    I downloaded Robin's example from your Feedback case.
    Only changed this:

    'row.Column("Flag").BooleanValue = flag
    row.Column("Flag").IntegerValue = if(flag, 1, 0)

    and it works too (this is another test from what I did before), in other words I didn't change the ShowData method that includes:

    str(row.Column("Flag").BooleanValue))
  11. Martin T

    Nov 29 Pre-Release Testers Germany

    @Enric H I posted a Feedback and they already answered:

    So, the solution for now, is changing all booleans to integers.

    Another bug for a dot release 2019r2.2

  12. No Alberto this do not work.
    If you save to disk

    row.Column("IsDead").BooleanValue= u.isDead // where u.isDead is true

    and then load from disk as

    u.isDead = if (rs.Column("IsDead").IntegerValue=1,true,false)

    the bool is loaded always as false.
    You need to save it as

    row.Column("IsDead").IntegerValue = if(u.isDead,1,0)

    Robin's exemple do not save to disk file and reload from disk.

  13. Norman P

    Nov 29 Pre-Release Testers, Xojo Pro under THE bus
    Edited 2 weeks ago

    @MartinTrippensee Another bug for a dot release 2019r2.2

    unlikely they'll do a 2.2
    check the betas channel :)

    edited : to clarify

  14. @Norman P unlikely they'll do a 2.2
    check the betas channel :)

    How?

  15. @Alberto D Did you get an error or always False too?

    Usually just the wrong value on the boolean, several times Xojo crashed in the debbuger checking the rowset contents

  16. Norman P

    Nov 29 Pre-Release Testers, Xojo Pro under THE bus

    sorry I meant that for Martin who is a member of the pre-release group as well

  17. Alberto D

    Nov 29 Pre-Release Testers, Xojo Pro
    Edited 2 weeks ago

    @Enric H No Alberto this do not work.
    If you save to disk

    row.Column("IsDead").BooleanValue= u.isDead // where u.isDead is true

    and then load from disk as

    u.isDead = if (rs.Column("IsDead").IntegerValue=1,true,false)

    the bool is loaded always as false.
    You need to save it as

    row.Column("IsDead").IntegerValue = if(u.isDead,1,0)

    Robin's exemple do not save to disk file and reload from disk.

    Sorry, I think I was not clear enough. The save to database (BooleanValue) is broken, so you need:

    row.Column("IsDead").IntegerValue = if(u.isDead,1,0)

    to save to database, once is there (you can open the sqlite file with another program and see 1 or 0 as expected, but with the original code you will see that nothing was written to the boolean column.
    When you load from the sqlite database you can use:

    u.isDead = rs.Column(ïsDead").BooleanValue

    and will get the correct value.

    Robin's example does create and save the information into Desktop/example.sqlite and pulls the information from that file to show the values on the listbox.

  18. The save to database (BooleanValue) is broken

    Yes, only the save is broken, this simplify the workaround at 50%

  19. Rick A

    Nov 29 Pre-Release Testers (Brazil. UTC-3:00)
    Edited 2 weeks ago

    This bug can silently break things (and damage data in databases) so it is a priority to fix this. And also check the behavior of row.Column(name).BooleanValue in the other supported databases.

or Sign Up to reply!