• 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