Using a Dummy Query to Determine Table Characteristics
Avoid using SQLColumns to determine characteristics about a table. Instead, use a dummy query with SQLDescribeCol.
Consider an application that allows the user to choose the columns that will be selected. Should the application use SQLColumns to return information about the columns to the user or prepare a dummy query and call SQLDescribeCol?
Case 1: SQLColumns Method
rc = SQLColumns (... "UnknownTable" ...);
// This call to SQLColumns will generate a query to the system catalogs...
// possibly a join which must be prepared, executed, and produce a result set
rc = SQLBindCol (...);
rc = SQLExtendedFetch (...);
// user must retrieve N rows from the server
// N = # result columns of UnknownTable
// result column information has now been obtained
Case 2: SQLDescribeCol Method
// prepare dummy query
rc = SQLPrepare (... "SELECT * FROM UnknownTable WHERE 1 = 0" ...);
// query is never executed on the server - only prepared
rc = SQLNumResultCols (...);
for (irow = 1; irow <= NumColumns; irow++) {
rc = SQLDescribeCol (...)
// + optional calls to SQLColAttributes
}
// result column information has now been obtained
// Note we also know the column ordering within the table!
// This information cannot be assumed from the SQLColumns example.
In both cases, a query is sent to the server, but in Case 1, the query must be evaluated and form a result set that must be sent to the client. Clearly, Case 2 is the better performing model.
To complicate this discussion, let us consider a database server that does not natively support preparing a SQL statement. The performance of Case 1 does not change, but the performance of Case 2 improves slightly because the dummy query is evaluated before being prepared. Because the Where clause of the query always evaluates to FALSE, the query generates no result rows and should execute without accessing table data. Again, for this situation, Case 2 outperforms Case 1.
