ODBC Configuration and Troubleshooting with Pervasive Software's ODBC 2.01

I. Introduction

The open database connectivity (ODBC) interface allows applications to access data in database management systems (DBMS) using structured query language (SQL) as a standard for accessing that data.

The required ODBC components consist of a Driver, the Driver Manager, a data source, and the Application.

The driver manager loads a specific driver when an ODBC application calls the SQLConnect or SQLDriver Connect function. This can also be accomplished by using the ODBC Administrator to attach a data source, which can then be selected by generic ODBC applications like Access. Applications can determine the functionality supported by a driver by calling SQLGetInfo, SQLGetFunctions and SQLGetTypeInfo; the driver performs the following tasks in response to ODBC function calls from an application.

• Establishes a connection to a data source
• Submits SQL requests to the data source
• Translates data to or from other formats, if requested by the application
• Returns results to the application

A Data Source consists of the data the user wants to use and its associated engine and DBMS systems.

Connecting to a Data Source

When an application uses a specific data source and its driver, it passes the connection information for that data source which is stored in the ODBC.INI file or registry to the driver in a call to SQLConnect.

II. Installation Configurations

The Win16 ODBC V2.x Installation program installs the following components in C:\BTI\WIN\BIN, by default. The installed files include the system files for Microsoft's ODBC v2.10 (Win16 interface only), which are only installed if your current version of ODBC is older than the included version.

And the following files are copied to your default Windows SYSTEM directory (C:\WINDOWS\SYSTEM, by default):

The Win32 ODBC V2.x installed files include the system files for Microsoft's ODBC v2.50, which are only installed if the version of ODBC on your system is older than the included version. By default, the following files are installed in C:\WINDOWS\SYSTEM for Windows 95 or in C:\WINNT\SYSTEM32 for Windows NT.

The following file is copied to your C:\WINDOWS, C:\WINNT or the user directory specified during installation. This file is copied only if the installation detects that it is not present.

III. ODBC Architecture

Using Win16 application with Win16 drivers.

Using Win32 application with Win32 drivers

ODBC Components path.

ODBC Application Paths: List of different engines that can be used by ODBC driver.

Note:

LE ===è Engine running on same machine as application - Local engine
RE===è Engine running on a different machine as application - Client Server

ODBC v2.01 require minimum patch level Btrieve Client/Server revision 6.15.440 or Scalable SQL Client/Server version 3.01 with patch SSQLNW301.EXE for NetWare, SSQLNT301.EXE for NT or Scalable SQL Client/Server version 4.0 or Btrieve Workstation Engine 6.15.430.

For the list of files please refer to the Components and Dcmatrix document available on the Pervasive FTP Site.

IV. Creating data source

The ODBC Administrator is used to make a data source available to the ODBC driver.
The ODBC Administrator is installed as an icon in the Pervasive Software program group during setup.
It is also installed as an icon in the windows control panel as ODBC.

Steps

1. Launch the ODBC Administrator.
3. In the Data Source dialog, select Add.
4. In the Add Data Source dialog, select Pervasive ODBC Interface and click OK.
5. When the Pervasive ODBC Interface for Windows/Windows 95 dialog appears.

2. Sssign a data source name, which appears in the ODBC Data Sources dialog.
4. Either select a Scalable SQL database name generated by Scalable SQL setup, or provide the directory paths to the data files.

V. Connecting to Data Sources via ODBC

a. Connecting to Win16 MS Access

To connect to Win16 MS Access:

2. Launch MS Access.
3. Select New Database from the File menu.
4. Assign a file name.
5. Select Attach Table from the File menu.
6. In the Attach dialog, select SQL Database and click OK.
7. In the SQL Data Source dialog, select the data source named earlier by the ODBC Administrator and click OK.
8. In the Attach Tables dialog, select the table to attach and click Attach. Click Close when the desired tables(s) is attached.

b. Connecting to Win32 MS Access

To connect to Win32 MS Access:

1. Launch MS Access.
3. Select New Database from the File menu.
4. Assign a file name.
5. Select Get external data, Link tables.
6. In the Link dialog, select files of types as ODBC Database, which brings the Select Data source screen.
7. In the Select Data source dialog, select the data source named earlier by the ODBC Administrator, and click OK.
8. In the Link Tables dialog, select the table to link, and click OK. Click Close when the desired tables(s) is attached.

V1.Running Pass Through query in MS Access.

1. Choose SQL Specific, Pass Through from the Query menu in query design mode. For Access 7, select Query and click New.
3. Accept the default of Design View.
4. Close the Show Table window.
5. Select SQL Specific from the Query menu and choose Pass-Through.
6. Entered the desired SQL statement.

VI. Troubleshooting

Issue: "SQL install no longer supported"

Solution:  This error occurs when installing ODBC v1.x on a workstation with driver manager v3.0 installed (this driver comes with office 97 or other Microsoft products).

By default, the MS Office 97 install upgrades the user to the ODBC 3.0 driver manager. While the ODBC Interface v2.0 setup accounts for this change the ODBC Interface v1.0 setup will fail to install, with a failure trying to use SQLInstallODBC(), a function dropped from the Microsoft feature set.

To enable the ODBC Interface to install on a system after MS OFFICE 97 has been installed requires renaming of the following items:

ODBC32.dll
ODBCCR32.dll
ODBCCP32.dll
ODBCINT.dll

After these items have been renamed install ODBC Interface v1.0. Apply the patch for ODBC interface v2.0. (Be aware NOT to setup any DSNs during install).

The MS OFFICE 97 environment can now be restored by renaming the changed items listed above,or performing a complete reinstall of MS Office 97.

Issue:  Status 234.

Solution:  When sorting on a non-indexed field, the external Sort path setting in registry under

KEY_LOCAL_MACHINE\SOFTWARE\Pervasive Software\Scalable SQL EngineV3.01\Workstation Edition\Settings

should be set to the location where the Btrieve engine is loaded.

Issue:  SSQL-24.

Solution:  This Scalable SQL engine must be run with the fully licensed MicroKernel Database Engine to use with ODBC driver (not development kit).

Issue:   MS reserved error –7778

Solution:  Make sure the DSN created is a System DSN or User DSN not a File DSN.

Microsoft Access, Microsoft Visual Basic

----------------------------------------------------------------

Issue: MS Access do not allow updating rows.

Solution:  MS Access and VB do not allow updating of rows on tables that do not have a unique index. Access 95 / 97 allows users to specify which columns constitute a unique identifier if an index does not exist, but a unique index should be created on these columns in advance for best performance on updates.

Issue:  MS Access shows each record as #Name.

Solution:  Microsoft Access may return #Name in table view when encountering column names containing invalid characters or Scalable SQL keywords. The following column name characters which might cause Microsoft Access to return a #name: back slash "\", forward slash "/", hyphen "-", NULL characters and NULL terminated strings, arithmetic operators, and unknown characters. #Name also may result when a mismatch exists between the defined column type and the actual data stored in the Btrieve data file. A problem caused by mismatched data is not always obvious. For example, a column of type CHAR filled with NULL bytes may result in a #name return. When a column of type MONEY is used as a key, the data sheet may show #name for every cell. This is apparently due to an internal error in Access.

Issue:  MS Access shows each record as #Delete.

Solution:  The Microsoft Access Jet engine may return a #deleted for each field returned. The default query used by Microsoft Access causes an internal comparison of the record set. This internal comparison is used to determine if a record has been deleted or modified from the database. The mechanism is known to work poorly for certain data types, notably TIME, MONEY, and the NUMERICSA and NUMERICSTS data types. Microsoft recommends using SQL pass-through query as a workaround to this problem.

With v2.01 driver you can add data source by specifying Jet/Access compatibility This option has been implemented by Pervasive’s ODBC driver to workaround the #Delete problem in MS Access.

Issue:  Getting message, "Someone else has changed the data," while trying to perform an update.

Solution:  When trying to update records in a table with a key field that is a MONEY data type, a window displays a message indicating that someone else has changed the data and the update does not occur. Access exhibits a variety of difficulties with tables that have a unique key containing a field of the TIME, DATE, or BFLOAT data type. Problems also exist with Access' handling of NUMERICSA and NUMERICSTS data types when used in the key. Also, this may result when a mismatch exists between the defined column type and the actual data stored in the Btrieve data file.

Issue: Receiving message, "Invalid date, time or timestamp".

Solution:  An "Invalid date, time, or timestamp" error may appear when opening a table in tableview. This is often the result of data stored in a null type other than the default.

For example if date data were stored with an assumed binarynull value of '128' an error will appear in Access because binary '128' is not a valid date. Selecting the "Null Enabled" checkbox, combined with a DSN "Login Script" of "Set Binarynull=128" is a workaround for this problem.

Issue:   Status Code 2103

Solution:  Status Code 2103 may be returned when using ODBC applications because the supported receive packet size for Scalable SQL 3.01 for NetWare (SSPXCOM.NLM) is 576. This may be encountered when Access sends very large statements. Status Codes 20 or 2103 may be returned when using ODBC applications because the default receive packet size for Btrieve v6.15 for NetWare (BSPXCOM.NLM) is 576. In some cases, the workstation communication module (wbticomm.dll/wbtrcall.dll, wbtrv32.dll/w32bticm.dll) may take a long time to return an error that it may appear as a hang condition. This may be encountered using Microsoft Access because is sends very large statements. Please increase the receive packet size by loading BSPXCOM.NLM with -r=1500 (Ethernet packet size) or –r=4096 (token ring). Use a value appropriate to your topology.

Issue: Status Code 802

Solution:  If you are running the Win32 ODBC driver, you may encounter an error when attempting to log in to the database. The ODBC driver may return a Status Code 802. To correct this problem, you may need to apply a Scalable SQL v3.01 NetWare patch. This patch contains a new w32bticm.dll and an updated SSPXCOM.NLM. If you have obtained this patch, please set the a -r parameter in the SQLSTART.ncf: load sspxcom.nlm -r=1500 for ethernet or –r=4096 for token ring network. Status Code 802 can occur if Winsock32.dll or Winsock.dll is missing or damaged. Also, Status Code 802 occurs if the Scalable SQL Requester target engine configuration is set to 1(set to server engine), but NTSSQL engine is not loaded.

TROUBLESHOOTING DDF ISSUES

Issue: Status Code 204.

Solutions:  The Pervasive ODBC Interface expects a dictionary to be properly built. An example of an improperly built dictionary is one where the table name index (Key #1 of FILE.DDF) is not created as"Case Insensitive," but is created as "Case Sensitive" instead. In this situation, attempts to attach to tables that are not specified with the same case the table name was originally defined produces a Status Code 204. In a properly built dictionary, you can specify table and field names with any case, regardless of the way it was originally defined.

Issue: Status Code 4.

Solution:  Incorrectly defined indexes in the dictionary produces Status Code 4 errors because the ODBC Interface cannot locate the data it needs based on a given Index. If the table definition within a set of DDFs does not match the index definition of the physical Btrieve file, Status Code 4 may be returned. Status Code 4 is likely to occur when trying to find particular rows in the table. If a dictionary is not built properly or has invalid or improperly structured data, ODBC applications will not function correctly. The most common symptom of this is an unexpected status code when accessing the database.

Issue: Status Code 6.

Solution:  Older versions of INDEX.DDF were created with only two indexes. However, a newly created INDEX.DDF contains three indexes. If the ODBC Interface tries to access an older dictionary, it automatically tries to add the new third index to INDEX.DDF if it is not already there. This index is built on three fields in the X$Index table: Xi$File, Xi$Number, and Xi$Part, and the index is created with the UNIQUE attribute. According to the definition of these fields, the combination of these three fields is always unique. However, some improperly structured dictionaries have invalid data including duplicate entries for these fields in INDEX.DDF. In this case, attempting to add this unique index will fail. This will produce a Status Code 6 when the ODBC interface needs to access this index while processing requests.

A user with an invalid dictionary should contact the application vendor or developer and request a valid dictionary.