SQL Cursor FETCH reading INSERT row outside of cursor

Products

• Datacom/DB
• Datacom
• DATACOM - AD
• Datacom/AD

Issue/Introduction

A program creates a Datacom SQL cursor and does a FETCH.

It does a DELETE for certain rows and then it does an INSERT outside of the cursor to re-add the row with modified values.

The next FETCH reads the record just inserted.

The program expects to read the next record in the cursor not the one just inserted.

 

Resolution

The behavior of the cursor when inserting rows can be controlled by using the DECLARE CURSOR SENSITIVE / INSENSITIVE option.

In this case use option:
DECLARE CURSOR ... SENSITIVE STATIC SCROLL

This will not read the row inserted outside the cursor when doing a FETCH.

 

Additional Information

See documentation section DECLARE CURSOR Statement.