BTI ODBC Driver Configuration Dialog
Pervasive Software ODBC Interface Configuration Dialog

The Pervasive Software ODBC Interface configuration dialog has the following options.

 Data Source Name

A name by which you will identify the data source.  For example, "Personnel Data."

 Description

A description of the data in the data source.  For example, "Hire date, salary history, and current review of all employees."

 Database Name

This edit box can be used to enter  the name of a Scalable SQL named database. This option cannot be used with the DDF Path and Data Path options.

 Database Names List Box

This list box contains the names of databases visible to Scalable SQL from this workstation. This option cannot be used with the DDF Path and Data Path options. If the list box is empty, pressing the Refresh button will cause it to be filled with the names of named databases visible from this workstation.

 Refresh

Check this box if you want to refresh the list box in order to select a named database as the context of the data source.  See Named Databases in the Comments section. This option cannot be used with the DDF Path and Data Path options.

 DDF Directory

The path to the dictionary files (*.DDF) for your database. This option cannot be used with the Database Names options. A new data dictionary will be created (at your option) if one is not located in this directory. The directory must already exist, however.

 Browse Button For DDF Directory

Next to the DDF Directory edit box is a browse button that brings up an Open Dialog box allowing you to select the path to the dictionary files (*.DDF) for your database.

 Database Directory

The path to the data files for your database, if different from the location of the data dictionary files.

 Browse Button For Database Directory

Next to the DDF Directory edit box is a browse button that brings up an Open Dialog box allowing you to select the path to the data files for your database.

 User Name

The user login ID for the data source.

 Password

The user password for the data source. If you prefer the password to be kept secret, leave the password blank; most ODBC-enabled applications will prompt for missing login information.

 Owner Names

Enter a list of owner names, up to eight  separated by commas, to be used in opening files on connections to this data source.

 Translate OEM characters

If this check box is checked, character data moving between the ODBC Interface and the underlying database will be translated.  Use this option if the database stores character data in the OEM character set, and your application needs to view the data in the character set used by Windows, Windows 95, or Windows NT.

 Scalable SQL Null Handling

When checked, Scalable SQL-style null handling is applied; when unchecked, ODBC-style null handling is applied.  See explanation below.

 Scalable SQL 4 Mode

Log into the connection in Scalable SQL 4 mode when checked.  When unchecked, login in Scalable SQL 3.x mode even if the underlying engine is Scalable SQL 4 compliant.

 Access/Jet Compatibility

Setting this check box turns on a special Access/Jet Compatibility mode that provides additional interoperability with Microsoft Access.

Access is known to have problems with certain data types (notably TIME, DECIMAL, and the various NUMERIC flavors).  These problems show up as #deleted errors and reports that a record has been updated by another user when it has actually not been updated by anyone else.

When the compatibility mode is turned on, the Interface takes special steps to ensure proper interpretation of these types.

 Date Format

Select the date format desired.  This option is only available when Scalable SQL 4 Mode is checked.  The options are: mdy, myd, dmy, dym, ymd and ydm. See explanation.

 Login Script

Enter one or more SQL statements to be executed immediately after login takes place. See explanation below.

Comments

Login Must Occur For Data Source To Be Added Or Modified

When adding or modifying a data source, the parameters you supply must allow a successful login to occur.  See the driver help file for guidelines on modifying data sources in situations when the database is inaccessible.

Named Databases

Database names are a feature of Scalable SQL and Scalable SQL for Netware.  The use of database names insulates the application from the need to know the specific physical location of a database, making the database configuration more maintainable over time. The ODBC Interface fully supports the use of database names, but provides no means for defining them.

Database names can be defined using the SQL Setup utility that is part of your version of Scalable SQL.

Date Format

If the date format is set to any value other than the default (mdy), SET DATEFORMAT is called for the specified date format after a successful login to the database.  This supports dates using the date format specified for updates and inserts.  For example, if Data Format is ymd, a date field can be formatted as in the following SQL statement:

Select * from tablename where datecol = '1925/12/25'

Login Scripts

A login SQL script consists of one or more SQL statements, separated by semicolons, that are to be executed on connection to the data source, after a successful login to the database.

This is useful, for example, to establish the global null values when the underlying data was built using null values other than the default.

This feature is especially useful for data sources to be used with third-party applications such as Borland Delphi, Visual Basic RDO, and Microsoft Access that do not offer easy ways to execute SQL statements on each connection as it is made.

In the Login Script edit control, put the SQL statement or statements you want to have executed during login to the database. If more than one SQL statement is to be executed, separate the statements with a semi-colon (;). For example:

SET BINARYNULL = 255;SET DECIMALNULL = ' '

In many cases you may wish to avoid the use of a semi-colon as statement separator.  For example, you may wish to use a semi-colon within the SQL statement, or you may need to use the connect string returned from SQLDriverConnect, which uses a semi-colon to separate attribute-value pairs.  In these instances, you can begin the script with any non-alphabetic character, and that character will be used as the delimiter. Use a dollar sign as the delimiter instead of the semi-colon as indicated in the example below:

For example, to connect to the Patients data source as described above, and then set values for BINARYNULL and DECIMALNULL global null values on this connection, you would use the following connection string:

DSN=Patients;DDFPATH=C:\BTI\WIN\DEMODATA;UID=Smith;
PWD=Sesame;$SET BINARYNULL = 255$SET DECIMALNULL = ';'

Test the effect of your script after connecting to the data source. If it has worked as you expected, delete the backup copy of the ODBC.INI file you made at the beginning of this section.

Scalable SQL-style null handling vs. ODBC-style null handling

Scalable SQL determines null values by examining the data for a given row and column, looking for a sentinel value in each byte of the column.  For example, the default value for a column of type NUMERIC is the space character.

For most data types, there is a valid sentinel value outside the scalar range of the type.  However, for a few types, such as INT(2) and INT(4), this is not the case.  The default sentinel value for integer types is zero, and an integer with a zero in all bytes has the legal scalar value zero.

Some ODBC-enabled applications construct WHERE clauses that test a column value with an IS NOT NULL construct, resulting in the exclusion of rows where every byte of the test columns value is the sentinel value.  This can result in the inadvertent exclusion of rows from a result set.

The ODBC Interfaces report as non-nullable any data type that does not have a possible null value outside its scalar range.  Nonetheless, some ODBC-enabled applications still construct tests of the type described in the preceding paragraph.

To allow these applications to operate correctly, the ODBC Interface modifies the SQL statement substituting WHERE 0 = 1 for WHERE MY_INT_COLUMN IS NULL and  WHERE 1 = 1 for WHERE MY_INT_COLUMN IS NOT NULL.  This behavior is applied whenever a test for null status is applied to a column which the ODBC Interface declares as not nullable.

Some programmers may want to expose the native null handling of Scalable SQL, which requires that the ODBC Interfaces null handling behavior be disabled.  To accomplish this, set the Scalable SQL Nulls check box in the Setup or SQLDriverConnect dialog.  When passing a connect string to SQLDriverConnect or SQLBrowseConnect, pass the attribute value pair NULLENABLED=yes.