18.1.4 Effects of update operations on a retrieval using a cursor
When an update operation is performed during a retrieval using a cursor, the results of the update operation might be applied to the retrieval results, depending on the timing. To prevent the results of update operations from being applied to retrieval results, do the following:
-
Close the cursor before adding or updating rows.
-
Specify data and search conditions in such a manner that rows to be added or updated are not included in the retrieval results.
The following example performs updating while a retrieval processing using a cursor is underway:
char *selSql = "SELECT * FROM T1 WHERE C1 BETWEEN 10 AND 20";
char *updSql = "UPDATE T1 SET C1=30 WHERE C1=20";
/* preprocessing for the SELECT statement */
rtnc = a_rdb_SQLPrepare(cnctContext, hStmt1,selSql);
:
/* retrieve rows */
rtnc = a_rdb_SQLFetch(cnctContext, hStmt1); ...1
/* preprocessing for UPDATE */
rtnc = a_rdb_SQLPrepare(cnctContext, hStmt2, updSql);
/* update rows */
rtnc = a_rdb_SQLExecute(cnctContext, hStmt2); ...2
/* retrieve rows */
rtnc = a_rdb_SQLFetch(cnctContext, hStmt1); ...3
:
- Explanation:
-
If rows whose C1 column is 20 are updated in 2, the HADB server is already performing retrieval processing asynchronously with the application program due to execution of the first FETCH in 1. Depending on the timing, the application program might not be able at 3 to retrieve rows whose C1 column is 20. If retrieval of rows whose C1 column is 20 has already been completed, the application program can retrieve rows.