A FireDAC Date Mystery
I have an app that connects to a MSSQL db through FireDAC and retrives a selection of log data, including the date and time of the logging in a datetime field.
I leave the app open for a longer period of time (about an hour, I think), then I refresh the view.
BAM! The dates show up as 1899. For some reason, FireDAC has decided to forget how to get the datetime in the right format.
I suspect that is is related to a connection object that has lost it’s connection, but why can it reconnect and get data, and yet get the date format wrong?
#FireDAC
Lars Fosdal 
Would the date be 30/12/1899 ?
LikeLike
Yes. The time part is correct, though.
It’s like it has forgotten how to deal with the date value. I suspect something related to ymd,dmy,mdy – but I can’t figure out where it happens. I do not touch FormatSettings anywhere, and the code works when I use ADO, instead of FireDAC.
LikeLike
The infamous zero date value 🙂 What format does MSSQL return a date/time field?
LikeLike
Different databases may have different date formats, but I don’t know if the returned value is a binary or a string. I suspect a string, though. I worry that FireDAC reverts to some sort of default instead of fetching the db format.
LikeLike
When converting ADO > FireDAC to MSSQL, I used FormatOptions of connection with FormatOptions do
begin
OwnMapRules := True;
with MapRules.Add do
begin
SourceDataType := dtDateTimeStamp;
TargetDataType := dtDateTime;
end;
with MapRules.Add do
begin
SourceDataType := dtTime;
TargetDataType := dtDateTime;
end;
with MapRules.Add do
begin
SourceDataType := dtFmtBCD;
TargetDataType := dtCurrency;
end;
with MapRules.Add do
begin
SourceDataType := dtBCD;
TargetDataType := dtCurrency;
end;
end;
LikeLike
Radek Červinka Thank you, I’ll give that a try to see if it helps!
LikeLike
Radek Červinka – It seems that this solves my mystery date issue. Really strange, though.
LikeLike
Maybe when reconnect, Firedac create new Connection object without some parameters. Maybe depends on how you handle oConnection.OnRestored := mOnRestored;
oConnection.OnRecover := mOnRecover;
oConnection.OnLosted := mOnLosted; Do you handle this specially, or you leave for FireDAC?
LikeLike
Radek Červinka – I left it to FireDAC.
What kind of actions do you perform in your event handler for these events?
LikeLike
Update: I found the code that stripped the date, and that was our fault (remains of ADO workarounds). I posted it on the official boards.
https://forums.embarcadero.com/thread.jspa?threadID=110660&tstart=0#680524
Still
– Why was the code was run ONLY after a reconnect?
– Why did adding a map rule on creating the connections cure the problem?
LikeLike
Lars Fosdal
some special (checking limits of connections from program licence) and and ask user about reconnect
LikeLike
Lars Fosdal
Rules remap some datatypes to another datatypes, some DBcomponents has problems with some date types – this was my problem, i don’t remember details. I do this on connection, so every dataset get this rules.
LikeLike
Radek Červinka – I have adopted your approach, and it solved the problem. In addition I could replace that ugly workaround with one line of code 🙂
Thank you so much for your input!
It does look like there is some sort of state change issue with regards to reconnects in FireDAC though, but I’ll leave that to Dmitry to figure out.
LikeLike
Lars Fosdal 🙂
Please, share this information “…one line of code 🙂 ”
😦
One of my projects include simultaneously working with MSSQL and with Firebird database. I choose FireDAC for that but now I’m a little fidgety.
LikeLike
Dobrin Petkov 🙂
Result := FieldByName(FieldName).AsDateTime;
LikeLike
Lars Fosdal :))))))))))))))
Well… the old solution.
10x 🙂
LikeLike
Which for some reason didn’t work reliably for dtTime, dtDate or dtTimeStamp in ADO, and that someone tried to do a workaround for.
LikeLike