SQLITE field Name handing issue causes Date bug

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]

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

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?

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

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

[quote=438171:@Norman Palardy]
Input Date: 2019-05-24 21:54:26
Output Date: 2019-05-24 21:54:26[/quote]

I am on 10.12.6 with Xojo2019R1.1

Weird… Maybe i should reboot my mac?

  • karen

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

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

[code]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[/code]

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

[code]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[/code]

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”

[quote=438169:@Dave S]what happens if you do this

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

[/quote]

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

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

  • karen

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

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

[quote=438174:@Karen Atkocius]I am on 10.12.6 with Xojo2019R1.1
[/quote]
Ditto

I avoid such things like the plague :slight_smile:

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:

[code]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[/code]

[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

Dim C as New Clipboard

C.Text = "Input Date: " + D.SQLDateTime + EndOfLine + "Output Date: " + D1.SQLDateTime
[/code]

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

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

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

This is useless…

[quote=438203:@Emile Schwarz] @Alberto De ;Poo if db.connect then // end if

This is useless…[/quote]

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”

I will not say it is ‘useless’, because if I comment that code then it doesn’t work :slight_smile:

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