And as i told you in your other thread(s): Don't use those DataTypes!
There is no DateTime DataType, and NUMERIC is not a DataType but an Affinity.
Use the proper Storage-Classes.
And TDateTime wants a double.
"Spot the mistake"
Close. Use REAL instead of Double (but SQLite will map Double to Real in 99.9999% of cases)
After your reply, I recreated the DB with DATETIME mapped to DOUBLE, in order to follow strictly your indications (unless I did not undestrand them fully).
The field in question now is:
Hereafter it is a query made on that table, where you can see that it does contain data (=writing seems to work, probably writing doe not guess datatype from db structure)Remove the LIMIT 5
Remove the LIMIT 5
I want to see ALL those Dates
Close. Use REAL instead of Double (but SQLite will map Double to Real in 99.9999% of cases)
OK, looks consistent.Remove the LIMIT 5
I want to see ALL those Dates
Attached
OK, looks consistent.Remove the LIMIT 5
I want to see ALL those Dates
Attached
Will have to check what got borked.
By any chance: Could you give me the INSERT-Statements for those Values? (dump the table).
Otherwise i would have to write a routine to import it to my local sqlite-database
Thx.OK, looks consistent.Remove the LIMIT 5
I want to see ALL those Dates
Attached
Will have to check what got borked.
By any chance: Could you give me the INSERT-Statements for those Values? (dump the table).
Otherwise i would have to write a routine to import it to my local sqlite-database
Attached!
Oh, you gotta be kidding me.
From Fields.inc - Line 407 --> Line 513Funny enough: AsFloat throws no exception despite the same code as above being in there
and this also didn't throw an exceptionSo what in blazes is going on?
That said: I apologize to tt
I did get the same Exception.
I just did. And you're right: AsDateTime is not throwing an exception.
Did you try also havin the eventDate field defined as follows?
eventDate DATETIME NOT NULL
That will work for some criptic reason (feeling is that TField or derived class guesses file type from ddl).
And with SQLite you're stuck with what to use: Do it correctly, and use REAL, INTEGER or TEXT for Date(Time), and remember it in the Frontend-Code,
or do it wrong using DATETIME as DataType for the Column during creation, so it gets written into the DDL-Metadata, and HOPE SQLite never changes this.
Regarding DateTime data type in SQLite3 look at "2.2. Date and Time Datatype" on https://www.sqlite.org/datatype3.html.
You can use DATETIME as "datatype" in CREATE TABLE statement. SQLite3 will perfectly accept it and Lazarus/FPC will recognize it and will map it to ftDateTime field type.
Yeah, that about sums it up.
The only thing coming to mind would be subclassing TSQLQuery, and override AsDateTime and the others with your own Getter/Setter‘s
EDIT: or maybe a class-helper.
Would have to chew on it.
After thinking about it, i don't think there is an "easy" solution (nevermind that subclassing/extending TSQLQuery would be the wrong class, since AsDateTime is a property of TField)Yeah, that about sums it up.
The only thing coming to mind would be subclassing TSQLQuery, and override AsDateTime and the others with your own Getter/Setter‘s
EDIT: or maybe a class-helper.
Would have to chew on it.
I would be glad to be helpful in some way.
Bottom Line: Since you have to "support" multiple DBMS (are you using TSQLConnector?), you'll probably have to write individual code for each connection-type
Kind of like (Pseudo-Code)
If ConnType='SQLite' And FieldType='Float' Then ReadAsFloat
Down-Side: You'd have to know if the Field you want to read is a "real" Float-Value (Say, Money-Balance) or a DateTime-Value
Just out of curiosity: How do you handle the different SQL-"Dialects"? (Yeah, nothing to do with the Problem of this Thread)
*snipp* or year-month-date-time order and time in UTC.Whatever for?
Handle local date/time in code. But everybody knows that.
Just make sure you store in either Tdatetime (double) or year-month-date-time order and time in UTC.
Handle local date/time in code. But everybody knows that.
Ah.... what i thought.Just out of curiosity: How do you handle the different SQL-"Dialects"? (Yeah, nothing to do with the Problem of this Thread)
So far I didn't encounter situations in which I need it, mostly my programs do quite standard sql queryies and if there is some logic involved I prefer to keep it in code, it makes me do more work in some situations, but reduces need to dive into db specific operations (like stored procedures).
In case it happens it would be something like
more or less what you suggested.
@ZvoniAh... OK
That is what I mean, but how a programmer handles it is often plain wrong. Of course a decent Database adheres to what I wrote. Programmers often make the wrong assumptions about date and time handling. That is all. Easy for you and me, but not for everyone.
At this point I noticed that liquibase was mapping DATETIME datatype to REAL and all this story started.It is no surprise. SQLite3 in fact stores datetime as REAL (or STRING) as stated in documentation.
We know that.At this point I noticed that liquibase was mapping DATETIME datatype to REAL and all this story started.It is no surprise. SQLite3 in fact stores datetime as REAL (or STRING) as stated in documentation.
So another library may recognize this data (which have real affinity) as real numbers - these real nubers represents Julian days ...
But when you use SQLite3 built-in date and time functions like datetime() on this "real" column you will see valid datetime values ...
Sorry if I say a stupid thing, I am old school, but I wonder if we can still call SQLite a database. It seems to me rather a byte array logger.It's a clear Yes and No :D
Apart my uninteresting thoughts, with this example we see that column datatype definition is optional, and even different rows of a table can contain unrelated datatypes.
Is the concept of datatype itself to be senseless here.
SQLite supports a strict typing mode, as of version 3.37.0 (2021-11-27), that is enabled separately for each table.Bottom Line: Use the modifier, and you get what you're used to
.....
.....
If the value cannot be losslessly converted in the specified datatype, then an SQLITE_CONSTRAINT_DATATYPE error is raised.
Every column definition must specify a datatype for that column. The freedom to specify a column without a datatype is removed.So, no DATETIME, VARCHAR and companions allowed (at least up until now)
The datatype must be one of following:
INT
INTEGER
REAL
TEXT
BLOB
ANY
No other datatype names are allowed, though new types might be added in future releases of SQLite.
..., because if the Metadata of the Column is Real, he cannot use Qry.FieldbyName('SomeDate').AsDateTime
No, he can‘t, as long as the returned Fieldtype is not ftDateTime...., because if the Metadata of the Column is Real, he cannot use Qry.FieldbyName('SomeDate').AsDateTime
He can use AsDateTime as long as real values stored in such column satisfy: "..., the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar. "
If the native type of the field is not a date or time value, then an attempt will be made to convert the field value from the native format to a TDateTime value when reading the field's content.That's what i don't understand.
...
Therefor, when reading or writing a field value for a field whose native data type is not a TDateTime-compatible value (dates or string values), an exception may be raised.
then an attempt will be made to convert the field value from the native format to a TDateTime
For given FieldType, in our case ftDateTime is created corresponding Field object. In our case TDateTimeField. So TField does not enters into "game".
The problem is that in the situation described we do not obtain a FieldType whose value is a ftDateTime.Yes this is the only way - use DATETIME as column data type in create table statement.
The only way to have this is to create the table in SQLite indicating the data type "DATETIME".
Just try creating a table with a REAL field, popolate it with TDateTime double values a read it doing AsDateTime. I will get the Execption.No use REAL as column data type is not a way.
Yes this is the only way - use DATETIME as column data type in create table statement.
No use REAL as column data type is not a way.
Yes I understand.
At client side you can use event handler TField.OnGetText, OnSetText https://www.freepascal.org/docs-html/fcl/db/tfield.ongettext.html to handle content of column and prepare it for DB-aware controls (such as TDBGrid etc.)
External tools that do no adhere to the standard are not supported on this platform, nor on the Sqlite platforms.
Drop the external tool because it is broken.
Lacak - who is the walking encyclopedia concerning databases and Freepascal - gave you enough info on how to properly handle it.
I am not going to investigate any further, since it is the external tool that is broken, not FPC's bindings and not Sqlite itself.
You can create descendant of TSQLQuery where you can override virtual method GetFieldClass() and return own TField descendant which will handle AsDateTime in a special way
(you can create own TSqliteFloatField = class(FloatField) ... define there GetAsDateTime and use this new field type for REAL columns (ftFloat))
For usage you can look at fpjsondataset.