Confusing DB timestamp_tz, timestamp_ltz values posted for a appropriate DB field via TOracleDataSet

[Andrik75]

Hello Marco,

My environment:
Oracle Server 11.2.0.4, 11.2.0.3
Oracle Client 11.2.0.1, 12.1.0.2
Windows 7
DOA 4.1 XE3
A DB table has a field as TIMESTAMP WITH LOCAL TIME ZONE
Current session time zone differs from the Windows time zone.

When change the value there in the field via a standard db time edit or its DevExpress analog and then post it, it returns value that differs exactly with difference between session and windows time zones. It's because in the procedure TOracleTimestamp.SetValues when the time zone parameter in the OCIDateTimeConstruct function is nil it constructs the timestamphp in Windows time zone instead of to do it in session time zone. When reading the value from DB it returns the value in the session time zone that is correct.
A proposed fix is to add the time zone definition there explicitly. The next code should be used there:
procedure TOracleTimestamp.SetValues(AYear: SmallInt; AMonth, ADay, AHour, AMinute, ASecond: Byte;
ANanoSeconds: Cardinal);
var
sTZ : AnsiString;
Query : TOracleQuery;
begin
sTZ := '';
if Assigned(FSession) and (FSession.Connected) then begin
Query := TOracleQuery.Create(nil);
try
Query.Session := FSession;
Query.SQL.Text := 'select sessiontimezone from dual';
Query.Execute;
if not Query.Eof then begin
sTZ := Query.Field(0);
end;
finally
FreeAndNil(Query);
end;
end;
if sTZ '' then begin
OCICall(OCIDateTimeConstruct(Session.envhp, errhp, timestamphp, AYear, AMonth, ADay, AHour, AMinute, ASecond, ANanoSeconds, PAnsiChar(sTZ), Length(sTZ)));
end else begin
OCICall(OCIDateTimeConstruct(Session.envhp, errhp, timestamphp, AYear, AMonth, ADay, AHour, AMinute, ASecond, ANanoSeconds, nil, 0));
end;
FIsNull := False;
Modified;
end;

Regards, Andrik75

 

[Marco Kalter]

Thanks for reporting this. We'll check it out.