Aurelian_N
(Aurelian N)
February 21, 2019, 12:59pm
1
Greetings.
So I have an app that does some processing and while doing an Insert in the database on the end of processing it seems that the date is super weird, instead of the right one.
Here is a part of the code :
[code]Dim nD As New Date
Dim sql As String
sql = “INSERT INTO users (name, pass, type, created) VALUES (‘c’, ‘a’, ‘b’, " + nD.SQLDate + " )”
Self.SQLExecute(sql)
Self.Commit
[/code]
So based on the
Dim nD As New Date
I should get a date of 2019-02-21 and the time but instead in the record side I get
just [quote]1996 [/quote] without any other details.
Any idea if this is a bug or something wrong with XOJO or current SQLite version ?
As usual , Latest OSX , Latest XOJO
Thanks.
Sascha_S
(Sascha S)
February 21, 2019, 1:05pm
2
You Statement is missing quotation marks around the Date.
Here’s a Prepared Statement Variant of your Code:
[code]Dim nD As New Date
Dim p As PreparedSQLStatement
p = db.Prepare(“INSERT INTO users (name, pass, type, created) VALUES (?, ?, ?, ?)”)
p.Bind(0, “c”, SQLitePreparedStatement.SQLITE_TEXT)
p.Bind(1, “a”, SQLitePreparedStatement.SQLITE_TEXT)
p.Bind(2, “b”, SQLitePreparedStatement.SQLITE_TEXT)
p.Bind(3, nd.SQLDate, SQLitePreparedStatement.SQLITE_TEXT)
p.SQLExecute[/code]
Aurelian_N
(Aurelian N)
February 21, 2019, 1:15pm
3
[quote=425681:@Sascha S]You Statement is missing quotation marks around the Date.
Here’s a Prepared Statement Variant of your Code:
[code]Dim nD As New Date
Dim p As PreparedSQLStatement
p = db.Prepare(“INSERT INTO users (name, pass, type, created) VALUES (?, ?, ?, ?)”)
p.Bind(0, “c”, SQLitePreparedStatement.SQLITE_TEXT)
p.Bind(1, “a”, SQLitePreparedStatement.SQLITE_TEXT)
p.Bind(2, “b”, SQLitePreparedStatement.SQLITE_TEXT)
p.Bind(3, nd.SQLDate, SQLitePreparedStatement.SQLITE_TEXT)
p.SQLExecute[/code][/quote]
I guess just doing the same think so it worked.
Thanks again.
AlbertoD
(AlbertoD)
February 21, 2019, 1:55pm
4
[quote=425680:@Aurelian Negrea]So based on the
Dim nD As New Date
I should get a date of 2019-02-21 and the time but instead in the record side I get
1996[/quote]
Interesting that in this case, without the correct quotes, the subtract operation is performed:
2019-02 = 2017
2017-21 = 1996
Just for reference, I think the correct sql string is:
sql = "INSERT INTO users (name, pass, type, created) VALUES ('c', 'a', 'b', '" + nD.SQLDate + "' )"
but you should use prepared statements as mentioned by Sascha.