In the MySQL native interface, the standard query method returns the entire result set to the client, where it can be accessed randomly by specifying the row number. Thus, the issue of cursor types doesn’t really arise. However, in the ODBC interface, the idea of returning all the results in one block to the client doesn’t exist, so instead there are various schemes for returning or exposing a subset of the results to the client, and this leads to the need for different cursor types:
The default is a forward-only cursor, which only permits reading the records in the result set in forward order. Although limiting, it provides the highest performance.
A static cursor references the selected records in the result set as they were at the time the selection was initially made. Any updates to those records which are initiated by other processes and which occur between the time the query was executed and the data was fetched by the client are ignored. This kind of cursor most closely reflects the standard MySQL cursor where the entire result set is transferred to the client immediately, but the implementation details are left to the ODBC driver.
A dynamic cursor will reflect updates to the selected records that occur between the time of the query and the time the rows are fetched. This kind of cursor is frequently not supported by ODBC drivers because of the implementation difficulties.
A keyset-driven cursor is a compromise between the static and dynamic cursors. At the time of the selection, the driver generates a key from the result data which is unique for each record in the result set, and then uses that key internally to access the actual data for each row as the row is fetched. This preserves the original order of the records in the result set, and ignores any inserts that would have otherwise affected the result set when using a dynamic cursor. Any updates and deletions to the underlying records will be reflected as the rows are fetched (in which case the SQLOP_FETCH_ROW operation should return cmdhdr.rcext set to SQL_ROW_UPDATED or SQL_ROW_DELETED).
