SQLFetch VS SQLExtendedFetch

[tcarvin]

The documentation I've read seems to indicate that I must use SQLExtendedFetch
for all SQL_CURSOR_TYPEs except SQL_CURSOR_FORWARD_ONLY. I don't want to
use the ExtendedFetch functionality since I'm only interested in using the
ODBC_CURSOR_STATIC. Also, if I set SQL_CONCURRENCY to SQL_CONCUR_READ_ONLY
will that provide me with the "snapshot" effect I'm looking for? I don't
want to lock the rows I'm looking at.

Thanks,
Tom Carvin

[Robert Sundström]

The differences between SQLFetch and SQLExtendedFetch (or SQLFetchScroll if we
talking about newer ODBC specifications) have little to do with the actual
isolation used in the query. Unfortunately, it is difficult to know and
control exactly how my particular database server will behave in the
conditions you describe. ODBC does not specify how the database should work
and which rows to "lock". If it is very important to you obtain a "snapshot"
effect and at the same time avoid locking of the base table, you are likely to
have two options:

1. Make a temporary table and populate it with the desired result set. This
avoids locking in the base table since you will be working with the static
cursor on the temporary table. Perhaps a good choice for database systems
based on pessimistic concurrency.

2. If you don't like temporary tables you can switch to a database system
based on optimistic concurrency, which will offer the features you desire
without temporary tables. This is provided it supports static cursors.
Optimistic systems don't rely on locks but rather assumes transactions will
succeed, and aborts any offending transactions on commit. No locks are held
during the transaction.

(Oh, I almost forgot to mention that Mimer is one of those systems offering
static cursors and optimistic concurrency control... :-)

-
Robert Sundström, Mimer SQL Development
Upright Database Technology AB, http://www.mimer.com
Validate your SQL statements at http://developer.mimer.com/parser