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.
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.
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.2 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.
Data Type of Symbolic Name | 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.2 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.