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?
Would the date be 30/12/1899 ?
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.
The infamous zero date value 🙂 What format does MSSQL return a date/time field?
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.
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;
Radek Červinka Thank you, I’ll give that a try to see if it helps!
Radek Červinka – It seems that this solves my mystery date issue. Really strange, though.
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?
Radek Červinka – I left it to FireDAC.
What kind of actions do you perform in your event handler for these events?
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?
Lars Fosdal
some special (checking limits of connections from program licence) and and ask user about reconnect
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.
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.
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.
Dobrin Petkov 🙂
Result := FieldByName(FieldName).AsDateTime;
Lars Fosdal :))))))))))))))
Well… the old solution.
10x 🙂
Which for some reason didn’t work reliably for dtTime, dtDate or dtTimeStamp in ADO, and that someone tried to do a workaround for.