Setting runtime data access options

This topic includes the following sections:

 

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

If you’re using ADO.NET, also see Time columns and ADO.NET.

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

Formats 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

timestamp

YYYY-MM-DD HH:MI:SS

SQL_TTYPE_IMESTAMP

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)

You can use the SQL command TO_CHAR to change the display format. For information, see TO_CHAR.

Changing the data type returned for datetime columns

Datetime columns are columns created from

By default, datetime columns are returned as SQL_TIMESTAMP values, but you can change the SQL data type for these columns by setting VORTEX_ODBC_DATETIME to the integer value for one of the SQL data types:

1 = SQL_CHAR

9 = SQL_DATE

10 = SQL_TIME

11 = SQL_TIMESTAMP (the default)

For example, to retrieve datetime columns as SQL_DATE values, 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. The internal type for a column is displayed in the Type field under "Support details" on the Column window of the DBA utility. See Viewing information about a column for more information.

Changing the data type for returned time columns

You can change the SQL data type for returned time columns by setting VORTEX_ODBC_TIME to one of the following integer values:

10 = SQL_TIME (the default)

11 = SQL_TIMESTAMP

This is useful when using ADO.NET, which retrieves SQL_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).

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.

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.

Masks for dates and times in SQL statements

When you write a date/time, date, or time column to a database, xfODBC must convert the data to the xfODBC driver’s internal date/time format. This is true if you create the SQL statement and if an ODBC-enabled application creates the SQL statement. 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).

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