Revision 11 of Database Systems Corp. (DSC) products now supports ODBC on the Windows NT platform. ODBC is the Microsoft standard for accessing all database products such as INFORMIX, ORACLE and SYBASE, as well as numerous PC database products such as dBASE and FoxPro. DSC originally supported directly its FASTPLUS product as well as certain hardware dependent file types. Recently CISAM, ACUCOBOL's VISION, and UNIFY have been added as supported databases. By supporting ODBC, DSC has significantly opened its products to a much wider range of information access.

ODBC is generally tied to the SQL Open Standard. It is a call level interface that bridges application programs or utilities (such as TRANSACT, TRANSVIEW, TELEMATION, etc.) with an independent database server, which communicates with a wide variety of database products. Although SQL is the de facto standard for database technology today, it still has some shortcomings when applied to certain transaction-based applications. Specifically, its row (or record) locking capability is generally left undefined and each database provider has implemented its own method. In some cases, it is actually left up to the application to perform this task. Also, the retrieval of a specific row (or record) involves considerable overhead; thus, the performance appears to lag behind traditional ISAM systems. SQL has been specifically optimized for group or set logic where large numbers or rows are affected by a single query or transaction, and its performance in this area is excellent. In many respects, this is the opposite emphasis of Database System Corp. products, where transactions generally apply to single rows or records.

The benefits to DSC customers of this support are numerous. Data Transport between FASTPLUS and ODBC databases is now seamless. ODBC data can now be accessed in the same manner as FASTPLUS using TRANSACT screens and formats. TRANSVIEW queries and TRANSWRITER reports now directly support ORACLE, SYBASE, FoxPro, etc. Data can be related among various different databases; thus, a FASTPLUS Owner file can be linked to an INFORMIX Member file (table). TELEMATION users can use ODBC databases directly as Customer files, Customer Extensions, Order data, etc.

DEFINITIONS

Prior to describing the ODBC interface to TRANSACT, etc., it is necessary to define certain terms which are used throughout this document.

DATASOURCE - This refers to a specific ODBC database. Each Datasource is supported by one database provider such as ORACLE or INFORMIX. A Datasource is defined to the ODBC driver using Microsoft's driver manager, which is independent of any application program or system. Multiple Datasources may be defined to this driver manager. Prior to accessing data in a Datasource, TRANSACT must first "connect" to the Datasource. TRANSACT may connect to more than one Datasource at a time; thus, TRANSACT may access information from ORACLE, INFORMIX, and SYBASE all at one time.

TABLE - A table is equivalent to an ISAM file. It is a unique collection of information that has common attributes. A Table contains one or more rows (records). Each row contains one or more columns (fields).

ROW - A row is equivalent to an ISAM record. Rows contain columns (fields) of information.

COLUMN - A column is equivalent to an ISAM record field.

INDEX - Any column within a Datasource Table can be defined as an index for optimum access of information. An index can also be comprised of multiple columns from a given Table.

One significant restriction is that the INDEX name must be UNIQUE for a given DATASOURCE. Thus, Tables must be defined such that the index columns have unique names within the DATASOURCE, not just within the Table.

SUPPORTED FUNCTIONALITY

As mentioned earlier, DSC now supports ODBC Tables as another file type within its product line. This is accomplished by translating internal TRANSACT DML functions and calls to SQL statements which are passed to the ODBC driver for execution.

a. Database Definition

The first step is to define an existing ODBC Datasource definition to TRANSACT. This is accomplished using the TRANSACT file definition. Likewise, an existing TRANSACT file definition (or a new definition) can be used to create a new Table within an existing ODBC Datasource. Effectively, the TRANSACT dictionary and the ODBC Datasource definition are synchronized. If a new Table is being created within the ODBC Datasource, TRANSACT builds a CREATE TABLE SQL statement from its own dictionary definition and submits this command to the ODBC driver for execution. When complete, the Table is accessible to not only TRANSACT but to any other application or utility that supports ODBC. An added benefit is that, if the Datasource is maintained on a system that supports other applications, the Table can be accessed even without ODBC.

An ODBC Table is equivalent to a TRANSACT File. Therefore, the common definition between ODBC and TRANSACT is the file definition within TRANSACT. Although ODBC maintains tables within a database, TRANSACT does not support the database definition. Instead, TRANSACT supports a large collection of files within its dictionary that may be defined in a number of Datasources.

Certain restrictions may apply when creating a TRANSACT Dictionary definition for an existing ODBC Table. TRANSACT does not support BLOBS, or large binary objects. Likewise, TRANSACT only supports character fields with a length of 70 characters. TRANSACT will break up a large fixed length character type column fields into multiple, 70 character fields. TRANSACT does not support large variable length character or binary columns.

b. Opening A Table

An ODBC Table is opened using the standard SELECT function within the TRANSACT DML. Each table is opened individually and is treated as a separate file within TRANSACT. TRANSACT first connects to the appropriate Datasource (if not already connected) and synchronizes its dictionary field definitions with ODBC's Table definition. Since ODBC data may be represented internally in a form different than TRANSACT, TRANSACT may need to translate individual fields as rows are fetched from the Table. This is important when considering the individual programming language requirements of the TRANSACT application. The overhead of connecting to a Datasource is considerable; thus, the first time TRANSACT issues a Connect statement to ODBC to a particular Datasource will require several seconds.

c. Data Query and Access

The heart of TRANSACT's support of ODBC is its ability to access information within a Datasource. The full realm of TRANSACT's search criteria is supported within ODBC. TRANSACT builds an SQL SELECT statement using any and all of the search criteria established using the TRANSACT DML FIND commands. The following table illustrates the TRANSACT FIND options and the equivalent ODBC SQL WHERE clause relationships:

TRANSACT FIND CRITERIA	ODBC WHERE CLAUSE EQUIVALENT
FINDEQ	<Column> = <Value>
FINDNE	<Column> NOT = <Value>
FINDGT	<Column> > <Value>
FINDGE	<Column> >= <Value>
FIND LT	<Column> < <Value>
FIND LE	<Column> <= <Value>
FINDP	<Column> LIKE <Value%>
FINDNP	<Column> NOT LIKE <Value%>
FINDCN	<Column> LIKE <%Value%>
FINDNC	<Column NOT LIKE <%Value%>

TRANSACT constructs the SQL SELECT statement and submits it to the ODBC Datasource driver for execution. The driver executes the statement and returns to TRANSACT. TRANSACT, using the GET and GETN commands, builds an SQL FETCH statement that retrieves one row at a time from the ODBC driver. As each row is retrieved, the columns are translated into a form usable by TRANSACT and the application. Columns are placed in fixed record positions as defined by the TRANSACT dictionary. Thus, the ODBC Row information appears to the application as if it were in a fixed length record (like most ISAM systems).

Complex search criteria is handled much more effectively by ODBC (and SQL) than by an ISAM system. If the Datasource is located on a remote system, the row qualification is handled much more efficiently by the remote server than by the application client.

d. ROWIDs and UNIQUE Indexes

It is appropriate here to describe another feature of ODBC that is not standard among all Datasources and drivers. This is the concept of a unique Rowid or Primary Key. Some Datasources require that each row within a Table must be uniquely identifiable via a Primary Key. In fact, the SQL standard requires this unique key concept. However, not all SQL Database providers support or require this. On the other hand, certain Database providers such as ORACLE, INFORMIX, and INGRESS, to name a few, do support the concept of a ROWID, which is a unique identifier for each row within a table. DSC requires that any file type supported within its dictionary have some type of unique record identification, whether by unique record number (FASTPLUS, CISAM, etc.) or a unique primary key (UNIFY, etc.). Since ODBC does not require that each Database Table have this unique key or ROWID, some Tables defined to ODBC may not be fully supported within TRANSACT. When ODBC returns a Table row during the SQL FETCH operation, TRANSACT retrieves the rows unique identifier and saves it for later reference to this particular row. This value is what is passed to the application in the RECNO field. Thus the method of identifying a row (record) to be updated, deleted, or just re-accessed is via the RECNO. If a Datasource Table does not contain such a unique identifier, then TRANSACT cannot successfully return to this row (record) with one of these functions.

e. Inserting New Rows (Records)

A new row can be inserted into an ODBC Table using the TRANSACT ADD command. This is a fairly simple process. TRANSACT accepts a fixed length record passed to it via the ADD command and builds an ODBC SQL INSERT statement. This statement contains a column name and value for each field defined in the TRANSACT dictionary. The statement is passed to the ODBC driver for execution. If successful, TRANSACT returns the success code to the application program. This function may fail at the driver level if the INSERT attempts to add column values which are inconsistent with the Datasource Table declaration (Range check, Non Unique Index, etc.). If this occurs, TRANSACT returns an unsuccessful return code to the application.

f. Deleting Rows

A row within an ODBC Table can likewise be deleted using the TRANSACT DELETE command. TRANSACT accepts the unique row identifier from the application program and constructs an ODBC SQL DELETE statement. The statement is passed to the ODBC driver for execution and TRANSACT returns the success/failure code to the application. This statement can only fail for security reasons or if the row within the table no longer exists.

g. Updating Rows

TRANSACT can update an ODBC Table using its standard CHANGE function. One distinction must be described, however. Within ISAM file types, TRANSACT requires that the record must be first locked using LGET or LRETRV prior to the CHANGE routine execution. The lock function is not supported within ODBC and, thus, a successful call to lock a record need not be executed prior to the CHANGE. ODBC does ensure that the UPDATE SQL command preserves data integrity at the point of its execution.

To perform the Update function, TRANSACT internally builds an SQL command (UPDATE) that modifies any columns that are changed within the application. It submits this UPDATE SQL command to the ODBC driver for execution. If successful, the CHANGE function returns a code informing the application of this success. Possible errors could be the prior deletion of this row or the fact that the data columns being modified did not conform to the Datasource Table definition qualifiers (Range check, data type, etc.).

TABLE (FILE) DEFINITION

To access and update information from an ODBC Datasource, TRANSACT and ODBC must first synchronize dictionaries. This is accomplished both ways - TRANSACT to ODBC and ODBC to TRANSACT. An existing ODBC Datasource Table can be defined under TRANSACT, and TRANSACT can issue a CREATE TABLE SQL command to define a new ODBC Datasource Table. When creating a TRANSACT dictionary file from ODBC, some restrictions apply. Not all column data types translate readily to TRANSACT. Even within ODBC, certain Datasource drivers do not support compatible column data types (or even naming conventions for like data types). TRANSACT does not support BLOBS (large binary blocks of data) or large variable length text fields.

Some Datasources are case sensitive. Therefore, when identifying Tables by name, you must be aware of this condition and enter the Table Name as it appears in the Datasource Directory. Also be aware of Tables and Datasource drivers that do not have unique ROWIDS or primary keys and the implication of supporting these tables under TRANSACT.

a. Creating a File from an ODBC Datasource Table

To build a TRANSACT file definition from an ODBC Datasource Table, use the File Definition function within any DSC product. A new auto build option (S) has been added allowing you to query the ODBC driver for all defined Datasources. For each Datasource, TRANSACT allows you to display all of the available Tables.

From the File Definition Menu, enter option 2 (Add New File), a unique 12 character filename, and enter "S" under the Automatic Build Source option. TRANSACT will display a window that allows you to enter the Datasource Name and Table Name which identify the appropriate ODBC Table.

ODBC DataSource Definition Screen

Press the F4 Function Key which displays all defined ODBC Datasources. When displayed, you can move the cursor through each of the displayed Datasources and, when the Enter key is pressed, the Datasource will automatically be returned to the above window. If a valid Datasource name is entered on this screen, you can press Function Key F5 to view all defined Tables within the Datasource. Likewise, a Table can be selected by moving the cursor through the displayed Tables and depressing the Enter key.

When building a TRANSACT dictionary from ODBC, you can automatically upcase the column names by entering "Y" on the above screen at the prompt "Upcase Fields?" By default, TRANSACT field names will appear in whatever case the ODBC Table definition has them defined. Upcasing field names is very useful, particularly within TRANSVIEW and TRANSWRITER. Some Datasource drivers require a login name and optional password. Enter these on the above screen.

When all of the above information has been properly entered, press Enter to create the new TRANSACT file definition. If the information has been entered correctly, a new file will be defined to TRANSACT.

b. Creating an ODBC Table From a TRANSACT File Definition

To build an ODBC Table from a TRANSACT File Definition, use the same File Definition Menu within any DSC product. If the file definition already exists, use Function 3 (Change Existing File) and enter the file definition name. A second screen will appear displaying the file pathname, file type, record size, and the Create File option. Change the file type to "S" and enter "Y" at the prompt "Create the File".

TRANSACT will then enter the File Field Definition Screen allowing you to change any of the dictionary fields. When finished, TRANSACT will prompt you with the same ODBC control screen that appears above. Enter the appropriate Table Name that you wish to create and the Datasource that will contain this Table. Be sure that any fields defined as indexes have unique names and are not already defined in another table within this Datasource.

RESTRICTIONS

The following restrictions are known as of the date of this document.

• ODBC is currently supported only on the WINDOWS NT version of DSC Products. It can reference Datasources on any WINDOWS or UNIX system that is defined to the WINDOWS ODBC driver. ODBC is not supported directly on UNIX.

• Field Names are restricted in length to 12 characters. Longer SQL column names are truncated to 12 characters and duplicates are given unique names.

• BLOBS and variable length text data are not supported. Certain Date types unique to each Datasource are not supported.

• Datasource Tables that do not support ROWID or are not defined with a unique index or primary key behave poorly when defined to TRANSACT. The proper connection between the DML calls such as GET and UPDATE is not present.

• ODBC Transaction Processing Control is not directly support. Instead TRANSACT’s Transaction control is applied.

REQUIREMENTS

In order to access any Datasource defined under ODBC, you must first obtain the ODBC Driver and software from Microsoft. Once this is installed, TRANSACT and other Database products must be loaded with the actual ODBC library calls maintained in the object file odbc_subs. This object is supplied with DSC products but is not loaded. Instead the object file odbc_dummy is the default library object that satisfies the ODBC references. On WINDOWS NT, the ODBC library references are satisfied by including the Microsoft library odbc32.lib.

To reference any particular Datasource provided by database developers such as ORACLE, INFORMIX, dBASE, etc. requires that you license from these providers their software, both on the remote server as well as the ODBC driver unique to the client. Microsoft provides its ODBC drivers in the Microsoft Office software.

FUTURE DEVELOPMENT

DSC intends to support ODBC from the UNIX client side. The ODBC drivers and control program are not available through Microsoft, but are provided through another software provider. DSC is also developing an ESQL interface within its products which may replace ODBC on UNIX. The ESQL interface will be specific to particular database products such as INFORMIX and ORACLE and would not be as universal as ODBC.