Fetch Loops

Fetch Loops

We've already noted that an SQL result set is a set of rows that a SELECT statement (or an equivalent to a SELECT statement, such as a catalog function or a stored procedure that contains a SELECT) returns. A fetch loop is a loop that contains calls to a function that takes a row from a result set. For example, suppose a column called column1 contains a number, and you want to display all occurrences of the column. Listing 12-2 shows a common prototype to get this done.

Listing 12-2 Common prototype for numeric display

 SQLRETURN rc; SQLHSTMT hstmt; SQLINTEGER numx; SQLINTEGER numx_indicator; SQLExecDirect(    hstmt,    "SELECT column1 FROM Table1",SQL_NTS); for (;;) {   rc=SQLFetch(hstmt);   if (rc==SQL_NO_DATA) break;   SQLGetData(    hstmt,    1,    targettype,       /* insert the correct type here */    &numx,    sizeof(numx),    &numx_indicator);   if (numx_indicator!=-1) printf("%d.\n",numx);   } SQLCloseCursor(    hstmt); 

Although the Listing 12-2 prototype works, it could be improved in several places. For example:

• Before the transaction

  Before beginning the transaction, determine when the session starts, and whether numx can contain NULL. Prefetch catalog information and keep the data in application buffers.

• Before the SELECT

  Before selecting, give the DBMS any information that it might be able to use for optimizing the transaction. The calls for this purpose are:

   SQLSetStmtAttr(    hstmt,SQL_ATTR_CONCURRENCY,SQL_ATTR_READ_ONLY,...); SQLSetStmtAttr(    hstmt,SQL_ATTR_CURSOR_SCROLLABLE,SQL_NONSCROLLABLE,...); SQLSetStmtAttr(    hstmt,SQL_ATTR_CURSOR_TYPE,SQL_CURSOR_FORWARD_ONLY,...); SQLSetStmtAttr(    hstmt,SQL_ATTR_NOSCAN,SQL_NOSCAN_ON,...); 

  There is also a call for setting the rowset size :

   SQLSetStmtAttr(    hstmt,SQL_ATTR_ROW_ARRAY_SIZE,20,...); 

  In this context, the rowset size is the number of rows that will be fetched at once and is not to be confused with the result set size , which is the total number of rows that are selected. Think of it this way:

  If a single call to SQLFetch can get 20 rows at once into an array, that's goodbecause all 20 rows can fit easily in one network packet. Unfortunately, the loop becomes far more complex. If you set the rowset size to 20, you must also (a) redefine numx and numx_indicator as arrays rather than integers, (b) check whether the DBMS returns SQLSTATE 01S02 (which means "I can't support this size so I've changed rowset size to some other value"), and (c) put a second loop, which prints each non-NULL occurrence of numx[n] , within the fetch loop. A serious ODBC application must use SQL_ATTR_ROW_ARRAY_SIZE .

• Before the loop

  Before beginning the loop, add an error check to ensure that column1 really is an INTEGER and not a SMALLINT or BIGINT. (Passing sizeof(numx) among the SQLGetData arguments has absolutely no effect on anything.) SQLDescribeCol would be appropriate here and would also be useful for testing whether column1 can contain NULL. If column1 can't contain NULL, there's no need for a NULL indicator variable (unless you're going to be doing an outer join or some other query that can evaluate to NULL).

• Before the fetch

  Before you fetch, change the SQLGetData call to SQLBindCol the use of SQLGetData for a short numeric variable is inappropriate. Use SQLGetData only when there is no loop, when the fetch is likely to fail, or when stack space for variables is limited. None of these conditions applies in this prototype. While you're at it, the SQLBindCol call should not only be before the SQLFetch call, you should place it before the loop starts. Frequently, programmers put SQLBindCol somewhere within the fetch loop so that it will be clear what the fetch is doing, but this wastes time. A fetch loop that contains SQLBindCol should end with:

   SQLFreeStmt(hstmt,SQL_UNBIND); 

• After the loop

  In this particular case, you would expect little advantage from processing the SQLFetch and the C printf statement asynchronously, which is possible by using different threads or via another SQLSetStmtAttr option. (If print speed is a real concern, improve it by fetching everything into an array and then calling printf after closing the cursor.) On the other hand, the asynchronous methods allow the user a chance to cancel, which is often a useful option for fetch loops. Finally, end with SQLEndTran instead of SQLCloseCursor if the DBMS is in manual-commit mode and will thus automatically close cursors when the transaction ends.

The Bottom Line: Fetch Loops

Improve your fetch loops by determining when the session starts and whether you need NULL indicators. Do this before the transaction starts.

Improve your fetch loops by using SQLSetStmtAttr to give the DBMS information that it can use to optimize the transaction for you.

Improve your fetch loops by determining the data type of the columns you're fetching and whether they can contain NULL. Do this before the loop starts.

Use SQLBindCol instead of SQLGetData unless (a) there is no loop, (b) the fetch is likely to fail, or (c) stack space for variables is limited. To save time, put SQLBindCol before the fetch loop starts.

If you invest a lot of your time in a fetch loop, the loop will run much more quickly.