Setting runtime data access options
This topic includes the following sections:
- Formats for returned dates and times
- Converting dates returned without centuries
- Treating invalid dates as null data
- Masks for dates and times in SQL statements
- Setting the base date for Julian day conversions
- Skipping records with MCBA deleted-record characters
- Changing the way xfODBC describes strings
- Creating a file for query processing options
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:
- For information on other options that affect how data is accessed by changing the way the system catalog is generated, see Setting catalog generation options.
- For information on how to set environment variables, see Setting environment variables for xfODBC.
- For information on SQL OpenNet options that affect xfODBC, see Setting SQL OpenNet client options in net.ini.
Date/time (timestamp), date, and time columns are returned with the following formats and default data types:
Format of returned data
Returned data type
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.
Datetime columns are columns created from
- date, time, or date/time column definitions in CREATE TABLE statements.
- repository fields that have User as the type, Date as the subtype (the Class field), and ^CLASS^=YYYYMMDDHHMISS as the user data. See Defining a new field.
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).
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.
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 do not set VORTEX_ODBC_TIME, time columns are returned as SQL_TIME values.
- SQL_TIMESTAMP values have both a date and a time, so to create a SQL_TIMESTAMP value, xfODBC includes the date 1-1-1.
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).
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:
- If the year for a retrieved date is between 0 and the value of SYNCENTURY, xfODBC uses the current century (20xx).
- If the year is between SYNCENTURY and 99 (inclusive), the previous century (19xx) is used.
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.
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.
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.
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).
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.
There are two instances in which you should not change/set SYNBASEDATE:
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:
- By default, SODBC_MCBA is not set (so xfODBC does not skip records that contain the MCBA deleted-record characters).
- To instruct xfODBC to skip records that contain MCBA deleted-record characters, set the SODBC_MCBA environment variable to any value.
- For stand-alone configurations, set SODBC_MCBA in the connect file, in the environment, or at the system level. For client/server configurations, set it in the connect file on the server.
- The SODBC_MCBA setting does not affect the way system catalogs are generated. This is used by the xfODBC driver when it accesses data. It is not used by dbcreate or DBA.
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 VORTEX_ODBC_CHAR is set to 12 or is not set (the default), the xfODBC driver passes and describes strings as SQL_VARCHAR.
- If VORTEX_ODBC_CHAR is set to 1, the xfODBC driver passes strings as SQL_VARCHAR, but describes them as SQL_CHAR.
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.
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:
- Each option must be on a separate line in the file, and each line must have the following format: set option option. For example:
set option logfile 'vtx4.log' set option tree on set option error on
- The GENESIS_INITSQL environment variable must be set to the path and filename of the options file and must be set in the environment. For client/server configurations, it must be set in the environment on the server or in the opennet.srv file (Windows only).
- If this environment variable is set in the DSN (i.e., is specified in the xfODBC Setup window for a DSN), logging will be limited to a single connection (a connection made with the DSN).