• The SQL...() Functions
  • SQLAllocConnect()
  • SQLAllocEnv()
  • SQLAllocStmt()
  • SQLBindCol()
  • SQLBindParameter()
  • SQLBrowseConnect()
  • SQLCancel()
  • SQLColAttributes()
  • SQLColumnPrivileges()
  • SQLColumns()
  • SQLConnect()
  • SQLDataSources()
  • SQLDescribeCol()
  • SQLDescribeParam()
  • SQLDisconnect()
  • SQLDriverConnect()
  • SQLDrivers()
  • SQLError()
  • SQLExecDirect()
  • SQLExecute()
  • SQLExtendedFetch()
  • SQLFetch()
  • SQLForeignKeys()
  • SQLFreeConnect()
  • SQLFreeEnv()
  • SQLFreeStmt()
  • SQLGetConnectOption()
  • SQLGetCursorName()
  • SQLGetData()
  • SQLGetFunctions()
  • SQLGetInfo()
  • SQLGetStmtOption()
  • SQLGetTypeInfo()
  • SQLMoreResults()
  • SQLNativeSql()
  • SQLNumParams()
  • SQLNumResultCols()
  • SQLParamData()
  • SQLParamOptions()
  • SQLPrepare()
  • SQLPrimaryKeys()
  • SQLProcedureColumns()
  • SQLProcedures()
  • SQLPutData()
  • SQLRowCount()
  • SQLSetConnectOption()
  • SQLSetCursorName()
  • SQLSetPos()
  • SQLSetScrollOptions()
  • SQLSetStmtOption()
  • SQLSpecialColumns()
  • SQLStatistics()
  • SQLTablePrivileges()
  • SQLTables()
  • SQLTransact()
• Using the SQL...() Functions
  • Using a Datasource
  • Handling Errors in SQL...() Statements
  • Quoting Names
  • Getting the Datasource from the User
• Summary

- 3 -
Using Visual C++ Data Access Functions

In Chapter 2, "Understanding MFC's ODBC Database Classes," you learned about the MFC data access objects. These C++ classes were built on the data access functions that are part of the SQL library that interfaces with ODBC. These functions offer a powerful interface to ODBC.

The first part of this chapter is a reference to the SQL...() functions that make up the ODBC SDK 2.x interface. Those functions that were present in the ODBC SDK 1.0 version (such as SQLSetParam()) and that have been deleted in more recent versions of ODBC aren't covered in this chapter. The second part of this chapter presents a set of functions that you can use to access datasets. It generally is more difficult to use the SQL...() functions than to use the MFC database classes described in Chapter 2. However, these functions do offer more flexibility to the programmer who is writing an application that must access many different types of datasources, with differing tables and schema (layout and definitions).

Not withstanding the existing functionality found in the MFC ODBC classes, there is no reason why you can't use both the MFC classes and the SQL...() functions in the same code. The MFC ODBC classes have all the necessary handles to allow usage of the SQL...() functions; in fact, the MFC ODBC classes use the SQL...() functions to perform most of their database manipulation tasks.

The SQL...() Functions

This reference to the SQL...() functions will show virtually everything you need to know in order to use most of these functions. However, some functions are complex and return a substantial amount of information. If you need more information, you can refer to the various help files available (including the MSDN CD set, the ODBC SDK, and Visual C++'s help facility).

---

NOTE

The SQL...() functions haven't changed substantially since their introduction. Minor changes to accommodate 32-bit programming (the original SQL...() functions were 16-bit) account for virtually all the changes found.

---

When you use the SQL...() functions, remember that there is a fixed order of usage. The sample code in the second part of this chapter provides more information about how to use the functions.

Each function is presented with a prototype, function arguments (if any), an explanation of the return value (if any), a description of what the function should do, and an explanation of possible failures that might occur when you use the function. Some functions will fail for a number of reasons. When you encounter a failure, use SQLError() to determine the cause of the failure.

---

NOTE

Using the SQL logging facility is often very useful in determining why an SQL function failed during development. Of course, you shouldn't expect your application's users to have SQL logging turned on.

Don't forget that SQL logging will significantly affect ODBC performance because detailed information is written to the logging file for each and every SQL operation. Don't turn on SQL logging indiscriminately; use it when you need it and then turn it off.

---

In all cases where the return value is shown as type RETCODE, you should create a variable defined as this type to hold the return code. You can then use either an if() or a switch() statement to check the return code for errors.

---

NOTE

There are two versions of the ODBC SDK 2.1: 2.10a and 2.10b. You should use 2.10b, which was released in August of 1995, if you're using Visual C++ 2.x. The version of ODBC that is included with Visual C++ 4.0 is 2.5. Version 2.5 is intended for use on both Windows 95 and on Windows NT versions 3.5 and 3.51.

---

---

NOTE

The current version of the ODBC SDK is 2.5, which is included with both Visual C++ 4 and Visual C++ 1.5x. Microsoft hasn't announced whether (or when) further updates to the ODBC SDK will occur. It can be assumed that new versions of Visual C++ may well include new versions of ODBC.

---

SQLAllocConnect()

Prototype:

RETCODE SQLAllocConnect(HENV henv, HDBC FAR * phdbc)

Parameters:

Return Value:

This function will return one of the following values:

If this function fails, your SQL function should end or the error should be corrected; the function should then be re-executed.

Usage:

The SQLAllocConnect() function is used to allocate the connection between the application and the datasource. It's called after a call to SQLAllocEnv(), and SQLAllocStmt() is called after a call to SQLAllocEnv().

You must call the SQLAllocConnect() function after getting an HENV handle from SQLAllocEnv(). Without a valid HENV handle, this function won't succeed. Always check the return code from this function for errors.

Notes:

The function's results are placed in the handle pointed to by the phdbc parameter. If the SQLAllocConnect() function fails, the phdbc handle will be set to SQL_NULL_HDBC.

SQLAllocEnv()

Prototype:

RETCODE SQLAllocEnv(HENV FAR * phenv)

Parameter:

\Return Value:

This function will return one of the following values:

If this function fails, your SQL function should end or the error should be corrected; the function should then be re-executed. Often, this function is the first SQL...() function that is called. If it fails, there may be no recovery.

Usage:

Call the SQLAllocEnv() function to initialize the SQL environment. You should make a matching SQLFreeEnv() call when your calling function has finished accessing the datasource. Always check the return code from these functions for errors.

Notes:

This function places the HENV in the supplied handle. If SQLAllocEnv() fails, the resultant phenv parameter is set to SQL_NULL_HENV.

SQLAllocStmt()

Prototype:

RETCODE SQLAllocStmt(HDBC hdbc, HSTMT FAR * hstmt)

Parameters:

Return Value:

This function will return one of the following values:

If this function fails, your SQL function should end or the error should be corrected; the function should then be re-executed.

Usage:

The SQLAllocStmt() function is used to allocate a statement handle. This statement handle is associated with the datasource to which the HDBC handle was connected.

Notes:

If this function fails, the returned HSTMT handle will be set to SQL_NULL_HSTMT.

SQLBindCol()

Prototype:

RETCODE SQLBindCol(HSTMT hstmt, UWORD icol, SWORD fCType,

PTR rbgValue, SDWORD cbValueMax, SDWORD FAR * pcbValue)

Parameters:

Return Value:

This function will return one of the following values:

If this function fails, your SQL function should end or the error should be corrected; the function should then be re-executed.

Usage:

Call the SQLBindCol() function only for the columns in a table that you need. You don't have to bind to every column in a table. Columns that don't have a variable bound to them will be discarded without error.

You make a call—usually in a loop—to SQLFetch() or SQLExtendedFetch() to actually get the data from a record.

Always check the return code from this function for errors.

Notes:

If this function fails, use the SQLError() function to find out why. When a column hasn't been bound and later must be accessed, use SQLGetData().

SQLBindParameter()

Prototype:

RETCODE SQLBindParameter(HSTMT hstmt, UWORD ipar, SWORD fParamType,

SWORD fCType, SWORD fSqlType, UDWORD cbColDef,

SWORD ibScale, PTR rgbValue, SDWORD cbValueMax, SDWORD pcbValue)

Parameters:

Return Value:

This function will return one of the following values:

If this function fails, your SQL function should end or the error should be corrected; the function should then be re-executed.

Usage:

Call the SQLBindParameter() function to bind a buffer to a parameter marker in an SQL statement. Always check the return code from this function for errors.

Notes:

If this function fails, use the SQLError() function to find out why. This function replaces the SQLSetParam() function found in ODBC version 1.x.

SQLBrowseConnect()

Prototype:

RETCODE SQLBrowseConnect(HDBC hdbc, UCHAR FAR * szConnStrIn, SWORD cbConnStrIn,

UCHAR FAR * szConnStrOut, SWORD cbConnStrOutMax, SWORD FAR * pcbConnStrOut)

Parameters:

Return Value:

This function will return one of the following values:

If this function fails, your SQL function should end or the error should be corrected; the function should then be re-executed.

Usage:

Call the SQLBrowseConnect() function to enumerate the attributes of a specific datasource. Always check the return code from this function for errors, making additional calls as necessary to gather the desired information to establish the connection.

Notes:

When a return code of either SQL_SUCCESS or SQL_SUCCESS_WITH_INFO is returned, your application will know that the enumeration process has completed and the application is connected to the datasource.

SQLCancel()

Prototype:

RETCODE SQLCancel (HSTMT hstmt)

Parameter:

Return Value:

This function will return one of the following values:

If this function fails, your SQL function should end or the error should be corrected; the function should then be re-executed.

Usage:

Call the SQLCancel() function to cancel an asynchronous operation pending on the parameter statement indicated by hstmt. Always check the return code from this function for errors.

Notes:

You can cancel functions running on hstmt that are running on other threads. You also can cancel functions on hstmt that require more data.

SQLColAttributes()

Prototype:

RETCODE SQLColAttributes (HSTMT hstmt, UWORD icol, UWORD fDescType,

PTR rgbDesc, SWORD cbDescMax, SWORD FAR * pcbDesc, SWORD FAR * pfDesc)

Parameters:

Return Value:

This function will return one of the following values:

If this function fails, your SQL function should end or the error should be corrected; the function should then be re-executed.

Usage:

Call the SQLColAttributes() function to gather information about a column in a table. Always check the return code from this function for errors.

Notes:

Table 3.1 shows the information that will be returned for columns.

SQLColumnPrivileges()

Prototype:

RETCODE SQLColumnPrivileges(HSTMT hstmt, UCHAR FAR * szTableQualifier,

SWORD cbTableQualifier, UCHAR FAR * szTableOwner, SWORD cbTableOwner,

UCHAR FAR * szTableName, SWORD cbTableName, UCHAR FAR * szColumnName,

SWORD cbColumnName)

Parameters:

Return Value:

This function will return one of the following values:

If this function fails, your SQL function should end or the error should be corrected; the function should then be re-executed.

Usage:

Call the SQLColumnPrivileges() function to obtain a list of columns and privileges for the specified table. Always check the return code from this function for errors.

Notes:

The information is returned as a result set.

SQLColumns()

Prototype:

RETCODE SQLColumns(HSTMT hstmt, UCHAR FAR * szTableQualifier,

SWORD cbTableQualifier, UCHAR FAR * szTableOwner,

SWORD cbTableOwner, UCHAR FAR * szTableName, SWORD cbTableName,

UCHAR FAR * szColumnName, SWORD cbColumnName)

Parameters:

Return Value:

This function will return one of the following values:

If this function fails, your SQL function should end or the error should be corrected; the function should then be re-executed.

Usage:

Call the SQLColumns() function obtain a list of the columns in a specified table. Always check the return code from this function for errors.

Notes:

The results are returned as a result set. The columns returned in the result set are shown in Table 3.2.

SQLConnect()

Prototype:

RETCODE SQLConnect(HDBC hdbc, UCHAR FAR * szDSN, SWORD cbDSN,

UCHAR FAR * szUID, SWORD cbUID, UCHAR FAR * szAuthStr, SWORD cbAuthStr)

Parameters:

Return Value:

This function will return one of the following values:

If this function fails, your SQL function should end or the error should be corrected; the function should then be re-executed.

Usage:

Call the SQLConnect() function to establish a connection between the application and a specific datasource. Always check the return code from this function for errors.

Notes:

The SQLConnect() function tells ODBC to load the driver in preparation for using the datasource.

SQLDataSources()

Prototype:

RETCODE SQL (HENV henv, UWORD fDirection, UCHAR FAR * szDSN,

SWORD cbDSNMax, SWORD FAR * pcbDSN, UCHAR FAR * szDescription,

SWORD cbDescriptionMax, SWORD FAR * pcbDescription)

Parameters:

Return Value:

This function will return one of the following values:

If this function fails, your SQL function should end or the error should be corrected; the function should then be re-executed.

Usage:

Call the SQLDataSources() function to enumerate a list of the currently installed datasources. Always check the return code from this function for errors.

Notes:

You should call the SQLDataSources() function in a loop while the application checks the return code. When the return code is SQL_NO_DATA_FOUND, then all the datasources have been enumerated.

SQLDescribeCol()

Prototype:

RETCODE SQL (HSTMT hstmt, UWORD icol, UCHAR FAR * szColName,

SWORD cbColNameMax, SWORD FAR * pcbColName,

SWORD FAR * pfSqlType, UDWORD FAR * pcbColDef,

SWORD FAR * pibScale, SWORD FAR * pfNullable)

Parameters:

Return Value:

This function will return one of the following values:

If this function fails, your SQL function should end or the error should be corrected; the function should then be re-executed.

Usage:

Call the SQLDescribeCol() function to get information about a specific column in a datasource. Always check the return code from this function for errors.

Notes:

If this function fails, use the SQLError() function to find out why.

SQLDescribeParam()

Prototype:

RETCODE SQLDescribeParam(HSTMT hstmt, UWORD ipar,

SWORD FAR * pfSqlType, UWORD FAR * pcbColDef,

SWORD FAR * pibScale, SWORD FAR * pfNullable)

Parameters:

Return Value:

This function will return one of the following values:

If this function fails, your SQL function should end or the error should be corrected; the function should then be re-executed.

Usage:

Call the SQLDescribeParam() function to obtain a description of the parameter marker in an SQL statement. Always check the return code from this function for errors.

Notes:

If this function fails, use the SQLError() function to find out why.

SQLDisconnect()

Prototype:

RETCODE SQLDisconnect(HDBC hdbc)

Parameter:

Return Value:

This function will return one of the following values:

If this function fails, your SQL function should end or the error should be corrected; the function should then be re-executed.

Usage:

Call the SQLDisconnect() function to disconnect from the currently connected datasource. Always check the return code from this function for errors.

Notes:

If this function fails, use the SQLError() function to find out why.

SQLDriverConnect()

Prototype:

RETCODE SQLDriverConnect(HDBC hdbc, HWND hwnd, UCHAR FAR * szConnStrIn,

SWORD cbConnStrIn, UCHAR FAR * szConnStrOut, SWORD cbConnStrOutMax,

SWORD FAR * pcbConnStrOut, UWORD fDriverCompletion)

Parameters:

Return Value:

This function will return one of the following values:

If this function fails, your SQL function should end or the error should be corrected; the function should then be re-executed.

Usage:

Call the SQLDriverConnect() function to connect to a specific driver. Always check the return code from this function for errors.

Notes:

If this function fails, use the SQLError() function to find out why.

SQLDrivers()

Prototype:

RETCODE SQLDrivers(HENV henv, UWORD fDirection, UCHAR FAR * szDriverDesc,

SWORD cbDriverDescMax, SWORD FAR * pcbDriverDesc,

UCHAR fAR * szDriverAttributes, SWORD cbDrvrAttrMax, SWORD FAR * pcbDrvrAttr)

Parameters:

Return Value:

This function will return one of the following values:

If this function fails, your SQL function should end or the error should be corrected; the function should then be re-executed.

Usage:

Call the SQLDrivers() function to list the drivers and driver attributes. Always check the return code from this function for errors.

Notes:

If this function fails, use the SQLError() function to find out why.

SQLError()

Prototype:

RETCODE SQLError(HENV henv, HDBC hdbc, HSTMT hstmt,

UCHAR FAR * szSqlState, SDWORD FAR * pfNativeError,

UCHAR FAR * szErrorMsg, SWORD cbErrorMsgMax, SWORD FAR * pcbErrorMsg)

Parameters:

Return Value:

This function will return one of the following values:

If this function fails, your SQL function should try to recover—displaying or saving whatever diagnostic information is appropriate—or the error should be corrected and the function should be re-executed.

Usage:

Call the SQLError() function to obtain more information about the error condition. Always check the return code from this function for errors.

Notes:

If this function fails, use the SQLError() function to find out why.

SQLExecDirect()

Prototype:

RETCODE SQLExecDirect(HSTMT hstmt, UCHAR FAR * szSqlStr, SDWORD cbSqlStr)

Parameters:

Return Value:

This function will return one of the following values:

If this function fails, your SQL function should end or the error should be corrected; the function should then be re-executed.

Usage:

Call the SQLExecDirect() function to execute (usually once) a preparable SQL statement. Always check the return code from this function for errors.

Notes:

This function is probably the fastest way to execute SQL statements when the statement needs to be executed only one time. If this function fails, use the SQLError() function to find out why.

SQLExecute()

Prototype:

RETCODE SQLExecute(HSTMT hstmt)

Parameter:

Return Value:

This function will return one of the following values:

If this function fails, your SQL function should end or the error should be corrected; the function should then be re-executed.

Usage:

Call the SQLExecute() function to execute a prepared statement using the parameter values contained in marker variables, if there are any. Always check the return code from this function for errors.

Notes:

If this function fails, use the SQLError() function to find out why.

SQLExtendedFetch()

Prototype:

RETCODE SQLExtenedeFetch(HSTMT hstmt, UWORD fFetchType,

SDWORD irow, UDWORD FAR * pcrow, UWORD FAR * rgfRowStatus)

Parameters:

Return Value:

This function will return one of the following values:

If this function fails, your SQL function should end or the error should be corrected; the function should then be re-executed.

Usage:

Call the SQLExtendedFetch() function to fetch one or more rows from a result set. Always check the return code from this function for errors.

Notes:

If this function fails, use the SQLError() function to find out why.

SQLFetch()

Prototype:

RETCODE SQLFetch(HSTMT hstmt)

Parameter:

Return Value:

This function will return one of the following values:

If this function fails, your SQL function should end or the error should be corrected; the function should then be re-executed.

Usage:

Call the SQLFetch() function to fetch a single row of data from the result set. Always check the return code from this function for errors.

Notes:

If this function fails, use the SQLError() function to find out why.

SQLForeignKeys()

Prototype:

RETCODE SQLForeignKeys(HSTMT hstmt,

UCHAR FAR * szPkTableQualifier, SWORD cbPkTableQualifier,

UCHAR FAR * szPkTableOwner, SWORD cbPkTableOwner,

UCHAR FAR * szPkTableName, SWORD cbPkTableName,

UCHAR FAR * szFkTableQualifier, SWORD cbFkTableQualifier,

UCHAR FAR * szFkTableOwner, SWORD cbFkTableOwner,

UCHAR FAR * szFkTableName, SWORD cbFkTableName)

Parameters:

Return Value:

This function will return one of the following values:

If this function fails, your SQL function should end or the error should be corrected; the function should then be re-executed.

Usage:

Call the SQLForeignKeys() function to return either a list of foreign keys in the specified table or a list of foreign keys in other tables that refer to the primary key in the specified table. Always check the return code from this function for errors.

Notes:

If this function fails, use the SQLError() function to find out why.

SQLFreeConnect()

Prototype:

RETCODE SQLFreeConnect(HDBC hdbc)

Parameter:

Return Value:

This function will return one of the following values:

SQL_SUCCESS The function was successful.

SQL_SUCCESS_WITH_INFO The function was successful, and more information is available.

SQL_ERROR The function failed. Call SQLError() to get more information about the specific failure.

SLQ_INVALID_HANDLE The function failed. The handle that was passed wasn't a valid handle. Possibly, the function that created the handle had failed and didn't return a valid handle.

If this function fails, your SQL function should end or the error should be corrected; the function should then be re-executed.

Usage:

Call the SQLFreeConnect() function to release the connection established with the SQLAllocConnect() function. Always check the return code from this function for errors.

Notes:

If this function fails, use the SQLError() function to find out why.

SQLFreeEnv()

Prototype:

RETCODE SQL (HENV henv)

Parameter:

Return Value:

This function will return one of the following values:

If this function fails, your SQL function should end or the error should be corrected; the function should then be re-executed.

Usage:

Call the SQLFreeEnv() function to free the environment established by the SQLAllocEnv() function. Always check the return code from this function for errors.

Notes:

If this function fails, use the SQLError() function to find out why.

SQLFreeStmt()

Prototype:

RETCODE SQLFreeStmt(HSTMT hstmt, UWORD fOption)

Parameters:

Return Value:

This function will return one of the following values:

If this function fails, your SQL function should end or the error should be corrected; the function should then be re-executed.

Usage:

Call the SQLFreeStmt() function to free the statement handle allocated by the SQLAllocStmt() function. Always check the return code from this function for errors.

Notes:

If this function fails, use the SQLError() function to find out why.

SQLGetConnectOption()

Prototype:

RETCODE SQL (HDBC hdbc, UWORD fOption, PTR pvParam)

Parameters:

Return Value:

This function will return one of the following values:

If this function fails, your SQL function should end or the error should be corrected; the function should then be re-executed.

Usage:

Call the SQLGetConnectOption() function to get the settings for the current connection. Always check the return code from this function for errors.

Notes:

If this function fails, use the SQLError() function to find out why.

SQLGetCursorName()

Prototype:

RETCODE SQLCursorName(HSTMT hstmt, UCHAR FAR * szCursor,

SWORD cbCursorMax, SWORD FAR * pcbCursor)

Parameters:

Return Value:

This function will return one of the following values:

If this function fails, your SQL function should end or the error should be corrected; the function should then be re-executed.

Usage:

Call the SQLGetCursorName() function to get the name for the cursor specified by the hstmt parameter. Always check the return code from this function for errors.

Notes:

If this function fails, use the SQLError() function to find out why.

SQLGetData()

Prototype:

RETCODE SQLGetData(HSTMT hstmt, UWORD icol, SWORD fCType,

PTR rgbValue, SDWORD cbValueMax, SDWORD FAR * pcbValue)

Parameters:

Drivers must support the final three C data type values listed in Table 3.8 from ODBC 1.0. Applications must use these values, rather than the ODBC 2.0 values, when calling an ODBC 1.0 driver.

Return Value:

This function will return one of the following values:

If this function fails, your SQL function should end or the error should be corrected; the function should then be re-executed.

Usage:

Call the SQLGetData() function to obtain information about a specific column in a datasource. Always check the return code from this function for errors.

Notes:

If this function fails, use the SQLError() function to find out why.

SQLGetFunctions()

Prototype:

RETCODE SQLGetFunctions(HDBC hdbc, UWORD fFunction, UWORD FAR * pfExists)

Parameters:

Return Value:

This function will return one of the following values:

If this function fails, your SQL function should end or the error should be corrected; the function should then be re-executed.

Usage:

Call the SQLGetFunctions() function to obtain information about other SQL...() functions. Always check the return code from this function for errors.

Notes:

If this function fails, use the SQLError() function to find out why. The functions supported are shown in Table 3.10.

SQLGetInfo()

Prototype:

RETCODE SQLGetInfo(HDBC hdbc, UWORD fInfoType, PTR rgbInfoValue,

SWORD cbInfoValueMax, SWORD FAR * pcbInfoValue)

Parameters:

Return Value:

This function will return one of the following values:

If this function fails, your SQL function should end or the error should be corrected; the function should then be re-executed.

Usage:

The SQLGetInfo() function returns a vast array of information about ODBC. Always check the return code from this function for errors.

Notes:

Refer to the documentation supplied with ODBC and Visual C++ for more information about this function. If this function fails, use the SQLError() function to find out why.

SQLGetStmtOption()

Prototype:

RETCODE SQLGetStmtOption(HSTMT hstmt, UWORD fOption, PTR pvParam)

Parameters:

Return Value:

This function will return one of the following values:

If this function fails, your SQL function should end or the error should be corrected; the function should then be re-executed.

Usage:

Call the SQLGetStmtOption() function to retrieve information about the specified statement. Always check the return code from this function for errors.

Notes:

If this function fails, use the SQLError() function to find out why.

SQLGetTypeInfo()

Prototype:

RETCODE SQLGetTypeInfo(HSTMT hstmt, SWORD fSqlType)

Parameters:

Return Value:

This function will return one of the following values:

If this function fails, your SQL function should end or the error should be corrected; the function should then be re-executed.

Usage:

Call the SQLGetTypeInfo() function to return information about a specific data type. Always check the return code from this function for errors.

Notes:

This functions returns the results as a result set. If this function fails, use the SQLError() function to find out why.

SQLMoreResults()

Prototype:

RETCODE SQLMoreResults(HSTMT hstmt)

Parameter:

Return Value:

This function will return one of the following values:

If this function fails, your SQL function should end or the error should be corrected; the function should then be re-executed.

Usage:

Call the SQLMoreResults() function to determine whether there are more results available from the SELECT, UPDATE, INSERT, or DELETE SQL statements. If there are more results, SQLMoreResults() will initiate processing for the additional results. Always check the return code from this function for errors.

Notes:

If this function fails, use the SQLError() function to find out why.

SQLNativeSql()

Prototype:

RETCODE SQLNativeSql(HDBC hdbc, UCHAR FAR * szSqlStrIn,

SDWORD cbSqlStrIn, UCHAR FAR * szSqlStr, SDWORD cbSqlStrMax,

SDWORD FAR * pcbSqlStr)

Parameters:

Return Value:

This function will return one of the following values:

If this function fails, your SQL function should end or the error should be corrected; the function should then be re-executed.

Usage:

Call the SQLNativeSql() function to translate an SQL string for a native ODBC driver. Always check the return code from this function for errors.

Notes:

If this function fails, use the SQLError() function to find out why.

SQLNumParams()

Prototype:

RETCODE SQLNumParams(HSTMT hstmt, SWORD FAR * pcpar)

Parameters:

Return Value:

This function will return one of the following values:

If this function fails, your SQL function should end or the error should be corrected; the function should then be re-executed.

Usage:

Call the SQLNumParams() function to get the number of parameters in the SQL statement. Always check the return code from this function for errors.

Notes:

If this function fails, use the SQLError() function to find out why.

SQLNumResultCols()

Prototype:

RETCODE SQLNumResultCols(HSTMT hstmt, SWORD FAR * pccol)

Parameters:

Return Value:

This function will return one of the following values:

If this function fails, your SQL function should end or the error should be corrected; the function should then be re-executed.

Usage:

Call the SQLNumResultCols() function to find out how many columns are in the result set. Always check the return code from this function for errors.

Notes:

If this function fails, use the SQLError() function to find out why.

SQLParamData()

Prototype:

RETCODE SQLParamData(HSTMT hstmt, PTR FAR * prgbValue)

Parameters:

Return Value:

This function will return one of the following values:

If this function fails, your SQL function should end or the error should be corrected; the function should then be re-executed.

Usage:

Call the SQLParamData() function with SQLPutData() to supply parameter data at statement execution time. Always check the return code from this function for errors.

Notes:

If this function fails, use the SQLError() function to find out why.

SQLParamOptions()

Prototype:

RETCODE SQLParamOptions(HSTMT hstmt, UWORD crow, UWORD FAR * pirow)

Parameters:

Return Value:

This function will return one of the following values:

If this function fails, your SQL function should end or the error should be corrected; the function should then be re-executed.

Usage:

Call the SQLParamOptions() function to specify values for parameters created with SQLBindParameter(). Always check the return code from this function for errors.

Notes:

If this function fails, use the SQLError() function to find out why.

SQLPrepare()

Prototype:

RETCODE SQLPrepare(HSTMT hstmt, UCHAR FAR * szSqlStr, SDWORD cbSqlStr)

Parameters:

Return Value:

This function will return one of the following values:

If this function fails, your SQL function should end or the error should be corrected; the function should then be re-executed.

Usage:

Call the SQLPrepare() function to prepare an SQL string for execution. Always check the return code from this function for errors.

Notes:

If this function fails, use the SQLError() function to find out why.

SQLPrimaryKeys()

Prototype:

RETCODE SQLPrimaryKeys(HSTMT hstmt, UCHAR FAR * szTableQualifier,

SWORD cbTableQualifier, UCHAR FAR * szTableOwner, SWORD cbTableOwner,

UCHAR FAR * szTableName, SWORD cbTableName)

Parameters:

Return Value:

This function will return one of the following values:

If this function fails, your SQL function should end or the error should be corrected; the function should then be re-executed.

Usage:

Call the SQLPrimaryKeys() function to retrieve the column names that comprise the primary key for the specified table. Always check the return code from this function for errors.

Notes:

If this function fails, use the SQLError() function to find out why.

SQLProcedureColumns()

Prototype:

RETCODE SQLProcedureColumns(HSTMT hstmt, UCHAR FAR * szProcQualifier,

SWORD cbProcQualifier, UCHAR FAR * szProcOwner, SWORD cbProcOwner,

UCHAR FAR * szProcName, SWORD cbProcName, UCHAR FAR * szColumnName,

SWORD cbColumnName)

Parameters:

Return Value:

This function will return one of the following values:

If this function fails, your SQL function should end or the error should be corrected; the function should then be re-executed.

Usage:

Call the SQLProcedureColumns() function to obtain a list of input and output parameters for the specified procedure. This function will also retrieve the columns that make up the result set for this procedure. Always check the return code from this function for errors.

Notes:

If this function fails, use the SQLError() function to find out why.

SQLProcedures()

Prototype:

RETCODE SQLProcedures(HSTMT hstmt, UCHAR FAR * szProcQualifier,

SWORD cbProcQualifier, UCHAR FAR * szProcOwner, SWORD cbProcOwner,

UCHAR FAR * szProcName, SWORD cbProcName)

Parameters:

Return Value:

This function will return one of the following values:

If this function fails, your SQL function should end or the error should be corrected; the function should then be re-executed.

Usage:

Call the SQLProcedures() function to retrieve a list of all the procedure names stored in the specified datasource. Always check the return code from this function for errors.

Notes:

If this function fails, use the SQLError() function to find out why.

SQLPutData()

Prototype:

RETCODE SQLPutData(HSTMT hstmt, PTR rgbValue, SDWORD cbValue)

Parameters:

Return Value:

This function will return one of the following values:

If this function fails, your SQL function should end or the error should be corrected; the function should then be re-executed.

Usage:

Call the SQLPutData() function to send data for a parameter or column to the driver at execution time. Always check the return code from this function for errors.

Notes:

If this function fails, use the SQLError() function to find out why.

SQLRowCount()

Prototype:

RETCODE SQLRowCount(HSTMT hstmt, SDWORD FAR * pcrow)

Parameters:

Return Value:

This function will return one of the following values:

If this function fails, your SQL function should end or the error should be corrected; the function should then be re-executed.

Usage:

Call the SQLRowCount() function to determine how many rows were affected by an UPDATE, DELETE, or INSERT, or by an SQL_UPDATE, SQL_DELETE, or SQL_ADD operation. Always check the return code from this function for errors.

Notes:

If this function fails, use the SQLError() function to find out why.

SQLSetConnectOption()

Prototype:

RETCODE SQLSetConnectOption(HDBC hdbc, UWORD fOption, UDWORD vParam)

Parameters:

Return Value:

This function will return one of the following values:

If this function fails, your SQL function should end or the error should be corrected; the function should then be re-executed.

Usage:

Call the SQLSetConnectOption() function to set connection options. Always check the return code from this function for errors.

Notes:

The SQLSetConnectOption() has many valid parameter values, which are detailed in the ODBC documentation. If this function fails, use the SQLError() function to find out why.

SQLSetCursorName()

Prototype:

RETCODE SQLSetCursorName(HSTMT hstmt, UCHAR FAR * szCursor, SWORD cbCursor)

Parameters:

Return Value:

This function will return one of the following values:

If this function fails, your SQL function should end or the error should be corrected; the function should then be re-executed.

Usage:

Call the SQLSetCursorName() function to associate a name with the specified hstmt. Always check the return code from this function for errors.

Notes:

If your application doesn't set cursor names, the driver will automatically generate default cursor names. If this function fails, use the SQLError() function to find out why.

SQLSetPos()

Prototype:

RETCODE SQLSetPos(HSTMT hstmt, UWORD irow, UWORD fOption, UWORD fLock)

Parameters:

Return Value:

This function will return one of the following values:

If this function fails, your SQL function should end or the error should be corrected; the function should then be re-executed.

Usage:

Call the SQLSetPos() function to set the cursor position in a result set. Always check the return code from this function for errors.

Notes:

If this function fails, use the SQLError() function to find out why.

SQLSetScrollOptions()

Prototype:

RETCODE SQLSetScrollOptions(HSTMT hstmt, UWORD fConcurrency,

UWORD crowKeyset, UWORD crowRowset)

Parameters:

Return Value:

This function will return one of the following values:

If this function fails, your SQL function should end or the error should be corrected; the function should then be re-executed.

Usage:

The SQLSetScrollOptions() function should be used only with ODBC 1.x drivers. For ODBC 2, use SQLSetStmtOption(). Always check the return code from this function for errors.

Notes:

If this function fails, use the SQLError() function to find out why.

SQLSetStmtOption()

Prototype:

RETCODE SQLSetStmtOption(HSTMT hstmt, UWORD fOption, UDWORD vParam)

Parameters:

Return Value:

This function will return one of the following values:

If this function fails, your SQL function should end or the error should be corrected; the function should then be re-executed.

Usage:

Call the SQLSetStmtOption() function to set statement options. Always check the return code from this function for errors.

Notes:

If this function fails, use the SQLError() function to find out why.

SQLSpecialColumns()

Prototype:

RETCODE SQLSpecialColumns(HSTMT hstmt, UWORD fColType,

UCHAR FAR * szTableQualifier, SWORD cbTableQualifier,

UCHAR FAR * szTableOwner, SWORD cbTableOwner,

UCHAR FAR * szTableName, SWORD cbTableName,

UWORD fScope, UWORD fNullable)

Parameters:

Return Value:

This function will return one of the following values:

If this function fails, your SQL function should end or the error should be corrected; the function should then be re-executed.

Usage:

Call the SQLSpecialColumns() function to retrieve information about the optimal set of columns that will uniquely identify a row in a table, or columns that are automatically updated when a value in the row has been updated by transactions. Always check the return code from this function for errors.

Notes:

If this function fails, use the SQLError() function to find out why.

SQLStatistics()

Prototype:

RETCODE SQLStatistics(HSTMT hstmt, UCHAR FAR * szTableQualifier,

SWORD cbTableQualifier, UCHAR FAR * szTableOwner, SWORD cbTableOwner,

UCHAR FAR * szTableName, SWORD cbTableName, UWORD fUnique, UWORD fAccuracy)

Parameters:

Return Value:

This function will return one of the following values:

If this function fails, your SQL function should end or the error should be corrected; the function should then be re-executed.

Usage:

Call the SQLStatistics() function to retrieve a list of statistics regarding a specified single table. Always check the return code from this function for errors.

Notes:

If this function fails, use the SQLError() function to find out why.

SQLTablePrivileges()

Prototype:

RETCODE SQLTablePrivileges(HSTMT hstmt,

UCHAR FAR * szTableQualifier, SWORD cbTableQualifier,

UCHAR FAR * szTableOwner, SWORD cbTableOwner,

UCHAR FAR * szTableName, SWORD cbTableName)

Parameters:

Return Value:

This function will return one of the following values:

If this function fails, your SQL function should end or the error should be corrected; the function should then be re-executed.

Usage:

Call the SQLTablePrivileges() function to return a list of tables and privileges for each table in the list. Always check the return code from this function for errors.

Notes:

The SQLTablePrivileges() function returns the results in the form of a result set. If this function fails, use the SQLError() function to find out why.

SQLTables()

Prototype:

RETCODE SQLTables(HSTMT hstmt,

UCHAR FAR * szTableQualifier, SWORD cbTableQualifier,

UCHAR FAR * szTableOwner, SWORD cbTableOwner,

UCHAR FAR * szTableName, SWORD cbTableName,

UCHAR FAR * szTableType, SWORD cbTableType)

Parameters:

Return Value:

This function will return one of the following values:

If this function fails, your SQL function should end or the error should be corrected; the function should then be re-executed.

Usage:

Call the SQLTables() function to obtain a list of tables in the datasource. Always check the return code from this function for errors.

Notes:

If this function fails, use the SQLError() function to find out why.

SQLTransact()

Prototype:

RETCODE SQLTransact(HENV henv, HDBC hdbc, UWORD fType)

Parameters:

Return Value:

This function will return one of the following values:

If this function fails, your SQL function should end or the error should be corrected; the function should then be re-executed.

Usage:

Call the SQLTransact() function to commit or rollback the transaction. Always check the return code from this function for errors.

Notes:

If this function fails, use the SQLError() function to find out why.

Using the SQL...() Functions

In the first part of this chapter, you learned about the library of SQL...() functions. These functions are usable in both C and C++ applications. However, it's possible to write a set of wrapper functions around some of the more commonly used sets of SQL...() commands.

The second part of this chapter shows how to use the SQL...() functions and presents a few useful functions (which t can be called from both C and C++ programs) that use these functions.

Using a Datasource

The first example is a simple function that accesses a table, determines what columns are in the accessed table, and then fetches data from the datasource. Although this routine is a simple implementation, it can form the basis of a number of useful applications.

First, look at the sequence of operation in accessing an SQL datasource. Unlike most other statements, the SQL...() statements require a rather fixed sequence of execution. You simply can't call the SQLExecute() function without first setting up the SQL environment. The most basic part of any data access application is the initialization of the SQL environment.

Listing 3.1 shows a simple function that initializes the ODBC SQL environment. This function gets the names of the columns in the datasource that the user selects and then fetches the first four columns in this datasource. The routine would be better if it were to check to make sure that there were more than four columns in the datasource. I also have coded checks for errors, but I didn't call error handlers in this example. A later example shows a simple error handler routine that can be used by most ODBC functions to display information to the developer and the user.

Listing 3.1. INITODBC.C: A simple function that initializes the ODBC SQL environment.

//  INITODBC.C

#include "windows.h"

#include "odbcmisc.h"

#include "sql.h"

#include "sqlext.h"

#include <stdio.h>

#include <stdlib.h>

#include <string.h>

#include <ctype.h>

/******************************************************************************

**

**      TITLE: ODBC1.c

**

**   FUNCTION: Open DataBase Connectivity interface code

**

**     INPUTS: VARIOUS

**

**    OUTPUTS: VARIOUS

**

**    RETURNS: YES

**

**      CALLS: ODBC routines: SQL...()

**

**     AUTHOR: Peter D. Hipson

**

**   COPYRIGHT 1995 BY PETER D. HIPSON, All rights reserved.

**

******************************************************************************/

// Static, for this module only:

// Routines:

void SimpleODBC(HWND hWnd)

{

#define STR_LEN 128+1

#define REM_LEN 254+1

/* Declare storage locations for result set data */

UCHAR  szQualifier[STR_LEN], szOwner[STR_LEN];

UCHAR  szTableName[STR_LEN], szColName[STR_LEN];

UCHAR  szTypeName[STR_LEN], szRemarks[REM_LEN];

SDWORD Precision, Length;

SWORD  DataType, Scale, Radix, Nullable;

/* Declare storage locations for bytes available to return */

SDWORD cbQualifier, cbOwner, cbTableName, cbColName;

SDWORD cbTypeName, cbRemarks, cbDataType, cbPrecision;

SDWORD cbLength, cbScale, cbRadix, cbNullable;

char    szSource[60];

char    szDirectory[132];

char    szTable[60];

//      Keep above, delete below...

char    szDSN[256];

char    szConStrOut[256];

char    szBuffer[513];

char    szColumn1[128];

char    szColumn2[128];

char    szColumn3[128];

char    szColumn4[128];

int             i;

int        j;

HENV    henv;

HDBC    hdbc;

HSTMT    hstmt = SQL_NULL_HSTMT;

RETCODE RC;

int             nConStrOut;

SDWORD  sdReturn;

SWORD    swReturn;

//  Keep this line:

    szSource[0] = '\0';

    szTable[0] = '\0';

    szDirectory[0] = '\0';

//  The GetODBC() function returns the ODBC source

//  table and directory that the user selects.

    GetODBC(

        szSource, sizeof(szSource),

        szTable, sizeof(szTable),

        szDirectory, sizeof(szDirectory));

    SQLAllocEnv(&henv);

    SQLAllocConnect(henv, &hdbc);

    RC = SQLDriverConnect(hdbc, hWnd,

        (unsigned char far *)szDSN, SQL_NTS,

        (unsigned char far *)szConStrOut, sizeof(szConStrOut),

        (short far *)&nConStrOut, SQL_DRIVER_COMPLETE);

    if (RC != SQL_SUCCESS && RC != SQL_SUCCESS_WITH_INFO)

    {// Call whatever error handler your application uses

    }

    else

    {// Connect was successful. Just continue in most cases

    }

    RC = SQLAllocStmt(hdbc, &hstmt);

    if (RC != SQL_SUCCESS && RC != SQL_SUCCESS_WITH_INFO)

    {// Could not allocate the statement! Call an error handler:

    }

//  Get the DBMS version string. Just for our information, it is not used.

    SQLGetInfo(hdbc, SQL_DBMS_VER, szConStrOut,

        sizeof(szConStrOut), &swReturn);

//  Get the columns in the specified table:

    RC = SQLColumns(hstmt,

        NULL, 0,    // All qualifiers

        NULL, 0,    // All owners

        szTable, SQL_NTS,       // The table!

        NULL, 0);       // All columns

    if (RC != SQL_SUCCESS && RC != SQL_SUCCESS_WITH_INFO)

    {// Could not determine columns! Call an error handler:

    }

//    Now bind variables to columns!

    SQLBindCol(hstmt, 1,  SQL_C_CHAR,   szQualifier, STR_LEN,&cbQualifier);

    SQLBindCol(hstmt, 2,  SQL_C_CHAR,   szOwner, STR_LEN, &cbOwner);

    SQLBindCol(hstmt, 3,  SQL_C_CHAR,   szTableName, STR_LEN,&cbTableName);

    SQLBindCol(hstmt, 4,  SQL_C_CHAR,   szColName, STR_LEN, &cbColName);

    SQLBindCol(hstmt, 5,  SQL_C_SSHORT, &DataType, 0, &cbDataType);

    SQLBindCol(hstmt, 6,  SQL_C_CHAR,   szTypeName, STR_LEN, &cbTypeName);

    SQLBindCol(hstmt, 7,  SQL_C_SLONG,  &Precision, 0, &cbPrecision);

    SQLBindCol(hstmt, 8,  SQL_C_SLONG,  &Length, 0, &cbLength);

    SQLBindCol(hstmt, 9,  SQL_C_SSHORT, &Scale, 0, &cbScale);

    SQLBindCol(hstmt, 10, SQL_C_SSHORT, &Radix, 0, &cbRadix);

    SQLBindCol(hstmt, 11, SQL_C_SSHORT, &Nullable, 0, &cbNullable);

    SQLBindCol(hstmt, 12, SQL_C_CHAR,   szRemarks, REM_LEN, &cbRemarks);

// Then get the column names:

    while(TRUE)

    {// Do till we break out:

        RC = SQLFetch(hstmt);

        if (RC == SQL_NO_DATA_FOUND)

        {// Fetch done; got last column...

                break;

        }

        if (RC != SQL_SUCCESS && RC != SQL_SUCCESS_WITH_INFO)

        {// Fetch failed; may (or may not) be fatal!

                break;

        }

        if (RC == SQL_SUCCESS || RC == SQL_SUCCESS_WITH_INFO)

        {// Fetch was OK; display the results:

            sprintf(szBuffer,

                "%20.20s %10.10s %15.15s %15.15s %10.10s %10.10s \n",

                szQualifier,

                szOwner,

                szColName,

                szTableName,

                szTypeName,

                szRemarks);

            OutputDebugString(szBuffer);

        }

    }

    SQLFreeStmt(hstmt, SQL_CLOSE);

    SQLFreeStmt(hstmt, SQL_UNBIND);

//  END: Get the columns in the specified table:

//  Get data from the table:

    strcpy(szConStrOut,

        "SELECT * FROM \"");

    strcat(szConStrOut, szTable);

    strcat(szConStrOut, "\" ");

    RC = SQLExecDirect(hstmt, (unsigned char far *)szConStrOut, SQL_NTS);

    if (RC != SQL_SUCCESS && RC != SQL_SUCCESS_WITH_INFO)

    {// Something is wrong; error message, and then DIE!

    }

    else

    {// Bind to whichever columns in result set are needed:

        SQLBindCol(hstmt, 1, SQL_C_CHAR, (unsigned char far *)szColumn1,

            sizeof(szColumn1), &sdReturn);

        SQLBindCol(hstmt, 2, SQL_C_CHAR, (unsigned char far *)szColumn2,

            sizeof(szColumn2), &sdReturn);

        SQLBindCol(hstmt, 3, SQL_C_CHAR, (unsigned char far *)szColumn3,

            sizeof(szColumn3), &sdReturn);

        SQLBindCol(hstmt, 4, SQL_C_CHAR, (unsigned char far *)szColumn4,

            sizeof(szColumn4), &sdReturn);

//      In our example, we will simply get up to 100 rows from the dataset:

        i = 0;

        j = 0;

        while(++j < 100)

        {// j is the number of rows

            RC = SQLFetch(hstmt);

            if (RC == SQL_ERROR || RC == SQL_SUCCESS_WITH_INFO)

            {// There was a problem!

            }

            if (RC == SQL_SUCCESS || RC == SQL_SUCCESS_WITH_INFO)

            {// Now we have our row's data! Use it (like write a report?)

                sprintf(szBuffer, "1 '%15.15s' 2 '%15.15s' "

                    "3 '%15.15s' 4 '%15.15s' 5 '%d' \n",

                    szQualifier, szOwner, szColName, szTypeName, i);

                OutputDebugString(szBuffer);

            }

            else

            {// That's all, folks... No more data here!

                break;

            }

        }

    }

    SQLFreeStmt(hstmt, SQL_DROP);

    SQLDisconnect(hdbc);

    SQLFreeConnect(hdbc);

    SQLFreeEnv(henv);

}

Take a look at the SimpleODBC() function. First, the following code fragment shows what is necessary to initialize the ODBC system.

    SQLAllocEnv(&henv);

    SQLAllocConnect(henv, &hdbc);

    RC = SQLDriverConnect(hdbc, hWnd,

        (unsigned char far *)szDSN, SQL_NTS,

        (unsigned char far *)szConStrOut, sizeof(szConStrOut),

        (short far *)&nConStrOut, SQL_DRIVER_COMPLETE);

    if (RC != SQL_SUCCESS && RC != SQL_SUCCESS_WITH_INFO)

    {// Call whatever error handler your application uses

    }

    else

    {// Connect was successful. Just continue in most cases

    }

    RC = SQLAllocStmt(hdbc, &hstmt);

Notice how a call is made to SQLAllocEnv() and then a call is made to SQLAllocConnect(). These two calls (which must be made in the order shown) initialize the ODBC environment and allocate the memory necessary for the connection handle.

After this setup is performed, it's then possible to connect the actual database to the application. This is done with a call to the SQLDriverConnect() function. This function takes, as arguments, information to let ODBC locate the datasource and the HDBC handle to connect to.

After the database has been connected, you need to open a statement handle. This statement handle is used to let the application issue SQL commands to the datasource.

At this point, the datasource is truly connected to the application, and the application is able to obtain both information about the datasource and information from the datasource.

In the sample program, the next step performed is to obtain information about the table that was opened. SQLColumns() is called to obtain information about each column in the datasource. SQLColumns() returned results are part of a result set. A result set is simply a set of "records" that the application is able to retrieve, either one at a time or in blocks.

    RC = SQLColumns(hstmt,

        NULL, 0,    // All qualifiers

        NULL, 0,    // All owners

        szTable, SQL_NTS,       // The table!

        NULL, 0);       // All columns

    if (RC != SQL_SUCCESS && RC != SQL_SUCCESS_WITH_INFO)

    {// Could not determine columns! Call an error handler:

    }

//    Now bind variables to columns!

    SQLBindCol(hstmt, 1,  SQL_C_CHAR,   szQualifier, STR_LEN,&cbQualifier);

    SQLBindCol(hstmt, 2,  SQL_C_CHAR,   szOwner, STR_LEN, &cbOwner);

    SQLBindCol(hstmt, 3,  SQL_C_CHAR,   szTableName, STR_LEN,&cbTableName);

    SQLBindCol(hstmt, 4,  SQL_C_CHAR,   szColName, STR_LEN, &cbColName);

    SQLBindCol(hstmt, 5,  SQL_C_SSHORT, &DataType, 0, &cbDataType);

    SQLBindCol(hstmt, 6,  SQL_C_CHAR,   szTypeName, STR_LEN, &cbTypeName);

    SQLBindCol(hstmt, 7,  SQL_C_SLONG,  &Precision, 0, &cbPrecision);

    SQLBindCol(hstmt, 8,  SQL_C_SLONG,  &Length, 0, &cbLength);

    SQLBindCol(hstmt, 9,  SQL_C_SSHORT, &Scale, 0, &cbScale);

    SQLBindCol(hstmt, 10, SQL_C_SSHORT, &Radix, 0, &cbRadix);

    SQLBindCol(hstmt, 11, SQL_C_SSHORT, &Nullable, 0, &cbNullable);

    SQLBindCol(hstmt, 12, SQL_C_CHAR,   szRemarks, REM_LEN, &cbRemarks);

In the preceding code fragment, first the SQLColumns() function is called. Then you must bind variables in the application to the columns in the result set that SQLColumns() returns. In this example, you will look at all the columns; however, many applications may need to use only a few of the result set columns (such as getting the name of the column and the column's data type).

// Then get the column names:

    while(TRUE)

    {// Do till we break out:

        RC = SQLFetch(hstmt);

        if (RC == SQL_SUCCESS || RC == SQL_SUCCESS_WITH_INFO)

        {// Fetch was OK; display the results:

            sprintf(szBuffer,

                "%20.20s %10.10s %15.15s %15.15s %10.10s %10.10s \n",

                szQualifier,

                szOwner,

                szColName,

                szTableName,

                szTypeName,

                szRemarks);

            OutputDebugString(szBuffer);

        }

    }

    SQLFreeStmt(hstmt, SQL_CLOSE);

    SQLFreeStmt(hstmt, SQL_UNBIND);

This code fragment shows how to get the actual records from the result set. The process, done in a while() loop, is simple and easy to program. A call to SQLFetch() at the beginning of the loop is followed by whatever code is necessary to process the information that SQLFetch() returns. The column names could be added to a list box to let the user select a specific column.

After the records from the result set have been processed, you need to discard the result set. You do this by using calls to SQLFreeStmt(), as the following code fragment shows. Notice that you needed to call SQLFreeStmt() two times with different arguments:

SQLFreeStmt(hstmt, SQL_CLOSE);

SQLFreeStmt(hstmt, SQL_UNBIND);

The first call to SQLFreeStmt() closed the statement handle. The second call was used to actually tell ODBC to discard the result set's contents and release the memory that the result set occupied.

To obtain information from a table in a datasource, you must actually issue an SQL command to fetch the desired records. This chapter won't try to detail SQL commands; SQL commands are covered in Chapter 5, "Learning Structured Query Language."

//  Get data from the table:

    strcpy(szConStrOut,

        "SELECT * FROM \"");

    strcat(szConStrOut, szTable);

    strcat(szConStrOut, "\" ");

    RC = SQLExecDirect(hstmt, (unsigned char far *)szConStrOut, SQL_NTS);

This example simply creates an SQL statement SELECT * FROM and appends the table name. You need to make sure that the table name is quoted in this example; however, the rules on quoting a name are based on whether the name contains embedded spaces or not. Later in this chapter, you will see an example of a function that quotes names when quotes are needed.

Like SQLColumns(), SQLExecDirect() returns the results of the SQL statement as a result set. This result set contains the records from the datasource that the SQL statement has selected (usually limited using a WHERE clause). Because the example simply gets all columns in the datasource, you must either know in advance (perhaps a database was created) or determine (using a call to SQLColumns()) what columns have been included in the result set.

Whenever a result set has been returned by an SQL...() function, you must bind variables in your application to the columns in the result set. The SQLFetch() function will place in these variables the data from the current row.

        SQLBindCol(hstmt, 1, SQL_C_CHAR, (unsigned char far *)szColumn1,

            sizeof(szColumn1), &sdReturn);

        SQLBindCol(hstmt, 2, SQL_C_CHAR, (unsigned char far *)szColumn2,

            sizeof(szColumn2), &sdReturn);

        SQLBindCol(hstmt, 3, SQL_C_CHAR, (unsigned char far *)szColumn3,

            sizeof(szColumn3), &sdReturn);

        SQLBindCol(hstmt, 4, SQL_C_CHAR, (unsigned char far *)szColumn4,

            sizeof(szColumn4), &sdReturn);

In this example, four columns are bound in the result set. If there are columns in the result set that your application doesn't need, you don't have to bind variables to any columns that you don't want.

You can always bind a character variable to a column, and the SQL...() routines will convert the column's data to a character-based format. However, the default conversion for numeric data might not be formatted the way you want.

After variables have been bound to columns, the next step is to actually fetch the rows from the result set. These rows are fetched with either SQLFetch() or SQLExtendedFetch(). In the following example, SQLFetch() is called to get the data from the result set.

        while(TRUE)

        {

            RC = SQLFetch(hstmt);

            if (RC == SQL_ERROR || RC == SQL_SUCCESS_WITH_INFO)

            {// There was a problem!

            }

            if (RC == SQL_SUCCESS || RC == SQL_SUCCESS_WITH_INFO)

            {// Now we have our row's data! Use it (like write a report?)

            }

            else

            {// That's all, folks... No more data here!

                break;

            }

        }

    }

The preceding code shows a simple while() loop; the first line in the loop is a call to SQLFetch(). After SQLFetch() completes successfully, you can use the data (which in this example is stored in the variables szBuffer1, szBuffer2, szBuffer3, and szBuffer4).

After the results of the result set have been processed by the application and the application is finished with the entire datasource, the statement handle should be discarded, as shown next. It's also necessary to disconnect from the datasource, free the connection handle, and then free the environment handle. These calls are done in the opposite order than when they were created, at the beginning of the sample function.

    SQLFreeStmt(hstmt, SQL_DROP);

    SQLDisconnect(hdbc);

    SQLFreeConnect(hdbc);

    SQLFreeEnv(henv);

Handling Errors in SQL...() Statements

When calls to SQL...() statements are made, the function will return a return code that should always be examined by the application to determine whether the function was successful or not. When a function fails, the application must determine what failed and, if possible, correct this failure with a minimum amount of interruption to the user's workflow. Except for the most disastrous failures, the application should try to recover without user interaction if possible. When the error problem is so serious that recovery is impossible, the application must notify the user and explain the problem.

Don't put a message like Error 0x1003 occurred, program ending in your application. This type of message went out with the 8088. Make sure that the error message provides as much information as possible to assist the user in correcting the problem. For example, Could not open database C:\MSOffice\Access\Samples\NorthWind.MDB, please check and make sure that the database is in this directory is a better message to give to the user.

Regardless of how your application "talks" to the user, the SQLError() function will let your application obtain information about the failure. The function shown in Listing 3.2 is an error function that will use the SQLError() function's return values to format an error message.

Listing 3.2. SQLERROR.C: An error handler.

//  SQLError.C

#include "windows.h"

#include "resource.h"

#include "odbcmisc.h"

#include "sql.h"

#include "sqlext.h"

#include <stdio.h>

#include <stdlib.h>

#include <string.h>

#include <ctype.h>

/******************************************************************************

**

**      TITLE: SQLError.c

**

**   FUNCTION: Open DataBase Connectivity interface code

**

**     INPUTS: VARIOUS

**

**    OUTPUTS: VARIOUS

**

**    RETURNS: YES

**

**      CALLS: ODBC routines: SQL...()

**

**     AUTHOR: Peter D. Hipson

**

**   COPYRIGHT 1995 BY PETER D. HIPSON, All rights reserved.

**

******************************************************************************/

// Static, for this module only:

// Routines:

// A typical SQL type query:

void    SQLPrintError(HENV henv, HDBC hdbc, HSTMT hstmt)

{

RETCODE RC;

char    szSqlState[256];

char    szErrorMsg[256];

char    szMessage[256];

SDWORD     pfNativeError;

SWORD    pcbErrorMsg;

    RC = SQLError(henv, hdbc, hstmt,

        szSqlState,

        &pfNativeError,

        szErrorMsg,

        sizeof(szErrorMsg),

        &pcbErrorMsg);

    if (RC == SQL_SUCCESS || RC == SQL_SUCCESS_WITH_INFO)

    {

        sprintf(szMessage, "SQL State = '%s', \nMessage = '%s'",

            szSqlState, szErrorMsg);

        MessageBox(NULL, szMessage, "ODBC Error...", MB_OK);

    }

    else

    {

        MessageBox(NULL, "SQLError() returned an error!!!",

            "ODBC Error...", MB_OK);

    }

}

The SQLPrintError() function shown in Listing 3.2 really isn't that user-friendly. It displays a cryptic message about SQL states and messages without telling the user exactly what went wrong and what to do about the failure.

A better example of an SQLPrintError() function would be a function that actually parsed the error condition returned by SQLError() and then offered both the reason and possible corrective actions. A hot link to WinHelp with a help file that contained more detailed information about both the failure and possible corrective action wouldn't be out of order. Of course, adding a help button would require writing a custom replacement for MessageBox(); however, that wouldn't be too difficult if you used the Visual C++ resource editor.

Quoting Names

When an SQL statement is built in a string, it's necessary to quote some names. Any name that contains embedded blanks must be quoted (column names often have embedded blanks, as in 'Zip Code').

The function QuoteName() shown in Listing 3.3 is a function that will quote a string if the string contains a character that isn't a letter, number, or an underscore. This function takes two parameters: a pointer to a buffer containing the string and the size of the buffer. This size parameter isn't the length of the string but the size of the memory allocated to the buffer. The size is needed so that the function can determine whether there is enough room to add the two quote characters to the string.

Listing 3.3. QUOTES.C: Puts quotes around a string.

//  QUOTES.C

#include "windows.h"

#include "resource.h"

#include "odbcmisc.h"

#include "sql.h"

#include "sqlext.h"

#include <stdio.h>

#include <stdlib.h>

#include <string.h>

#include <ctype.h>

/******************************************************************************

**

**      TITLE: Quotes.c

**

**   FUNCTION: Open DataBase Connectivity interface code

**

**     INPUTS: VARIOUS

**

**    OUTPUTS: VARIOUS

**

**    RETURNS: YES

**

**      CALLS: ODBC routines: SQL...()

**

**     AUTHOR: Peter D. Hipson

**

**   COPYRIGHT 1995 BY PETER D. HIPSON, All rights reserved.

**

******************************************************************************/

// Static, for this module only:

// Routines:

BOOL QuoteName(

 char * szName,

 int    nMaxLength)

{

// This function will enclose, in quotes, an SQL name if it contains

// a character that is not alphabetic, numeric, or an underscore

int i;

BOOL bMustQuote = FALSE;

    for (i = 0; i < (int)strlen(szName); i++)

    {

        if(!__iscsym(szName[i]))

        {

             bMustQuote = TRUE;

        }

    }

    if (bMustQuote)

    {//     Had a special character!

         if((int)strlen(szName) + 2 > nMaxLength)

          {//     Error: No room for quotes!

               bMustQuote = FALSE;

          }

          else

          {//     Quote this string...

               memmove(&szName[1], &szName[0], strlen(szName) + 1);

               szName[0] = '"';

               strcat(szName, "\"");

          }

    }

    return(bMustQuote);

}

Calling QuoteName() makes it easy to pass correct SQL commands because if a name that must be quoted doesn't have quotes, the SQL command will fail.

Getting the Datasource from the User

It's necessary to get the datasource name from the user. You can do this by using a simple set of C++ functions (which are callable from C code). The final part of this section shows the GetODBC() function that is called to get the datasource and table names.

This code is written in two parts. The first part is the main calling routine, GetODBC(), which is in the file GETODBC.CPP. This file is shown in Listing 3.4.

Listing 3.4. GETODBC.CPP: The GetODBC() function.

//  GETODBC.CPP

#include "stdafx.h"

#include <afxdb.h>

// Include *YOUR* application header here (instead of application.h):

#include "application.h"

#include "odbcmisc.h"

#include "sql.h"

#include "sqlext.h"

#include <stdio.h>

#include <stdlib.h>

#include <string.h>

#include <commdlg.h>

#include "odbctabl.h"

#include "odbcinfo.h"

/******************************************************************************

**

**      TITLE: GETODBC.CPP - Database Developer's Guide with Visual C++

**

**   FUNCTION: Open DataBase Connectivity interface code

**

**     INPUTS: VARIOUS

**

**    OUTPUTS: VARIOUS

**

**    RETURNS: YES

**

**    WRITTEN: 1 February 1995

**

**      CALLS: ODBC routines: SQL...()

**

**  CALLED BY: Things that need data from databases

**

**     AUTHOR: Peter D. Hipson

**

**   COPYRIGHT 1995 BY PETER D. HIPSON, All rights reserved.

**

******************************************************************************/

// Static, for this module only:

// Functions (may be shared):

// Shared data objects, not otherwise allocated:

// Routines:

BOOL GetODBC(

    char * szDataSource,

    int    nDataSourceSize,

    char * szDataTable,

    int    nDataTableSize,

    char * szDataDir,

    int    nDataDirSize)

{

BOOL        bReturnCode = TRUE;

    ODBCInfo    COdbcInfo;

    if (szDataSource)

        szDataSource[0] = '\0';

    if (szDataTable)

        szDataTable[0] = '\0';

    if (szDataDir)

        szDataDir[0] = '\0';

    bReturnCode = COdbcInfo.GetInfo();

//    A little debugging output for the programmer!

    TRACE("At the GetODBC() end: return %d Datasource "

    "'%s' table '%s' datadir '%s'\n",

        bReturnCode,

        (const char *)COdbcInfo.m_DataSourceName,

        (const char *)COdbcInfo.m_DataTableName,

        (const char *)COdbcInfo.m_DataTableDir);

    if (bReturnCode && szDataSource != NULL)

    {// User wants the datasource name

        if (COdbcInfo.m_DataSourceName.GetLength() < nDataSourceSize)

        {

            strcpy(szDataSource, COdbcInfo.m_DataSourceName);

        }

        else

        {

            szDataSource[0] = '\0';

            bReturnCode = FALSE;

        }

    }

    if (bReturnCode && szDataTable != NULL)

    {// User wants the datatable name

        if (COdbcInfo.m_DataTableName.GetLength() < nDataTableSize)

        {

            strcpy(szDataTable, COdbcInfo.m_DataTableName);

        }

        else

        {

            szDataTable[0] = '\0';

            bReturnCode = FALSE;

        }

    }

    if (bReturnCode && szDataDir != NULL)

    {// User wants the datatable directory name

        if (COdbcInfo.m_DataTableDir.GetLength() < nDataTableSize)

        {

            strcpy(szDataDir, COdbcInfo.m_DataTableDir);

        }

        else

        {

            szDataDir[0] = '\0';

            bReturnCode = FALSE;

        }

    }

//  Finally, return either success or failure.

    return(bReturnCode);

}

The GetODBC() function creates an object of class ODBCInfo. This class is in the file ODBCINFO.CPP, which is shown in Listing 3.5. Notice that even though a C++ class is used, the CDatabase class is used in this function. This is because CDatabase offers integrated functionality to the program. This functionality could also have been incorporated by using the SQL...() functions if desired.

Listing 3.5. ODBCINFO.CPP: Get ODBC datasource information.

//  ODBCINFO.CPP

#include "stdafx.h"

// After stdafx.h, include the application's main .H file.

// The application's resources must have the IDD_SELECT_ODBC_TABLE dialog

// defined! (save ODBC.RC, and copy using the resource editor and clipboard)

#include "APPLICATION.h"

#include <afxdb.h>

#include "sql.h"

#include "sqlext.h"

#include "odbcinfo.h"

#include <stdio.h>

#include <stdlib.h>

#include <string.h>

#include <commdlg.h>

#include "odbctabl.h"

/******************************************************************************

**

**      TITLE: ODBCINFO.CPP

**

**   FUNCTION: Open DataBase Connectivity interface code

**

**     INPUTS: VARIOUS

**

**    OUTPUTS: VARIOUS

**

**    RETURNS: YES

**

**    WRITTEN: 1 February 1995

**

**      CALLS: ODBC routines: SQL...()

**

**  CALLED BY: Things that need data from databases

**

**     AUTHOR: Peter D. Hipson

**

**      NOTES: Win 3.11 & later.

**

**   COPYRIGHT 1995 BY PETER D. HIPSON, All rights reserved.

**

******************************************************************************/

// Static, for this module only:

// Shared data objects, not otherwise allocated:

// Routines:

// Construction

ODBCInfo::ODBCInfo()

{// Initialize variables, etc.

//  This sets the default table types that

//  will be displayed. To not display a table type at

//  startup time, change the appropriate variable to FALSE.

    m_Synonyms = TRUE;

    m_SystemTables = TRUE;

    m_Tables = TRUE;

    m_Views = TRUE;

}

BOOL ODBCInfo::GetInfo()

{

HSTMT        hstmt = SQL_NULL_HSTMT;

char        szConStrOut[256];

SWORD        swReturn;

CString        CDBType;

RETCODE        RC;

int            nReturnCode = TRUE;

    TRY

    {

        if (!m_CDodbc.Open(m_TableInfo))

        {//    User selected Cancel. Go home. No more playing for 'im.

            return(FALSE);

        }

    }

    CATCH(CDBException, e)

    {// User probably hit Return w/o selecting datasource! Msg and return!

        return FALSE;

    }

    END_CATCH

    m_DatabaseName = m_CDodbc.GetDatabaseName();

    m_Connect = m_CDodbc.GetConnect();

    m_CanUpdate = m_CDodbc.CanUpdate();

    m_CanTransact = m_CDodbc.CanTransact();

//    C++'s MFC CRecordSet() class is a bit too unflexible to

//        really work well with an undefined database.

//        Therefore, we simply break into the older API (SQL...()) calls

    RC = SQLGetInfo(m_CDodbc.m_hdbc, SQL_DATA_SOURCE_NAME, szConStrOut,

        sizeof(szConStrOut), &swReturn);

    m_DataSourceName = szConStrOut;

// Lines below are simply for debugging (nice to see what happened):

//

//      TRACE("Datasoure: '%s'\n", m_DataSourceName);

//

//      SQLGetInfo(m_CDodbc.m_hdbc, SQL_DRIVER_NAME, szConStrOut,

//             sizeof(szConStrOut), &swReturn);

//      TRACE("Driver Name %s\n", szConStrOut);

//

//      SQLGetInfo(m_CDodbc.m_hdbc, SQL_DRIVER_VER, szConStrOut,

//             sizeof(szConStrOut), &swReturn);

//      TRACE("Driver Version %s\n", szConStrOut);

//

//      SQLGetInfo(m_CDodbc.m_hdbc, SQL_ODBC_VER, szConStrOut,

//             sizeof(szConStrOut), &swReturn);

//      TRACE("ODBC Version %s\n", szConStrOut);

//

//      SQLGetInfo(m_CDodbc.m_hdbc, SQL_SERVER_NAME, szConStrOut,

//             sizeof(szConStrOut), &swReturn);

//      TRACE("Server Name %s\n", szConStrOut);

//

//      SQLGetInfo(m_CDodbc.m_hdbc, SQL_DATABASE_NAME, szConStrOut,

//             sizeof(szConStrOut), &swReturn);

//      TRACE("Database Name %s\n", szConStrOut);

//

//      SQLGetInfo(m_CDodbc.m_hdbc, SQL_DBMS_VER, szConStrOut,

//          sizeof(szConStrOut), &swReturn);

//      TRACE("DBMS Version %s\n", szConStrOut);

//    Once a datasource is provided, we need to get the TABLE that

//    the user will want. If the datasource is a text file,

//    we use a CFileDialog object (with modifications...)

    SQLGetInfo(m_CDodbc.m_hdbc, SQL_DBMS_NAME, szConStrOut,

        sizeof(szConStrOut), &swReturn);

    CDBType = szConStrOut;

    if (CDBType == "TEXT")

    {// Data type is text. Use common dialog support to open file.

    //  This code will break under Windows 95's new Explorer system,

    //  which does not support common dialog template modifications

    //  in the same manner as Windows 3.x and Windows NT! It forces

    //  usage of "old style" dialog box!

        CString Filter;

        Filter =

            "CSV Files (*.csv)|*.csv|"

            "TAB Files (*.tab)|*.tab|"

            "Text Files (*.txt)|*.txt|"

            "Data Files (*.dat)|*.dat||";

        CFileDialog dlg(TRUE, "txt", NULL, OFN_FILEMUSTEXIST | OFN_HIDEREADONLY,

            (const char *)Filter);

//        Patch to use our dialog box template:

        dlg.m_ofn.hInstance = AfxGetInstanceHandle();

        dlg.m_ofn.lpTemplateName = MAKEINTRESOURCE(TABLESELECT);

        dlg.m_ofn.Flags |= OFN_ENABLETEMPLATE;

        if (dlg.DoModal() == IDOK)

        {

            m_DataTableName = dlg.GetPathName();

            int nPosition = m_DataTableName.ReverseFind('\\');

            if (nPosition > 0)

            {

                m_DataTableDir = m_DataTableName.Left(nPosition);

                m_DataTableName = m_DataTableName.Mid(nPosition + 1);

            }

        }

        else

        {

            nReturnCode = FALSE;

        }

    }

    else

    {// Data type is not text; possibly Access, dBASE, or FoxPro

    //  (but could be others)

        OdbcTabl OTDlg;

        OTDlg.m_Synonyms = m_Synonyms;

        OTDlg.m_SystemTables = m_SystemTables;

        OTDlg.m_Tables = m_Tables;

        OTDlg.m_Views = m_Views;

        OTDlg.m_CDB = &m_CDodbc;

        if (OTDlg.DoModal() == IDOK)

        {

            m_DataTableName = OTDlg.m_TableName;

        }

        else

        {

            nReturnCode = FALSE;

        }

    }

//    Finally, a successful return

    return(nReturnCode);

}

void    ODBCInfo::PrintError(HENV henv, HDBC hdbc, HSTMT hstmt)

{// Private, programmer's error handler. Outputs to the debugging

//  terminal and doesn't use a message box!

RETCODE RC;

char    szSqlState[256];

char    szErrorMsg[256];

SDWORD     pfNativeError;

SWORD    pcbErrorMsg;

    RC = SQLError(

        henv,

        hdbc,

        hstmt,

        (UCHAR FAR*)szSqlState,

        &pfNativeError,

        (UCHAR FAR*)szErrorMsg,

        sizeof(szErrorMsg),

        &pcbErrorMsg);

    TRACE("SQL ERROR:\n");

    if (RC == SQL_SUCCESS || RC == SQL_SUCCESS_WITH_INFO)

    {

        TRACE("%s\n", szSqlState);

        TRACE("%s\n", szErrorMsg);

    }

    else

    {

        TRACE("%s\n", "SQLError() returned an error!!!");

    }

}

The ODBCInfo class shows a number of features. First, there is a call to the CDatabase::Open() function to open a datasource. Then you must prompt the user to select a table in the datasource. The call to the CDatabase::Open() function is followed by a number of other CDatabase member function calls, as the following edited code fragment shows:

    m_CDodbc.Open(m_TableInfo

    m_DatabaseName = m_CDodbc.GetDatabaseName();

    m_Connect = m_CDodbc.GetConnect();

    m_CanUpdate = m_CDodbc.CanUpdate();

    m_CanTransact = m_CDodbc.CanTransact();

    RC = SQLGetInfo(m_CDodbc.m_hdbc, SQL_DATA_SOURCE_NAME, szConStrOut,

        sizeof(szConStrOut), &swReturn);

    m_DataSourceName = szConStrOut

    SQLGetInfo(m_CDodbc.m_hdbc, SQL_DBMS_NAME, szConStrOut,

        sizeof(szConStrOut), &swReturn);

;

This example shows the call to Open() and then calls to get the database name and the connect string and finds out whether the database can be updated (not all datasources are updatable) and whether the database supports transactions. You also get the datasource name and the DBMS name.

After you have the database, you must determine which table in the database the user is working with. Again, this type of information is individual to each application: perhaps there will be a number of predefined tables that will be used, or perhaps the user may have to be prompted to supply information about the table.

In my example, I present either a custom dialog box of class OdbcTabl (for databases that aren't text-based) or I use the CFileDialog MFC class, with a custom template, to select which text file the user will be using for the table.

The sample function actually has two dialog box templates (see ODBC.RC, included on this book's CD, and Figure 3.1). The ODBCTABL.CPP file is shown in Listing 3.6. This class uses calls to the SQL...() functions to manage the gathering of information about tables in the specified datasource.

Figure 3.1. Dialog boxes for ODBCTABL.

Listing 3.6. ODBCTABL.CPP: Dialog box allowing users to select a table.

// odbctabl.cpp : implementation file

//

#include "stdafx.h"

#include <afxdb.h>

// After stdafx.h, include the application's main .H file.

// The application's resources must have the IDD_SELECT_ODBC_TABLE dialog

// defined! (save ODBC.RC, and copy using the editor)

#include "sql.h"

#include "sqlext.h"

#include "odbctabl.h"

#ifdef _DEBUG

#undef THIS_FILE

static char BASED_CODE THIS_FILE[] = __FILE__;

#endif

/////////////////////////////////////////////////////////////////////////////

// OdbcTabl dialog

OdbcTabl::OdbcTabl(CWnd* pParent /*=NULL*/)

    : CDialog(OdbcTabl::IDD, pParent)

{

    //{{AFX_DATA_INIT(OdbcTabl)

    m_Synonyms = FALSE;

    m_SystemTables = FALSE;

    m_Tables = FALSE;

    m_Views = FALSE;

    //}}AFX_DATA_INIT

}

void OdbcTabl::DoDataExchange(CDataExchange* pDX)

{

    CDialog::DoDataExchange(pDX);

    //{{AFX_DATA_MAP(OdbcTabl)

    DDX_Control(pDX, IDC_SYNONYMS, m_CSynonyms);

    DDX_Control(pDX, IDC_VIEWS, m_CViews);

    DDX_Control(pDX, IDC_TABLES, m_CTables);

    DDX_Control(pDX, IDC_SYSTEM_TABLES, m_CSystemTables);

    DDX_Control(pDX, IDC_LIST1, m_TableList);

    DDX_Check(pDX, IDC_SYNONYMS, m_Synonyms);

    DDX_Check(pDX, IDC_SYSTEM_TABLES, m_SystemTables);

    DDX_Check(pDX, IDC_TABLES, m_Tables);

    DDX_Check(pDX, IDC_VIEWS, m_Views);

    //}}AFX_DATA_MAP

}

BEGIN_MESSAGE_MAP(OdbcTabl, CDialog)

    //{{AFX_MSG_MAP(OdbcTabl)

    ON_BN_CLICKED(IDC_SYNONYMS, OnSynonyms)

    ON_BN_CLICKED(IDC_SYSTEM_TABLES, OnSystemTables)

    ON_BN_CLICKED(IDC_TABLES, OnTables)

    ON_BN_CLICKED(IDC_VIEWS, OnViews)

    //}}AFX_MSG_MAP

END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////

// OdbcTabl message handlers

BOOL OdbcTabl::OnInitDialog()

{

    CDialog::OnInitDialog();

    m_TableList.SetTabStops(75);

     LoadTableList();

    return TRUE;  // Return TRUE  unless you set the focus to a control

}

void OdbcTabl::LoadTableList()

{

HSTMT    hstmt = SQL_NULL_HSTMT;

long    lReturnLength;

int        i;

SWORD    swReturn;

RETCODE    RC;

char    szQualifier[128];

char    szOwner[128];

char    szName[128];

char    szType[128];

char    szConStrOut[256];

char    szRemarks[254];

    RC = SQLAllocStmt(m_CDB->m_hdbc, &hstmt);

    if (RC != SQL_SUCCESS && RC != SQL_SUCCESS_WITH_INFO)

    {

        TRACE("SQLAllocStmt() FAILED!!!!\n");

        PrintError(SQL_NULL_HENV, m_CDB->m_hdbc, hstmt);

    }

    RC = SQLTables (hstmt,

        (unsigned char far *)"%", SQL_NTS, (unsigned char far *)"", 0,

        (unsigned char far *)"", 0, (unsigned char far *)"", 0);

    SQLFreeStmt(hstmt, SQL_CLOSE);

    SQLFreeStmt(hstmt, SQL_UNBIND);

    SQLGetInfo(m_CDB->m_hdbc, SQL_MAX_OWNER_NAME_LEN,

       &i, sizeof(int), &swReturn);

    szRemarks[0] = '\0';

    if (m_CTables.GetCheck() == 1)

    {

        strcat(szRemarks, "'TABLE'");

    }

    if (m_CSystemTables.GetCheck() == 1)

    {

        if (strlen(szRemarks) > 0)

            strcat(szRemarks, ", ");

        strcat(szRemarks, "'SYSTEM TABLE'");

    }

    if (m_CViews.GetCheck() == 1)

    {

        if (strlen(szRemarks) > 0)

            strcat(szRemarks, ", ");

        strcat(szRemarks, "'VIEW'");

    }

    if (m_CSynonyms.GetCheck() == 1)

    {

        if (strlen(szRemarks) > 0)

            strcat(szRemarks, ", ");

        strcat(szRemarks, "'SYNONYM'");

    }

    RC = SQLTables(hstmt,

        NULL, SQL_NTS,    // Table qualifier

        NULL, SQL_NTS,    // Table owner

        NULL, SQL_NTS,    // Table name

        (unsigned char far *)szRemarks, strlen(szRemarks));

    if (RC != SQL_SUCCESS && RC != SQL_SUCCESS_WITH_INFO)

    {

        PrintError(SQL_NULL_HENV, m_CDB->m_hdbc, hstmt);

    }

    SQLGetInfo(m_CDB->m_hdbc, SQL_DBMS_VER, szConStrOut,

       sizeof(szConStrOut), &swReturn);

    TRACE("%s\n", szConStrOut);

//    Now bind variables to columns!

    RC = SQLBindCol(hstmt, 1, SQL_C_CHAR,

        szQualifier, sizeof(szQualifier), &lReturnLength);

    RC = SQLBindCol(hstmt, 2, SQL_C_CHAR,

        szOwner, sizeof(szOwner), &lReturnLength);

    RC = SQLBindCol(hstmt, 3, SQL_C_CHAR,

        szName, sizeof(szName), &lReturnLength);

    RC = SQLBindCol(hstmt, 4, SQL_C_CHAR,

        szType, sizeof(szType), &lReturnLength);

    RC = SQLBindCol(hstmt, 5, SQL_C_CHAR,

        szRemarks, sizeof(szRemarks), &lReturnLength);

// Then get the table names:

    m_TableList.ResetContent();

    while(TRUE)

    {

         RC = SQLFetch(hstmt);

        if (RC == SQL_ERROR || RC == SQL_SUCCESS_WITH_INFO)

        {

            TRACE("SQLFetch() FAILED!!!!\n");

            PrintError(SQL_NULL_HENV, m_CDB->m_hdbc, hstmt);

        }

        if (RC == SQL_SUCCESS || RC == SQL_SUCCESS_WITH_INFO)

        {

//             Must set tab stops for this list to look good!

            sprintf(szRemarks, "%s\t%s", szType, szName);

            m_TableList.AddString(szRemarks);

        }

         else

        {// That's all, folks...

            break;

        }

    }

    m_TableList.SetCurSel(0);

     SQLFreeStmt(hstmt, SQL_CLOSE);

    SQLFreeStmt(hstmt, SQL_UNBIND);

    SQLFreeStmt(hstmt, SQL_DROP);

}

void OdbcTabl::OnSynonyms()

{

    // TODO: Add your control notification handler code here

     LoadTableList();

}

void OdbcTabl::OnSystemTables()

{

    // TODO: Add your control notification handler code here

     LoadTableList();

}

void OdbcTabl::OnTables()

{

    // TODO: Add your control notification handler code here

     LoadTableList();

}

void OdbcTabl::OnViews()

{

    // TODO: Add your control notification handler code here

     LoadTableList();

}

void OdbcTabl::OnOK()

{

CString        TempString;

    // TODO: Add extra validation here

    m_TableList.GetText(m_TableList.GetCurSel(), TempString);

//    Get everything after the tab...

    m_TableName    = TempString.Mid(TempString.Find('\t') + 1);

    CDialog::OnOK();

}

void    OdbcTabl::PrintError(HENV henv, HDBC hdbc, HSTMT hstmt)

{// Private, programmer's error handler. Outputs to the debugging

//     terminal and doesn't use a message box!

RETCODE RC;

char    szSqlState[256];

char    szErrorMsg[256];

SDWORD     pfNativeError;

SWORD    pcbErrorMsg;

    RC = SQLError(henv, hdbc, hstmt,

        (UCHAR FAR*)szSqlState,

        &pfNativeError,

        (UCHAR FAR*)szErrorMsg,

        sizeof(szErrorMsg),

        &pcbErrorMsg);

    TRACE("SQL ERROR:\n");

    if (RC == SQL_SUCCESS || RC == SQL_SUCCESS_WITH_INFO)

    {

        TRACE("%s\n", szSqlState);

        TRACE("%s\n", szErrorMsg);

    }

    else

    {

        TRACE("%s\n", "SQLError() returned an error!!!");

    }

}

Summary

This chapter introduced you to the ODBC SQL...() functions and provided a reference section. A set of sample routines provided practical examples of how to use the SQL...() functions and also showed examples of how to use the MFC database objects that were covered in Chapter 2.

This chapter completes Part I of this book. You will create more sophisticated examples of decision support and transaction processing applications when you reach Part III, "An Introduction to Database Front-End Design," and Part IV, "Advanced Programming with Visual C++." The next chapter, "Optimizing the Design of Relational Databases," introduces you to database design methodology and shows you some of the CASE design tools that are available for Access and client-server databases.