Client 1 - Connecting to the Server | PostgreSQL (2nd Edition)

Client 1 ”Connecting to the Server

Now that you understand the basic architecture of the ODBC API and you have defined a PostgreSQL data source, let's look at some sample code. This first client application connects to a database and then exits. Listing 12.1 provides an example that is much more complex than the sample clients in earlier chapters ”ODBC is a complex API.

Listing 12.1 `odbc/client1.c`

 1 /* client1.c */  2  3 #include <sql.h>  4 #include <sqlext.h>  5  6 #include <stdio.h>  7  8 typedef enum { FALSE, TRUE } bool;  9 10 int main( int argc, char * argv[] ) 11 { 12   SQLRETURN   result; 13   SQLHENV     envHandle; 14   SQLHDBC     conHandle; 15 16   SQLAllocHandle( SQL_HANDLE_ENV, 17                  SQL_NULL_HANDLE, 18                  &envHandle ); 19 20   SQLSetEnvAttr( envHandle, 21                 SQL_ATTR_ODBC_VERSION, 22                (SQLPOINTER)SQL_OV_ODBC2, 23                 0 ); 24 25   SQLAllocHandle( SQL_HANDLE_DBC, 26                   envHandle, 27                   &conHandle ); 28 29   result = SQLConnect( conHandle,  // connection handle 30                argv[1], SQL_NTS,   // data source name 31                argv[2], SQL_NTS,   // user name 32                argv[3], SQL_NTS ); // password 33 34 35   if( result == SQL_SUCCESS  result == SQL_SUCCESS_WITH_INFO ) 36   { 37     printf( "connection ok...\n" ); 38     return( 0 ); 39   } 40   else 41   { 42     printf( "connection failed...\n" ); 43     return( -1 ); 44   } 45 }

If you want to run this program, you will need to provide three arguments: the name of a data source, a valid username, and a password. Here is an example:

 $ ./client1 MoviesDSN korry cows connection ok...

Now, let's look through the code.

The first thing you'll notice when you work with ODBC is that you have to create a lot of handles. Remember that a handle is an opaque data type ”there is a data structure behind a handle, but you can't get to it. There are only three things that you can do with a handle: You can create it, you can destroy it, and you can pass it to a function.

ODBC Handle Types

ODBC defines four different types of handles:

A SQLHENV is an environment handle ” it functions as the top-level handle to the ODBC API. You must create an environment handle before you can do anything else with ODBC.
A SQLHDBC is a handle to a database connection. When you connect to a database, you initialize a SQLHDBC handle. After you have a valid database connection handle, you can allocate a statement handle.
A statement handle has the type SQLHSTMT . You must create a statement handle before you can send a command to the database. Result set information is returned through a SQLHSTMT handle.
The last handle type defined by ODBC is SQLHDESC . A SQLHDESC handle is a descriptor handle. Descriptor handles are used when you are writing an ODBC driver (as opposed to a client application) and may be used in sophisticated error-handling code. I've never needed to allocate a SQLHDESC myself ; you probably won't need to either.

You create an environment handle at line 13 and initialize it by calling SQLAllocHandle (SQL_HANDLE_ENV,...) . There are three arguments to the SQLAllocHandle() function. The first argument specifies what type of handle you are trying to create. The second argument specifies the parent of the new handle. The final argument is a pointer to the handle that you want to initialize. Table 12.1 shows how to allocate different types of handles using SQLAllocHandle() . Notice that an environment handle doesn't have a parent, so you pass SQL_NULL_HANDLE as the second argument.

Table 12.1. SQLAllocHandle() Arguments

Symbolic Name	Data Type of New Handle	Type of Parent	Description
`SQL_HANDLE_ENV`	`SQLHENV`	No parent	Environment handle
`SQL_HANDLE_DBC`	`SQLHDBC`	`SQLHENV`	Database connection handle
`SQL_HANDLE_STMT`	`SQLHSTMT`	`SQLHDBC`	Statement handle
`SQL_HANDLE_DESC`	`SQLHDESC`	`SQLHDBC`	Descriptor handle

After you have an initialized environment handle, you need to tell the ODBC library what version of ODBC you expect to find. Use the SQLSetEnvAttr() function to tell ODBC that you are going to interact using the ODBC 2.x protocol. The PostgreSQL ODBC driver is written to the ODBC 2.5 specification, so you can't call any of the driver-supplied functions that were added in ODBC 3.0. (Note: The driver manager translates many 3.0 functions into 2.x requests , but I find that the results generally are not reliable.)

At line 25, you allocate a connection handle (a SQLHDBC ). Compare this function call with your earlier call to SQLAllocHandle() :

 SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &envHandle ); SQLAllocHandle( SQL_HANDLE_DBC, envHandle,       &conHandle );

You can see in Table 12.1 that an environment handle does not have a parent. When you allocate an environment handle, you pass SQL_NULL_HANDLE instead of a parent. When you allocate a connection handle, you allocate it within the context of an environment; you provide an environment handle as the second parameter to SQLAllocHandle() .

At this point in the example code, you have allocated an environment handle, declared which ODBC protocol you want to use, and allocated a connection handle. You still have not connected to a data source. There are three functions that we can use to connect to a data source: SQLConnect() , SQLDriverConnect() , and SQLBrowseConnect() . The simplest connection function is SQLConnect() . Here is the function prototype for SQLConnect() :

 SQLRETURN SQLConnect( SQLHDBC     ConnectionHandle,                       SQLCHAR   * DataSourceName,                       SQLSMALLINT DataSourceLength,                       SQLCHAR   * UserName,                       SQLSMALLINT UserNameLength,                       SQLCHAR   * Password,                       SQLSMALLINT PasswordLength );

When you call SQLConnect() , you provide a connection handle, a data source name, a username, and a password. In this sample code, you use command-line arguments for the data source name, username, and password. Notice that you don't actually compute the length of each string that you pass to SQLConnect() ”instead, you pass SQL_NTS to tell ODBC that you are sending NULL- terminated strings.

The other connection functions ”( SQLDriverConnect() and SQLBrowseConnect() ” are more complex. I'll show you how to use SQLDriverConnect() in a later example, but the PostgreSQL ODBC driver does not support SQLBrowseConnect() .

SQLConnect() returns a SQLRETURN value. One of the things that complicates ODBC programming is that ODBC defines two different SUCCESS values, SQL_SUCCESS and SQL_SUCCESS_WITH_INFO, and you have to check for either of these values. I'll discuss the difference between these two values in the next section.

In the sample code, you just print a message to tell the user whether he could connect to the requested data source. I'm cheating a little in this example ”a well-behaved application would tear down the database connection and properly discard the environment and connection handles. In this case, the application exits immediately after finishing its interaction with the database. If you still had more work to do and no longer needed the database connection, it would be a good idea to free up the resources required to maintain the connection.

Client 1 - Connecting to the Server