Geregistreerd: di mei 25 2004, 09:57 berichten: 26
Maybe I'm missing something, but I'm finding the datetime support a bit lacking. It looks like ASGSQLite always uses StrToDateTime to convert the strings in the db to dates. This is problematic given that this function uses locale settings. Sure, you could force it with ShortDateFormat/DateSeparator, but then you don't display it appropriate to their locale.
There needs to be some machine-independent way of storing the data in the database. For example, it could always be stored as yyyy-mm-dd hh:nn:ss.mmm in the database and only converted to locale when displaying/editing.
Perhaps you could accomplish this be saving ShortDateFormat/DateSeparator before loading data and replacing them with 'yyyy-mm-dd'/'-'. Then you load the data (all the StrToDateTime calls work like you'd want them to), then you set the variables back to what the user had set. I've tried this outside of the code and it seems to work fine. However, I'm limited to what I can do outside of the source code. I think it should be handled inside of the components.
Geregistreerd: wo jan 25 2006, 04:34 Woonplaats: groningen (NL) berichten: 1561
I agree that you have a point here. Other databases store the date as a TDateTime field (double), but since sqlite is typeless, the data is stored as a string. I'm not sure if saving/restoring the dateformats will work under all situations, but I'll investigate. Albert albert
Geregistreerd: di mei 25 2004, 09:57 berichten: 26
Well, looking on the SQLite page, it appears they are standardizing on the yyyy-mm-dd hh:nn:ss format, at least for their date functions. This really is the best format to pick (with the addition of milliseconds), as it allows this statement to work:
select * from sometable order by datefield
Perhaps we could designate the "timestamp" field type as always being in this format? Right now, timestamp is simply mapped to the same as date/datetime. If so, you wouldn't need to mess with the saving/restoring the date formats. Just write a very simple parser that parses yyyy-mm-dd hh:nn:ss.mmm into a date time.
Geregistreerd: wo jan 25 2006, 04:34 Woonplaats: groningen (NL) berichten: 1561
I'm affraid I don't quite understand what you mean. The parser function is not a problem, but why don't I have to mess with saving / restoring? Using a parser might introduce a problem since the datetime fields are parsed by TDataSet (otherwise the dataware component will not work) and I had a lot of trouble finding the right way to work with dates anyhow. But I agree that this is the best way to do so I'll get into it and look for a workable solution. albert
Geregistreerd: di mei 25 2004, 09:57 berichten: 26
I mean alter TASQLiteBaseQuery.UnpackBuffer to add this case:
ftTimeStamp: begin TempT := DateTimeToNative(FieldType, YYYYMMDDParser(Buffer)); Move(TempT, result, sizeof(TDateTime)); end;
where YYYYMMDDParser is a function that parses yyyy-mm-dd hh:nn:ss.mmm formatted strings and returns a TDateTime.
That way you wouldn't need to much about with any locale-specific variables. You'd get the TDateTime value into the field and Delphi could handle it from there. If I've understood the code correctly, that is
Geregistreerd: di mei 25 2004, 09:57 berichten: 26
Well, it's not even 4pm here in the US, so I'm going to keep rambling
Okay, so I tried a couple of patches to the source to try out my idea. I wound up ditching the idea of using ftTimeStamp because there is no such thing as a TTimeStampField class, which screws up everything. So instead, I've used ftDateTime.
First off, I changed GetFieldInfo so it would recognize 'datetime' fields separately of 'date' fields. Previously, it just set all 'datetime' fields to 'date' ones (which actually seems like a bit of a bug).
else if vt = 'datetime' then begin //FieldType := ftDate; // jpierce FieldType := ftDateTime; // jpierce FieldLen := 20; end
Second, I rewrote part of TASQLiteBaseQuery.UnpackBuffer. Right now this is hardcoded, but this could be decided based on a variable of the connection and/or individual query.
Last of all, here's my really basic parser. It requires that the date be in strict yyyy-mm-dd hh:nn:ss (I left off the msecs).
function YYYYMMDDParser(Str: PChar): TDateTime; var Year, Month, Day, Hour, Min, Sec, MSec: Word; PartStart, PartEnd: Integer; CurStr: String; begin Result := 0;
try if Length(Str) >= 10 then // 10 = Length of YYYY-MM-DD begin Year := StrToInt(Copy(Str, 1, 4)); Month := StrToInt(Copy(Str, 6, 2)); Day := StrToInt(Copy(Str, 9, 2));
Result := EncodeDate(Year, Month, Day); end;
if Length(Str) > 10 then // it has a time begin Hour := StrToInt(Copy(Str, 12, 2)); Min := StrToInt(Copy(Str, 15, 2)); Sec := StrToInt(Copy(Str, 18, 2)); MSec := 0; // still not sure what to do on this one
Result := Result + EncodeTime(Hour, Min, Sec, MSec); end; except Result := 0; end; end;
Oh, and to make the posting work right in the table component, I made this change in TASQLiteTable.InternalPost:
for i := 0 to FieldList.Count - 1 do if FieldList[i].DataType = ftDateTime then // jpierce SQLStr := SQLStr + FieldList[i].FieldName + '=' + // jpierce QuotedStr(FormatDateTime('yyyy-mm-dd hh:nn:ss', // jpierce FieldByName(FieldList[i].FieldName).AsDateTime)) + ',' // jpierce else // jpierce SQLStr := SQLStr + FieldList[i].FieldName + '=' + // jpierce QuotedStr(FieldByName(FieldList[i].FieldName).AsString) + ','; // jpierce
I tried this out and it works great. Basically, after UnpackBuffer is done, it looks like any other datetime field. Data-aware controls take to it just fine.
I'm not familiar enough with the code yet to say if those are all the changes needed. There might be a couple of other places where you need to treat the datetime differently. I haven't got into using UpdateSQLs with the query components yet. But the principle should be the same.
Geregistreerd: wo jan 25 2006, 04:34 Woonplaats: groningen (NL) berichten: 1561
Thank you for the patch, I'll try things out. Since we have some hollidays here it might take a few days, but if things work out I'll adapt your changes.
I have changed the dateroutine to support yyyy-mm-dd yyyy-mm-dd hh:nn yyyy-mm-dd hh:nn:ss yyyy-mm-dd hh:nn:ss.mmm
To remain compatible to other versions (for people already having dates in there locale format) I'm considering to add two properties (to ASQLiteBaseQuery): ShortDateFormat, containing a shortdateformat for use of strtodatetimex (also changed header) and sqlitedateformat a boolean (default true) to indicate your routine. Since a similar problem goes for decimalseparator etc. I'm considering a property for this too.
Geregistreerd: wo jan 25 2006, 04:34 Woonplaats: groningen (NL) berichten: 1561
I did have to add the dateformat routine on other places. I'm investigating if the TASQLite query suffers from this, otherwise the properties are only needed in TASQLiteTable and not in TASQLiteBaseQuery. The TASQLiteTable.internalpost has got major changes. I'm testing now and hope the routines to be available somewhere today.
Geregistreerd: wo jan 25 2006, 04:34 Woonplaats: groningen (NL) berichten: 1561
I have uploaded the newest sources. You need asqlite.pas, asgrout.pas (your format routine is here), asqlite.dcr (which contains the additional bitmaps for the ide)
I have roughly tested things, but will be a few days off. Didn't want you to wait for the changes but you might find some problems still. Therefore it's a developers release :wink:
