Introduction to FairCom's ODBC

This document introduces ODBC, the FairCom DB SQL ODBC driver, and how they work together to provide access from a wealth of desktop tools to FairCom DB SQL environments:

• A FairCom DB SQL environment is an SQL interface over c-tree.
• ODBC: Microsoft’s Open Data Base Connectivity has become a widely accepted database access standard on the desktop.
• Desktop tools and applications that support ODBC can access any data source supported by an ODBC driver. These tools include client/server development, query, and personal productivity tools.

In V11 and later, FairCom DB SQL ODBC drivers for Unix are available on AIX and Solaris (Sparc) platforms.

Overview of ODBC

The Open Database Connectivity (ODBC) interface from Microsoft has emerged as the standard mechanism for client applications to access data from a variety of different sources through a single interface. Users of applications supporting ODBC merely select a new database from a point-and-click menu to connect transparently to that data source.

To become accessible from ODBC client applications, database environments must provide software, called a driver, on the client system where the application resides. The driver translates the standard ODBC function calls into calls the data source can process, and returns data to the application. Each data source provides a driver on the client system for applications to use to access data from that source.

The FairCom ODBC Driver extends this plug-and-play interoperability to the FairCom Database Engine. It allows any Microsoft Windows tool or application that supports the ODBC call library to easily use c-tree as a data source. With it, applications based on tools such as Visual Basic can include c-tree as a data source.

The ODBC interface specifies two major components:

• A library of function calls that allow applications to connect with a database system and issue statements through an application programming interface (API)
• Syntax for Structured Query Language (SQL) statements, based on existing standards

ODBC drivers fit in as a layer of “middleware” in the ODBC architecture. The ODBC architecture includes the following layers:

Application

An ODBC application is any program that calls ODBC functions and uses them to issue SQL statements. For example, many vendors have added ODBC support to their existing Windows-based tools, such as PowerBuilder™ and Impromptu^®, so those tools can use ODBC for data access.

ODBC Driver Manager

A Microsoft-supplied dynamic-link library (DLL) that routes calls from an application to the appropriate ODBC driver for a data source. An application sees the ODBC driver manager and a driver as a single entity that processes requests to a particular data source. The ODBC driver manager loads the requested driver in response to an application’s call to the ODBC SQLConnect() or SQLDriverConnect() functions.

ODBC Driver

A dynamic link library (DLL) that processes ODBC function calls for a specific data source. The driver connects to the data source and translates the standard SQL statements into syntax the data source can process. It also returns any requested data to the application. There are ODBC drivers for every major database system.

Data Source

The combination of a database system, the operating system it uses, and any network software required to access it. (ODBC defines a database system (DBMS) as any vendor’s implementation of a data access system that provides an SQL interface.)

The following figure shows the components involved in a typical ODBC environment.

Quick Tour

FairCom provides a set of tutorials showing the major components of our core technology. See the ReadMe file for the latest version of these quick tutorials: SQL ODBC ReadMe.

Introductory Tutorial

<faircom>\drivers\sql.odbc\tutorials\ODBCTutorial1.c

This tutorial will take you through the basic use of the FairCom DB SQL ODBC Interface.

As with all other examples in the c-tree tutorial series, this tutorial simplifies the creation and use of a database into four simple steps: Initialize(), Define(), Manage(), and You’re Done() !

Tutorial #1: Introductory - Simple Single Table

We wanted to keep this program as simple as possible. This program does the following:

• Initialize() - Connects to the FairCom Database Engine.
• Define() - Defines and creates a "customer master" (custmast) table/file.
• Manage() - Adds a few rows/records; Reads the rows/records back from the database; displays the column/field content; and then deletes the rows/records.
• Done() - Disconnects from FairCom Database Engine.

Note our simple Main() function:

/*

* main()

*

* The main() function implements the concept of "init, define, manage

* and you're done..."

*/

int main(int argc, char* argv[])

{

Initialize();

Define();

Manage();

Done();

printf("\nPress <ENTER> key to exit . . .\n");

getchar();

return(0);

}

We suggest opening the source code with your own editor.

Init

First we need to open a connection to a database by providing the FairCom Database Engine with a user name, password and the database name.

Below is the code for Initialize():

/*

* Initialize()

*

* Perform the minimum requirement of logging onto the c-tree Server

*/

void Initialize(void)

{

RETCODE rc;

printf("INIT\n");

/* allocate environment handle */

if ((rc = SQLAllocEnv(&hEnv)) != SQL_SUCCESS)

Handle_Error(0, NULL, "SQLAllocEnv()");

/* allocate connection handle */

if ((rc = SQLAllocConnect(hEnv, &hDbc)) != SQL_SUCCESS)

Handle_Error(SQL_HANDLE_ENV, hEnv, "SQLAllocConnect()");

/* connect to server */

printf("\tLogon to server...\n");

if ((rc = SQLConnect(hDbc, MY_DSN, SQL_NTS, "admin", SQL_NTS, "ADMIN", SQL_NTS)) != SQL_SUCCESS)

Handle_Error(SQL_HANDLE_DBC, hDbc, "SQLConnect()");

/* allocate statement handle */

if ((rc = SQLAllocHandle(SQL_HANDLE_STMT, hDbc, &hStmt)) != SQL_SUCCESS)

Handle_Error(SQL_HANDLE_DBC, hDbc, "SQLAllocHandle(SQL_HANDLE_STMT)");

Define

Define() establishes specific data definitions. This involves defining columns/fields and creating the tables/files with optional indexes.

Below is the code for Define():

/*

* Define()

*

* Create the table for containing a list of existing customers

*/

void Define(void)

{

RETCODE rc;

printf("DEFINE\n");

/* create table */

printf("\tCreate table...\n");

if ((rc = SQLExecDirect(hStmt,

"CREATE TABLE custmast ( \

cm_custnumb CHAR(4), \

cm_custzipc CHAR(9), \

cm_custstat CHAR(2), \

cm_custrtng CHAR(1), \

cm_custname VARCHAR(47), \

cm_custaddr VARCHAR(47), \

cm_custcity VARCHAR(47))",

SQL_NTS)) != SQL_SUCCESS)

Handle_Error(SQL_HANDLE_STMT, hStmt, "SQLExecDirect(CREATE TABLE)");

}

Done

When an application and/or process has completed operations with the database, it must release resources by closing the open files and disconnecting from the database engine.

Below is the code for Done():

/*

* Done()

*

* This function handles the housekeeping of closing connection and

* freeing of associated memory

*/

void Done(void)

{

RETCODE rc;

printf("DONE\n");

/* free statement handle */

if ((rc = SQLFreeHandle(SQL_HANDLE_STMT, hStmt)) != SQL_SUCCESS)

Handle_Error(SQL_HANDLE_DBC, hDbc, "SQLFreeHandle(SQL_HANDLE_STMT)");

/* disconnect from server */

printf("\tLogout...\n");

if ((rc = SQLDisconnect(hDbc)) != SQL_SUCCESS)

Handle_Error(SQL_HANDLE_DBC, hDbc, "SQLDisconnect()");

/* free connection handle */

if ((rc = SQLFreeHandle(SQL_HANDLE_DBC, hDbc)) != SQL_SUCCESS)

Handle_Error(SQL_HANDLE_ENV, hEnv, "SQLFreeHandle(SQL_HANDLE_DBC)");

/* free environment handle */

if ((rc = SQLFreeHandle(SQL_HANDLE_ENV, hEnv)) != SQL_SUCCESS)

Handle_Error(0, NULL, "SQLFreeHandle(SQL_HANDLE_ENV)");

}

Additional Resources

We encourage you to explore the additional resources listed here:

• Complete source code for this tutorial can be found in ODBCTutorial1.c in your installation directory, within the <faircom>\drivers\sql.odbc\tutorials directory for your platform.
• Additional documentation may be found on the FairCom Web site at: www.faircom.com
• This book contains all of the ODBC Driver documentation. Additional documentation for other APIs and administering the FairCom DB Database Engine may be found in the Documentation section of the FairCom website.