Kbase P131914: How will OpenEdge DataServer products support DateTime data-types in OpenEdge 10.1C01?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
09/04/2010 |
|
Status: Verified
GOAL:
How will OpenEdge DataServer products support DateTime data-types in 10.1C01?
GOAL:
How will OpenEdge DataServer for Oracle support DateTime data-types in 10.1C01?
GOAL:
How will OpenEdge DataServer for MS SQL Server support DateTime data-types in 10.1C01?
FACT(s) (Environment):
OpenEdge 10.1C
MS SQL DataServer
Oracle DataServer
All Supported Operating Systems
FIX:
In 10.1C01 release, limited support of Datetime data-types in the Oracle and MSS DataServer products will be provided in OpenEdge 10.1C01; full functional support will be shipped in 10.2A release. Details about the support available in 10.1C01 as below:
Oracle DataServer:
In 10.1C service pack 1 (10.1C01) release, The DataServer for Oracle has been enhanced to support Oracle datetime datatypes and their mapping to OpenEdge datetime types. The two Oracle datetime datatypes, 'TIMESTAMP' (abbrev.:. TS) and TIMESTAMP WITH LOCAL TIME ZONE' (abbrev.: TSLTZ), introduced in Oracle release 9i will be supported in OE Release 10.1C01. Oracle 9i, or later, versions of the OCI client libraries are required for support of these new datatypes.
The following datatype mapping will be supported from OpenEdge to the Oracle schema holder:
1. Prior to OE 10.1C01, the Oracle 'Date' types mapped to a 'Date' column which held the Date portion and a surrogate 'Integer' column that held the time portion of an Oracle 'Date' to the "seconds" precision. This default behavior will be retained for backward compatibility.
2. The dictionary migration utility has been upgraded to handle the mapping of an OpenEdge 'Datetime' data type to an Oracle 'TIMESTAMP' (TS) data type. When the Oracle 'TIMESTAMP' is pulled back into a schema image, the Oracle 'TIMESTAMP' is then mapped to an OpenEdge 'Datetime' data type by default. Alternatively, users are allowed to change the 'Oracle 'TIMESTAMP' mapping in their schema image to an OpenEdge 'Character' or 'Date' type.
3. The Oracle 'TIMESTAMP WITH LOCAL TIME ZONE' (TSLTZ) data type can also be pulled from an Oracle data source into an Oracle DataServer's schema image. This is mapped as 'Datetime' type by default. Users are also allowed to change the schema image mapping to OE 'Date' type. The value in the column is translated from the database time zone into the time zone of the OpenEdge client when retrieved into a client application, and visa versa when written.
The DataServer for Oracle has been enhanced to handle OE 'Datetime','Date' and 'Character' datatype that are mapped to Oracle 'TS' datatype in the 'WHERE' clause of an OE client database request.
The DataServer for Oracle has been enhanced to handle OE 'Datetime' ,'Date' that are mapped to Oracle 'TSLTZ' datatype in the 'WHERE' clause of an OE client database request.
SESSION TIME-SOURCE has been enhanced to support expansion of SYSDATE to SYSTIMESTAMP. This expands existing support for session:time-source for DATE functions to timestamps for DATETIME functions.
NOTE 1: When a character value is mapped to an Oracle TIMESTAMP, it must be formatted into a fixed session-level timestamp format of 'YYYYMMDDHH24MISSFF'. The sesssion level format cannot be changed during the DataServer session if character data from the client application is to be interpreted correctly in data translation to the server.
NOTE 2: The OpenEdge Datetime data type supports fractional seconds up to millisecond accuracy and must round off values it receives from Oracle with greater precision.
Not all features of Datetime are supported in OpenEdge Release 10.1C Service Pack 1. In particular the following Datetime features are NOT supported in 10.1C Service Pack 1:
1. Support for the Oracle 'TIMESTAMP WITH TIME ZONE' (abbrev.: TSTZ) datatype will be unavailable.
2. Support for OpenEdge 'DateTime-TZ' datatype will be unavailable.
3. Support for Oracle 'TIMESTAMP WITH LOCAL TIME ZONE' mapped to 'Character' will be unavilable.
4. Support of Datetime/Datetime-TZ as a parameter to, or in the result sets of, a RUN STORED-PROCEDURE statement. This includes the 'SEND-SQL-STATEMENT' procedure, the PROC-TEXT-BUFFER results record and the LOAD-RESULTS-INTO phrase.
5. On migration OE 'Datetime' datatype will be mapped to .Oracle 'TS' datatype by default. Support for mapping OE 'Datetime' datatype to Oracle 'TSLTZ' datatype will be unavailable on migration.
MSS DataServer:
In 10.1C service pack 1 (10.1C01) release, the DataServer for MSS has been enhanced to support datetime datatypes in SQL Server (till Sql Server 2005) with full support for 'datetime' datatypes and NO SUPPORT for 'datetime-tz'.
1. Prior to OE 10.1C01, the MSS 'Datetime' datatype mapped to a OE 'Date' datatype as default. With Datetime support in 101c01, 'Datetime' datatype in Sql Server can be directly mapped to OE 'Datetime' datatype. The default behavior will be retained for backward compatibility i.e. MSS 'Datetime' would be pulled as OE 'Date' datatype by default. However, the user can set the 'OE_101C_DATETIME=yes' environment variable and then MSS 'Datetime' can be pulled as OE 'Datetime' in the schema-holder by default. Note that the user can anytime change the mapping of MSS 'Datetime' to OE 'Datetime','Date' or 'character' as per his or her requirement.
2. Please note that In Sql server subsecond accuracy is upto .333 seconds or 1/300th of a second. Rounding at the millisecond level is granular to a third of a millisecond, so values will round to .000, .003 and .007 seconds.
Users who write datetime comparison expressions in their ABL code, especially equality matching, should use caution when using the DataServer for MS SQL Server. The user must be careful about the millisecond precision of the values he/she is comparing due to the 1/300 millisecond rounding performed internally by Windows and MS SQL Server.
One way for customers to avoid this issue is to ensure that all their datetime values are derived from a Windows or SQL Server source rather than obtaining them from the OpenEdge client or an OpenEdge database where the datetime values can be fabricated with greater precision. By setting their TIME-SOURCE attribute to their logical database of their MSS DataServer schema holder, the values derived for MTIME, NOW, TIME and TODAY will be generated from a Windows time source. This can avoid application conflicts with time values derived from other time sources with precision values of finer granularity.
3. When using run stored-proc to execute an MS Sql server stored procedure, the user must initialize the OUTPUT param with an initial value other than UNKNOWN (?). This initial value will be overwritten by whatever the Sql-server returns to the Output parameter. In case when a NULL is returned, the initial value will remain unchanged..