Good evening group
I converted my Access database to SQLite.
I set up the connection and I was also able to connect to the SQlite database. I can read a simple text (name and weight), but when I go to the more complex ones I get errors that did not come out before, for example:
near “#2023” syntax error
or
Date string cannot be empty
Why is this because the date management changes between Access and SQLite?
It’s likely differences in SQL syntax between Access and SQLite. Can you show the SQL code that is producing errors.
In the code:
rows = db.SelectSQL("SELECT * FROM Ingressi “+filtro + " order by Data”)
Error is:
Filter where Year(data)=2024
Reason no such function: Year
That’s the reason. SQLite does not offer that function. You can see the docs:
https://www.sqlite.org/lang_datefunc.html
You need to update your ‘filtro’
Dates surrounded by # is an Access convention.
Dates in SQLite are usually held as strings (but could be ‘real’ or integer)
String dates would be ‘2016-01-01 10:20:05.123’
Assuming your dates went in as strings, you could perhaps filter
where data >'2024' and data < '2025'
I think I need to study sqlite formatting for dates and its application in fields … where can I find this information?
I need to understand how to change the search from Access to SQlite.
In access:rows = db.SelectSQL(“SELECT * FROM Promemoria where Data=”#“+ FiltroData.SQLDate+”#“), in SQlite
rows = db.SelectSQL(“SELECT * FROM Promemoria where Data='”+ FiltroData.SQLDate+”'")
In Access: DataFormattata =rows.Column(“Data”).DateValue
in Sqlite ?
or
In Access: DataFormattata=rows2.Column(“Data”).DateTimeValue
in Sqlite ?
In Access: filtro=filtro+ " and (Month(data)=1 or Month(data)=2 or Month(data)=3)"
In SQlite ?
You may start here:
Don’t be fooled by the title above, the site show far more that a simple introduction.
Provided you read xojo SQLiteDatabase pages and get an eye on sqlite.com/sqlite.org.
Alternatively, you may also check:
www.w3schools.com/sql
For example, i have this:
dim DataFormattata as new date
DataFormattata.SQLDate =rows.Column(“Data”).DateTimeValue.ToString
rows.column(“Data”)=2022-04-21 00:00:00
I need read: 21/04/2022
dim DataFormattata as new date
DataFormattata.SQLDate =rows.Column(“Data”).DateTimeValue.ToString
rows.column(“Data”)=2022-04-21 00:00:00
You are instantiating an old date (API) object, and you are passing a DateTimeVal ? (API2).
Create a small db with only say 10 Records and play with it / read the SQLiteDatabase documentation.
Add some break in your code to read how things are in the debugger…
go to the Data types part and read there how data types are stored in SQLite.
If you can use DateTime instead of Date, it may help using DateTime.FromString DateTime.ToString, see:
https://documentation.xojo.com/api/data_types/datetime.html#description
https://documentation.xojo.com/api/data_types/datetime.html#datetime-fromstring
https://documentation.xojo.com/api/data_types/datetime.html#datetime-tostring
This may work:
dim DataFormatData as DateTime
DataFormatData = DateTime.FromString(rows.Column("Data").DateTimeValue.ToString)
dim s as string = DataFormatData.ToString("dd/MM/yyyy") //will give 21/04/2024
Ciao Federico,
the date as 21/04/2022 is a one of the many way you can view a date.
In Sqlite date are always in as sqlDate (IE yyyy-mm-ddd in your example: 2022-04-21), probably as sqlDateTime (2022-04-21 00:00:00 for example)
In SqLite they are always string (so you can find for substring or using the SqLite date functions)
If the user enter a date as string in your format you can use the Datetime fuction to have a DateTime object and then execute your query.
for your query, since you are learning this topic, use the proper way, don’t concatenate strings:
rows=db.SelectSql(“select * from promemoria where data=?”, myDateTime.toString)
I always convert date/time to seconds from 1970 and store them as doubles, thus making comparisons easy. I convert to/from seconds in Xojo, and handle the timezone at the same time. That way, too, the user can choose the date/time format the application gives them. IMO it is always best to separate the presentation layer from the data storage layer.
You are right, presentation is different from storage.
In any case date time in sqlite is a string natively and you have a lot of native functions.
Moreover Xojo can read them directly as datetime object without any conversion.
You can create a DateTime directly using seconds since 1970, too. And the storage requirement is smaller in your SQLite database.
SQL/ANSI date format (yyyy-mm-dd) was DESIGNED to make comparisons easy.
Hello group
I continue my attempts to read the date in the correct format that interests me.
If I read the date field with from my database, i’m read in my ListBox:
2022-05-08 00:00:00 from rows.Column(“Data”).StringValue
I wanted to set the format European/Italy/Rome and I tried to write:
Datetime.FromString(rows.Column(“Data”), Nil,New TimeZone(“Europe/Rome”))
and NOT WORK
i’m test this code:
Var d as DateTime = datetime.FromString(rows.Column(“Data”))
Var s As String = d.ToString(Locale.Current, DateTime.FormatStyles.Short, DateTime.FormatStyles.None) and works well. I get the format I’m interested in, the one I use here in Italy: dd-mm-yyyy
Is there another way to achieve the same result while spending fewer resources?
Are you trying to add the resulting DateTime to the Listbox?
Use that.
At the moment I was able to read the dates in the format that interests me.
In the Database they are stored as Ex: 2024-10-31 00:00:00
and I read them in the Italian format with the function that I created and that I will use throughout the program:
ReadDataFromDB(DataRead as string) as string
Var d as DateTime = datetime.FromString(DateRead)
Var s As String = d.ToString(Locale.Current, DateTime.FormatStyles.Short, DateTime.FormatStyles.None)
return s
and I would also like to store dates from a text field written as 12/31/2024 in the format 12/31/2024 00:00:00 and I tried to write another function:
StoreDateInDB(DateRead as string) as datetime
Var d As String = DataRead
Var s As DateTime = DatetIme.FromString(d)
return s
to record it in the database field with:
RecordRecord.Column(“Data”).DateTimeValue = StoreDatainDB(textData.text)
But it doesn’t work. Where am I wrong?