Using SQLBindCol
The application binds columns by calling SQLBindCol. This function binds one column at a time. With it, the application specifies the following:
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.
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 Converting Data from SQL to C Data Types in Appendix D: Data Types. For information about the byte length, see Data Buffer Length.
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.
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 that 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 interested only in whether the salary is NULL, because 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 preceding 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 preceding example. For more information about alignment, see Alignment.
// 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 that 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);