How to read DateTimeOffset from OracleObject
How to read DateTimeOffset from OracleObject
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?
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?
-
- Devart Team
- Posts: 2420
- Joined: Wed 02 Nov 2011 09:44
Re: How to read DateTimeOffset from OracleObject
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.
-
- Devart Team
- Posts: 2420
- Joined: Wed 02 Nov 2011 09:44
Re: How to read DateTimeOffset from OracleObject
You can read datetime offset. Let's consider the following example:
And use the following code:
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');
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
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.
Modifying items through SqlDeveloper leads to the following situation:
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.
That is because after reading it, the modified timestmap has invalid offset: "7.5.2019 7:55:54 -123:12".
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;
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"
}
]
}
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);
-
- Devart Team
- Posts: 2420
- Joined: Wed 02 Nov 2011 09:44
Re: How to read DateTimeOffset from OracleObject
We have reproduced the issue. We will investigate it and post here about the results as soon as possible.
-
- Devart Team
- Posts: 2420
- Joined: Wed 02 Nov 2011 09:44
Re: How to read DateTimeOffset from OracleObject
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.
-
- Devart Team
- Posts: 2420
- Joined: Wed 02 Nov 2011 09:44
Re: How to read DateTimeOffset from OracleObject
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
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
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
Is this a bug or should there be some way to convert the time zone name to offset? I couln't found any
-
- Devart Team
- Posts: 2420
- Joined: Wed 02 Nov 2011 09:44
Re: How to read DateTimeOffset from OracleObject
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.
You can use the OCI mode as a temporary workaround if this suits your scenario.
-
- Devart Team
- Posts: 2420
- Joined: Wed 02 Nov 2011 09:44
Re: How to read DateTimeOffset from OracleObject
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.
-
- Devart Team
- Posts: 2420
- Joined: Wed 02 Nov 2011 09:44
Re: How to read DateTimeOffset from OracleObject
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
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
We are using the earlier example to read DateTimeOffset from Oracle:
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.
After updating DevArt dotConnect for Oracle to 9.13.1098 reading offset in TZR format seems to be broken. The timestamp in the DB isPinturiccio 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); ...
Code: Select all
03.12.2020 11:02:08,598895000 EUROPE/HELSINKI
Code: Select all
3.12.2020 9:02:08 +02:00
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
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
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.