Converting ZDatasetParam unit to use ZFormatSettings

[MJFShark]

Currently the ZDatasetParams unit uses the older {$IFDEF WITH_FORMATSETTINGS}FormatSettings{$ELSE}SysUtils{$ENDIF}.ShortDateFormat type date/time formattings. I'd like to convert it to use the FConSettings^.ReadFormatSettings.DateFormat type calls instead. However there are a couple of snags:

1. FConnSettings can be nil (I did the conversion one-to-one and then realized this lol.)

2. Need to decide to use ReadFormatSettings vs WriteFormatSettings. (on a side note it looks like the TZRowAccessor is inconsistent on this, setdate uses writeformat while settime and setdatetime use readformat.)

#1 involves just a check and fallback to the old method, but I was wondering if there was a better way to handle it.

#2 I'm not sure on as I've never had a need for them to be different. I can imagine using WriteFormat for ptInput and ReadFormat for ptOutput but it starts getting weird fast... lol. Thoughts or comments appreciated!

-Mark

[MJFShark]

After some more research I realized that I had a fundamental misunderstand of how Zeos handles datetime format settings.

I was thinking that the ConSettings's ReadFormatSettings and WriteFormatSettings are set from the ZFormatSettings, but they are not. They're either set by ConnProps_DateReadFormat, etc) or are set to hard coded iso defaults (which happened to be my ZFormatSettings, so I didn't notice they are different.)

Is there a plan to integrate ConSettings and ZFormatSettings?

It looks like to completely control date/time formats in zeos you need to:

#1 Set all the various FormatSettings:

ZConn.FormatSettings.DisplayDateFormatSettings.Format := DateFormat;
ZConn.FormatSettings.DisplayTimeFormatSettings.Format := TimeFormat;
ZConn.FormatSettings.DisplayTimestampFormatSettings.Format := DateFormat + ' ' + TimeFormat;
ZConn.FormatSettings.DisplayTimestampFormatSettings.DateFormat := DateFormat;
ZConn.FormatSettings.DisplayTimestampFormatSettings.TimeFormat := TimeFormat;

ZConn.FormatSettings.EditDateFormatSettings.Format := DateFormat;
ZConn.FormatSettings.EditTimeFormatSettings.Format := TimeFormat;
ZConn.FormatSettings.EditTimestampFormatSettings.Format := DateFormat + ' ' + TimeFormat;

#2 then set the various properties:

ZConn.Properties.Values['DateReadFormat'] := DateFormat;
ZConn.Properties.Values['TimeReadFormat'] := TimeFormat;
ZConn.Properties.Values['DateTimeReadFormat'] := DateFormat + ' ' + TimeFormat;

ZConn.Properties.Values['DateWriteFormat'] := DateFormat;
ZConn.Properties.Values['TimeWriteFormat'] := TimeFormat;
ZConn.Properties.Values['DateTimeWriteFormat'] := DateFormat + ' ' + TimeFormat;

Or am I completely missing something?

Thanks!

-Mark

[marsupilami]

Hello Mark,

we have two sets of formatsettings for date and time.

#1 Set all the various FormatSettings:

ZConn.FormatSettings.DisplayDateFormatSettings.Format := DateFormat;
ZConn.FormatSettings.DisplayTimeFormatSettings.Format := TimeFormat;
ZConn.FormatSettings.DisplayTimestampFormatSettings.Format := DateFormat + ' ' + TimeFormat;
ZConn.FormatSettings.DisplayTimestampFormatSettings.DateFormat := DateFormat;
ZConn.FormatSettings.DisplayTimestampFormatSettings.TimeFormat := TimeFormat;

ZConn.FormatSettings.EditDateFormatSettings.Format := DateFormat;
ZConn.FormatSettings.EditTimeFormatSettings.Format := TimeFormat;
ZConn.FormatSettings.EditTimestampFormatSettings.Format := DateFormat + ' ' + TimeFormat;

These format settings get used by TZFields when talking to the user. They control how Date, Time and DateTime values are represented in TDBGrid and so on.

#2 then set the various properties:

ZConn.Properties.Values['DateReadFormat'] := DateFormat;
ZConn.Properties.Values['TimeReadFormat'] := TimeFormat;
ZConn.Properties.Values['DateTimeReadFormat'] := DateFormat + ' ' + TimeFormat;

ZConn.Properties.Values['DateWriteFormat'] := DateFormat;
ZConn.Properties.Values['TimeWriteFormat'] := TimeFormat;
ZConn.Properties.Values['DateTimeWriteFormat'] := DateFormat + ' ' + TimeFormat;

These settings are used by Zeos when talking to the server - but only if we talk to the server using strings and not binary valules. The dblib driver is an example for this. With the dblib driver cannot bind parameters for queries. So we convert all paramaters to strings and insert them into the query. For this to work we need to know how to represent dates and times so the server can understand them.

Best regards,

Jan

[MJFShark]

Thanks Jan!

The non-tzfield format handling (ZSysUtils) is used for parameter .value string->date/time/timestamp conversions and that's where I hit some issues. It looks like 'm' or 'mm' for minutes (instead of n) is only handled by the time conversions and not the timestamp conversions. Since the default FormatSettings.LongTimeFormat for the US is ''h:mm:ss AMPM' that causes some very surprising results as the current timestamp conversion will add the minutes to the months counter and then do a conversion from there. There's some very cool code in there for those conversions btw, but I was surprised that converting a date of "01-02-2007 12:34:56" (using the US default format: 'M/d/yyyy h:mm:ss AMPM') would return "2/21/2018 12:00:56 PM".

The parameter .value string->date/time/timestamp conversions currently use the system's FormatSettings values directly and not FConSettings or ZFormatSettings.

I think a good solution would be to add 'mm' handling to the timestamp conversion functions in ZSysUtils, but I'm sure that's non-trivial or it would have been done in the first place I'm guessing. Another option would be to use the ZFormatSettings conversion routines for the .value parameter conversions.

-Mark

[MJFShark]

Here's a modification to TryUniToTimeStamp to attempt to handle the 'm' is for minute if it follows 'h' issue. I put a define in just to make it easier to do a/b type testing. I wasn't sure about all the delimiter chars I used to cancel the MIsMinute boolean, so they could probably be adjusted. I'd love to hear feedback as to whether this is a good or bad idea. If it seems good than TryRawToTimestamp , DateTimeToRaw and DateTimeToUni would have to be modified similarly.

-Mark

Code: Select all

{$DEFINE FORMAT_MINUTE_HANDLING}
function TryUniToTimeStamp(Value: PWideChar; Len: Cardinal;
  const Format: String; var TimeStamp: TZTimeStamp): Boolean;
var VEnd{End of value +1}, PTZ{remander of TimeZone sign},
  PFDot{Dot position of Fraction part}: PWideChar;
  PF, FEnd: PChar;
  F, B: Byte;
  {$IFDEF FORMAT_MINUTE_HANDLING}
  MIsMinute: Boolean;
  {$ENDIF}
label Next, zFlush, TimeZ, jmpFrac;
begin
  Result := False;
  {$IFDEF FORMAT_MINUTE_HANDLING}
  MIsMinute := False;
  {$ENDIF}
  PF := Pointer(Format);
  FillChar(TimeStamp, SizeOf(TZTimeStamp), #0);
  if (PF = nil) or (Value = nil) or (Len=0) then Exit;
  FEnd := PF+Length(Format);
  VEnd := Value + Len;
  Len := 0;
  PFDot := nil;
  while (PF < FEnd) and (Value < VEnd) do begin
    if PWord(Value)^ > High(Byte) then Exit;
    B := Byte(PWord(Value)^ or $0020);
    F := {$IFDEF UNICODE}PWord(PF)^ or $0020{$ELSE}PByte(PF)^ or $20{$ENDIF};
    case B of
      Byte('0')..Byte('9'): begin
          {$IFDEF FORMAT_MINUTE_HANDLING}
          if Byte(F) = Byte('h') then
            MIsMinute := True
          else if MIsMinute and not (Byte(F) in [Byte('m'), Byte(':'),Byte('-'),Byte('/'),Byte('\'),Byte(' '), Byte('t')]) then
            MIsMinute := False;
          {$ENDIF}
          B := B - Byte('0');
          case Byte(F) of //lower() of
            Byte('y'):  TimeStamp.Year := TimeStamp.Year * 10 + B;
            Byte('m'):
              begin
                {$IFDEF FORMAT_MINUTE_HANDLING}
                if MIsMinute then
                  TimeStamp.Minute := TimeStamp.Minute * 10 + B
                else
                {$ENDIF}
                  TimeStamp.Month := TimeStamp.Month * 10 + B;
                end;
            Byte('d'):  TimeStamp.Day := TimeStamp.Day * 10 + B;
            Byte('h'):  TimeStamp.Hour := TimeStamp.Hour * 10 + B;
            Byte('n'):  TimeStamp.Minute := TimeStamp.Minute * 10 + B;
            Byte('s'):  TimeStamp.Second := TimeStamp.Second * 10 + B;
            Byte('.'):  begin
                          if PFDot = nil then begin
                            PFDot := Value+1;
                            while PFDot < VEnd do
                              if PWord(PFDot)^ = Word('.')
                              then Break
                              else Inc(PFDot);
                            if PFDot = VEnd then
                              PFDot := Value;
                          end;
                          if PFDot > Value then begin
                            Dec(PF); //we still have a number of a second
                            Continue;
                          end else goto jmpFrac
                        end;
            Byte('z'):  begin //fractions start here(delphi logic) -> is this correct? ISO uses the '.' indicator and Z represents the timezone!
jmpFrac:        TimeStamp.Fractions := TimeStamp.Fractions * 10 + B;
                inc(Len);
                if ((Value+1) < VEnd) and (PWord(Value+1)^<High(Byte)) and (Byte(PWord(Value+1)^) in [Byte('0')..Byte('9')]) then begin
                  Inc(Value);  {if a server returns micro or nanoseconds and the format with the z indicators is to short ...}
                  Continue;
                end else
                  goto zFlush;
              end;
            Byte('p'):  begin
                TimeStamp.TimeZoneHour := B;
                goto TimeZ;
              end;
            else begin
              if (F in [Byte(':'),Byte('-'),Byte('/'),Byte('\'),Byte(' '), Byte('t')])
              then Dec(PF) //we have a numeric value found. As long we have delimiters we sort the num back to the last format char
              else Inc(PF);
              Continue;
            end;
          end;
          goto Next;
        end;
      Byte('+'), Byte('-'): begin
          case F of
            Byte('y'): TimeStamp.IsNegative := PWord(Value)^ = Byte('-');
            Byte('p'): begin
TimeZ:          PTZ := Value;
                Inc(Value);
                while (Value < VEnd) do begin
                  if PWord(Value)^ > High(Byte) then Exit;
                  B := Byte(PWord(Value)^);
                  if (B in [Byte('0')..Byte('9')]) then begin
                    TimeStamp.TimeZoneHour := TimeStamp.TimeZoneHour * 10 + (B - Byte('0'));
                    Inc(Value);
                  end else if B = Byte(':') then begin
                    if ((Value+1) < VEnd) and (PWord(Value+1)^ <= High(Byte)) then
                      case Byte(PWord(Value+1)^) of
                        Byte('1'): {not possible} TimeStamp.TimeZoneMinute := 15;
                        Byte('3'): TimeStamp.TimeZoneMinute := 30;
                        Byte('4'): TimeStamp.TimeZoneMinute := 45;
                        else Break;
                      end
                    else Exit;
                    Inc(Value, 2);
                  end;
                  if {$IFDEF UNICODE}PWord{$ELSE}PByte{$ENDIF}(PTZ)^ = Ord('-') then
                    TimeStamp.TimeZoneHour := -TimeStamp.TimeZoneHour;
                end
              end
            else if (B = F) or (F in [Ord('/'),Ord('\'),Ord(' ')])
            then goto Next
            else Exit;
          end;
          Inc(Value);
        end;
      Byte('.'): if (PF = FEnd -1) or ({$IFDEF UNICODE}PWord(PF+1)^ or $0020{$ELSE}PByte(PF+1)^ or $20{$ENDIF} = Ord('p'))
                then Inc(Value)
                else if F = Byte('.')
                  then goto next
                  else goto zFlush;
      else if (B = F) or
          ((B = Byte('t')) and (F = Byte(' '))) or
          ((B = Byte(' ')) and (F = Ord('t'))) then begin //delimiter?
Next:   Inc(Value);
        Inc(PF);
      end else if (B in [Ord(':'),Ord('-'),Ord('/'),Ord('\'),Ord(' ')]) then begin
zFlush: Inc(Value);
        if Value = VEnd then Break;
        B := {$IFDEF UNICODE}PWord{$ELSE}PByte{$ENDIF}(PF)^;
        while ({$IFDEF UNICODE}PWord{$ELSE}PByte{$ENDIF}(PF)^ = B) and (PF < FEnd) do Inc(PF);
        F := {$IFDEF UNICODE}Byte(PWord(PF)^ or $0020){$ELSE}PByte(PF)^ or $20{$ENDIF};
        B := Byte(PWord(Value-1)^ or $0020);
        if (F in [Byte(' '), Byte('t')]) and (B in [Byte(' '), Byte('t')]) then
          Exit;
      end else
        Exit;
    end;
  end;
  if (Len > 0) and (Len < 9) then
    TimeStamp.Fractions := TimeStamp.Fractions * FractionLength2NanoSecondMulTable[Len];
  Result := True;
end;

[EgonHugeist]

Hello Mark,

as you already know: The upper compoent layer is not related to the dbc layer. From my POV i'ts better if such conversion (user format) happen on the compoent-layer only. Reason is quite simple: Each DataSet, the more each field, can have it's own format. It simply seems to be impossible to catch all format's on core. If it was me i would support ISO1806 formats from dbc down only. The Connection-Properties formatsettings are used on dbc only. Mainly for all drivers, sending/retrieving textvalues for date/time/timestamp values.
So can you make an example of what you wanna do?

Is that related to viewtopic.php?f=50&t=139576?

[MJFShark]

Hi Michael,

Thanks! I see that you've just committed a change to have ZParams use ZFormatSettings if available. Nice! My other comments are just related to Delphi's handling of the 'm' format character. If it appears directly (disregarding separators) after an 'h' character it should be treated as a minute and not a month (sorry to reiterate that, I'm sure you're already aware.) Currently Zeos handles 'm' correctly for time-only conversions but not for timestamp conversions. I get around this issue by 'fixing' my formatsettings before I load Zeoslib, but it would be good (I think) to not require that. This issue is specific to the ZSysUtils.pas timestamp conversions.

Also, totally agreed on iso1806. Date format handling has always been such a nightmare!

-Mark