Why does the error occur sometimes when using cursor update in the ESQL application?
For example:
We have a table with schema:
dmSQL> def table card1;
create table SYSADM.CARD1 (
NUM SERIAL(1),
DD INTEGER not null,
EE CHAR(1) not null,
FIRSTNAME VARCHAR(30) not null,
LASTNAME VARCHAR(30) not null ,
TITLE VARCHAR(30) default null ,
BMP LONG VARBINARY default null )
in DEFTABLESPACE lock mode page fillfactor 100 ;
alter table SYSADM.CARD1 primary key ( NUM, DD, EE, FIRSTNAME);
Then write the ESQL/C code:
EXEC SQL declare scroll_cursor scroll cursor for
select Num,dd,ee,LastName, FirstName from Card1
where NUM < 9 order by EE,NUM, DD,FIRSTNAME
into :Num, :LastName :indvalue, :FirstName :indvalue
for update of LastName;
The above ESQL/C code returned the error.
When use cursor for update, there are two limitations:
- The columns included in the ORDER BY clause must be the index.
- If there are multiple columns in the ORDER BY clause, the sequence of the columns must be same as the index key’s definition.
So the ORDER BY clause in the above ESQL/C code should be modified to:
Create an index with then same sequence with NUM, DD, EE, FIRSTNAME Order by NUM, DD, EE, FIRSTNAME
To keep same sequence with the index key’s definition.