SQLITE field Name handing issue causes Date bug

  1. 3 months ago

    Karen A

    May 24 Pre-Release Testers
    Edited 3 months ago

    Am I doing something wrong or is there a HUGE bug in date handling in the SQLiteDatabase?

    To test out what datatypes Xojo actually returns for Sqlite field 'Data Types"' (BTW RecordSet.ColumnTypes reports often ignores SQLite affinity rules for the specified Column type in what it reports) I created an in memory SQLite DB with a table called Numtest which has a column named 'a DATE' that has a column type of DATE. Prior to this I inserted one record.

    Dim D as New Date
    
    Dim RS as RecordSet= DB.SQLSelect("SELECT * FROM NumTest")
    RS.Edit
    RS.Field("a DATE").DateValue = D
    RS.Update
    RS.Close
    
    RS = DB.SQLSelect("SELECT * FROM NumTest")
    Dim D1 as Date  = RS.Field("a DATE").DateValue
    RS.Close
    
    Dim C as New Clipboard
    C.Text = "Input Date: " + D.SQLDateTime + EndOfLine + "Output Date: " + D1.SQLDateTime

    This is what is on the clipboard:

    Input Date: 2019-05-24 23:17:23
    Output Date: 2019-05-23 23:53:30

    If I am not doing something stupid that is very bad!!!!

    - Karen

    Dim db As New SQLiteDatabase
    
    if db.connect then
      //
    end if
    
    db.SQLExecute( "create table numtest (pk INTEGER PRIMARY KEY, ""a DATE"" DATE) ")
    db.SQLExecute( "insert into numtest (pk, ""a DATE"") values(1, '2019-01-09') ")
    
    Dim D As New Date
    
    Dim RS As RecordSet= DB.SQLSelect("SELECT * FROM NumTest")
    RS.Edit
    RS.Field("a DATE").DateValue = D
    RS.Update
    RS.Close
    
    RS = DB.SQLSelect("SELECT * FROM NumTest")
    Dim D1 As Date = RS.Field("a DATE").DateValue
    RS.Close
    
    Dim C As New Clipboard
    C.Text = "Input Date: " + D.SQLDateTime + EndOfLine + "Output Date: " + D1.SQLDateTime

    gave me
    Input Date: 2019-05-24 23:24:46
    Output Date: 2019-01-09 00:00:00

    Dim db As New SQLiteDatabase
    
    if db.connect then
      //
    end if
    
    db.SQLExecute( "create table numtest (pk INTEGER PRIMARY KEY, ""aDATE"" DATE) ")
    db.SQLExecute( "insert into numtest (pk, ""aDATE"") values(1, '2019-01-09') ")
    
    Dim D As New Date
    
    Dim RS As RecordSet= DB.SQLSelect("SELECT * FROM NumTest")
    RS.Edit
    RS.Field("aDATE").DateValue = D
    RS.Update
    RS.Close
    
    RS = DB.SQLSelect("SELECT * FROM NumTest")
    Dim D1 As Date = RS.Field("aDATE").DateValue
    RS.Close
    
    Dim C As New Clipboard
    C.Text = "Input Date: " + D.SQLDateTime + EndOfLine + "Output Date: " + D1.SQLDateTime

    gave me
    Input Date: 2019-05-24 23:25:39
    Output Date: 2019-05-24 23:25:39

    The only difference "a DATE" and "aDATE"

  2. Dave S

    May 24 San Diego, California USA
    Edited 3 months ago

    what happens if you do this

    dim D1 as new date
    d1.sqldatetime = rs.field("a date").stringvalue

    and the fields name is "a date" with a SPACE in it?

  3. Norman P

    May 24 Pre-Release Testers, Xojo Pro great-white-software.com/blog
    Dim db As New SQLiteDatabase
    
    Call db.connect
    
    db.SQLExecute( "create table numtest (pk INTEGER PRIMARY KEY, adate DATE) ")
    db.SQLExecute( "insert into numtest (pk, adate) values(1, '2019-01-09') ")
    
    Dim D As New Date
    
    Dim RS As RecordSet= DB.SQLSelect("SELECT * FROM NumTest")
    RS.Edit
    RS.Field("aDATE").DateValue = D
    RS.Update
    RS.Close
    
    RS = DB.SQLSelect("SELECT * FROM NumTest")
    Dim D1 As Date = RS.Field("aDATE").DateValue
    RS.Close
    
    Dim C As New Clipboard
    C.Text = "Input Date: " + D.SQLDateTime + EndOfLine + "Output Date: " + D1.SQLDateTime
    

    gave me

    Input Date: 2019-05-24 21:54:26
    Output Date: 2019-05-24 21:54:26

  4. Alberto D

    May 24 Pre-Release Testers
    Edited 3 months ago

    I think your code is not updating your database, so the output value is a date from yesterday.

  5. Karen A

    May 24 Pre-Release Testers

    @Norman P Input Date: 2019-05-24 21:54:26
    Output Date: 2019-05-24 21:54:26

    I am on 10.12.6 with Xojo2019R1.1

    Weird... Maybe i should reboot my mac?

    - karen

  6. Karen A

    May 24 Pre-Release Testers

    @Alberto D;Poo I think your code is not updating your database, so the output value is a date from yesterday.

    Not possible... its an in memory database and that value is never bettered into that field... and teh minutes change each time I run it!

    - karen

  7. Alberto D

    May 24 Pre-Release Testers
    Edited 3 months ago

    I think is the space on "a DATE" and maybe RS.Update

  8. Alberto D

    May 24 Pre-Release Testers Answer
    Edited 3 months ago
    Dim db As New SQLiteDatabase
    
    if db.connect then
      //
    end if
    
    db.SQLExecute( "create table numtest (pk INTEGER PRIMARY KEY, ""a DATE"" DATE) ")
    db.SQLExecute( "insert into numtest (pk, ""a DATE"") values(1, '2019-01-09') ")
    
    Dim D As New Date
    
    Dim RS As RecordSet= DB.SQLSelect("SELECT * FROM NumTest")
    RS.Edit
    RS.Field("a DATE").DateValue = D
    RS.Update
    RS.Close
    
    RS = DB.SQLSelect("SELECT * FROM NumTest")
    Dim D1 As Date = RS.Field("a DATE").DateValue
    RS.Close
    
    Dim C As New Clipboard
    C.Text = "Input Date: " + D.SQLDateTime + EndOfLine + "Output Date: " + D1.SQLDateTime

    gave me
    Input Date: 2019-05-24 23:24:46
    Output Date: 2019-01-09 00:00:00

    Dim db As New SQLiteDatabase
    
    if db.connect then
      //
    end if
    
    db.SQLExecute( "create table numtest (pk INTEGER PRIMARY KEY, ""aDATE"" DATE) ")
    db.SQLExecute( "insert into numtest (pk, ""aDATE"") values(1, '2019-01-09') ")
    
    Dim D As New Date
    
    Dim RS As RecordSet= DB.SQLSelect("SELECT * FROM NumTest")
    RS.Edit
    RS.Field("aDATE").DateValue = D
    RS.Update
    RS.Close
    
    RS = DB.SQLSelect("SELECT * FROM NumTest")
    Dim D1 As Date = RS.Field("aDATE").DateValue
    RS.Close
    
    Dim C As New Clipboard
    C.Text = "Input Date: " + D.SQLDateTime + EndOfLine + "Output Date: " + D1.SQLDateTime

    gave me
    Input Date: 2019-05-24 23:25:39
    Output Date: 2019-05-24 23:25:39

    The only difference "a DATE" and "aDATE"

  9. Karen A

    May 24 Pre-Release Testers

    @Dave S what happens if you do this

    dim D1 as new date
    d1.sqldatetime = rs.field("a date").stringvalue

    Doing that i get:
    Input Date: 2019-05-25 00:27:14
    Output Date: 2019-05-23 23:53:30

    and the fields name is "a date" with a SPACE in it?

    Yes. That is allowed if you quote the field name in the column definition.

    - karen

  10. Karen A

    May 24 Pre-Release Testers
    Edited 3 months ago

    @Alberto D;Poo The only difference "a DATE" and "aDATE"

    Well there is the bug! using "A Date" as column name should be legal.

    I don't typically use column names with spaces but i did it for readability and less typing as I did not want to have to alias the column names in the Select statement!

    - karen

  11. Alberto D

    May 24 Pre-Release Testers
    Edited 3 months ago

    But is good that is not a Date bug or something mayor. I think you can change "a DATE" to "a_DATE" and should work, let me test.

    Edit: Also using 10.12.6 and Xojo2019R1.1

  12. Norman P

    May 24 Pre-Release Testers, Xojo Pro great-white-software.com/blog
    Edited 3 months ago

    @Karen A I am on 10.12.6 with Xojo2019R1.1

    Ditto

    @Karen A Yes. That is allowed if you quote the field name in the column definition.

    I avoid such things like the plague :)

    @Karen A Well there is the bug! using "A Date" as file name should be legal.

    If I need such a thing I use _ instead of a space
    at least that is trivially portable - the space not so much
    now I know some will say "but i'll never have to move this to another database" and then when they do all the pain comes to roost

  13. Alberto D

    May 24 Pre-Release Testers

    @Karen A Well there is the bug! using "A Date" as column name should be legal.

    I don't typically use column names with spaces but i did it for readability and less typing as I did not want to have to alias the column names in the Select statement!

    - karen

    This code works, so I think is a bug with rs.Field and having spaces on the name:

    Dim db As New SQLiteDatabase
    
    if db.connect then
      //
    end if
    
    db.SQLExecute( "create table numtest (pk INTEGER PRIMARY KEY, ""a DATE"" DATE) ")
    db.SQLExecute( "insert into numtest (pk, ""a DATE"") values(1, '2019-01-09') ")
    
    Dim D As New Date
    
    db.SQLExecute( "UPDATE numtest SET ""a Date"" = '"+d.SQLDateTime+"' ")
    
    dim rs as RecordSet = DB.SQLSelect("SELECT * FROM NumTest")
    Dim D1 As Date = RS.Field("a DATE").DateValue
    RS.Close
    
    Dim C As New Clipboard
    C.Text = "Input Date: " + D.SQLDateTime + EndOfLine + "Output Date: " + D1.SQLDateTime
  14. Karen A

    May 24 Pre-Release Testers

    @Alberto D;Poo But is good that is not a Date bug or something mayor. I think you can change "a DATE" to "a_DATE" and should work, let me test.

    Don't bother BUT the bug goes beyond using column names ... You get teh same issue if you alias columns:

    after getting rid of spaces n teh column names using this code:

    Dim D as New Date
    
    Dim RS as RecordSet= DB.SQLSelect("SELECT key0, aDate as 'a Date' FROM NumTest")
    RS.Edit
    RS.Field("a Date").DateValue = D
    RS.Update
    RS.Close
    
    RS = DB.SQLSelect("SELECT key0, aDate as 'a Date' FROM NumTest")
    
    Dim D1 as new Date 
    D1.SQLDateTime = RS.Field("a Date").StringValue
    RS.Close
    
    Dim C as New Clipboard
    
    C.Text = "Input Date: " + D.SQLDateTime + EndOfLine + "Output Date: " + D1.SQLDateTime

    I get:
    Input Date: 2019-05-25 01:09:28
    Output Date: 2019-05-23 23:53:30

    That still is an edge case, though potentially less rare (and so a bigger issue) than using spaces in column names... i suspect any character that would cause ether column name to need to be quoted (like a period) would cause an issue.

    I seem to keep getting tied up with edge cases! I guess Xojo has more than few rough edges!

    - karen

    - karen

  15. Norman P

    May 24 Pre-Release Testers, Xojo Pro great-white-software.com/blog

    Spaces in column names are "unusual"
    And every time I've encountered them its "hey why doesn't this work when I do ....."
    Be safe and get rid of the spaces
    Replace them with _ and be happy and it will work on any db

  16. Alberto D

    May 24 Pre-Release Testers

    My guess is that RS.Update is the problem.

    I think this because this works:

    Dim D1 as new Date 
    D1.SQLDateTime = RS.Field("a Date").StringValue
    RS.Close

    I changed part of the code to:

    Dim RS As RecordSet= DB.SQLSelect("SELECT * FROM NumTest")
    RS.Edit
    RS.Field("a DATE").DateValue = D
    RS.Update
    If db.Error Then
      MsgBox("DB Error: " + db.ErrorMessage)
    End If
    RS.Close

    and this is what I get:

    DB Error: near "DATE": syntax error

    I guess RS.Update is not handling the space as it is expected.

    Maybe there is enough information to file a bug report?

  17. Karen A

    May 24 Pre-Release Testers
    Edited 3 months ago

    @Norman P Spaces in column names are "unusual"
    And every time I've encountered them its "hey why doesn't this work when I do ....."
    Be safe and get rid of the spaces
    Replace them with _ and be happy and it will work on any db

    I usually do... but this time I decided not to.

    It also happens if there are spaces or periods in column name aliases, which are meant to be lay person readable (well so are column names actually) ... so it is a bug that should be fixed (or at least documented!), even though easily avoided.

  18. Emile S

    May 24 Europe (France, Strasbourg)

    @Alberto D;Poo if db.connect then // end if

    This is useless…

  19. Rick A

    May 25 (Brazil. GMT-3:00)

    @Emile S @Alberto De ;Poo if db.connect then // end if

    This is useless…

    if not db.connect then
      msgbox "Wow! Connection crash. Houston, we have a problem."
    end if

    The topic name should be "Database Field names handling, silent huge bug"

  20. Alberto D

    May 25 Pre-Release Testers

    @Emile S This is useless…

    I will not say it is 'useless', because if I comment that code then it doesn't work :)

    I learned that from the docs , I guessed is a good practice to check for errors (I didn't put the complete code in this example).

  21. Newer ›

or Sign Up to reply!