While doing some migrations between databases i discovered that my data was always getting corrupted so i decided to investigate more . While checking the Database.FieldSchema for each table to automatically detect which kind of data i have and guiding me on this from and from i was setting the date format for fields according to docs for Date as FieldType 8,9 or 10 as it says in the docs . so far so good until my data started to get corrupted so i start to search why my Timestamp (10) Field was givving me 5 instead of 10 so the best way was to check the source and i find out that on SQLite3 engine which XOJO has in the latest IDE the DateTime Type is set as 5 so i was lucky that i wasted a lot of time and a test db instead of actual real data .
Could somebody please check and validate this and update the Docs accordantly so that other guys don`t get the same mistakes .
Thanks a lot.
Sqlite doesn’t have a Date, Time or DateTime datatype…
As many (all?) here will attest. for SQLite you should always store dates using SQLDATE format in a text/string field type. Which by the way is just what the SQLite docs say
from the Xojo docs
Known issue <https://xojo.com/issue/21805>. I reported it in July of 2012. It comes back to bite me every now and then too. A bug report came in on Shorts (our reporting tool) and I had one of my employees look at it only to find out that I had already discovered and reported it. Such is the life of dealing with a couple of dozen projects a year.
Well Bob, i
m glad that you understand my point , any fix on that ? as so far im little bit stuck here and it seems that even the DB changed they are still using. that table from the docs as reference and they are mixed as i get reference from old table with data from new format so it is weird honestly .
Dave i got your point but it seems that on the docs says something else and in reality it is something else and as Bob was saying it seems that the bug is 5 years old already and never changed or fixed.
m relying on those is that im doing some dynamic backups on some databases and i want the code to automatically detect the type according to those and to update it properly in the other database and some times are mixed like from SQLite to MySQL and vice versa so i cannot mess customers data with this .
Thanks guys and we are waiting updates from XOJO team for this.
We say use TimeStamp since that is reported correctly by Xojo. That solution may or may not be available to you.