InformIT: Visual C++ 6 Unleashed > Creating ODBC Applications

Table of Contents

Creating ODBC Applications

In this section, you will be looking at how to create an ODBC application using the ODBC API directly. In the next chapter, you will see how you can use the MFC database classes and AppWizard to help you create database applications.

Most ODBC applications will perform the following basic steps:

Allocate the ODBC environment and connection handles.
Write a function to handle errors using environment handles.
Connect to a data source.
Execute SQL statements or retrieve query results.
Disconnect from the data source.
Free the ODBC environment.

In this section, you will learn how to connect and disconnect to and from the database, and how to write an ODBC error handling routine. Later in the chapter, executing SQL and retrieving query results will be covered in more detail.

Allocating and Freeing ODBC Handles

Before using any other ODBC functions, you must first allocate the ODBC environment, which initializes some of the internal structures and handles used by ODBC. To do this, you must first allocate a variable of type SQLHENV, which will serve as a handle to your ODBC environment. This handle is then initialized with the SQLAllocHandle() function that you saw previously.

To allocate the ODBC environment, HandleType should be set to SQL_HANDLE_ENV, and OutputHandlePtr should point to the environment handle to be allocated. InputHandle should be set to SQL_NULL_HANDLE because the environment isn't derived from another handle. You will see an example of this when you look at SQLConnect() later.

You should only allocate one environment for an application. The same environment can, however, be used for multiple threads and multiple data source connections.

When you are finished with ODBC in your application, you should free the ODBC environment with a call to SQLFreeHandle(), passing a HandleType of SQL_HANDLE_ENV and the environment handle you have allocated.

Setting Your Application's ODBC Version

The ODBC driver manager is designed to support version 2 drivers and applications, as well as newer ODBC 3 components. Depending on the version of ODBC that your application is using, certain functions will behave differently. This requires that your application specify which version of the ODBC API it is using before you go on to allocate connection handles.

Setting the version of ODBC that your application is using is done by calling SQLSetEnvAttr()to set the SQL_ATTR_ODBC_VERSION environment attribute to SQL_OV_ODBC3. You can see an example of this when you connect to a data source.

Allocating a Connection Handle

Next, you allocate a connection handle for each data source that you intend to use. This is also done with the SQLAllocHandle() function that you saw previously—HandleType is set to SQL_HANDLE_DBC, and you should pass the previously allocated environment handle in InputHandle. A pointer to the connection handle to be allocated should be passed in OutputHandlePtr.

After you have allocated a connection handle, you can attach the returned connection handle to a data source with the SQLConnect. functions that you will see next.

When you are finished with a connection, you should free the connection with a call to SQLFreeHandle().

Connection Options

The ODBC API allows you to set many different options associated with a connection handle by calling SQLSetConnectAttr():

SQLRETURN SQLSetConnectAttr(SQLHDBC ConnectionHandle, SQLINTEGER Attribute,
    SQLPOINTER ValuePtr, SQLINTEGER StringLength);

You should pass a previously allocated SQLHDBC in ConnectionHandle, although, depending on the attribute set, it doesn't need to be connected to a data source yet. The value passed in Attribute determines the option to be set and may include the values listed next, as well as others that may be defined later. You will look at several of these options in more detail later in this chapter:

SQL_ATTR_ACCESS_MODE sets read-only mode.
SQL_ATTR_ASYNC_ENABLE enables asynchronous operations (more on this later).
SQL_ATTR_AUTO_IPD enables automatic population of the IPD after a call to SQLPrepare().
SQL_ATTR_AUTOCOMMIT sets auto-commit mode for transaction processing.
SQL_ATTR_CURRENT_CATALOG sets the catalog (also called database or qualifier) for the current connection. This is generally something like a specific database name.
SQL_ATTR_LOGIN_TIMEOUT sets the timeout value for establishing a connection to a data source.
SQL_ATTR_CONNECTION_TIMEOUT sets the timeout value for operations on a connection, other than login or query execution.
SQL_ATTR_METADATA determines how string arguments are used in catalog functions.
SQL_ATTR_ODBC_CURSORS specifies the type of cursor support that is used.
SQL_ATTR_PACKET_SIZE sets the size of the network packets used.
SQL_ATTR_QUIET_MODE disables dialogs from the driver or sets the parent window for any dialogs.
SQL_ATTR_TRACE enables tracing for the driver manager.
SQL_ATTR_TRACEFILE sets the name of the trace file.
SQL_ATTR_TRANSLATE_LIB sets the name of a DLL to use for translation between a driver and a data source.
SQL_ATTR_TRANSLATE_OPTION sets an option value specific to the translation DLL being used.
SQL_ATTR_TXN_ISOLATION sets the level of transaction isolation in use.

The ValuePtr and StringLength parameters should contain information specific to the option being set.

You can retrieve the current settings for any of these options with a call to SQLGetConnectAttr().

Starting Development on an ODBC Program

Now that you've seen how to allocate the ODBC environment, you are ready to start writing an ODBC program. This section contains simple steps that you can follow that will write an MFC program that uses ODBC calls to connect to an ODBC database. Later in this chapter, more code will be added to each section.

Step 1

Start a new MFC AppWizard (exe) project. As you can see in Figure 20.3, a new project called ODBCTest is created using MFC. (This same project will be used throughout this chapter.) Click OK and choose Single Document in step 1 of 6. Click Next.

Figure 20.3 The MFC AppWizard is a great way to generate database applications.

Step 2

In step 2 of 6 choose Header Files Only, as shown in Figure 20.4. This will allow database support without including any prewritten MFC database code. Click Next, and accept the defaults in step 3 of 6. Click Next, and turn off the Printing and print preview in step 4 of 6. You can accept the rest of the defaults, so you can click Finish here. Click OK when the New Project Information dialog box appears. Your application will then be generated by the MFC AppWizard.

Figure 20.4 You can support OLE DB and ODBC by choosing Header Files Only inside your MFC AppWizard.

Step 3

In the header file for your view (ODBCTestView.h), add handle variables for the environment and the connection, and add a function prototype for setting up the database. New code needed to do this is shown in gray below:

// ODBCTestView.h: interface of the CODBCTestView class
//
/////////////////////////////////////////////////////////////////////////////

#if
!defined(AFX_ODBCTESTVIEW_H__6807EBC2_A9E9_11D3_994A_C4720EBCE741__INCLUDED_)
#define AFX_ODBCTESTVIEW_H__6807EBC2_A9E9_11D3_994A_C4720EBCE741__INCLUDED_

#if _MSC_VER > 1000
#pragma once
#endif // _MSC_VER > 1000

class CODBCTestView : public CView
{
protected: // create from serialization only
    CODBCTestView();
    DECLARE_DYNCREATE(CODBCTestView)
/////////////////////////////////////////////////
//    Added by Chuck Wood
    SQLHANDLE hOdbcEnv; //ODBC Environment handle
    SQLHANDLE hDbConn;  //ODBC Connection handle
    void setUpODBC();   //Set up the ODBC environment
/////////////////////////////////////////////////


// Attributes
public:
    CODBCTestDoc* GetDocument();

Step 4

In the constructor for your view in your view's source file (ODBCTestView.cpp), call your (soon-to-be-written) setUpODBC function, as shown below in gray:

CODBCTestView::CODBCTestView()
{


    setUpODBC();    //Added by Chuck Wood to set up to ODBC Connection


}

Step 5

Finally, at the bottom of your view source file (ODBCTestView.cpp), write a setUpODBC function, as shown here:

// setUpODBC function written by Chuck Wood to allocate ODBC variables
void CODBCTestView::setUpODBC() {
        SQLRETURN sr;                           //Return code for your ODBC calls
// Allocate Environment
        sr = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hOdbcEnv);
        if(sr != SQL_SUCCESS && sr != SQL_SUCCESS_WITH_INFO)
                MessageBox("Error in Allocating Environment.");
// Set the App's ODBC Version
        sr = SQLSetEnvAttr(hOdbcEnv, SQL_ATTR_ODBC_VERSION,
        (SQLPOINTER)SQL_OV_ODBC3, SQL_IS_INTEGER);
        if(sr != SQL_SUCCESS && sr != SQL_SUCCESS_WITH_INFO)
                MessageBox("Error in Setting ODBC Version.");
// Allocate Connection
        sr = SQLAllocHandle(SQL_HANDLE_DBC, hOdbcEnv, &hDbConn);
        if(sr != SQL_SUCCESS && sr != SQL_SUCCESS_WITH_INFO)
                MessageBox("Error in Allocating Connection.");
// Set Connect Timeout
         sr = SQLSetConnectAttr(hDbConn, SQL_ATTR_LOGIN_TIMEOUT, (void*)5, 0);
         if(sr != SQL_SUCCESS && sr != SQL_SUCCESS_WITH_INFO)
                 MessageBox("Error in Setting Login Timeout.");
}

Step 6

Somewhere, you must free the handles that you've allocated using the SQLFreeHandle() function. This is done in the view's destructor in your view source file (ODBCTestView.cpp):

CODBCTestView::~CODBCTestView() {
    if (hDbConn != SQL_NULL_HANDLE)  {


        //Free connection
        SQLFreeHandle(SQL_HANDLE_DBC, hDbConn);
    }
    if (hOdbcEnv != SQL_NULL_HANDLE)
        //Free environment
        SQLFreeHandle(SQL_HANDLE_ENV, hOdbcEnv);
}

When you run your program, you should see the MFC View display. If everything works, there should be no message that displays.

ODBC Error Handling

The ODBC API provides two levels of diagnostic information about calls to the API. At the first level, each function returns a SQLRETURN value, which will contain a small set of values indicating the general success or failure of an operation. You've already seen some error handling with the SQLRETURN variable in the previous code example. At the second level, each function call also generates at least one diagnostic record. These diagnostic records give specific information about any errors that occurred, or other information about the operation.

`SQLRETURN` Values

All the ODBC functions return a SQLRETURN (signed short) value, which will receive a value indicating the success or failure of a function. If a call is completed successfully, it will return either SQL_SUCCESS or SQL_SUCCESS_WITH_INFO, which is used to notify your application that additional information about the operation can be retrieved by calling SQLGetDiagRec(). SQL_SUCCESS_WITH_INFO is often used to pass warning messages to your application. These diagnostic records are useful both for debugging your application and for deciding how to handle certain situations at runtime.

Currently, all error return codes are defined to be less than 0, although there are some other positive return codes that don't necessarily indicate successful completion. These include SQL_NO_DATA (called SQL_NO_DATA_FOUND in ODBC 2), which is returned when an operation completes successfully, but there is no data to be had; SQL_STILL_ EXECUTING, which is used with asynchronous operations; and SQL_NEED_DATA, which is used to indicate that the function needs additional data to complete.

In most cases, if an error occurs in an ODBC function, it will return SQL_ERROR (or SQL_INVALID_HANDLE if things are really wrong).

Diagnostic Records

Each of the ODBC API functions can generate a set of diagnostic records that reflect information about the performance of the operation. These diagnostic records are stored in the structures associated with the ODBC handle that generated the error. For instance, if an error occurs when calling SQLExecDirect(), the statement handle that was used will contain the diagnostic records, whereas errors in something like SQLConnect() will be stored in the connection handle that was used.

The diagnostic records generated by a function can be accessed from the handle until another call is made that uses that handle.

All ODBC calls will return at least a header record and may contain many additional status records. These records consist of a predefined set of fields, as well as others that may be defined by the particular driver you are using.

`SQLGetDiagField()`

You can retrieve the value of a particular field from a diagnostic record by using the SQLGetDiagField() function:

SQLRETURN SQLGetDiagField(SQLSMALLINT HandleType, SQLHANDLE Handle,
    SQLSMALLINT RecNumber, SQLSMALLINT DiagIdentifier, SQLPOINTER DiagInfoPtr,
    SQLSMALLINT BufferLength, SQLSMALLINT * StringLengthPtr);

The Handle and HandleType parameters are used to specify the handle to retrieve records from and its type.

The RecNumber field is used to specify the record to retrieve. To retrieve information from the header record, this field should be set to 0. You can retrieve the total number of additional status records available for a handle by retrieving the SQL_DIAG_NUMBER field from record 0.

The DiagIdentifier parameter specifies which field to return. This may be one of the fields that is predefined in the ODBC API or any additional fields that may be added by specific drivers. The following are some of the most common fields (note that the header record and status records contain different fields):

Header Record Fields

SQL_DIAG_NUMBER—Number of status records available
SQL_DIAG_RETURN_CODE—The return code for the previous function call

Status Record Fields

SQL_DIAG_SQLSTATE—A five-character SQLSTATE code (see SQLSTATEs)
SQL_DIAG_MESSAGE_TEXT—A plaintext message about the error or warning
SQL_DIAG_NATIVE—The native error code from the driver or data source
SQL_DIAG_COLUMN_NUMBER—The column (if any) associated with this record
SQL_DIAG_ROW_NUMBER—The row (if any) associated with this record

For details on the other fields available in diagnostic records, see the online documentation for SQLGetDiagField().

The information for the requested field is returned in the buffer at DiagInfoPtr, and the length of the data is returned at StringLengthPtr.

`SQLSTATES`

There are a great number of possible values for SQLSTATE codes that can be returned in the SQL_DIAG_SQLSTATE field, many of which may have a variety of different meanings, depending on the driver you are using. Because of this, you should be careful in how you use the SQLSTATE codes in your programming logic if you intend to use a variety of drivers.

However, in most cases, the following SQLSTATEs can be safely used in your programming logic because they are generally implemented in the same way in most drivers and can be useful in controlling program flow:

01004—Data truncated
01S02—Option value changed
HY008—Operation canceled
HYC00—Optional feature not implemented
HYT00—Timeout expired

For a complete listing of possible SQLSTATEs, see Appendix A of the ODBC 3 Programmer's Guide, included in the online documentation. In addition, the SQLSTATEs that can be returned for each function are listed in the online documentation for that function.

`SQLGetDiagRec()`

In addition to the SQLGetDiagField() function shown previously, ODBC 3 provides the SQLGetDiagRec() function, which will retrieve some of the most commonly used fields from a status record in one fell swoop. This function will return the SQLSTATE, the native error code, and the diagnostic message text that is contained in status records. However, to access the header record, you must use SQLGetDiagField().

Writing an ODBC Error Handling Routine

One of the most common routines to write for ODBC programs is a routine that handles all ODBC errors. With such a routine, it's easy to add error handling to your ODBC database. Furthermore, writing an error routine is simple when armed with the knowledge found in this section. As we continue with our same ODBCTest example, add an error prototype to the view header file (ODBCTestView.h) as shown in gray in the following code:

protected: // create from serialization only
        CODBCTestView();
        DECLARE_DYNCREATE(CODBCTestView)

/////////////////////////////////////////////////////////////////////////////
//      Added by Chuck Wood
        SQLHANDLE hOdbcEnv;                       //ODBC Environment handle
        SQLHANDLE hDbConn;                       //ODBC Connection handle
        void setUpODBC();                       //Set up the ODBC environment


        void displayODBCError (SQLRETURN sr, char *inMessage = NULL); //Display error
/////////////////////////////////////////////////////////////////////////////

Then, at the bottom of your view's source file (ODBCTestView.cpp) write the displayODBCError routine using the SQLGetDiagRec function to retrieve error information:

// displayODBCError function written by Chuck Wood
// to display ODBC Errors
void CODBCTestView::displayODBCError (SQLRETURN sr,
                    char *inMessage) {
    if(sr != SQL_SUCCESS && sr != SQL_SUCCESS_WITH_INFO) {
        SQLCHAR SqlState[6];
        SQLINTEGER NativeError;
        SQLCHAR ErrMsg[SQL_MAX_MESSAGE_LENGTH];
        int i = 1;
        char message[512];
        strcpy (message, "");
        if (inMessage) {
            strcpy(message, inMessage);
            strcat(message, " — ");
        }
        sprintf(message, "%sError in SQLConnect(): %d.",
                message, sr);
        MessageBox(message);
        while(SQLGetDiagRec(SQL_HANDLE_DBC, hDbConn, i,
                SqlState, &NativeError,
                ErrMsg, sizeof(ErrMsg), NULL)
                != SQL_NO_DATA) {
            sprintf(message,
    "Diag: %d, SQLSTATE: %s NativeError: %d ErrMsg: %s",
                i++, SqlState, NativeError, ErrMsg);
            MessageBox(message);
        }
    }
}

Now when a SQLRETURN code returns an error, you can call this function to display the error with minimal coding overhead.

Connecting to a Data Source

After you allocate a connection handle with SQLAllocConnect(), you must connect the handle to a data source before you can start operating on that data source. ODBC provides three functions that can be used for this. SQLConnect() provides the most direct method of connecting from your code, and SQLDriverConnect() presents a dialog to the user to choose a data source. The third connect function, SQLBrowseConnect(), can be used to browse available data sources, while prompting for any additional information that may be required.

`SQLConnect()`

The first of the connection functions that you will look at is SQLConnect(). This function provides you with the most direct programmatic control of the connection, although if you want to allow the user to choose data sources at runtime, you need to code the user interface for selecting data sources yourself. That said, let's look at how it works. The prototype for SQLConnect() is shown in the following:

SQLRETURN SQLConnect(SQLHDBC ConnectionHandle, SQLCHAR* ServerName,
    SQLSMALLINT NameLength1, SQLCHAR* UserName, SQLSMALLINT NameLength2,
    SQLCHAR* Authentication, SQLSMALLINT NameLength3);

When calling SQLConnect(), ConnectionHandle should be a connection handle that was previously allocated with SQLAllocConnection(). The remaining parameters are used to pass strings for the data source name (ServerName), user ID (UserName), and password (Authentication). For many data sources, such as text files, that are on the local machine, you don't need to specify a user ID or password, and you can pass NULL pointers for UserName and Authentication.

When you call SQLConnect() or one of the other connect functions, the ODBC driver manager will load the requested driver if it isn't already loaded and will connect to the requested data source. If an error occurs, SQLConnect() will return SQL_ERROR (or SQL_INVALID_HANDLE), and you should call SQLGetDiagRec() to retrieve specific information about the error(s).

Writing a Program to Connect to a Database

This section shows how you can use SQLConnect() to connect to a data source named "VCUnleashed":

Find some way to call a connection routine. In my example, I added a menu named Connect With SQLConnect. I then used the ClassWizard to create an OnViewConnectwithsqlconnect function that is called every time this menu option is selected.

After you have defined a function that is called to connect to the database, you need to code the function in your view's source file (ODBCTestView.cpp). The code needed to connect to the VCUnleashed database is shown in gray:

void CODBCTestView::OnViewConnectwithsqlconnect()  {


// Function for connecting to the database written by Chuck Wood
    SQLRETURN sr;                    //Return code for your ODBC calls
    char szDSN[] = "VCUnleashed";    //ODBC name
    char szUID[] = "";               //User ID
    char szAuthStr[] = "";           //Password
// Connect to Data Source
    sr = SQLConnect(hDbConn, (UCHAR *)szDSN, SQL_NTS,
                    (UCHAR *)szUID, SQL_NTS,
                    (UCHAR *) szAuthStr, SQL_NTS);
    if(sr != SQL_SUCCESS && sr != SQL_SUCCESS_WITH_INFO)
        displayODBCError(sr, "Error in OnViewConnectwithsqlconnect");
    else
        MessageBox("Connected OK");


}

The previous code example shows how to use the SQLConnect function, and how to call the displayODBCError function written in the last section if an error occurs.

    if (hDbConn != SQL_NULL_HANDLE)  {
        //Disconnect from database
        SQLDisconnect(hDbConn);
        //Free connection
        SQLFreeHandle(SQL_HANDLE_DBC, hDbConn);
    }


    if (hOdbcEnv != SQL_NULL_HANDLE)
        //Free environment
        SQLFreeHandle(SQL_HANDLE_ENV, hOdbcEnv);
}

`SQLDriverConnect()`

The next of the connect functions that you will look at is SQLDriverConnect(), which opens a dialog to the user for selecting a data source. SQLDriverConnect() can also be used to pass additional connection parameters that aren't supported by the SQLConnect() function. Here is the prototype for SQLDriverConnect():

SQLRETURN SQLDriverConnect(SQLHDBC ConnectionHandle, SQLHWND WindowHandle,
    SQLCHAR * InConnectionString, SQLSMALLINT StringLength1,
    SQLCHAR * OutConnectionString, SQLSMALLINT BufferLength,
    SQLSMALLINT * StringLength2Ptr, SQLUSMALLINT DriverCompletion);

ConnectionHandle should be passed a connection handle that was previously allocated with SQLAllocHandle(), and WindowHandle may be passed the window handle for the parent of any dialog boxes that may be created.

The InConnectionString parameter points to a connection string that is passed into SQLDriverConnect(), consisting of a series of attribute keywords and their values. For example, to connect to a data source named SalesData for user JohnDoe with a password of JaneDoe, you might pass a string like the following:

"DSN=SalesData;UID=JohnDoe;PWD=JaneDoe"

The DSN, UID, and PWD attribute keywords are defined by ODBC, although individual drivers may support many additional keywords.

The value that you pass for DriverCompletion determines whether a dialog is presented to the user for selecting a data source. If you set DriverCompletion to SQL_DRIVER_NOPROMPT, no dialog will be presented. If InConnectionString includes incorrect or insufficient information, SQLDriverConnect() will return SQL_ERROR.

On the other hand, if you specify SQL_DRIVER_PROMPT, a dialog will always be presented to the user—the attributes passed in InConnectionString are used only as initial values in the dialog.

If you specify SQL_DRIVER_COMPLETE or SQL_DRIVER_COMPLETE_REQUIRED, a dialog is presented to the user only if the attributes passed in InConnectionString are insufficient to connect to a data source. If you have specified SQL_DRIVER_COMPLETE_REQUIRED, only the controls for required information will be enabled in the dialog.

When a connection to a data source is established, SQLDriverConnect() will return the actual connection string that was used in OutConnectionString. This can be used to determine the options that the user has selected from a dialog.

`SQLBrowseConnect()`

The last of the connection functions is SQLBrowseConnect(), which provides an iterative method for your application to browse available data sources. Note that this function doesn't provide the user interface for browsing, as SQLDriverConnect() does. SQLBrowseConnect() is generally available only for client-server database systems and isn't usually supported for local databases, such as MS Access. Here is the prototype for SQLBrowseConnect():

SQLRETURN SQLBrowseConnect(SQLHDBC ConnectionHandle, SQLCHAR * InConnectionString,
    SQLSMALLINT StringLength1, SQLCHAR * OutConnectionString,
    SQLSMALLINT BufferLength, SQLSMALLINT * StringLength2Ptr);

When calling SQLBrowseConnect(), you should first pass in a connection string in InConnectionString similar to that used in SQLDriverConnect(). If the information in this string is sufficient to connect to a data source, SQLBrowseConnect() will return SQL_SUCCESS or SQL_SUCCESS_WITH_INFO, and your connection is all set to go.

If additional information is required, SQLBrowseConnect() will return SQL_NEED_DATA. Even more specific information is returned in OutConnectionString.

In general, attributes that must be specified are of the following format: KEYWORD : PROMPT =?;, where KEYWORD is an attribute name, such as UID, and PROMPT is a string suitable for prompting the user. (You don't need to use the prompt string when building InConnectionString.) In addition, some attributes returned in OutConnectionString will present a list of possible choices in the following format:


   KEYWORD:PROMPT={CHOICE1, CHOICE2, CHOICE3};

For example, if you call SQLBrowseConnect() with InConnectionString of DSN=MyRDBMS, it may return SQL_NEED_DATA with OutConnectionString receiving DSN=MyRDBMS;UID:User Name=?;DATABASE:Database={EmpDB, InventoryDB, AccountsDB}.

You then should be able to call SQLBrowseConnect() again with InConnectionString of DSN=MyRDBMS;UID=JohnDoe;DATABASE=EmpDB. If all goes well, SQLBrowseConnect() will return SQL_SUCCESS or SQL_SUCCESS_WITH_INFO and your connection is ready for executing SQL statements against the data source.

`SQLDataSources()`

If you want to code your own browsing function, you can retrieve the available data sources by calling SQLDataSources():

SQLRETURN SQLDataSources(SQLHENV EnvironmentHandle, SQLUSMALLINT fDirection,
    SQLCHAR * ServerName, SQLSMALLINT BufferLength1, SQLSMALLINT * xNameLength1Ptr,
    SQLCHAR * Description, SQLSMALLINT BufferLength2, SQLSMALLINT * NameLength2Ptr);

To list all the available data sources, you should call SQLDataSources() with fDirection set to SQL_FETCH_FIRST. This will return the data source name in the buffer at ServerName and its description at Description. In most cases, you can set the NameLength1 and NameLength2 parameters to NULL because you usually don't need to receive the length of the null-terminated strings that are returned.

To retrieve the name and description for the remaining data sources, you should call SQLDataSources(), with fDirection set to SQL_FETCH_NEXT, until it returns SQL_NO_DATA_FOUND, which indicates that all entries have been read. By adding the following function at the bottom of your view source file, adding the appropriate function prototype in the view class definition in the view header file, and adding a function call in the instructor, you can display a list of data sources like the one shown in Figure 20.5.

Figure 20.5 Output from the listDataSources() function

// Function to list the data sources written by Chuck Wood
void CODBCTestView::listDataSources() {
    UWORD fDirection = SQL_FETCH_FIRST;
    SQLRETURN sr;
    SQLCHAR szDSN[SQL_MAX_DSN_LENGTH+1];
    SQLCHAR szDescription[100];
    char message[4096];
    strcpy (message, "");        //Initialize message
    do {
        sr = SQLDataSources(hOdbcEnv, fDirection,
            (UCHAR *)szDSN, sizeof(szDSN), NULL,
            (UCHAR *)szDescription,
            sizeof(szDescription), NULL);
        if(sr != SQL_SUCCESS
            && sr != SQL_SUCCESS_WITH_INFO)
            sprintf(message,
               "%s\nSQLDataSources returns: %d\n",
               message, sr);
        else
            sprintf(message, "%sDSN: [%s] Desc: [%s]\n",
                    message, szDSN, szDescription);
        fDirection = SQL_FETCH_NEXT;
    } while(sr == SQL_SUCCESS || sr == SQL_SUCCESS_WITH_INFO);
    MessageBox(message);
}

Retrieving Connection Information

In addition to the connection options, you can access a wide range of information about the ODBC driver and data source associated with an SQLHDBC by using the SQLGetInfo() function:

SQLRETURN SQLGetInfo( SQLHDBC ConnectionHandle, SQLUSMALLINT InfoType,
    SQLPOINTER InfoValuePtr, SQLSMALLINT BufferLength, SQLSMALLINT * StringLengthPtr);

When calling SQLGetInfo(), ConnectionHandle should be a connection handle that was previously attached to a data source. The InfoType parameter is used to specify which piece of information is to be retrieved. For more on the specific values supported, see the online documentation for SQLGetInfo(). For now, let's say that just about anything you might want to know about a driver or a data source is available via this function, including information on conformance levels, support for various functions, and data types supported.

Depending on the value you pass for InfoType, the data returned at InfoValuePtr may take several different formats, including null-terminated strings, 16-bit integers, or 32-bit values. You should set BufferLength to the maximum size of the buffer at InfoValuePtr. On return, the value at StringLengthPtr will contain the actual length of the data returned.

Executing SQL Statements | Next Section

Previous Section