Roberto Padovani wrote:
Hi all!
two things: 1) a (possible) improvement to sqlite3ds; 2) the old
problem with dates and time
1) I found a sqlite database that uses the TIMESTAMP data type, but
this is not directly supported by the Tsqlite3Dataset written by Luiz.
I added the if-else checks in sqlite3ds.pas for this type, which
refers to ftTimestamp of TFieldType in db.pas. And I also added
db.DefaultFieldClasses[ftTimeStamp] := TDateTimeField at run-time,
because in db.pas it is defined as nil (and I didn't want to recompile
it).
So, now my app can understand a timestamp field as a TDateTime instead
of a string...
2)and here comes the problem! The code:
var d: TDateTime;
..
d := database.FieldByName('birthday').asDateTime;
...
will always store the zero date: 30 Dec 1899
why ?
It's necessary to see the changes you made.
To add a new type is necessary to modify InternalInitFieldDefs,
GetFieldData and SetFieldData
Being urgent, at the moment I am reading the dates as strings by using
the "date" or "datetime" function of SQLite in the query, i.e.:
SELECT date(birthday) AS string_birthday FROM people;
and then parsing them with the powerful functions in the freepascal
RTL, but I would like to understand this once for all.
Here's how sqlite works (for good and bad):
- You can create tables with any "field type": TIMESTAMP, TIME_STAMP,
QWERTY etc
- In any of this "field type" you can store anything: a integer, a
float, a string
Many sqlite managers make assumptions (each one create its own
convention). Examples:
BOOLEAN will store TRUE or FALSE strings,
DATE will store "02-12-2007".
This is completely random.
So program X can store DATE as "02-12-2007" but program Y will store as
"02/12/2007". The program Z, worried with memory stores as "02122007"
but program W is more smart so will store as "021207".
Supporting these conventions it would lead to code bloat and performance
issues and someone would always ask "hey my DATE format is not
supported, please do it".
In other words, sqliteds, as it was designed, has the objective of add a
way to fpc programs to use sqlite as a database backend. So it does. It
was not designed for access data files created with other managers or
frameworks.
See as a balance between feature and code size/performance. Any design
taken has advantages and disadvantages. In the sqliteds case, the
decision was to trend to code simplicity, privilege the most common
cases in detriment of not so common.
If you come until here, don't be pessimist. Some solutions:
- Don't forget is open source. You can modify to fill your needs. I can
help with this. Send what you already did.
- If you don't want to modify sqliteds file directly you can write
descendants.
- Try sqlitepass and sqldb/sqlite3. I vaguely remenber of sqlitepass to
support timestamp. I don't know about sqldb.
- If you just want to import the data of a "strange" db file (you won't
need to access directly this file all time) than convert to the sqliteds
format (Double). I already started a program that does this but not
finished. I can also help you.
Luiz
_________________________________________________________________
To unsubscribe: mail [EMAIL PROTECTED] with
"unsubscribe" as the Subject
archives at http://www.lazarus.freepascal.org/mailarchives
