Number: TB0022

Availability: As of BuildPro 8.00.

Introduction

BuildPro functionality has been introduced to provide consistency between databases. This simplifies the process of supporting multiple databases and makes sharing data with non-BuildPro applications effortless.

Functionality Changes

Old Functionality

• An oracle native date has two key parts - date and time. A date is stamped with a time 01:01:01 (relevant to a 24 hour clock).

• Blank dates are created in the database.

• File statements using a native date field in an index select NULL dates at the high end. eg. BLANK/VALID DATES/NULLS

• "OldSqlDates" configures BuildPro to build and access the date columns according to the BuildPro storage type definition (Primarily for TODAY upgrades). TODAY created these fields in the format specified by the data dictionary. For example, if the field was specified as Character, then with Oracle the date field mapped to CHAR(8) instead of DATE(7) as BuildPro now builds and expects.

• "DisableNulls" was implemented due to the inconsistency of NULL handling between databases. This made BuildPro processing complex when handling NULLS, and degraded performance.

New Functionality

• An oracle native date is stamped a time of 00:00:00.

• A date with a value of '0' or BLANK are written to the database as NULL.

• File statements using a native date field in an index, select NULL dates at the low end eg. NULL/VALID DATES. This is consistent with other relational databases and the ability to do this was first introduced into Oracle in release 8.1.6.

• "OldSqlDates" must not be set to True. This means that all Oracle relational database tables that contain date columns built in this manner, must be unloaded to an ASCII file, the table dropped, and then using BuildPro the table needs to be recreated and loaded. When "OldSqlDates" is not set, BuildPro creates DATE fields in Oracle as DATE(7) irrespective of the BuildPro storage type definition (character, long integer, or double precision).

• "DisableNulls" can be set to True but will have no effect on DATE fields as BLANKS are not valid entries. Oracle 8.1.6 and above, allows selection of NULLS at either the high or low end ie. SELECT statements can include "NULLS high" or "NULLS low". Other databases select Nulls low. OracleNativeDates ensures that NULLS are treated low. This corrects earlier performance issues.

Environment Settings

NOTE: The initialisation setting "OldSqlDates" is not supported when using the new Native Date functionality.

A new setting "OracleNativeDates" can be found in the initialisation files ".todayrc" and "wintoday.ini". The default value is "True" meaning that Oracle Native Date functionality is in operation. When using a client connecting to a SCO server, the client wintoday.ini must be edited to set OracleNativeDates=False.

Requirement

If "OldSqlDates" has been used previously, all tables should be recreated in the manner described above.

Before using the OracleNativeDates an Oracle Procedure should be used on existing data to:

• Convert all 01:01:01 timestamps to 00:00:00 where a valid date exists.
   
• Convert all BLANK dates to NULL dates
   
• Convert all zero dates ie 00/00/00 to NULL dates