How to read DateTimeOffset from OracleObject

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
pakarjon
Posts: 6
Joined: Tue 05 Mar 2019 13:22

How to read DateTimeOffset from OracleObject

Post by pakarjon » Tue 30 Apr 2019 12:04

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?

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: How to read DateTimeOffset from OracleObject

Post by Pinturiccio » Fri 03 May 2019 14:06

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.

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: How to read DateTimeOffset from OracleObject

Post by Pinturiccio » Mon 06 May 2019 14:09

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();

pakarjon
Posts: 6
Joined: Tue 05 Mar 2019 13:22

Re: How to read DateTimeOffset from OracleObject

Post by pakarjon » Tue 07 May 2019 06:47

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".

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: How to read DateTimeOffset from OracleObject

Post by Pinturiccio » Wed 08 May 2019 15:59

We have reproduced the issue. We will investigate it and post here about the results as soon as possible.

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: How to read DateTimeOffset from OracleObject

Post by Pinturiccio » Wed 29 May 2019 13:18

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.

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: How to read DateTimeOffset from OracleObject

Post by Pinturiccio » Fri 07 Jun 2019 08:48

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

pakarjon
Posts: 6
Joined: Tue 05 Mar 2019 13:22

Re: How to read DateTimeOffset from OracleObject

Post by pakarjon » Fri 14 Jun 2019 05:03

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

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: How to read DateTimeOffset from OracleObject

Post by Pinturiccio » Fri 14 Jun 2019 13:59

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.

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: How to read DateTimeOffset from OracleObject

Post by Pinturiccio » Wed 03 Jul 2019 11:47

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.

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: How to read DateTimeOffset from OracleObject

Post by Pinturiccio » Thu 04 Jul 2019 13:34

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

Post Reply