Setting runtime data access options

This topic includes the following sections:

 

xfODBC has many options that enable you to control how it behaves as it accesses data. To be effective, these options must be set before you connect to a database.

Note that third-party applications used to access Synergy data usually have options, such as query time-out, that use underlying ODBC calls.

Setting formats for dates and times

Defaults for date and time data and the settings that control these defaults are discussed in the sections that follow. However, rather than relying on these defaults, it is generally best to use scalar functions to apply necessary formatting for date, time, datetime, and timestamp data when writing to or receiving data from a database. The scalar functions you can use for this purpose include CURDATE, CURDATETIME, CURRENT_DATE, CURRENT_DATETIME, CURRENT_TIME, CURRENT_TIMESTAMP, CURTIME, CURTIMESTAMP, DAYNAME, HOUR, NOW, SYSDATE, TO_CHAR, and TO_DATE. See Scalar functions for more information on these, and if you are using ADO.NET, see Time columns and ADO.NET.

The following are some examples: 

SELECT mycol FROM mytable WHERE mytimestampcol < CONVERT(TO_DATE(mydatetimeliteral,"DD-MM-YYYY HH:MI:SS"),SQL_TYPE_TIMESTAMP)
SELECT TO_CHAR(mydatetimecol,"DD-MM-YYYY HH:MI:SS") FROM mytable WHERE...
SELECT * FROM mytable WHERE mychardatetimecol < TO_CHAR(NOW(),'YYYY-MM-DD HH:MI:SS')
SELECT...WHERE mydatetimecol < CURRENT_DATETIME()
SELECT...WHERE CONVERT(TO_DATE(mycharcol,"DD-MM-YYYY HH:MI:SS"),SQL_TYPE_TIMESTAMP)< NOW()
Important

Avoid converting a column to an expression if the column resolves to an index used for optimization. This will likely prevent optimization. For example, the first of the following is more likely to enable optimization than the second if mydatetimecol resolves to an index:

...mydatetimecol < CONVERT(TO_DATE(somedatetimeliteral,"DD-MM-YYYY HH:MI:SS"),SQL_TYPE_TIMESTAMP)
...CONVERT(TO_DATE(mydatetimecol,"DD-MM-YYYY HH:MI:SS"),SQL_TYPE_TIMESTAMP) < somedatetimeliteral

Default formats and types for returned dates and times

Date/time (timestamp), date, and time columns are returned with the following formats and default data types:

Database column

Format of returned data

Returned data type

datetime and timestamp

YYYY-MM-DD HH:MI:SS.UUUUUU

SQL_TYPE_TIMESTAMP

date

YYYY-MM-DD

SQL_TYPE_DATE

time

HH:MI:SS

SQL_TYPE_TIME (System.TimeSpan for ADO.NET; see Time columns and ADO.NET)

Changing the default data type returned for datetime columns

Datetime columns are columns created from

By default datetime columns are returned as SQL_TYPE_TIMESTAMP values. This is the standard default, but you can change this by setting the VORTEX_ODBC_DATETIME environment variable to the integer value for one of the following SQL data types:

1 = SQL_TYPE_CHAR

9 = SQL_TYPE_DATE

10 = SQL_TYPE_TIME

11 = SQL_TYPE_TIMESTAMP

For example, to retrieve datetime columns as SQL_TYPE_DATE values by default, set VORTEX_ODBC_DATETIME to 9.

If you set VORTEX_ODBC_DATETIME, set it in the system environment. For client/server configurations, set it on the client. For a service, such as IIS or SQL Server, you must reboot after setting VORTEX_ODBC_DATETIME, unless you set it in the DSN (see Env. variables).

Tip

Datetime columns have an internal type of DATETIME. This is the value that is displayed in the Type field under “Support details” on the Column window of the DBA program (see Viewing information about a column).

Changing the default data type for returned time columns

The standard default data type for returned time columns is SQL_TYPE_TIME. You can change this default by setting the VORTEX_ODBC_TIME environment variables to one of the following integer values:

10 = SQL_TYPE_TIME

11 = SQL_TYPE_TIMESTAMP

This is useful when using ADO.NET, which retrieves SQL_TYPE_TIME columns as System.TimeSpan, a .NET data type that represents a time interval, which is generally more difficult to use than a specific time. (See Time columns and ADO.NET.) Setting VORTEX_ODBC_TIME to 11, however, enables you to get time columns as timestamp values. Note the following:

If you set VORTEX_ODBC_TIME, set it in the system environment. For client/server configurations, set it on the client. For a service, such as IIS or SQL Server, you must reboot after setting VORTEX_ODBC_TIME, unless you set it in the DSN (see Env. variables).

Masks for dates and times in SQL statements

With a date/time, date, or time column, xfODBC must convert the data to the xfODBC driver’s internal date/time format. This is true whether you create the SQL statement or an ODBC-enabled application creates it. The xfODBC driver uses four masks to interpret date/time, date, and time columns. By default, these are YYYY-MM-DD HH:MI:SS, YYYY-MM-DD, HH:MI:SS, and YYYY_MM_DD HH:MI:SS.UUUUUU. xfODBC first attempts to use the first mask (YYYY-MM-DD HH:MI:SS). If it’s unable to use this, it attempts to use the second mask (YYYY-MM-DD), and so on. Dates and times specified in SQL statements must have dates and times that match one of the masks. You can, however, modify the masks. If you want the xfODBC driver to accept other date and time formats, use SET OPTION DATETIME (see SET OPTION). You can also use TO_DATE and TO_TIME (as noted under Setting formats for dates and times above), to format date/time data.

Treating invalid dates as null data

If a database has invalid date data, SELECT statements fail. This occurs even if the columns with invalid dates are not referenced in the SELECT statement. You can, however, instruct the xfODBC driver to treat invalid dates as null by setting the convert_error option to yes. See Specifying handling of invalid dates for information.

Converting dates returned without centuries

When a system catalog is generated, each date field that doesn’t include a century (in other words, each date with a YY year, rather than YYYY) is formatted as a date with a rolling (RR) century. (See Date and time fields.) Then, when the xfODBC driver retrieves a date with a rolling century, it converts it to a date with a century (a YYYY date). The century part of the date is determined by the SYNCENTURY environment variable:

The default for SYNCENTURY is 50. If SYNCENTURY is not set or is set to a negative value, 50 is the cutoff year.

For standalone configurations, set SYNCENTURY in the connect file or in the environment. For client/server configurations, set it in the connect file on the server.

Note

If the century for dates whose years fall between SYNCENTURY and 99 is set to the current century, most likely the SODBC_NOROLL environment variable was set when the system catalog was generated. This environment variable was used for Y2K conversions and is no longer necessary. However, if it was set when the system catalog was generated, xfODBC ignores the SYNCENTURY setting, which results in two-digit years being stored as YY years rather than RR (rolling) years.

Setting the base date for Julian day conversions

When you enter a date into a field with the JJJJJJ format, xfODBC stores the date as the difference between the date you entered and the value of SYNBASEDATE. By default, SYNBASEDATE is set to 1752-09-14 (14 September 1752), but you can change this value. Use the format YYYY-MM-DD.

For stand-alone configurations, set SYNBASEDATE in the connect file or environment; for client/server configurations, set it in the connect file on the server.

The SYNBASEDATE variable is used by the xfODBC driver when it accesses data. It is not used by DBA or dbcreate. so it does not affect the way system catalogs are generated.

Important

There are two instances in which you should not change/set SYNBASEDATE:

  • If you’ve used xfODBC to modify dates in a database, do not change SYNBASEDATE. Doing so will corrupt the date data, because changing SYNBASEDATE changes part of the equation used to store and retrieve dates.
  • If you use the Julian functions %JPERIOD or %NDATE, do not set SYNBASEDATE. When SYNBASEDATE is not the default (1752-09-14), %JPERIOD and %NDATE retrieve and store dates using a different equation than xfODBC, which will corrupt dates in your database.

Skipping records with MCBA deleted-record characters

The SODBC_MCBA environment variable enables you to instruct xfODBC to skip records that contain the MCBA deleted-record characters, which are four right brackets (]]]]) at the beginning or end of a record. Note the following:

Changing the way xfODBC describes strings

xfODBC passes and, by default, describes strings as SQL_VARCHAR (that is, with trailing spaces removed). You can, however, instruct the xfODBC driver to describe strings as SQL_CHAR (though they are always passed as SQL VARCHAR) which was the default behavior in xfODBC versions prior to 8.3. Note the following:

If you set VORTEX_ODBC_CHAR, set it in the system environment. (For a service, such as IIS or SQL Server, you must either reboot after setting VORTEX_ODBC_CHAR or set it in the DSN. See Env. variables.) For client/server configurations, set it on the client.

Note that VORTEX_ODBC_CHAR is used by the xfODBC driver when it sends data to the application. It does not affect the way system catalogs are generated.

Creating a file for query processing options

The GENESIS_INITSQL environment variable enables you to specify a file that contains predefined SET OPTION commands. (This includes all SET OPTION commands except DATETIME, SORTPAGES, and TMPINDEX.) For information on SET OPTION commands, see SET OPTION. The SQL statements in this file are executed each time a connection is made to the driver.

Note the following:

set option logfile 'vtx4.log'
set option tree on
set option error on