OS/2 eZine

16 May 2001
 

Douglas Clark is a program management consultant who first started using OS/2 version 1.3. He's married, with 2 girls, and is old enough to remember when 4 color mainframe terminals were a big thing.

If you have a comment about the content of this article, please feel free to vent in the OS/2 eZine discussion forums.


Previous Article
Home
 Next Article



Mesa 2 Spreadsheet www.sundialsystems.com

ODBC on OS/2 Part 1

Many years ago, in the precambrian era of computing, databases were big monolithic animals. Each database had its own Application Programming Interface (API), its own interface methods for programs (and users) to put data into the database and for getting data out of the database. No database exchanged data with any other database - at least not willingly or easily. Applications and programs written for one database would not work on another database. Then two things happened that radically changed the database world and brought databases into the modern computing era: SQL and ODBC.

SQL is a language for interacting with databases, it defines the language statements used to create tables for storing data, and statements to input, update, delete and retrieve data from databases. SQL was developed by IBM researchers in the 1970's from a prototype that was named SEQUEL, which was an acronym for Structured English Query Language. The language is built on some database concepts that were espoused by Dr. E. H. Codd in various publications in the 1970's and finally simplified in his famous 12 rules in 1985. The SQL language defines the language statements used to create tables for storing data, and statements to input, update, delete and retrieve data from databases. Before SQL, each database had its own language for manipulating the database. After SQL databases appeared, a programmer or database administrator could use the same language to manipulate databases from different vendors.

But SQL by itself was not enough. The problem still remained of how to get those SQL statements to the database within some programming language, and how that programming language interacted with the data returned by some of the SQL statements. These problems are addressed by ODBC.

ODBC (Open Database Connectivity) is a Microsoft API for programs that interface to databases. It defines C language functions for processing SQL statements and for other tasks involved in interfacing to databases. With ODBC a single program could now be written that would interface with multiple databases. Instead of having to write code specific to each database, a programmer could code to the ODBC API and his application would work with any database that supports ODBC.

ODBC as a standard might not have been enough to change the database world; after all there were other database APIs, such as Borland Database Engine and Q+E Software's database library, which were designed to accomplish the same thing. But the marketing muscle of Microsoft caused ODBC to become the predominate standard - and it caused the database vendors to build and release ODBC drivers that made their databases ODBC compliant. Most databases now have ODBC drivers available; the exceptions seem mainly to be those databases which came from Unix

This article is about ODBC: what it is, how to install it, how to use it, and how to write programs using ODBC. It is divided into 5 parts:

  1. ODBC for Users - describes what ODBC is and how it works, and how to use it.
  2. Installing ODBC - covers installing the main ODBC files, where to get those files, and how to have multiple versions running at the same time.
  3. ODBC Drivers and Databases - where we look at each ODBC enabled database, both client or server, available for OS/2; how to install the ODBC driver, where to get the driver and/or database or client. My hope is to cover every ODBC driver that exists or existed for OS/2 - if you know of some obscure driver check the list to see if it is covered and email me.
  4. ODBC for Programmers - describes what you need to write applications that access data through ODBC.
  5. Wrap-up - discusses the role of ODBC, examine a few of possible scenarios for the future of ODBC on OS/2, lists some ODBC tools and accessories and where to get drivers, etc.

The first three parts are aimed at regular users - those that want to use ODBC applications; part 4 is aimed at programmers who want to create ODBC applications or just to access data more conveniently than before. And although this article is about ODBC, ODBC is worthless without a database to talk to, so in part 3 (ODBC Databases and Drivers) we will discuss most of the ODBC enabled databases that run as servers on OS/2, or that OS/2 users are concerned with as clients, and how to connect to those databases.

* Like everything in life there are exceptions - some ODBC drivers, for example Intersolv dBase & Text drivers, don't need a database to talk to because they are the database.

Part 1 - ODBC for Users

In this part we talk about what ODBC is and does, how it works, and how you use ODBC to access data.

ODBC comes in a number of versions, with version 2.5 being the most common. In addition ODBC can come from a number of sources:

  • Visigenic - which is now owned by Borland.
  • InterSolv - who changed their name to Merant.
  • Unix - in the form of iODBC, an open-source ODBC driver manager.

The Visigenic and Intersolv distributions are mostly interchangeable and the rest of this document will discuss Visigenic version 2.5 unless otherwise specified. The iODBC distribution will not work interchangeably with most OS/2 ODBC drivers or applications. See Part 2 (Installing ODBC) where we discuss the different versions and how to install both, if needed.

Before we talk about ODBC we should first talk a little about databases. A database is an application that stores and retrieves data. Sometimes database vendors provide additional functions like data backup and restore functions, or in the case of Microsoft Access, report and screen building functions, but for the purposes of this article we will concentrate on the storing and retrieving data.

Databases come in two basic flavors:

  • Local, or sometimes called "desktop" - designed to be accessed (only) on the machine where the database resides. Examples are dBase, Paradox, and Approach.
  • Client/server - where the database resides on one machine and the users of the database are on client machines. The machine the database is on is called the database server, and may or may not be the same machine as the network server. The operating system used on the database server and the client machines may or may not be the same.

(Some databases, like Watcom SQL/Sybase SQL Anywhere, function both ways: as a local database the application talks directly to the database engine, as a client/server the application talks to a client.)

Applications that run against local databases generally make calls directly into the database engine, while applications that run against client/server databases make calls into a database "client" which handles the communication to the database engine on the server machine. This "client" is officially called a Client Application Enabler (CAE). The figure below shows both varieties.

What is ODBC

ODBC is an interface standard between applications and databases - specifically between an application and a database client. It provides a consistent interface - a consistent set of functions - across multiple databases. This allows a single application to work against multiple databases without having to be rewritten or recompiled. For the programmer this means only having to write to one programming interface standard that will work against multiple databases. For the user this means that an ODBC application - such a spreadsheet, database editor or report writer - will work with any database that has an ODBC driver.

This is accomplished by ODBC defining common terms, data, and concepts that apply to all databases. It then defines functions and specific C language data types for working with those terms, data and concepts. The translation between this common-to-all database world and a specific database is handled by a database driver. The application (and programmer) only sees and works with the ODBC world.

To use an analogy, you can think of ODBC as an "operating system" for databases. For example applications in OS/2 can access files on a variety of storage devices and file systems - FAT, HPFS, JFS, CD-ROM, NFS, FTP to name a few - without the application really knowing or caring what type of file system or device the file came from. When you open a file or save to a file in an application for the most part you really can't tell what type of file system or device you are looking at. This is because the operating system hides the differences between the file systems and devices by providing a common interface to programs for working with files. Likewise with ODBC - it hides the differences between databases by providing a common interface to programs and users for working with databases.

Without ODBC, an application written to run against a database must be written specifically for that database, since each database has its own interface standards and protocols. If the application is to run against multiple databases, the part that talks to the database must be written for each database the application will run against.

So ODBC is a software appliance that provides a common interface for applications to use when accessing databases.

How it Works

ODBC is made up of three components:

  1. Database Drivers - handle the translation between ODBC's API and each specific database. Each database requires its own Database Driver. Database Drivers are usually supplied by the database manufacturer, but can also be purchased from third party vendors.
  2. The Driver Manager - provides the ODBC interface to an application and is responsible for loading the appropriate database driver needed by the application.
  3. The Administrator - provides a graphical user interface for creating and configuring data sources, i.e. setting up database drivers so they can be used by applications.

When an application connects to a database the driver manager finds and loads the appropriate driver manager for that database. The application talks to the Driver Manager; the Driver Manager talks to the Database Driver; the Database Driver talks to the database - or the database client, whichever the case may be.

However to be entirely correct an application does not connect to a database, it actually connects to something called a Data Source Name or DSN. A DSN consists of a database driver, together with any parameters that are needed, and a user provided name. You create DSNs with the ODBC Administrator by picking a Database Driver and filling in the parameters that are needed.When the application connects to the DSN to Driver Manager looks up which Database Driver is used for that DSN and loads that Database Driver.

To go back to our operating system/file system analogy. Each different file system recognized by OS/2 is handled by an "Installable File System", or IFS. The IFS handles the translations needed for the operating system to talk to a specific file system. Each different IFS used by OS/2 is specified in the CONFIG.SYS file and is loaded by the operating system when OS/2 boots up. For instance on my machine I have 5 IFS statements or file systems: one for HPFS, one for CD-ROMs, one that handles network drives, one to handle NFS (Unix) type drives, and one that makes FTP servers look like a drive. Think of the Installable File Systems as Database Drivers. But I don't work with file systems on OS/2; I work with drive letters, e.g. I access files on drive C: or drive F:, etc. The drives or drive letters, are an abstraction of the file system to me and applications on OS/2. When OS/2 boots up it loads each file system and assigns drive letters as appropriate. Just as in ODBC you don't work with Database Driver, but rather with DSNs.

In order for ODBC to access data a "data source" must be defined. A data source is a Database Driver, along with any required parameters, which is given a name; hence data sources are usually called Data Source Names or DSN.

ODBC uses 2 files where DSN and Database Driver information are kept:

  • ODBCINST.INI - stores information about each ODBC Database Driver installed.
  • ODBC.INI - stores information about each DSN that has been created.

On OS/2 these are standard INI type "profile" files, meaning the values are stored in binary. On Windows 3.1 and Unix they are text files; on Win 95/NT the information is stored in the registry.

ODBCINST.INI

ODBCINST.INI is a binary file which you can edit with any number of INI file editors. The file a 3 major sections, plus a section for each Database Driver and Translator. The sections are:

  1. ODBC Core - one entry containing a usage count key.
  2. ODBC Translators - this section has an entry for each translator DLL that can be used by ODBC. The translators are used for translating from one code page to another. Normally there is only one translator installed - OEM to ANSI.
  3. ODBC Drivers - this section has an entry for each Database Driver installed.
  4. Drivers/translators - each Database Driver and translator installed has a section in the file. The section for a Database Driver contains parameters and information that driver needs to operate. At a minimum there are two entries: one for Driver and one for Setup, each of which lists the DLL path and name of the DLL used for the driver or to run the setup screen. Most drivers include the setup routines in the driver DLL so the same name appears in both entries.

When each ODBC driver is installed its installation routine makes

  • an entry in the ODBC Drivers major section (called an application section in OS/2) saying the driver is installed,
  • adds a major (application) section for the driver, with minor (key/value pairs) entries for the driver's parameters.

The figure below shows the ODBCINST.INI file from Win16 - because it is easier to see the file structure in a text file.

The figure below shows a section of the OS/2 ODBCINST.INI as it is edited in the application INIE. Notice that each key in the ODBC Drivers section also appears as its own "application" or major section.

The figure below shows the keys associated with a driver's application section.

ODBC.INI

The ODBC.INI file is also a binary *.INI type file on OS/2, and a text file on WinOS/2. This file lists all the Data Source Names that you have created on the machine. It has one section titled ODBC Data Sources which lists all the Data Sources that have created on the machine, and then one section for each Data Source. The individual Data Source sections have entries for parameters that Data Source needs to keep track of. What those parameters are and contain are entirely dependent on the Data Source.

The figure below shows an example of an OS/2 ODBC.INI.

Normally the ODBC Administrator uses the ODBCINST.INI file to build the ODBC.INI file and the Driver Manager and drivers use the ODBC.INI file. It is possible to run ODBC applications without an Administrator, provided you build and maintain the files some other way. Since OS/2 has no standard distribution for the Administrator, some vendors include the Administrator with their ODBC drivers (e.g. DB2), some provide routines for adding their driver's information to the files without an administrator (e.g. Sybase SQL Anywhere). It should even be possible to run ODBC without the ODBCINST.INI file, if you have some other way of creating the ODBC.INI file; however some drivers - mostly InterSolv - use the ODBCINST.INI file in addition to the ODBC.INI file, so you are safest having both files.

Both files appear to be very sensitive to case (upper and lower) for the text, for the application names, keys and values - although that also depends to some extent on the driver.

How You Use ODBC

In order to use ODBC you must first create a DSN.

Data Sources (DSNs) are created with the ODBC Administrator. This is a little GUI application that you use to create a data source by picking a driver and filling parameters in one or more dialog boxes. In the figure below you can see 5 data sources that have already been created: one for Sybase SQL Anywhere, one for dBase, one for DB2, and two for Oracle - one on an OS/2 server and one on an NT server.

Please note: the data source name is case sensitive on OS/2. Using the wrong case when connecting to a DSN will result in unpredictable behavior.

To create a new Data Source you click on the Add button. This brings up a list of all available/installed Database Drivers. Pick a Database Driver and click on OK.

This brings up the setup screen for that Database Driver; the figure below shows the setup screen for MS SQL Server. You enter the name of the Data Source, and any other required parameters. For MS SQL Server three parameters are required and the rest are optional.

Once you have a Data Source created you can use that Data Source in an application. Within the application you generally have to connect to the Data Source before any data is available. The figure below shows a connection screen for connecting to a Data Source from the ODBC Command Processor application.

And how is the application after the connection is made, in this case to PostgreSQL.

The next series of figures show one sequence you can use to connect to ODBC Data Sources from Lotus Approach, a database application. In the below figure the File - Open dialog box is shown with ODBC selected from the File Type drop down list. On the left side are listed the ODBC Data Sources installed.

Selecting a Data Source on the left side and clicking the Connect button causes a list of tables from the Data Source/database to be displayed.

Selecting a table and pressing OK brings up the Approach main screen.


[Click image to view full size.]

Summary

Open Database Connectivity, or ODBC, is a standard designed by Microsoft to allow inter-operability among applications and databases. Despite being a Microsoft standard, or perhaps because of it, ODBC has migrated to almost all the operating systems currently in existence - including OS/2. Next time we'll look at installing ODBC on your OS/2 system.

Previous Article
Home
 Next Article

Copyright (C) 2001. All Rights Reserved.