• SQLExtendedFetch (ODBC 1.0, Level 2)
  • Syntax
  • Returns
  • Diagnostics
  • Comments
    • Binding
    • Positioning the Cursor
    • Processing Errors
    • fFetchType Argument
    • irow Argument
    • rgfRowStatus Argument
  • Code Example
  • Related Functions
• SQLFetch (ODBC 1.0, Core)
  • Syntax
  • Returns
  • Diagnostics
  • Comments
  • Code Example
  • Related Functions
• SQLFetchPrev (SOLID Extension)
  • Syntax
  • Returns
  • Diagnostics
  • Comments
  • Code Example
  • Related Functions
• SQLForeignKeys (ODBC 1.0, Level 2)
  • Syntax
  • Returns
  • Diagnostics
  • Comments
  • Code Example
  • Related Functions
• SQLFreeConnect (ODBC 1.0, Core)
  • Syntax
  • Returns
  • Diagnostics
  • Comments
  • Code Example
  • Related Functions

SQLExtendedFetch (ODBC 1.0, Level 2)

SQLExtendedFetch extends the functionality of SQLFetch in the following ways:

It returns rowset data (one or more rows), in the form of an array, for each bound column.

It scrolls through the result set according to the setting of a scroll-type argument.

SQLExtendedFetch works in conjunction with SQLSetStmtOption.

To fetch one row of data at a time in a forward direction, an application should call SQLFetch.

For more information about scrolling through result sets, see "Using Block and Scrollable Cursors" in Chapter 7, "Retrieving Results."

Syntax

RETCODE SQLExtendedFetch(hstmt, fFetchType, irow, pcrow, rgfRowStatus)

The SQLExtendedFetch function accepts the following arguments:

Returns

SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_NO_DATA_FOUND, SQL_STILL_EXECUTING, SQL_ERROR, or SQL_INVALID_HANDLE.

Diagnostics

When SQLExtendedFetch returns either SQL_ERROR or SQL_SUCCESS_WITH_INFO, an associated SQLSTATE value may be obtained by calling SQLError. The following table lists the SQLSTATE values commonly returned by SQLExtendedFetch and explains each one in the context of this function; the notation "(DM)" precedes the descriptions of SQLSTATEs returned by the Driver Manager. The return code associated with each SQLSTATE value is SQL_ERROR, unless noted otherwise.

Comments

SQLExtendedFetch returns one rowset of data to the application. An application cannot mix calls to SQLExtendedFetch and SQLFetch for the same cursor.

An application specifies the number of rows in the rowset by calling SQLSetStmtOption with the SQL_ROWSET_SIZE statement option.

Binding

If any columns in the result set have been bound with SQLBindCol, the driver converts the data for the bound columns as necessary and stores it in the locations bound to those columns. The result set can be bound in a column-wise (the default) or row-wise fashion.

Column-Wise Binding

To bind a result set in column-wise fashion, an application specifies SQL_BIND_BY_COLUMN for the SQL_BIND_TYPE statement option. (This is the default value.) For each column to be bound, the application:

1. Allocates an array of data storage buffers. The array has as many elements as there are rows in the rowset, plus an additional element if the application will search for key values or append new rows of data. Each buffer’s size is the maximum size of the C data that can be returned for the column. For example, when the C data type is SQL_C_DEFAULT, each buffer’s size is the column length. When the C data type is SQL_C_CHAR, each buffer’s size is the display size of the data. For more information, see "Converting Data from SQL to C Data Types" and "Precision, Scale, Length, and Display Size" in Appendix D, "Data Types."

2. Allocates an array of SDWORDs to hold the number of bytes available to return for each row in the column. The array has as many elements as there are rows in the rowset.

3. Calls SQLBindCol:

The rgbValue argument specifies the address of the data storage array.

The cbValueMax argument specifies the size of each buffer in the data storage array.

The pcbValue argument specifies the address of the number-of-bytes array.

When the application calls SQLExtendedFetch, the driver retrieves the data and the number of bytes available to return and stores them in the buffers allocated by the application:

For each bound column, the driver stores the data in the rgbValue buffer bound to the column. It stores the first row of data at the start of the buffer and each subsequent row of data at an offset of cbValueMax bytes from the data for the previous row.

For each bound column, the driver stores the number of bytes available to return in the pcbValue buffer bound to the column. This is the number of bytes available prior to calling SQLExtendedFetch. (If the number of bytes available to return cannot be determined in advance, the driver sets pcbValue to SQL_NO_TOTAL. If the data for the column is NULL, the driver sets pcbValue to SQL_NULL_DATA.) It stores the number of bytes available to return for the first row at the start of the buffer and the number of bytes available to return for each subsequent row at an offset of sizeof(SDWORD) from the value for the previous row.

Row-Wise Binding

To bind a result set in row-wise fashion, an application:

1. Declares a structure that can hold a single row of retrieved data and the associated data lengths. For each bound column, the structure contains one field for the data and one SDWORD field for the number of bytes available to return. The data field’s size is the maximum size of the C data that can be returned for the column.

2. Calls SQLSetStmtOption with fOption set to SQL_BIND_TYPE and vParam set to the size of the structure.

3. Allocates an array of these structures. The array has as many elements as there are rows in the rowset, plus an additional element if the application will search for key values or append new rows of data.

4. Calls SQLBindCol for each column to be bound:

The rgbValue argument specifies the address of the column’s data field in the first array element.

The cbValueMax argument specifies the size of the column’s data field.

The pcbValue argument specifies the address of the column’s number-of-bytes field in the first array element.

When the application calls SQLExtendedFetch, the driver retrieves the data and the number of bytes available to return and stores them in the buffers allocated by the application:

For each bound column, the driver stores the first row of data at the address specified by rgbValue for the column and each subsequent row of data at an offset of vParam bytes from the data for the previous row.

For each bound column, the driver stores the number of bytes available to return for the first row at the address specified by pcbValue and the number of bytes available to return for each subsequent row at an offset of vParam bytes from the value for the previous row. This is the number of bytes available prior to calling SQLExtendedFetch. (If the number of bytes available to return cannot be determined in advance, the driver sets pcbValue to SQL_NO_TOTAL. If the data for the column is NULL, the driver sets pcbValue to SQL_NULL_DATA.)

Positioning the Cursor

The following operations require a cursor position:

Positioned update and delete statements.

Calls to SQLGetData.

Calls to SQLSetPos with the SQL_DELETE, SQL_REFRESH, and SQL_UPDATE options.

An application can specify a cursor position when it calls SQLSetPos. Before it executes a positioned update or delete statement or calls SQLGetData, the application must position the cursor by calling SQLExtendedFetch to retrieve a rowset; the cursor points to the first row in the rowset. To position the cursor to a different row in the rowset, the application calls SQLSetPos.

The following table shows the rowset and return code returned when the application requests different rowsets.

For example, suppose a result set has 100 rows and the rowset size is 5. The following table shows the rowset and return code returned by SQLExtendedFetch for different values of irow when the fetch type is SQL_FETCH_RELATIVE:

Before SQLExtendedFetch is called the first time, the cursor is positioned before the start of the result set.

For the purpose of moving the cursor, deleted rows (that is, rows with an entry in the rgfRowStatus array of SQL_ROW_DELETED) are treated no differently than other rows. For example, calling SQLExtendedFetch with fFetchType set to SQL_FETCH_ABSOLUTE and irow set to 15 returns the rowset starting at row 15, even if the rgfRowStatus array for row 15 is SQL_ROW_DELETED.

Processing Errors

If an error occurs that pertains to the entire rowset, such as SQLSTATE S1T00 (Timeout expired), the driver returns SQL_ERROR and the appropriate SQLSTATE. The contents of the rowset buffers are undefined and the cursor position is unchanged.

If an error occurs that pertains to a single row, the driver:

Sets the element in the rgfRowStatus array for the row to SQL_ROW_ERROR.

Posts SQLSTATE 01S01 (Error in row) in the error queue.

Posts zero or more additional SQLSTATEs for the error after SQLSTATE 01S01 (Error in row) in the error queue.

After it has processed the error or warning, the driver continues the operation for the remaining rows in the rowset and returns SQL_SUCCESS_WITH_INFO. Thus, for each error that pertains to a single row, the error queue contains SQLSTATE 01S01 (Error in row) followed by zero or more additional SQLSTATEs.

After it has processed the error, the driver fetches the remaining rows in the rowset and returns SQL_SUCCESS_WITH_INFO. Thus, for each row that returned an error, the error queue contains SQLSTATE 01S01 (Error in row) followed by zero or more additional SQLSTATEs.

If the rowset contains rows that have already been fetched, the driver is not required to return SQLSTATEs for errors that occurred when the rows were first fetched. It is, however, required to return SQLSTATE 01S01 (Error in row) for each row in which an error originally occurred and to return SQL_SUCCESS_WITH_INFO. For example, a static cursor that maintains a cache might cache row status information (so it can determine which rows contain errors) but might not cache the SQLSTATE associated with those errors.

Error rows do not affect relative cursor movements. For example, suppose the result set size is 100 and the rowset size is 10. If the current rowset is rows 11 through 20 and the element in the rgfRowStatus array for row 11 is SQL_ROW_ERROR, calling SQLExtendedFetch with the SQL_FETCH_NEXT fetch type still returns rows 21 through 30.

If the driver returns any warnings, such as SQLSTATE 01004 (Data truncated), it returns warnings that apply to the entire rowset or to unknown rows in the rowset before it returns error information applying to specific rows. It returns warnings for specific rows along with any other error information about those rows.

fFetchType Argument

The fFetchType argument specifies how to move through the result set. It is one of the following values:

SQL_FETCH_NEXT

SQL_FETCH_FIRST

SQL_FETCH_LAST

SQL_FETCH_PRIOR

SQL_FETCH_ABSOLUTE

SQL_FETCH_RELATIVE

SQL_FETCH_BOOKMARK

If the value of the SQL_CURSOR_TYPE statement option is SQL_CURSOR_FORWARD_ONLY, the fFetchType argument must be SQL_FETCH_NEXT.

---

Note. In ODBC 1.0, SQLExtendedFetch supported the SQL_FETCH_RESUME fetch type. In ODBC 2.0, SQL_FETCH_RESUME is obsolete and the Driver Manager returns SQLSTATE S1C00 (Driver not capable) if an application specifies it for an ODBC 2.0 driver.

---

---

The SQL_FETCH_BOOKMARK fetch type was introduced in ODBC 2.0; the Driver Manager returns SQLSTATE S1106 (Fetch type out of range) if it is specified for an ODBC 1.0 driver.

---

Moving by Row Position

SQLExtendedFetch supports the following values of the fFetchType argument to move relative to the current rowset:

It supports the following values of the fFetchType argument to move to an absolute position in the result set:

Positioning to a Bookmark

When an application calls SQLExtendedFetch with the SQL_FETCH_BOOKMARK fetch type, the driver retrieves the rowset starting with the row specified by the bookmark in the irow argument.

To inform the driver that it will use bookmarks, the application calls SQLSetStmtOption with the SQL_USE_BOOKMARKS option before opening the cursor. To retrieve the bookmark for a row, the application either positions the cursor on the row and calls SQLGetStmtOption with the SQL_GET_BOOKMARK option, or retrieves the bookmark from column 0 of the result set. If the application retrieves a bookmark from column 0 of the result set, it must set fCType in SQLBindCol or SQLGetData to SQL_C_BOOKMARK. The application stores the bookmarks for those rows in each rowset to which it will return later.

Bookmarks are 32-bit binary values; if a bookmark requires more than 32 bits, such as when it is a key value, the driver maps the bookmarks requested by the application to 32-bit binary values. The 32-bit binary values are then returned to the application. Because this mapping may require considerable memory, applications should only bind column 0 of the result set if they will actually use bookmarks for most rows. Otherwise, applications should call SQLGetStmtOption with the SQL_GET_BOOKMARK statement option or call SQLGetData for column 0.

irow Argument

For the SQL_FETCH_ABSOLUTE fetch type, SQLExtendedFetch returns the rowset starting at the row number specified by the irow argument.

For the SQL_FETCH_RELATIVE fetch type, SQLExtendedFetch returns the rowset starting irow rows from the first row in the current rowset.

For the SQL_FETCH_BOOKMARK fetch type, the irow argument specifies the bookmark that marks the first row in the requested rowset.

The irow argument is ignored for the SQL_FETCH_NEXT, SQL_FETCH_PRIOR, SQL_FETCH_FIRST, and SQL_FETCH_LAST, fetch types.

rgfRowStatus Argument

In the rgfRowStatus array, SQLExtendedFetch returns any changes in status to each row since it was last retrieved from the data source. Rows may be unchanged (SQL_ROW_SUCCESS), updated (SQL_ROW_UPDATED), deleted (SQL_ROW_DELETED), added (SQL_ROW_ADDED), or were unretrievable due to an error (SQL_ROW_ERROR). For static cursors, this information is available for all rows. For keyset, mixed, and dynamic cursors, this information is only available for rows in the keyset; the driver does not save data outside the keyset and therefore cannot compare the newly retrieved data to anything.

---

Note Some drivers cannot detect changes to data. To determine whether a driver can detect changes to refetched rows, an application calls SQLGetInfo with the SQL_ROW_UPDATES option.

---

The number of elements must equal the number of rows in the rowset (as defined by the SQL_ROWSET_SIZE statement option). If the number of rows fetched is less than the number of elements in the status array, the driver sets remaining status elements to SQL_ROW_NOROW.

When an application calls SQLSetPos with fOption set to SQL_DELETE or SQL_UPDATE, SQLSetPos changes the rgfRowStatus array for the changed row to SQL_ROW_DELETED or SQL_ROW_UPDATED.

---

Note For keyset, mixed, and dynamic cursors, if a key value is updated, the row of data is considered to have been deleted and a new row added.

---

Code Example

The following two examples show how an application could use column-wise or row-wise binding to bind storage locations to the same result set.

For more code examples, see SQLSetPos.

Column-Wise Binding

In the following example, an application declares storage locations for column-wise bound data and the returned numbers of bytes. Because column-wise binding is the default, there is no need, as in the row-wise binding example, to request column-wise binding with SQLSetStmtOption. However, the application does call SQLSetStmtOption to specify the number of rows in the rowset.

The application then executes a SELECT statement to return a result set of the employee names and birthdays, which is sorted by birthday. It calls SQLBindCol to bind the columns of data, passing the addresses of storage locations for both the data and the returned numbers of bytes. Finally, the application fetches the rowset data with SQLExtendedFetch and prints each employee’s name and birthday.

#define ROWS 100
#define NAME_LEN 30
#define BDAY_LEN 11
UCHAR     szName[ROWS][NAME_LEN], szBirthday[ROWS][BDAY_LEN];
SWORD     sAge[ROWS];
SDWORD    cbName[ROWS], cbAge[ROWS], cbBirthday[ROWS];
UDWORD    crow, irow;
UWORD     rgfRowStatus[ROWS];
SQLSetStmtOption(hstmt, SQL_CONCURRENCY, SQL_CONCUR_READ_ONLY);
SQLSetStmtOption(hstmt, SQL_CURSOR_TYPE, SQL_CURSOR_KEYSET_DRIVEN);
SQLSetStmtOption(hstmt, SQL_ROWSET_SIZE, ROWS);
retcode = SQLExecDirect(hstmt,

   "SELECT NAME, AGE, BIRTHDAY FROM EMPLOYEE ORDER BY 3, 2, 1",

            SQL_NTS);
if (retcode == SQL_SUCCESS) {
   SQLBindCol(hstmt, 1, SQL_C_CHAR, szName, NAME_LEN, cbName);
   SQLBindCol(hstmt, 2, SQL_C_SSHORT, sAge, 0, cbAge);
   SQLBindCol(hstmt, 3, SQL_C_CHAR, szBirthday, BDAY_LEN,

              cbBirthday);
   /* Fetch the rowset data and print each row. */
   /* On an error, display a message and exit.  */
   while (TRUE) {
      retcode = SQLExtendedFetch(hstmt, SQL_FETCH_NEXT, 1, &crow,

                                  rgfRowStatus);
      if (retcode == SQL_ERROR || retcode == SQL_SUCCESS_WITH_INFO) {
         show_error();
      }
      if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO){
         for (irow = 0; irow < crow; irow++) {
            if (rgfRowStatus[irow] != SQL_ROW_DELETED && 

               rgfRowStatus[irow] != SQL_ROW_ERROR)
               fprintf(out, "%-*s  %-2d  %*s",

                       NAME_LEN-1, szName[irow], sAge[irow],

                       BDAY_LEN-1, szBirthday[irow]);
         }
      } else {
         break;
      }
   }
}

Row-Wise Binding

In the following example, an application declares an array of structures to hold row-wise bound data and the returned numbers of bytes. Using SQLSetStmtOption, it requests row-wise binding and passes the size of the structure to the driver. The driver will use this size to find successive storage locations in the array of structures. Using SQLSetStmtOption, it specifies the size of the rowset.

The application then executes a SELECT statement to return a result set of the employee names and birthdays, which is sorted by birthday. It calls SQLBindCol to bind the columns of data, passing the addresses of storage locations for both the data and the returned numbers of bytes. Finally, the application fetches the rowset data with SQLExtendedFetch and prints each employee’s name and birthday.

#define ROWS 100
#define NAME_LEN 30
#define BDAY_LEN 11
typedef struct {

   UCHAR     szName[NAME_LEN];

   SDWORD    cbName;

   SWORD     sAge;

   SDWORD    cbAge;

   UCHAR     szBirthday[BDAY_LEN];

   SDWORD    cbBirthday;

   }   EmpTable;
EmpTable rget[ROWS];
UDWORD   crow, irow;
UWORD    rgfRowStatus[ROWS];
SQLSetStmtOption(hstmt, SQL_BIND_TYPE, sizeof(EmpTable));
SQLSetStmtOption(hstmt, SQL_CONCURRENCY, SQL_CONCUR_READ_ONLY);
SQLSetStmtOption(hstmt, SQL_CURSOR_TYPE, SQL_CURSOR_KEYSET_DRIVEN);
SQLSetStmtOption(hstmt, SQL_ROWSET_SIZE, ROWS);
retcode = SQLExecDirect(hstmt,
   "SELECT NAME, AGE, BIRTHDAY FROM EMPLOYEE ORDER BY 3, 2, 1",

            SQL_NTS);
if (retcode == SQL_SUCCESS) {
   SQLBindCol(hstmt, 1, SQL_C_CHAR, rget[0].szName, NAME_LEN,

              &rget[0].cbName);
   SQLBindCol(hstmt, 2, SQL_C_SSHORT, &rget[0].sAge, 0,

              &rget[0].cbAge);
   SQLBindCol(hstmt, 3, SQL_C_CHAR, rget[0].szBirthday, BDAY_LEN,

              &rget[0].cbBirthday);
   /* Fetch the rowset data and print each row. */
   /* On an error, display a message and exit.  */
   while (TRUE) {
      retcode = SQLExtendedFetch(hstmt, SQL_FETCH_NEXT, 1, &crow,

                                 rgfRowStatus);
      if (retcode == SQL_ERROR || retcode == SQL_SUCCESS_WITH_INFO) {
         show_error();
      }
      if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO){
         for (irow = 0; irow < crow; irow++) {
            if (rgfRowStatus[irow] != SQL_ROW_DELETED && 

               rgfRowStatus[irow] != SQL_ROW_ERROR)
               fprintf(out, "%-*s  %-2d  %*s",

                    NAME_LEN-1, rget[irow].szName, rget[irow].sAge,

                    BDAY_LEN-1, rget[irow].szBirthday);
         }
      } else {
         break;
      }
   }
}

Related Functions

SQLFetch (ODBC 1.0, Core)

SQLFetch fetches a row of data from a result set. The driver returns data for all columns that were bound to storage locations with SQLBindCol.

Syntax

RETCODE SQLFetch(hstmt)

The SQLFetch function accepts the following argument.

Returns

SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_NO_DATA_FOUND, SQL_STILL_EXECUTING, SQL_ERROR, or SQL_INVALID_HANDLE.

Diagnostics

When SQLFetch returns either SQL_ERROR or SQL_SUCCESS_WITH_INFO, an associated SQLSTATE value may be obtained by calling SQLError. The following table lists the SQLSTATE values commonly returned by SQLFetch and explains each one in the context of this function; the notation "(DM)" precedes the descriptions of SQLSTATEs returned by the Driver Manager. The return code associated with each SQLSTATE value is SQL_ERROR, unless noted otherwise.

Comments

SQLFetch positions the cursor on the next row of the result set. Before SQLFetch is called the first time, the cursor is positioned before the start of the result set. When the cursor is positioned on the last row of the result set, SQLFetch returns SQL_NO_DATA_FOUND and the cursor is positioned after the end of the result set. An application cannot mix calls to SQLExtendedFetch and SQLFetch for the same cursor.

If the application called SQLBindCol to bind columns, SQLFetch stores data into the locations specified by the calls to SQLBindCol. If the application does not call SQLBindCol to bind any columns, SQLFetch doesn’t return any data; it just moves the cursor to the next row. An application can call SQLGetData to retrieve data that is not bound to a storage location.

The driver manages cursors during the fetch operation and places each value of a bound column into the associated storage. The driver follows these guidelines when performing a fetch operation:

SQLFetch accesses column data in left-to-right order.

After each fetch, pcbValue (specified in SQLBindCol) contains the number of bytes available to return for the column. This is the number of bytes available prior to calling SQLFetch. If the number of bytes available to return cannot be determined in advance, the driver sets pcbValue to SQL_NO_TOTAL. (If SQL_MAX_LENGTH has been specified with SQLSetStmtOption and the number of bytes available to return is greater than SQL_MAX_LENGTH, pcbValue contains SQL_MAX_LENGTH.)

---

Note The SQL_MAX_LENGTH statement option is intended to reduce network traffic and may not be supported by all drivers. To guarantee that data is truncated, an application should allocate a buffer of the desired size and specify this size in the cbValueMax argument.

---

If rgbValue is not large enough to hold the entire result, the driver stores part of the value and returns SQL_SUCCESS_WITH_INFO. A subsequent call to SQLError indicates that a truncation occurred. The application can compare pcbValue to cbValueMax (specified in SQLBindCol) to determine which column or columns were truncated. If pcbValue is greater than or equal to cbValueMax, then truncation occurred.

If the data value for the column is NULL, the driver stores SQL_NULL_DATA in pcbValue.

SQLFetch is valid only after a call that returns a result set.

For information about conversions allowed by SQLBindCol and SQLGetData, see "Converting Data from SQL to C Data Types" in Appendix D, "Data Types."

Code Example

See SQLBindCol, SQLColumns, SQLGetData, and SQLProcedures.

Related Functions

SQLFetchPrev (SOLID Extension)

SQLFetchPrev fetches a row of data from a result set. The driver returns data for all columns that were bound to storage locations with SQLBindCol.

Syntax

RETCODE SQLFetchPrev(hstmt)

The SQLFetchPrev function accepts the following argument.

Returns

SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_NO_DATA_FOUND, SQL_ERROR, or SQL_INVALID_HANDLE.

Diagnostics

When SQLFetchPrev returns either SQL_ERROR or SQL_SUCCESS_WITH_INFO, an associated SQLSTATE value may be obtained by calling SQLError. The following table lists the SQLSTATE values commonly returned by SQLFetchPrev and explains each one in the context of this function. The return code associated with each SQLSTATE value is SQL_ERROR, unless noted otherwise.

Comments

SQLFetchPrev positions the cursor on the previous row of the result set. SQLFetchPrev returns SQL_NO_DATA_FOUND and the cursor is positioned before the start of the result set if SQLFetchPrev is called before SQLFetch has been called. When the cursor is positioned on the first row of the result set, SQLFetchPrev returns the data of the first row again. An application cannot mix calls to SQLExtendedFetch and SQLFetchPrev for the same cursor.

If the application called SQLBindCol to bind columns, SQLFetchPrev stores data into the locations specified by the calls to SQLBindCol. If the application does not call SQLBindCol to bind any columns, SQLFetchPrev doesn’t return any data; it just moves the cursor to the next row. An application can call SQLGetData to retrieve data that is not bound to a storage location.

The driver manages cursors during the fetch operation and places each value of a bound column into the associated storage. The driver follows these guidelines when performing a fetch operation:

SQLFetchPrev accesses column data in left-to-right order.

After each fetch, pcbValue (specified in SQLBindCol) contains the number of bytes available to return for the column. This is the number of bytes available prior to calling SQLFetchPrev. If the number of bytes available to return cannot be determined in advance, the driver sets pcbValue to SQL_NO_TOTAL.

If rgbValue is not large enough to hold the entire result, the driver stores part of the value and returns SQL_SUCCESS_WITH_INFO. A subsequent call to SQLError indicates that a truncation occurred. The application can compare pcbValue to cbValueMax (specified in SQLBindCol) to determine which column or columns were truncated. If pcbValue is greater than or equal to cbValueMax, then truncation occurred.

If the data value for the column is NULL, the driver stores SQL_NULL_DATA in pcbValue.

SQLFetchPrev is valid only after a call that returns a result set.

For information about conversions allowed by SQLBindCol and SQLGetData, see "Converting Data from SQL to C Data Types" in Appendix D, "Data Types."

Code Example

See SQLBindCol, SQLColumns, SQLGetData, and SQLProcedures.

Related Functions

SQLForeignKeys (ODBC 1.0, Level 2)

SQLForeignKeys can return:

A list of foreign keys in the specified table (columns in the specified table that refer to primary keys in other tables).

A list of foreign keys in other tables that refer to the primary key in the specified table.

The driver returns each list as a result set on the specified hstmt.

Syntax

RETCODE SQLForeignKeys(hstmt, szPkTableQualifier, cbPkTableQualifier, szPkTableOwner, cbPkTableOwner, szPkTableName, cbPkTableName, szFkTableQualifier, cbFkTableQualifier, szFkTableOwner, cbFkTableOwner, szFkTableName, cbFkTableName)

The SQLForeignKeys function accepts the following arguments.

Returns

SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_STILL_EXECUTING, SQL_ERROR, or SQL_INVALID_HANDLE.

Diagnostics

When SQLForeignKeys returns SQL_ERROR or SQL_SUCCESS_WITH_INFO, an associated SQLSTATE value may be obtained by calling SQLError. The following table lists the SQLSTATE values commonly returned by SQLForeignKeys and explains each one in the context of this function; the notation "(DM)" precedes the descriptions of SQLSTATEs returned by the Driver Manager. The return code associated with each SQLSTATE value is SQL_ERROR, unless noted otherwise.

Comments

If szPkTableName contains a table name, SQLForeignKeys returns a result set containing the primary key of the specified table and all of the foreign keys that refer to it.

If szFkTableName contains a table name, SQLForeignKeys returns a result set containing all of the foreign keys in the specified table and the primary keys (in other tables) to which they refer.

If both szPkTableName and szFkTableName contain table names, SQLForeignKeys returns the foreign keys in the table specified in szFkTableName that refer to the primary key of the table specified in szPkTableName. This should be one key at most.

SQLForeignKeys returns results as a standard result set. If the foreign keys associated with a primary key are requested, the result set is ordered by FKTABLE_QUALIFIER, FKTABLE_OWNER, FKTABLE_NAME, and KEY_SEQ. If the primary keys associated with a foreign key are requested, the result set is ordered by PKTABLE_QUALIFIER, PKTABLE_OWNER, PKTABLE_NAME, and KEY_SEQ. The following table lists the columns in the result set.

The lengths of VARCHAR columns shown in the table are maximums; the actual lengths depend on the data source. To determine the actual lengths of the TABLE_QUALIFIER, TABLE_OWNER, TABLE_NAME, and COLUMN_NAME columns, an application can call SQLGetInfo with the SQL_MAX_QUALIFIER_NAME_LEN, SQL_MAX_OWNER_NAME_LEN, SQL_MAX_TABLE_NAME_LEN, and SQL_MAX_COLUMN_NAME_LEN options.

---

Note The FK_NAME and PK_NAME columns were added in ODBC 2.0. ODBC 1.0 drivers may return different, driver-specific columns with the same column numbers.

---

Code Example

This example uses four tables:

In the SALES_ORDER table, CUSTOMER_ID identifies the customer to whom the sale has been made. It is a foreign key that refers to CUSTOMER_ID in the CUSTOMER table. EMPLOYEE_ID identifies the employee who made the sale. It is a foreign key that refers to EMPLOYEE_ID in the EMPLOYEE table.

In the SALES_LINE table, SALES_ID identifies the sales order with which the line item is associated. It is a foreign key that refers to SALES_ID in the SALES_ORDER table.

This example calls SQLPrimaryKeys to get the primary key of the SALES_ORDER table. The result set will have one row and the significant columns are:

Next, the example calls SQLForeignKeys to get the foreign keys in other tables that reference the primary key of the SALES_ORDER table. The result set will have one row and the significant columns are:

Finally, the example calls SQLForeignKeys to get the foreign keys in the SALES_ORDER table the refer to the primary keys of other tables. The result set will have two rows and the significant columns are:

#define TAB_LEN SQL_MAX_TABLE_NAME_LEN + 1
#define COL_LEN SQL_MAX_COLUMN_NAME_LEN + 1
LPSTR   szTable;             /* Table to display       */
UCHAR   szPkTable[TAB_LEN];  /* Primary key table name */
UCHAR   szFkTable[TAB_LEN];  /* Foreign key table name */
UCHAR   szPkCol[COL_LEN];    /* Primary key column     */
UCHAR   szFkCol[COL_LEN];    /* Foreign key column     */
HSTMT      hstmt;
SDWORD      cbPkTable, cbPkCol, cbFkTable, cbFkCol, cbKeySeq;
SWORD      iKeySeq;
RETCODE      retcode;
/* Bind the columns that describe the primary and foreign keys.  */
/* Ignore the table owner, name, and qualifier for this example. */
SQLBindCol(hstmt, 3, SQL_C_CHAR, szPkTable, TAB_LEN, &cbPkTable);
SQLBindCol(hstmt, 4, SQL_C_CHAR, szPkCol, COL_LEN, &cbPkCol);
SQLBindCol(hstmt, 5, SQL_C_SSHORT, &iKeySeq, TAB_LEN, &cbKeySeq);
SQLBindCol(hstmt, 7, SQL_C_CHAR, szFkTable, TAB_LEN, &cbFkTable);
SQLBindCol(hstmt, 8, SQL_C_CHAR, szFkCol, COL_LEN, &cbFkCol);
strcpy(szTable, "SALES_ORDER");
/* Get the names of the columns in the primary key.              */
retcode = SQLPrimaryKeys(hstmt,
                         NULL, 0,           /* Table qualifier   */
                         NULL, 0,           /* Table owner       */
                         szTable, SQL_NTS); /* Table name        */
while ((retcode == SQL_SUCCESS) || (retcode == SQL SUCCESS_WITH_INFO)) {
   /* Fetch and display the result set. This will be a list of the */
   /* columns in the primary key of the SALES_ORDER table.         */
   retcode = SQLFetch(hstmt);
   if (retcode == SQL_SUCCESS || retcode != SQL_SUCCESS_WITH_INFO)
      fprintf(out, "Column: %s    Key Seq: %hd \n", szPkCol, iKeySeq);
}
/* Close the cursor (the hstmt is still allocated).                */
SQLFreeStmt(hstmt, SQL_CLOSE);
/* Get all the foreign keys that refer to SALES_ORDER primary key. */
retcode = SQLForeignKeys(hstmt,
                         NULL, 0,          /* Primary qualifier    */
                         NULL, 0,          /* Primary owner        */
                         szTable, SQL_NTS, /* Primary table        */
                         NULL, 0,          /* Foreign qualifier    */
                         NULL, 0,          /* Foreign owner        */
                         NULL, 0);         /* Foreign table        */
while ((retcode == SQL_SUCCESS) || (retcode == SQL_SUCCESS_WITH_INFO)) {
   /* Fetch and display the result set. This will be all of the    */
   /* foreign keys in other tables that refer to the SALES_ORDER   */
   /* primary key.                                                 */
   retcode = SQLFetch(hstmt);
   if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
      fprintf(out, "%-s ( %-s ) <-- %-s ( %-s )\n", szPkTable,

              szPkCol, szFkTable, szFkCol);
}
/* Close the cursor (the hstmt is still allocated).                 */
SQLFreeStmt(hstmt, SQL_CLOSE);
/* Get all the foreign keys in the SALES_ORDER table.               */
retcode = SQLForeignKeys(hstmt,
                         NULL, 0,           /* Primary qualifier    */
                         NULL, 0,           /* Primary owner        */
                         NULL, 0,           /* Primary table        */
                         NULL, 0,           /* Foreign qualifier    */
                         NULL, 0,           /* Foreign owner        */
                         szTable, SQL_NTS); /* Foreign table        */
while ((retcode == SQL_SUCCESS) || (retcode == SQL_SUCCESS_WITH_INFO)) {
   /* Fetch and display the result set. This will be all of the    */
   /* primary keys in other tables that are referred to by foreign */
   /* keys in the SALES_ORDER table.                               */
   retcode = SQLFetch(hstmt);
   if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
      fprintf(out, "%-s ( %-s )--> %-s ( %-s )\n", szFkTable, szFkCol,
              szPkTable, szPkCol);
}
/* Free the hstmt. */
SQLFreeStmt(hstmt, SQL_DROP);

Related Functions

SQLFreeConnect (ODBC 1.0, Core)

SQLFreeConnect releases a connection handle and frees all memory associated with the handle.

Syntax

RETCODE SQLFreeConnect(hdbc)

The SQLFreeConnect function accepts the following argument.

Returns

SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_ERROR, or SQL_INVALID_HANDLE.

Diagnostics

When SQLFreeConnect returns SQL_ERROR or SQL_SUCCESS_WITH_INFO, an associated SQLSTATE value may be obtained by calling SQLError. The following table lists the SQLSTATE values commonly returned by SQLFreeConnect and explains each one in the context of this function; the notation "(DM)" precedes the descriptions of SQLSTATEs returned by the Driver Manager. The return code associated with each SQLSTATE value is SQL_ERROR, unless noted otherwise.

Comments

Prior to calling SQLFreeConnect, an application must call SQLDisconnect for the hdbc. Otherwise, SQLFreeConnect returns SQL_ERROR and the hdbc remains valid. Note that SQLDisconnect automatically drops any hstmts open on the hdbc.

Code Example

See SQLBrowseConnect and SQLConnect.

Related Functions