Zeoslib Portal

There is something wrong with Date in SQlite.
What I get in Zeos 7.3 r6490 Delphi 10.3.3-Win32 SQlite 3.31.0.
Michal

I thought DateTime is unsupported in SQLite...?

https://www.sqlite.org/datatype3.html

2.2. Date and Time Datatype
SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.
Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.

P.s.: this is why I use Unix timestamps and just convert it within the application.

Ok, it is possable, but why does the same sqlite (3310100) work correct with zeos 7.3 version 5939?

Best regards,

Theo

SQLite Expert allows you to enter Date:
Michal

I think that problems may arise from conflicts between the different standards used to present the Date(and their various settings in operating systems).

Michal

Hi Michal,

I tried several builds of zeos 7.3 and I can reproduce that all versions before and including build 5939 work exelent and have no problems with the dates in sqlite db. After build 5939 something is changed and problems arise. In my tests I use always the same applications, the same sqlite version and the same lazarus. I tried at different workstations, so in my opinion there is something changed in zeos whiche caused the problem.

Best regards,

Theo

tkamphuis wrote: ↑01.05.2020, 16:15 Ok, it is possable, but why does the same sqlite (3310100) work correct with zeos 7.3 version 5939?

Hello Theo - what should I say? If you want an embedded database that works correctly I can only advise against using sqlite and suggest Firebird. While sqlite explicitly isn't type safe, Firebird is. Declaring a date column will get you a date column in Zeos - always. Sqlite - by design - requires Zeos to do guesswork.

For finding the root of the probem: I did have a look at the changes after rev. 5939. Rev. 5940 has no changes concerning sqlite. So this revision also should work?
I think at this stage we really need an example application that demonstrates the problem. This doesn't need to be a complete project. But a table definition, example data and a pascal procedure that shows the problem would help.
If you want to upload an existing application somewhere, I could provide space for that and send you information regarding that as a private message.

Best regards,

Jan

Hello Jan,

Link to the example download:

https://www.dropbox.com/t/o36BS883vZQbdkb4

Best regards,

Theo

Hello Jan,

I think that I found a solution. I Use SQLite expert to define the tables. I used the the Date datatype in SQLite expert with the setting 'Store DateTime values' as Numeric. Now I changed that type to Text and voilá everything works. It seems that the latest zeos 7.3 could not handle the numeric date types anymore.

Best Regards,

Theo

@EgonHugeist, @Jan, @Fr0sT, @mdaems, All

ZEOS 7.3.x svn 6491:
https://sourceforge.net/p/zeoslib/code- ... sting-7.3/

Compiles:

- Lazarus 2.0.0(fpc 3.3.1) - Raspbian32 Buster for Raspberry Pi https://www.getlazarus.org/setup/?download#raspberry_pi,
- Lazarus 2.0.8(fpc 3.0.4)-Win32,
- Lazarus 2.0.8(fpc 3.0.4)-Win64,
- Delphi 7,
- RAD Studio 2007-Win32/C++32 with small hpp fixes,
- RAD Studio XE2 -Win32/Win64/C++32 with small hpp fixes,
- Delphi 10 Seattle-Win32/Win64,
- Delphi 10.3.3 Rio-Win32/Win64/Android/Android64.

mORMot PerfTest r5960 passes(Delphi 10.3.3 Rio Win32/Win64; Delphi 2007)

Michal

Hello Michal and Jan,

I tested the last revision 6495 and all date problems with SQLite are gone. Everything works like charm now.

Best Regards,

Theo

@EgonHugeist, @Jan, @Fr0sT, @mdaems, All

ZEOS 7.3.x svn 6495:
https://sourceforge.net/p/zeoslib/code- ... sting-7.3/

Compiles:

- Lazarus 2.0.0(fpc 3.3.1) - Raspbian32 Buster for Raspberry Pi https://www.getlazarus.org/setup/?download#raspberry_pi,
- Lazarus 2.0.8(fpc 3.0.4)-Win32,
- Lazarus 2.0.8(fpc 3.0.4)-Win64,
- Delphi 7,
- RAD Studio 2007-Win32/C++32 with small hpp fixes,
- RAD Studio XE2 -Win32/Win64/C++32 with small hpp fixes,
- Delphi 10 Seattle-Win32/Win64,
- Delphi 10.3.3 Rio-Win32/Win64/Android/Android64.

my mORMot PerfTest r5960 passes(Delphi 10.3.3 Rio Win32/Win64; Delphi 2007)

Michal

marsupilami wrote: ↑01.05.2020, 09:57As aehimself said - this version was very old. It was part of a test to see how we can integrate users that use git. I deleted that branch now.

Make sure that you delete the branch from GitHub too - it's still visible to me. https://www.wikihow.com/Delete-a-GitHub-Branch

aehimself wrote: ↑06.05.2020, 17:30

marsupilami wrote: ↑01.05.2020, 09:57As aehimself said - this version was very old. It was part of a test to see how we can integrate users that use git. I deleted that branch now.

Make sure that you delete the branch from GitHub too - it's still visible to me. https://www.wikihow.com/Delete-a-GitHub-Branch

Unfortunately - yes - it is still visible. svn-all-fast-export didn't delete it. I am not sure if svn-all-fast-export will continue to work correctly if do changes on the local git repository. What happens if I delete the branch from the github repo but not from the local one? I assume it should not be a problem?
Honestly time is short for me currently. So I try to not do anything that might break the currently working process. I might not have the time to fix things if it breaks now.

marsupilami wrote: ↑07.05.2020, 09:45Unfortunately - yes - it is still visible. svn-all-fast-export didn't delete it. I am not sure if svn-all-fast-export will continue to work correctly if do changes on the local git repository. What happens if I delete the branch from the github repo but not from the local one? I assume it should not be a problem?
Honestly time is short for me currently. So I try to not do anything that might break the currently working process. I might not have the time to fix things if it breaks now.

I guess if you delete it from GitHub only and try to push, it will either fail or recreate the remote branch; not sure. The real question is how branches are created when exporting via svn-all-fast-export...? Even if you delete your local branch, it might recreate it again just to return to the original state.
Anyhow; I don't mind my name being present on the unofficial repository ^^