Page 1 of 1
How to read DateTimeOffset from OracleObject
Posted: Tue 30 Apr 2019 12:04
by pakarjon
We have need identical to
viewtopic.php?f=1&t=38496, but now with OracleObject.
It supposedly does not have any method interface to read timestamp data out of its columns.
Is there or can there be an interface to read DateTimeOffset from object's column?
Re: How to read DateTimeOffset from OracleObject
Posted: Fri 03 May 2019 14:06
by Pinturiccio
We have reproduced the issue when the object field of the Oracle type TIMESTAMP(6) WITH TIME ZONE returns as System.DateTime. We will investigate the possibility to return value of the OracleTimeStamp type for the TIMESTAMP(6) WITH TIME ZONE values from OracleObject instead of System.DateTime and post here when we get any results.
Re: How to read DateTimeOffset from OracleObject
Posted: Mon 06 May 2019 14:09
by Pinturiccio
You can read datetime offset. Let's consider the following example:
Code: Select all
CREATE OR REPLACE TYPE SEMENB.TAddress AS OBJECT (
Country VARCHAR2(30),
City VARCHAR2(30),
Street VARCHAR2(30),
Apartment NUMBER,
offset TIMESTAMP(6) WITH TIME ZONE
);
CREATE TABLE EmpObject (
Code NUMBER PRIMARY KEY,
Person VARCHAR2(40),
Address TAddress,
Job VARCHAR2(9)
);
INSERT INTO EmpObject (Code, Person, Address, Job) VALUES (1, 'SMITH', TAddress('UK', 'London', 'Street', 12, '07-AUG-2017 2:00:00 PM +6:00'), 'CLERK');
INSERT INTO EmpObject (Code, Person, Address, Job) VALUES (2, 'JONES', TAddress('USA', 'New York', 'Street', 418, '09-AUG-2017 2:00:00 PM -04:00'), 'MANAGER');
And use the following code:
Code: Select all
OracleConnection connection = new OracleConnection("your connection string");
OracleCommand command = new OracleCommand("SELECT * FROM EmpObject", connection);
connection.Open();
OracleDataReader dataReader = command.ExecuteReader();
int index = dataReader.GetOrdinal("Address");
while (dataReader.Read())
{
OracleObject oraObj = dataReader.GetOracleObject(index);
if (!oraObj.IsNull)
{
string country = (string)oraObj["Country"];
string city = (string)oraObj["City"];
string street = (string)oraObj["Street"];
decimal Apartment = (decimal)oraObj["Apartment"];
Console.WriteLine(oraObj["offset"].GetType()); //System.DateTime
OracleTimeStamp offset = (OracleTimeStamp)oraObj.GetOracleValue("offset");
Console.WriteLine(offset.TimeZoneOffset);
}
}
connection.Close();
Re: How to read DateTimeOffset from OracleObject
Posted: Tue 07 May 2019 06:47
by pakarjon
We tried to read timestamps the way you advised.
This however seems not to work in a case where timestamp value in the database has been set using using 'TZR' timezone format.
Example:
My SqlDeveloper has timestamp format:
DD.MM.RRRR HH24:MI:SSXFF TZR.
I have items that has been inserted into the database through devart library.
Then after insert I use Oracle SqlDeveloper and modify those objects somehow and a db trigger will change their modified timestamp.
Code: Select all
TRIGGER TRG_BF_UP_SET_MODIFIED BEFORE UPDATE ON "ELEMENT" FOR EACH ROW
BEGIN
:NEW.MODIFIED := CURRENT_TIMESTAMP;
END;
Modifying items through SqlDeveloper leads to the following situation:
Code: Select all
{
"items": [
{
"created": "07.05.2019 08:53:55,365082000 +03:00",
"modified": "07.05.2019 08:55:54,527512000 EUROPE\/HELSINKI"
},
{
"created": "07.05.2019 08:53:53,629522000 +03:00",
"modified": "07.05.2019 08:55:54,532500000 EUROPE\/HELSINKI"
},
{
"created": "07.05.2019 08:53:55,346704000 +03:00",
"modified": "07.05.2019 08:55:54,536629000 EUROPE\/HELSINKI"
}
]
}
Here I have the original created timestamp in absolute time zome format and modified timestamp in time zone region format.
When I read this data through devart, it will lead to an exception when reading "modified" timestamp.
Code: Select all
var oracleTimeStamp = (OracleTimeStamp)obj.GetOracleValue(columnName);
return new DateTimeOffset(oracleTimeStamp.Value, oracleTimeStamp.TimeZoneOffset);
That is because after reading it, the modified timestmap has invalid offset:
"7.5.2019 7:55:54 -123:12".
Re: How to read DateTimeOffset from OracleObject
Posted: Wed 08 May 2019 15:59
by Pinturiccio
We have reproduced the issue. We will investigate it and post here about the results as soon as possible.
Re: How to read DateTimeOffset from OracleObject
Posted: Wed 29 May 2019 13:18
by Pinturiccio
We have fixed the bug with reading incorrect timestamp's timezone and offset from an Oracle object field when the timezone is presented as a string value. We will post here when the corresponding build of dotConnect for Oracle is available for download.
Re: How to read DateTimeOffset from OracleObject
Posted: Fri 07 Jun 2019 08:48
by Pinturiccio
New build of dotConnect for Oracle 9.7.770 is available for download.
It can be downloaded from
https://www.devart.com/dotconnect/oracle/download.html (trial version) or from Devart Account (for users with valid subscription only).
For more information, please refer to
viewtopic.php?t=38884
Re: How to read DateTimeOffset from OracleObject
Posted: Fri 14 Jun 2019 05:03
by pakarjon
I tried my above example with 9.7.770 version. Now when reading 'MODIFIED' timestamps the OracleTimeStamp has TimeZone="EUROPE/HELSINKI" but Offset="00:00". Practically this means that timestamps don't have the necessary offset information to properly convert them to DateTimeOffset.
Is this a bug or should there be some way to convert the time zone name to offset? I couln't found any
Re: How to read DateTimeOffset from OracleObject
Posted: Fri 14 Jun 2019 13:59
by Pinturiccio
We have reproduced the issue with the Direct mode. We will investigate it and post here about the results as soon as possible.
You can use the OCI mode as a temporary workaround if this suits your scenario.
Re: How to read DateTimeOffset from OracleObject
Posted: Wed 03 Jul 2019 11:47
by Pinturiccio
We have fixed the bug with reading incorrect timestamp's timezone and offset in the Direct connection mode when the timezone is presented as a string value. We will post here when the corresponding build of dotConnect for Oracle is available for download.
Re: How to read DateTimeOffset from OracleObject
Posted: Thu 04 Jul 2019 13:34
by Pinturiccio
New build of dotConnect for Oracle 9.7.790 is available for download.
It can be downloaded from
https://www.devart.com/dotconnect/oracle/download.html (trial version) or from Devart Account (for users with valid subscription only).
For more information, please refer to
viewtopic.php?t=38981
Re: How to read DateTimeOffset from OracleObject
Posted: Thu 03 Dec 2020 09:18
by slmnn
We are using the earlier example to read DateTimeOffset from Oracle:
Pinturiccio wrote: ↑Mon 06 May 2019 14:09
You can read datetime offset. Let's consider the following example:
Code: Select all
...
OracleTimeStamp offset = (OracleTimeStamp)oraObj.GetOracleValue("offset");
Console.WriteLine(offset.TimeZoneOffset);
...
After updating DevArt dotConnect for Oracle to 9.13.1098 reading offset in TZR format seems to be broken. The timestamp in the DB is
Code: Select all
03.12.2020 11:02:08,598895000 EUROPE/HELSINKI
but we are reading
The same issue persists with the latest 9.13.1127.
With version 9.12.1064 the timestamp is read correctly. Also reading timestamps with offset in TZH:TZM seems to be working fine.
Re: How to read DateTimeOffset from OracleObject
Posted: Sat 12 Dec 2020 15:39
by Shalex
We have reproduced the bug with retrieving the TIMESTAMP WITH LOCAL TIME ZONE values in the TZR (time zone region) format in the Direct mode and will notify you when it is fixed.
Re: How to read DateTimeOffset from OracleObject
Posted: Thu 17 Dec 2020 22:27
by Shalex
The bug with retrieving the TIMESTAMP WITH LOCAL TIME ZONE values in the TZR (time zone region) format in the Direct mode is fixed in v9.14.1160:
viewtopic.php?f=1&t=44320.