This topic includes the following sections:
xfODBC is a package of components that enables you to make Synergy data accessible to third-party applications that can use ODBC, applications such as Crystal Reports, Microsoft Access, Microsoft Query, and Visual Basic. xfODBC includes the following (see xfODBC components below for more information):
- The dbcreate and xfODBC Database Administrator (DBA) programs, which enable you to generate a system catalog (an essential component for ODBC access) from a Synergy repository
- The xfODBC driver, which provides ODBC access to your Synergy database via the system catalog created from the repository for the database
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 databases and third-party applications conform to a standard set of rules for data access.
To communicate with a database, an ODBC-enabled 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.
|
|
Additionally, for client/server configurations xfODBC uses SQL OpenNet, which supports SSL encryption (data packet encryption) and other features (see SQL OpenNet below).
xfODBC support
xfODBC supports ODBC access from an ODBC-enabled application on a Windows machine to Synergy data on the same machine or on a server with a Synergy-supported Windows, UNIX, or OpenVMS operating system.
xfODBC supports up to 1,024 concurrent ODBC handles (which generally have a one-to-one correspondence with connections) and can access Synergy ISAM files, relative files, and ASCII text files. (You can read data from ASCII text files, but you cannot update them via xfODBC.)
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).
- See RESTRICT.TXT, a text file distributed with Synergy/DE, for information on restrictions to ODBC support.
- See Appendix B: SQL Support for information on SQL statements, commands, and functions supported by xfODBC.
To control access to data, xfODBC supports users (level-based and privilege-based) and table access levels. See Managing access with users, groups, and table settings.
|
To update a Synergy database, we strongly recommend using a Synergy application that’s designed to efficiently maintain database integrity. If you use an ODBC-enabled application to update a Synergy database, you may run into record-locking issues. |
|
A JDBC driver is available for JDBC access, and an xfODBC driver is available for UNIX. These components are used in place of the xfODBC driver (tod32.dll/tod64.dll), they both use the Synergy database driver (vtx4), and they use the same licensing as the standard Windows xfODBCdriver. However, note that the JDBC driver and the xfODBC driver for UNIX are not supported. If you are interested in using one of these unsupported components, contact Synergy/DE Developer Support. |
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 below), and they support users needed for access (see Managing access with users, groups, and table settings).
xfODBC has two types of components:
- Administrative components, which prepare a Synergy database for ODBC access
- Runtime components, which access a Synergy database with the xfODBC driver
Administrative components
Figure 2 illustrates how administrative components work together to generate a system catalog. These components are described below. (The generation process is documented in Creating a System Catalog.)
|
|
Repository files are ISAM files generated by Synergy/DE Repository; these files describe and define Synergy data files, providing index, tag, field, structure, and key information, along with other definitions. Repository files 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 the system catalog.
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 the xfODBC Database Administrator (DBA) program. 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 can use the xfODBC Database Administrator (DBA) program to create and modify this file automatically, or you can perform these steps manually with a text editor. DBA and dbcreate can use the conversion setup file when regenerating a system catalog. For more information, see Generating and editing a conversion setup file.
Database Administrator (DBA) program
The DBA program enables you to view and customize some elements of the system catalog. It also enables you to verify a system catalog, generate a conversion setup file, and generate a system catalog that supports level-based users. See DBA basics for more information.
dbcreate utility
The dbcreate utility generates the system catalog from repository definitions in repository files. The repository definitions must contain all the structure, tag, field, and key information you need in the system catalog. For more information, see dbcreate utility and Generating the system catalog.
Runtime components
The following components enable you to access Synergy data from ODBC-enabled applications and, in some cases, set options for data access. 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.
To use xfODBC, you’ll need a Synergy database. A Synergy database consists of files of one of the following types:
- Synergy ISAM files. We strongly recommend using ISAM files with xfODBC because they enable the xfODBC driver to create additional keys for optimization. xfODBC can work with tagged files, which are ISAM files that contain multiple types of records.
- Relative files, which contain a single type of data record with a single record number key.
- ASCII text files. Data can be read sequentially from ASCII text files via xfODBC, but not updated. There is no key.
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.
A system catalog is a group of Synergy database files and tables that enable the xfODBC driver to access a Synergy database. The files and tables store information about the database: file location, column and key information, access levels, and other information necessary to access Synergy data (e.g., user information). System catalogs are generated from repositories (see Repository files above). Note the following:
- A system catalog will have SODBC_GROUPS and SODBC_USERS files only if it supports level-based users. These files store information on users that belong to groups that define their access level to the database. See Managing access with users, groups, and table settings for more information.
- A system catalog will have GENESIS_USERS and GENESIS_AUTHS only if it supports privilege-based users. These files/tables store information on privilege-based users and their database and object-level privileges. Users of this type are created and managed (e.g., assigned privileges) using the GRANT, REVOKE, and SET PASSWORD statements. See Managing access with users, groups, and table settings for more information.
- The GENESIS_DUAL table is a read-only table with a synonym (dual) and with one row and one column. It is used for statements that require a single row (e.g., “SELECT curdate() FROM dual”).
In Windows and UNIX environments, system catalog tables are composed of two ISAM files (with .ism and .is1 extensions). In OpenVMS, these are composed of one ISAM file with the .ism extension.
|
Table name |
Filenames |
Contents |
|---|---|---|
|
GENESIS_AUTHS |
GENESIS_AUTHS.ISM |
Object privileges (SELECT, UPDATE, INSERT, DELETE) for privilege-based users |
|
GENESIS_COLUMNS |
GENESIS_COLUMNS.ISM |
Field size, type, and position information |
|
GENESIS_DEPENDS |
GENESIS_DEPENDS.ISM |
SQL view dependency information and information about the names, owners, and database names for the views |
|
GENESIS_DUAL |
GENESIS_DUAL.ISM |
A read-only table with one row and one column for operations such as “SELECT curdate() FROM dual” |
|
GENESIS_FORKEYS |
GENESIS_FORKEYS.ISM |
Foreign key information |
|
GENESIS_INDEXES |
GENESIS_INDEXES.ISM |
Access keys |
|
GENESIS_TABLES |
GENESIS_TABLES.ISM |
File, structure, access level, and tag information |
|
GENESIS_VIEWS |
GENESIS_VIEWS.ISM |
SQL view definitions, which include information such as view name and the query used to create the view |
| GENESIS_USERS | GENESIS_USERS.ISM, GENESIS_USERS.IS1 | Privilege-based user information: user name, password (encrypted with SHA512 encoding scheme), and database privileges (CONNECT, RESOURCE, or DBA) |
|
GENESIS_XCOLUMNS |
GENESIS_XCOLUMNS.ISM |
Column references for access key segments |
|
N/A (does not appear as a table in DBA) |
SODBC_GROUPS.ISM |
Information for groups (for level-based users): 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 |
Level-based user information: username, 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, etc.). 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. They also set connection options. See Setting up access with DSNs for more information.
Environment setup file
The environment setup file is an optional 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, see Setting environment variables in an environment setup file.
xfODBC driver (tod32.dll or tod64.dll)
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 for the data files, the driver then reads the data files and transfers data between the database and the third-party application.
Synergy database driver (vtx4)
The 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
An 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 can also be “ODBC-enabled.”
A Synergy product that enables xfODBC to work in a client/server configuration. Figure 1 in xfODBC requirements and installation illustrates how SQL OpenNet works with xfODBC runtime components to access Synergy data. For more information on SQL OpenNet, including information on SSL encryption (data packet encryption) available with SQL OpenNet, see Configuring Connectivity Series.
