does 6.6.4-stable support firebird 2.1 fully?

[lukezeoslib]

my developing environment: delphi 7 + zeoslib + firebird 2.1 db (dialect=3)

I saved a string '2009-01-01 00:23:32.200' to a timestamp field, it was ok, but when I used
FormatDateTime('yyyy-mm-dd hh:nn:ss.zzz',FieldByName('datetime').AsDateTime)) to load datetime from field, the millisecond was lost, and I just got '2009-01-01 00:23:32.000' .

Is there any way can solve this problem? I really need the value of milliseconds.

Below is the saving clause:
insert into foo (datetime) values ('2009-01-01 00:23.32.200')

[seawolf]

Hi, I almost found a solution, but are you executing

insert into foo (datetime) values ('2009-01-01 00:23.32.200')

Data are stored correctly? Did you checked using FlameRobin (o something else)?

Because it looks to me also the insert command does not save correctly data.

[seawolf]

In order to read milliseconds you need to change:

src\plain\ZPlainFirebirdInterbaseConstants.pas

TISC_TIMESTAMP = record
timestamp_date: ISC_DATE;
timestamp_time: ISC_TIME;
end;

(previous declaration is not correct)

src\dbc\ZDbcInterbase6Utils.pas

function TZResultSQLDA.GetTimestamp(const Index: Integer): TDateTime;
var
TempDate: TCTimeStructure;
begin
CheckRange(Index);
{$R-}
with FXSQLDA.sqlvar[Index] do
begin
Result := 0;
if (sqlind <> nil) and (sqlind^ = -1) then
Exit;

case (sqltype and not(1)) of
SQL_TIMESTAMP : begin
FPlainDriver.isc_decode_timestamp(PISC_TIMESTAMP(sqldata), @TempDate);
Result := SysUtils.EncodeDate(TempDate.tm_year + 1900,
TempDate.tm_mon + 1, TempDate.tm_mday) + EncodeTime(TempDate.tm_hour,
TempDate.tm_min, TempDate.tm_sec, Word((PISC_TIMESTAMP(sqldata).timestamp_time mod 10000) div 10));
end;
SQL_TYPE_DATE : begin
FPlainDriver.isc_decode_sql_date(PISC_DATE(sqldata), @TempDate);
Result := SysUtils.EncodeDate(Word(TempDate.tm_year + 1900),
Word(TempDate.tm_mon + 1), Word(TempDate.tm_mday));
end;
SQL_TYPE_TIME : begin
FPlainDriver.isc_decode_sql_time(PISC_TIME(sqldata), @TempDate);
Result := SysUtils.EncodeTime(Word(TempDate.tm_hour), Word(TempDate.tm_min),
Word(TempDate.tm_sec), Word((PISC_TIME(sqldata)^ mod 10000) div 10));
end;
else
Result := Trunc(GetDouble(Index));
end;
end;
{$IFOPT D+}
{$R+}
{$ENDIF}
end;

Anyway, as I supposed, Zeoslib at the moment don't write millisec to the DB, but I need more time to fix that problem

[seawolf]

In order to write msec it is necessary to change, on unit ZDbcInterbase6Utils.pas:

procedure TZParamsSQLDA.UpdateDateTime(const Index: Integer;
Value: TDateTime);
var
y, m, d: word;
hr, min, sec, msec: word;
SQLCode: SmallInt;
TmpDate: TCTimeStructure;
TmpTime: Cardinal;
begin
CheckRange(Index);
{$R-}
with FXSQLDA.sqlvar[Index] do
begin
DecodeDate(Value, y, m, d);
DecodeTime(Value, hr, min, sec, msec);
TmpDate.tm_year := y - 1900;
TmpDate.tm_mon := m - 1;
TmpDate.tm_mday := d;
TmpDate.tm_hour := hr;
TmpDate.tm_min := min;
TmpDate.tm_sec := sec;
TmpDate.tm_wday := 0;
TmpDate.tm_yday := 0;
TmpDate.tm_isdst := 0;
if (sqlind <> nil) and (sqlind^ = -1) then
Exit;
SQLCode := (sqltype and not(1));

case SQLCode of
SQL_TYPE_DATE : FPlainDriver.isc_encode_sql_date(@TmpDate, PISC_DATE(sqldata));
SQL_TYPE_TIME : begin
FPlainDriver.isc_encode_sql_time(@TmpDate, PISC_TIME(sqldata));
PISC_TIME(sqldata)^ := PISC_TIME(sqldata)^ + msec*10;
end;
SQL_TIMESTAMP :
begin
FPlainDriver.isc_encode_timestamp(@TmpDate,PISC_TIMESTAMP(sqldata));
PISC_TIMESTAMP(sqldata).timestamp_time :=PISC_TIMESTAMP(sqldata).timestamp_time + msec*10;
end;

else
raise EZIBConvertError.Create(SInvalidState);
end;
if (sqlind <> nil) then
sqlind^ := 0; // not null
end;
{$IFOPT D+}
{$R+}
{$ENDIF}
end;

[mdaems]

Seawolf,

Current tests don't fail on this change. So I commit. (SVN rev 639)
Do you think it's reasonable to write a test case for that in the test suite? (Make sure it's fb only if you do.)

Mark

[seawolf]

You're right and I've already done it in order to test that change.
Unfortunately I wrote this test on a different pc. Within 24 hour i'll post it.

[seawolf]

I added this test to ZTestDbcInterbase.pas

(you have to add DateUtils,Math on the uses)

procedure TZTestDbcInterbaseCase.TestMsec;
var
Statement: IZStatement;
ResultSet: IZResultSet;
ThisTime : TDateTime;
oldTimeFormat: string;
begin
Statement := Connection.CreateStatement;
CheckNotNull(Statement);
Statement.SetResultSetType(rtScrollInsensitive);
Statement.SetResultSetConcurrency(rcUpdatable);
Statement.ExecuteUpdate('delete from DATE_VALUES where D_ID=4');
ResultSet := Statement.ExecuteQuery('select D_ID, D_DATE, D_TIME, D_DATETIME, D_TIMESTAMP from DATE_VALUES');
CheckNotNull(ResultSet);
OldTimeFormat := LongTimeFormat;
LongTimeFormat := 'hh:mm:ss.zzz';
ThisTime := Now;
ResultSet.MoveToInsertRow;
ResultSet.UpdateInt(1, 4);
ResultSet.UpdateDate(2,ThisTime);
ResultSet.UpdateTime(3,ThisTime);
ResultSet.UpdateTimestamp(4,ThisTime);
ResultSet.UpdateTimestamp(5,ThisTime);
ResultSet.InsertRow;
ResultSet.Last;
Check(ResultSet.GetInt(1) <> 0);
CheckEquals(Trunc(ThisTime), ResultSet.GetDate(2));
CheckEquals(RoundTo(Frac(ThisTime),-11), RoundTo(ResultSet.GetTime(3),-11));
CheckEquals(ThisTime, ResultSet.GetTimeStamp(4));
CheckEquals(ThisTime, ResultSet.GetTimeStamp(5));
ResultSet.DeleteRow;
LongTimeFormat := OldTimeFormat;
ResultSet.Close;
Statement.Close;
end;

[lukezeoslib]

Thanks all your reply, I have solved this problem.

I used the FB SQL grammar, here is the clause:
select cast(datetime as char(24)) as datetime from warninglog where ...

And then, after I opened the query, I fetched the datatime as a string with this sub-clause:
LeftStr(fdbquery.FieldByName('datetime').AsString,23)

Because the FB always return 4 char as millisecond, such as xxx.0230, and the last char is always a '0'.