Chapter 7
IN THIS CHAPTER
Connecting through a remote driver
Connecting through ODBC
Seeing what really happens when you make a connection
With a stand-alone desktop database system, communication is never an issue. The data-driven application you write has only one place to go for data: the database on your hard disk. Your desktop database management system (DBMS) provides the interface between your application code and the database. This simple situation, once very common, has largely been replaced by client/server database systems that reside on a local area network (LAN) or wide area network (WAN), or by Cloud-based systems such as Microsoft’s OneDrive, Amazon’s AWS, or Google Drive. In these more complicated configurations, you must communicate with different database back ends in different ways.
In this chapter, I discuss client/server systems. A simple client/server system has one server machine that hosts the database. Multiple client computers are connected to the server over a LAN. Users sit at the client machines, which execute your database application program. Larger systems can have multiple servers, each holding different databases. The part of your program written in a host language such as C++, C#, or Java is executed on the client machine, but the SQL is sent over the network to a server. Before it’s sent to the server, the SQL must be translated into something the database understands. Several methods of doing this exist.
The simplest form of communication between an application and a database is through a native driver. Figure 7-1 shows how a native driver specific to Oracle 18c connects your application to an Oracle 18c database.
This arrangement is not much different from that of a stand-alone desktop database system. The Oracle 18c native driver is specifically designed to take SQL from the application and translate it into Oracle 18c database commands. When the database returns a result set, the native driver translates it into a standard SQL result set and passes it back to the application.
Because native drivers are specifically designed to work with a particular database, they can be highly optimized for that specific situation and, thus, have very good performance. That specificity, which makes possible the native driver’s greatest strength, is also its biggest weakness. When you build a database system that uses a native driver to connect to one type of database — say, Oracle 18c — the connection doesn’t work with any other type of database, such as SQL Server.
When you write a database application, the part of the application that communicates with the database is called the application programming interface (API). When you’re communicating to databases through native drivers, every native driver is different from all the others, so the API is different for each one, too. This situation complicates the design and development of applications that must deal with multiple data sources.
Native drivers are great if you know that the application you’re writing will have to interface with only one specific data source, both now and in the future. You can’t beat the performance of a well-designed native driver. If, however, your application may need to pull data from more than one source, you may want to consider one of the interface options that aren’t product-specific. One of these options is ODBC, which is covered in the next section.
An application may need to access data in multiple databases of incompatible types. Incorporating multiple APIs into your code isn’t a desirable solution.
Fortunately, you have a better way. Open Database Connectivity (ODBC) is a widely accepted standard method of communicating with most popular database formats. It accomplishes this task by adding an extra layer between the application and the database. Figure 7-2 shows this arrangement. It’s unlikely that you’d want to connect any realistic application to five data sources, as shown in Figure 7-2, but with ODBC, you could.
The application communicates directly with the driver manager. The front end of the driver manager always presents the same API to the application. The back end of the driver manager connects to a driver that’s specific to the database on the back end. The driver in turn connects to the database. This arrangement means that you, as the application programmer, never have to worry about the details of how to connect to the database on the back end. All you have to do to be successful is make your program compatible with the ODBC API. The driver manager makes sure that the correct driver is in place to communicate with the database.
To provide its function, ODBC can be conceptually (and physically) divided into four major components:
In broad terms, the application is the component closest to the user, and the data source is the component that holds the data. Each type of data source has its own driver. Through the driver, the driver manager manages communication between the application and the data source. The next few sections take a closer look at each component.
The application is a piece of software that interacts directly with the user. It also requires access to data, which is why the user wants to interact with it in the first place. If you’re an application programmer, the application is the one ODBC component that you create. It can be a custom program written in a procedural language such as C++ or Visual Basic. It can be a spreadsheet or a word processing package. It can be an interactive query tool. Just about any piece of software that works with data and interacts with a user can be the application portion of an ODBC system. The data accessed by the application can be from a relational database, from an Indexed Sequential Access Method (ISAM) file, or from a straight ASCII text file. ODBC provides a lot of flexibility in what kinds of applications can use it and in what kinds of data those applications can access.
The driver manager is a library (in Windows, a Dynamic-Link Library [DLL]) that provides a common interface to applications, regardless of what data source is being accessed. It performs such functions as the following:
The value of the driver manager is that the application can make function calls to it without regard for which driver or data source is currently in use. After the application identifies the needed driver and data source by sending the driver manager a connection handle, the driver manager loads the driver and builds a table of pointers to the functions in that driver. The application programmer doesn’t need to worry about maintaining a table of pointers to functions in the driver; the driver manager does that job under the covers.
Aside from the vendors of databases themselves, such as Microsoft and Oracle, driver managers are written and distributed by companies that specialize in database interfacing. Simba Technologies, Progress Software, Easysoft Limited, and OpenLink Software are examples of companies that provide the driver-manager and driver components of ODBC systems.
Drivers are libraries that implement the functions of the ODBC API. Each driver has a common interface to the driver manager, but its interface to its data source is customized to that particular data source.
Companies that specialize in driver development, such as those listed in the preceding section, have developed and made available drivers for most of the popular data sources in use today. As a result, most people never need to write their own drivers. Only people who work with unusual data sources or require functions that aren’t supported by standard drivers need to write their own drivers, using a procedural language such as C or Java.
DBMS-based drivers operate on multiuser systems operating in client/server mode. This mode of operation features a balance between the client and server machines, both of which do significant processing. The application, driver manager, and driver all reside on the client machine. Together, they comprise the client part of the client/server system.
The data source is composed of the DBMS, such as SQL Server, Oracle, or DB2, and the database itself. These components are located on the server machine. When the server hosts the DBMS as well as the database, it is called a two-tiered configuration. Figure 7-3 shows the two-tier configuration.
The data source, as the name implies, is the source of the data that’s accessed by the application. It can be a spreadsheet file, an ASCII file, or a database under the control of a DBMS. The user needn’t know the technical details of the data source, such as file type, DBMS, operating system, or hardware. The name of the data source is all he needs to know.
Application development consists of writing, compiling, linking, executing, and debugging. When you get an application to function the way you want it to, you can release it to users. Applications that use ODBC are linked to the driver manager’s import library at link time. The import library contains those parts of ODBC that deal with importing instructions from the application. In Windows, the import library is named
. In addition to ODBC32.LIB
ODBC32.LIB
, a running application uses ODBC32.DLL
and a driver that’s compatible with the data source. ODBC32.DLL
remains loaded in memory as long as any running application requires it. When the last ODBC-enabled application terminates, ODBC32.DLL
is unloaded from memory.
ODBC makes extensive use of the concept of handles. A handle is an integer value that identifies an object used by an application. ODBC uses three types of handles that are related in a hierarchical fashion:
An application can have one (and only one) environment handle. Conversely, each environment handle can be assigned to one (and only one) application. A single environment handle can own multiple connections, each represented by a single connection handle. Each connection can own multiple statements, each represented by a single statement handle.
Figure 7-4 shows how to use environment handles, connection handles, and statement handles to establish a connection to a data source, execute some SQL statements, and then break the connection.
An ODBC operation takes place in distinct stages. Each stage builds on the one that precedes it. Handles provide the mechanism for the exchange of commands and information. First, an environment is established. Next, a connection between application and data source is built. Then an SQL statement is sent to the data source for processing. Results are returned from the data source to the application. Finally, the connection is terminated.
ODBC-enabled applications communicate with the rest of the ODBC system by making function calls. The first step in the process is allocating an environment handle and a connection handle. Two invocations of the function call SQLAllocHandle
do the job. The driver manager allocates space in memory for the requested handles and returns the handles to the application. The first invocation of SQLAllocHandle
initializes the ODBC interface, in addition to allocating memory for global information. If the first SQLAllocHandle
function executes successfully, execution can proceed to the second SQLAllocHandle
function. This particular invocation of SQLAllocHandle
allocates memory for a connection handle and its associated connection information. SQLAllocHandle
takes the active environment handle as input and returns a pointer to the newly allocated connection handle as an output. Depending on which development tool they’re using, application programmers may or may not have to explicitly allocate environment and connection handles.
After environment and connection handles have established a link between the application and the driver manager, the next step in the process is linking the driver manager to the appropriate driver. Two functions are available for accomplishing this task: SQLConnect
and SQLDriverConnect
. SQLConnect
is the simpler of the two functions, requiring only the connection handle, data source name, user identifier, and user password as input parameters. When the establishment of a connection requires more information than SQLConnect
provides, SQLDriverConnect
is used. It passes a connection string to the driver attached to the data source.
In a Windows system, after the connection between the driver manager and the driver has been established, the driver manager obtains a library handle for the driver; then it calls the Windows function GetProcAddress
for each function in the driver. The function addresses are stored in an array associated with the connection handle.
Now that the driver has been loaded, environment and connection handles can be called in it. The function SQLAllocHandle
can be used for this purpose, as it was used to call the environment and connection handles in the driver manager. If the application uses the function SQLSetConnectOption
to set options for the connection, the driver manager calls the driver’s SQLSetConnectOption
function to enable those options to be set.
At last, the driver manager completes the connection to the data source by calling SQLConnect
or SQLDriverConnect
. If the driver is a one-tier driver, there’s no network connection to make, so this stage is trivial. If the driver is multitier, it calls the network interface software in the client machine, which connects to the server machine that holds the data source. To make this connection, the driver uses information that was stored in the ODBC.INI
file when the data source name was created.
After the connection is established in a client/server system, the driver usually sends the username and password to the server for validation. If the username and password are valid, the driver returns a standard SQL_SUCCESS
code to the driver manager. If they’re not valid, the server returns an error code to the driver. Then the driver translates this error code to the standard ODBC error code and returns it to the driver manager as SQLSTATE, and the driver manager returns SQLSTATE to the application.
With the connection at last established, an SQL statement can be executed. Even this process, however, requires multiple stages. First, a statement handle must be allocated. The application does this by issuing an SQLAllocHandle
call. When the driver manager receives this call, it allocates a statement handle and then sends an SQLAllocHandle
call to the driver. Then the driver allocates its own statement handle before returning control to the driver manager, which returns control to the application.
After the statement handle has been allocated, an SQL statement can be executed. There is more than one way to do this, but the simplest is the SQLExecDirect
function. SQLExecDirect
takes a character string as input and sends it to the server. The character string should be a valid SQL statement. If necessary, the driver translates the statement from ODBC-standard SQL to commands understood by the data source on the server. When the data source receives the request for action, it processes the command and then returns any results to the application via the driver and driver manager. The exact details of this processing and how the results are returned to the client application may differ from one data source to another. These differences are masked by the driver so that the application always sees standard ODBC responses, regardless of what data source it is communicating with.
Following is a fragment of C code showing the allocation of environment, connection, and statement handles and connection to a data source:
SQLRETURN cliRC = SQL_SUCCESS;
int rc = 0;
SQLHANDLE henv; /* environment handle */
SQLHANDLE hdbc; /* connection handle */
SQLHANDLE hstmt; /* statement handle */
char dbAlias[SQL_MAX_DSN_LENGTH + 1];
char user[MAX_UID_LENGTH + 1];
char pswd[MAX_PWD_LENGTH + 1];
/* check the command line arguments */
rc = CmdLineArgsCheck1(argc, argv, dbAlias, user, pswd);
if (rc != 0)
{
return 1;
}
/* allocate an environment handle */
cliRC = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
if (cliRC != SQL_SUCCESS)
{
printf("
--ERROR while allocating the environment handle.
");
printf(" cliRC = %d
", cliRC);
printf(" line = %d
", __LINE__);
printf(" file = %s
", __FILE__);
return 1;
}
/* set attribute to enable application to run as ODBC 3.0 application */
cliRC = SQLSetEnvAttr(henv,
SQL_ATTR_ODBC_VERSION,
(void *)SQL_OV_ODBC3,
0);
ENV_HANDLE_CHECK(henv, cliRC);
/* allocate a database connection handle */
cliRC = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
ENV_HANDLE_CHECK(henv, cliRC);
/* connect to the database */
cliRC = SQLConnect(hdbc,
(SQLCHAR *)dbAlias, SQL_NTS,
(SQLCHAR *)user, SQL_NTS,
(SQLCHAR *)pswd, SQL_NTS);
DBC_HANDLE_CHECK(hdbc, cliRC);
/* allocate one or more statement handles */
cliRC = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
DBC_HANDLE_CHECK(hdbc, cliRC);
18.221.66.185