Contents|Index|Previous|Next

Using SQLBindCol

The application binds columns by calling SQLBindCol. This function binds one column at a time. With it, the application specifies:

odbc00090000.gif The column number. Column 0 is the bookmark column; this column is not included in some result sets. All other columns are numbered starting with the number 1. It is an error to bind a higher numbered column than there are columns in the result set; this error cannot be detected until the result set has been created, so it is returned by SQLFetch, not SQLBindCol.

odbc00090000.gif The C data type, address, and byte length of the variable bound to the column. It is an error to specify a C data type to which the SQL data type of the column cannot be converted; this error might not be detected until the result set has been created, so it is returned by SQLFetch, not SQLBindCol. For a list of supported conversions, see Appendix D, “Data Types.” For information about the byte length, see “Data Buffer Length” in Chapter 4, “ODBC Fundamentals.”

odbc00090000.gif The address of a length/indicator buffer. The length/indicator buffer is optional. It is used to return the byte length of binary or character data or return SQL_NULL_DATA if the data is NULL. For more information, see “Using Length/Indicator Values” in Chapter 4, “ODBC Fundamentals.”

When SQLBindCol is called, the driver associates this information with the statement. When each row of data is fetched, it uses the information to place the data for each column in the bound application variables.

For example, the following code binds variables to the SalesPerson and CustID columns. Data for the columns will be returned in SalesPerson and CustID. Because SalesPerson is a character buffer, the application specifies its byte length (11) so the driver can determine whether to truncate the data. The byte length of the returned title, or whether it is NULL, will be returned in SalesPersonLenOrInd.

Because CustID is an integer variable and has fixed length, there is no need to specify its byte length; the driver assumes it is sizeof(SQLUINTEGER). The byte length of the returned customer ID data, or whether it is NULL, will be returned in CustIDInd. Note that the application is only interested in whether the salary is NULL, as the byte length is always sizeof(SQLUINTEGER).

SQLCHAR SalesPerson[11];

SQLUINTEGER CustID;

SQLINTEGER SalesPersonLenOrInd, CustIDInd;

SQLRETURN rc;

SQLHSTMT hstmt;

// Bind SalesPerson to the SalesPerson column and CustID to the CustID column.

SQLBindCol(hstmt, 1, SQL_C_CHAR, SalesPerson, sizeof(SalesPerson),

&SalesPersonLenOrInd);

SQLBindCol(hstmt, 2, SQL_C_ULONG, &CustID, 0, &CustIDInd);

// Execute a statement to get the sales person/customer of all orders.

SQLExecDirect(hstmt, "SELECT SalesPerson, CustID FROM Orders ORDER BY SalesPerson",

SQL_NTS);

// Fetch and print the data. Print "NULL" if the data is NULL. Code to check if rc

// equals SQL_ERROR or SQL_SUCCESS_WITH_INFO not shown.

while ((rc = SQLFetch(hstmt)) != SQL_NO_DATA) {

if (SalesPersonLenOrInd == SQL_NULL_DATA)

printf("NULL ");

else

printf("%10s ", SalesPerson);

if (CustIDInd == SQL_NULL_DATA)

printf("NULL\n");

else

printf("%d\n", CustID);

}

// Close the cursor.

SQLCloseCursor(hstmt);

The following code executes a SELECT statement entered by the user and prints each row of data in the result set. Because the application cannot predict the shape of the result set created by the SELECT statement, it cannot bind hard-coded variables to the result set as in the previous example. Instead, the application allocates a buffer that holds the data and a length/indicator buffer for each column in that row. For each column, it calculates the offset to the start of the memory for the column and adjusts this offset so that the data and length/indicator buffers for the column start on alignment boundaries. It then binds the memory starting at the offset to the column. From the driver’s point of view, the address of this memory is indistinguishable from the address of a variable bound in the previous example. For more information about alignment, see “Alignment” in Chapter 17, “Programming Considerations.”

// This application allocates a buffer at run time. For each column, this buffer

// contains memory for the column’s data and length/indicator. For example:

//

// column 1 column 2 column 3 column 4

// <------------><---------------><-----><------------>

// db1 li1 db2 li2 db3 li3 db4 li4

// | | | | | | | |

// _____V_____V________V_______V___V___V______V_____V_

// |__________|__|_____________|__|___|__|__________|__|

//

// dbn = data buffer for column n

// lin = length/indicator buffer for column n

// Define a macro to increase the size of a buffer so it is a multiple of the alignment

// size. Thus, if a buffer starts on an alignment boundary, it will end just before the

// next alignment boundary. In this example, an alignment size of 4 is used because

// this is the size of the largest data type used in the application’s buffer -- the

// size of an SDWORD and of the largest default C data type are both 4. If a larger

// data type (such as _int64) was used, it would be necessary to align for that size.

#define ALIGNSIZE 4

#define ALIGNBUF(Length) Length % ALIGNSIZE ? \

Length + ALIGNSIZE - (Length % ALIGNSIZE) : Length

SQLCHAR SelectStmt[100];

SQLSMALLINT NumCols, *CTypeArray, i;

SQLINTEGER *ColLenArray, *OffsetArray, SQLType, *DataPtr;

SQLRETURN rc;

SQLHSTMT hstmt;

// Get a SELECT statement from the user and execute it.

GetSelectStmt(SelectStmt, 100);

SQLExecDirect(hstmt, SelectStmt, SQL_NTS);

// Determine the number of result set columns. Allocate arrays to hold the C type,

// byte length, and buffer offset to the data.

SQLNumResultCols(hstmt, &NumCols);

CTypeArray = (SQLSMALLINT *) malloc(NumCols * sizeof(SQLSMALLINT));

ColLenArray = (SQLINTEGER *) malloc(NumCols * sizeof(SQLINTEGER));

OffsetArray = (SQLINTEGER *) malloc(NumCols * sizeof(SQLINTEGER));

OffsetArray[0] = 0;

for (i = 0; i < NumCols; i++) {

// Determine the column's SQL type. GetDefaultCType contains a switch statement that

// returns the default C type for each SQL type.

SQLColAttribute(hstmt, ((SQLUSMALLINT) i) + 1, SQL_DESC_TYPE, NULL, 0, NULL, (SQLPOINTER) &SQLType);

CTypeArray[i] = GetDefaultCType(SQLType);

// Determine the column's byte length. Calculate the offset in the buffer to the

// data as the offset to the previous column, plus the byte length of the previous

// column, plus the byte length of the previous column's length/indicator buffer.

// Note that the byte length of the column and the length/indicator buffer are

// increased so that, assuming they start on an alignment boundary, they will end on

// the byte before the next alignment boundary. Although this might leave some holes

// in the buffer, it is a relatively inexpensive way to guarantee alignment.

SQLColAttribute(hstmt, ((SQLUSMALLINT) i)+1, SQL_DESC_OCTET_LENGTH, NULL, 0, NULL, &ColLenArray[i]);

ColLenArray[i] = ALIGNBUF(ColLenArray[i]);

if (i)

OffsetArray[i] = OffsetArray[i-1]+ColLenArray[i-1]+ALIGNBUF(sizeof(SQLINTEGER));

}

// Allocate the data buffer. The size of the buffer is equal to the offset to the data

// buffer for the final column, plus the byte length of the data buffer and

// length/indicator buffer for the last column.

void *DataPtr = malloc(OffsetArray[NumCols - 1] +

ColLenArray[NumCols - 1] + ALIGNBUF(sizeof(SQLINTEGER)));

// For each column, bind the address in the buffer at the start of the memory allocated

// for that column's data and the address at the start of the memory allocated for that

// column's length/indicator buffer.

for (i = 0; i < NumCols; i++)

SQLBindCol(hstmt,

((SQLUSMALLINT) i) + 1,

CTypeArray[i],

(SQLPOINTER)((SQLCHAR *)DataPtr + OffsetArray[i]),

ColLenArray[i],

(SQLINTEGER *)((SQLCHAR *)DataPtr + OffsetArray[i] + ColLenArray[i]));

// Retrieve and print each row. PrintData accepts a pointer to the data, its C type,

// and its byte length/indicator. It contains a switch statement that casts and prints

// the data according to its type. Code to check if rc equals SQL_ERROR or

// SQL_SUCCESS_WITH_INFO not shown.

while ((rc = SQLFetch(hstmt)) != SQL_NO_DATA) {

for (i = 0; i < NumCols; i++) {

PrintData((SQLCHAR *)DataPtr[OffsetArray[i]], CTypeArray[i],

(SQLINTEGER *)((SQLCHAR *)DataPtr[OffsetArray[i] + ColLenArray[i]]));

}

}

// Close the cursor.

SQLCloseCursor(hstmt);