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.
[code]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
[/code]
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
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!
[quote=438174:@Karen Atkocius]I am on 10.12.6 with Xojo2019R1.1
[/quote]
Ditto
I avoid such things like the plague
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
[quote=438180:@Karen Atkocius]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[/quote]
This code works, so I think is a bug with rs.Field and having spaces on the name:
[quote=438181:@Alberto De 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.
[/quote]
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:
[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
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!
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
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: [quote]DB Error: near “DATE”: syntax error[/quote]
I guess RS.Update is not handling the space as it is expected.
Maybe there is enough information to file a bug report?
[quote=438187:@Norman Palardy]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[/quote]
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.