ODBC Drivers and Database Access Utility

This chapter contains information about ODBC drivers and the Database Access utility.

You can find additional information in the online help.

Overview of Open Database Connectivity (ODBC)

ODBC is an industry standard application program interface (API) that provides a solution to a common problem--how to write programs that access a Database Management System (DBMS) without making them dependent on specific DBMS designs. ODBC provides API access to SQL-accessible databases, allowing application programs to use SQL to access data from many kinds of sources. The independence of the access method from the original design of the database means that developers can design and produce a single version of an application that can work with, for example, Oracle or Sybase data, without requiring a separate version for each DBMS. Users simply add database drivers to link the application to their choice of DBMS.

ODBC is a component of the Windows® Open Services Architecture (WOSA). Personal Communications supports ODBC 2.0, for development of 32-bit applications for Windows XP.

An ODBC driver provides an application program with standard and uniform SQL access to a DBMS database. An ODBC driver allows the designers and users of the application program to ignore the SQL differences between various DBMS systems.

Figure 1 shows an overview of the Personal Communications Database Access utility and the ODBC DB2® drivers.

Figure 1. Database Access Overview

Components of ODBC

There are four components in the ODBC architecture:

• The application component defines and performs the work to be accomplished. It calls the ODBC API functions that submit SQL statements and retrieve results.
• The driver manager component loads drivers on behalf of an application.
• The driver component processes ODBC function calls, submits SQL requests to a specific data source, and passes results to the application. The driver transforms the application's SQL syntax into the syntax expected by the driver's associated DBMS.
• The data source component is the data to be accessed and the means for doing so, including the associated operating system, DBMS and network platform.

From the application program's perspective, the driver manager and the driver appear to be a single unit for processing ODBC function calls. Figure 2 shows the relationship between the four components:

Figure 2. ODBC Components

Application Component

An application using ODBC performs the following tasks:

• Requests a session (connection) with a data source.
• Sends SQL requests to the data source.
• Defines storage areas and data formats for the results of the SQL requests.
• Requests the results of the defined query.
• Processes errors.
• Requests commit or rollback operations for transaction control.
• Terminates the session with the data source.

Any program that accesses a DBMS, such as a spreadsheet, a mail server, or a report generator, can be designed to use ODBC.

Driver Manager

The driver manager is a dynamic link library (DLL), the purpose of which is to load drivers. It also performs the following functions:

• Maps a data source name to a specific driver DLL.
• Processes ODBC initialization calls.
• Provides the entry points to the ODBC functions for each driver.
• Provides parameter and sequence validation for ODBC calls.

Driver

A driver is a DLL that implements ODBC function calls and interacts with a data source.

The driver manager loads a driver when the application calls the SQLBrowseConnect, SQLConnect, or SQLDriverConnect function.

A driver performs the following tasks in response to ODBC function calls from an application:

• Establishes a connection to a data source.
• Submits requests to the data source.
• Translates data to or from other formats, if requested by the application.
• Returns results to the application.
• Formats errors into standard error codes and returns them to the application.
• Declares and manipulates cursors if necessary. This operation is invisible to the application unless there is a request for access to a cursor name.
• Initiates transactions of the data source. This operation is invisible to the application.

Data Source

A data source is an instance of a DBMS product and any remote operating system and the protocol required to access it.

Database Access

Database Access lets you access host or PC databases and retrieve data, using ODBC drivers. If you specify the conditions for accessing data in each window, the desired query can be created automatically. See the online help for extensive information on using the Database Access utility with the Personal Communications ODBC drivers.

From Database Access, you can perform any of the following actions:

• You can see and modify the SQL statements that are created automatically.
• You can see available database table lists.
• You can copy spreadsheet data to the clipboard
• You can show data in the grid in the main window spreadsheet, send data to other applications (for example Lotus® 1-2-3® or Microsoft® Excel), or send data to a printer.
• If you want to know the meaning of items in the windows, you can see an explanation when you click on the item and then click help.

Accessing an ODBC Data Source

To access an ODBC data source, do one of the following:

• Click on the Data Source button on the toolbar.
  
• Select Data Source from the query pull-down menu.

The SQL Data Source window appears.

Select the data source you want to access; then click OK.

Selecting Database Tables

To select database tables, do one of the following:

• Click on the Tables button on the toolbar.
  
• Select Tables from the Query pull-down.

The Tables window appears.

Joining Conditions for Multiple Tables

To join conditions for multiple tables, do one of the following:

• Click on the Join Conditions button on the toolbar.
  
• Select Join from the Query pull-down menu.

The Join By window appears.

Selecting Database Columns

To select database columns, do one of the following:

• Click on the Columns button on the toolbar.
  
• Select Columns from the Query pull-down menu.

The Columns window appears.

Selecting Query Conditions

To select query conditions for the data, do one of the following:

• Click on the Query Conditions button on the toolbar.
  
• Select Query Conditions from the Query pull-down menu.

The Query Conditions window appears.

Selecting Sort Conditions

To select sort conditions for each column, do one of the following:

• Click on the Order button on the toolbar.
  
• Select Order from the Query pull-down menu.

The Order By window appears.

Viewing the Current SQL Statement

To view the current SQL statement, do one of the following:

• Click on the SQL Statement button on the toolbar.
  
• Select SQL Statement from the Query pull-down menu.
• Click the SQL button in any window selected from the Query pull-down menu.

The SQL Statement window appears.

Database Size Limit

When you use Database Access, the maximum number of each value that can be displayed in the spreadsheet is as follows:

When you use another application (for example Lotus 1-2-3), refer to the manual for that application because the limit might be different.