Programming Interfaces Guide
   CHAPTER 4. ODBC Programming     

Working with result sets

ODBC provides extensive support for different kinds of cursor and cursor operations.

     For an introduction to cursors, see Working with cursors.

A cursor is opened using SQLExecute or SQLExecDirect, rows are fetched using SQLFetch or SQLExtendedFetch and the cursor is closed using SQLFreeStmt.

To fetch values from a cursor, the application can use either SQLBindCol or SQLGetData. If you use SQLBindCol, values are automatically retrieved on each fetch. If you use SQLGetData, you must call it for each column after each fetch.

The following code fragment opens and reads a cursor. Error checking has been omitted to make the example easier to read.

. . .
HDBC dbc;
HSTMT stmt;
RETCODE retcode;
long emp_id;
char emp_lname[20];

SQLAllocStmt( dbc, &stmt );
SQLExecDirect( stmt,
            "select emp_id,emp_lname
            from employee", SQL_NTS );
SQLBindCol( stmt, 1, SQL_C_LONG, &emp_id,
            sizeof(emp_id), NULL );
SQLBindCol( stmt, 2, SQL_C_CHAR, &emp_lname,
            sizeof(emp_lname), NULL );

for(;;) {
   retcode = SQLFetch( stmt );
   if( retcode == SQL_NO_DATA_FOUND ) break;
   print_employee( emp_id, emp_lname);
}

/* Using SQL_CLOSE closes the cursor
   but does not free the statement */
SQLFreeStmt( stmt, SQL_CLOSE );
. . .

Notes 

• ODBC documentation suggests that you use SELECT... FOR UPDATE to indicate that a query is updateable using positioned operations. You do not need to use the FOR UPDATE clause in Adaptive Server Anywhere; SELECT statements are automatically updateable as long as the underlying query supports updates. That is to say, as long as a data modification statement on the columns in the result is meaningful, then positioned data modification statements can be carried out on the cursor.

• ODBC 3.0 provides a cursor type called a block cursor. When you use a block cursor, you can use SQLFetch to fetch a block of rows, rather than a single row.

• There are two alternatives for carrying out positioned updates and deletes in ODBC. You can send positioned UPDATE and DELETE statements using SQLExecute or you can use SQLSetPos to carry out the operation. Depending on the parameters supplied (SQL_POSITION, SQL_REFRESH, SQL_UPDATE, SQL_DELETE) SQLSetPos sets the cursor position and allows an application to refresh data, or update or delete data in the result set.

  With Adaptive Server Anywhere, you should use SQLSetPos to carry out positioned operations on cursors.

Bookmarks and cursors 

ODBC provides bookmarks, which are values used to identify rows in a cursor. Adaptive Server Anywhere supports bookmarks for all kinds of cursor except dynamic cursors.

Before ODBC 3.0, a database could specify only whether it supports bookmarks or not. There was no way for a database server to indicate for what kind of cursor bookmarks were supported. Adaptive Server Anywhere returns that it does support bookmarks. There is therefore nothing in ODBC to prevent you from trying to use bookmarks with dynamic cursors; however, you should not use this combination.