What is xfODBC?

xfODBC is a package of components that enables you to make your Synergy data accessible to third‑party applications that can use ODBC, such as Crystal Reports, Microsoft Access, Microsoft Query, and Visual Basic. xfODBC includes the xfODBC Database Administrator (DBA) and dbcreate programs, which enable you to create system catalogs from your Synergy repository definitions, and it includes the xfODBC driver, which uses system catalogs to provide ODBC access to your Synergy database. See xfODBC components.

At the heart of xfODBC is a technological standard called Open Database Connectivity (ODBC). Through ODBC drivers, ODBC enables a wide variety of applications to access various databases by ensuring that both the databases and the third‑party applications conform to a standard set of rules for data access. When you instruct an ODBC‑enabled application to communicate with a database, the application first calls the Microsoft‑supplied ODBC Driver Manager. The ODBC Driver Manager then calls the ODBC driver that communicates with that particular database. (For xfODBC, this is the xfODBC driver.) The ODBC driver translates messages and data between the application and the database, using an ODBC version of SQL to communicate with the application, and using the database’s version of SQL to communicate with the database.

1. An ODBC‑enabled application accessing a Synergy database.

xfODBC supports up to 1,024 concurrent ODBC handles (which generally have a one‑to‑one correspondence with connections) and can access Synergy Database files, relative files, tagged files, and ASCII text files. (You can read data from ASCII text files, but you can’t update them.)

The xfODBC driver supports ODBC 3.8 and SQL92 entry level syntax plus extensions. It supports up to 64 table references (including inline views and table joins).

xfODBC components

xfODBC consists of several components. The main component, the xfODBC driver, enables you to access your Synergy data from third‑party applications. Before the driver can do this, however, your Synergy database must be prepared for ODBC access. To prepare a Synergy database for ODBC access, a system catalog must be generated from the database’s repository files. System catalogs describe Synergy databases in a way that the xfODBC driver can understand (see System catalog).

The following sections describe all the files and components used to

Administrative components

Figure 2 illustrates how the administrative components work together to generate a system catalog. These components are described in the sections that follow. (The generation process itself is described in detail in Creating a System Catalog.)

2. Administrative components generating a system catalog.

Synergy database

To use xfODBC, you’ll need a Synergy database. A Synergy database consists of files of one of the following types:

The Synergy database is a runtime component. It is not directly involved in the creation of a system catalog. Repository files are used to create the system catalog.

Repository files

Repository files refer to the ISAM files generated by Synergy/DE Repository; these files describe and define the actual data files, providing index, tag, field, structure, and key information, along with other definitions. They often have the filenames rpsmain and rpstext (along with the .ism filename extension and, for Windows and UNIX, the .is1 filename extension). xfODBC uses repository data definitions to create data definitions in system catalogs.

When the term repository (all lowercase) is used, it refers to the repository files (rpsmain and rpstext, or their equivalents in your database); the term Repository (capital “R”) refers to the Synergy application you use to define your repository files.

For more information on using Repository, see the Repository User’s Guide.

Connect file

The connect file is a text file you create to tell xfODBC where to find your Synergy data files and the system catalog that describes those data files. The connect file can also be used to define environment variables used by the xfODBC driver, set the convert_error option (which instructs the xfODBC driver to treat invalid dates as null data), and set Synergy driver logging (which enables you to determine if a system catalog is cached). You must have a connect file to open the system catalog in DBA.

For more information, see Setting Up a Connect File.

Conversion setup file

The conversion setup file is a text file that stores information about table locations and access levels. You will probably use DBA to create and modify this file automatically, but you can also perform these steps manually with a text editor. DBA and dbcreate can use the conversion setup file when regenerating a system catalog. For information, see Generating and editing a conversion setup file.

Database Administrator program (DBA)

With its graphical user interface, the xfODBC Database Administrator (DBA) program enables you to manage system catalogs. Use DBA to initialize user and group access to your database; create a conversion setup file; generate, maintain, customize, and verify your system catalogs; and run dbcreate. You can start DBA from the command line or from the Synergy Control Panel (in Windows Control Panel).

dbcreate utility

The dbcreate utility generates system catalogs using repository definitions in repository files. The repository definitions must contain all the structure, tag, field, and key information you need in the system catalog.

You can run this utility from the command line or from within DBA. For more information on generating a system catalog, see Creating a System Catalog.

Runtime components

The runtime components enable you to access Synergy data from ODBC‑enabled applications. Some of these components are distributed with xfODBC. Others must be created for your Synergy data—namely the system catalog, the connect file, a DSN, and possibly an environment setup file.

Figure 1 illustrates how xfODBC runtime components work together to access Synergy data.

Synergy database

A Synergy database can consist of Synergy ISAM files, relative files, tagged files, or ASCII text files. We use the term data files to refer to the files that make up a Synergy database. See Synergy database.

System catalog

For ODBC‑enabled applications to access a Synergy database, a system catalog is required. The system catalog is generated by dbcreate or DBA and provides a “translation” of the Synergy database in a form that the xfODBC driver can understand. You might think of the system catalog as a kind of road map that contains “directions” for xfODBC, providing table location, column, key, access, and other necessary information about the Synergy database.

The dbcreate utility and the DBA program enable you to generate a system catalog from repository files. The DBA program also enables you to view and modify your system catalog files.

A system catalog is composed of tables for database files and ten system tables (see table below). In Windows and UNIX environments, these tables are composed of two ISAM files with the .ism and .is1 filename extensions. In OpenVMS, these are composed of one ISAM file with the .ism extension. Each system table contains specific information about the Synergy database, except the GENESIS_DUAL table, which is a read‑only table with a synonym (dual) and with one row and one column that’s used for statements that require a single row—for example, “SELECT curdate() FROM dual”.

Important

Without the user and group files (sodbc_users.*, sodbc_groups.*), there is no user or password validation when connecting to the database, and all connected users have read‑only access to all tables in the database. Be sure to generate these files (see Generating the system catalog) and keep them with the other system catalog files.

[spacer]

System Catalog Files

Table name

Filenames

Contents

GENESIS_COLUMNS

GENESIS_COLUMNS.ISM
GENESIS_COLUMNS.IS1

Field size, type, and position information

GENESIS_DEPENDS

GENESIS_DEPENDS.ISM
GENESIS_DEPENDS.IS1

SQL view dependency information and information about the names, owners, and database names for the views

GENESIS_DUAL

GENESIS_DUAL.ISM
GENESIS_DUAL.IS1

A read‑only table with one row and one column for operations such as “SELECT curdate() FROM dual”

GENESIS_FORKEYS

GENESIS_FORKEYS.ISM
GENESIS_FORKEYS.IS1

Foreign key information

GENESIS_INDEXES

GENESIS_INDEXES.ISM
GENESIS_INDEXES.IS1

Access keys

GENESIS_TABLES

GENESIS_TABLES.ISM
GENESIS_TABLES.IS1

File, structure, access level, and tag information

GENESIS_VIEWS

GENESIS_VIEWS.ISM
GENESIS_VIEWS.IS1

SQL view definitions, which include information such as view name and the query used to create the view

GENESIS_XCOLUMNS

GENESIS_XCOLUMNS.ISM
GENESIS_XCOLUMNS.IS1

Column references for access key segments

N/A (does not appear as a table in DBA)

SODBC_GROUPS.ISM
SODBC_GROUPS.IS1

Group ID, group name, number of users assigned to each group, group access level, and group description

N/A (does not appear as a table in DBA)

SODBC_USERS.ISM
SODBC_USERS.IS1

User name, password, user’s full name, and group ID

DSN

A data source name (DSN) is a text file that contains the information needed to access a database (the name of the connect file, user and password information, and so forth). Once you’ve created a DSN for a database, users can access the database from an ODBC‑enabled application by selecting the DSN. DSNs make connection details invisible to end users and free end users from having to remember the location of the data files and other connection information. See Setting up access with DSNs for information.

Environment setup file

The optional environment setup file is a text file you write to define the data environment variables that are used by xfODBC when locating Synergy data files. The environment setup file is typically used to set environment variables that are used in the Open filename field of a repository file definition.

For more information on using an environment setup file, see Setting environment variables in an environment setup file.

xfODBC driver

The xfODBC driver is a DLL (tod32.dll or tod64.dll) called by the ODBC Driver Manager whenever a third‑party, ODBC‑enabled application accesses a Synergy database. The xfODBC driver uses a connect file to locate Synergy data files and the system catalog. Using the system catalog as a road map, the driver then reads the data files and transfers data between the database and the third‑party application.

Synergy database driver

They Synergy database driver (vtx4) enables the xfODBC driver to access Synergy databases. The Synergy database driver directly processes SQL commands.

External components

The following are not part of xfODBC, but they work with xfODBC.

ODBC Driver Manager

A DLL provided by Microsoft that opens and closes ODBC drivers as directed by an ODBC‑enabled application.

ODBC‑enabled application

A 32‑bit application running on Windows that uses the ODBC API to access databases. Crystal Reports, Microsoft Access, and Microsoft Query are examples. Synergy applications that use SQL Connection are also “ODBC‑enabled.”

SQL OpenNet

A Synergy product that enables xfODBC to work in a client/server configuration. Figure 1 illustrates how SQL OpenNet works with xfODBC runtime components to access Synergy data.

For more information on SQL OpenNet, see Configuring Connectivity Series.