create procedure sp_ATable
(
)
begin
 declare ts timestamp;
 declare i integer;
 declare stmt statement;

 set ts = current_timestamp;

 prepare stmt from
 '
   select 1
   from information.tables
   where upper(name) = ''ATABLE''
 ';
 execute stmt;
 if (rowsaffected(stmt) = 1) then
   execute immediate 'drop table ATable';
 end if;
 execute immediate
 '
   create table ATable
   (
     ID integer generated by default as identity not null,
     SField1 varchar(20) collate enu default ''SField1 row ''||cast(ID as 
varchar(7)),
     SField2 varchar(20) collate enu default ''SField2 row ''||cast(ID as 
varchar(7)),
     SField3 varchar(20) collate enu default ''SField3 row ''||cast(ID as 
varchar(7)),
     SField4 varchar(20) collate enu default ''SField4 row ''||cast(ID as 
varchar(7)),
     SField5 varchar(20) collate enu default ''SField5 row ''||cast(ID as 
varchar(7)),
     SField6 varchar(20) collate enu default ''SField6 row ''||cast(ID as 
varchar(7)),
     SField7 varchar(20) collate enu default ''SField7 row ''||cast(ID as 
varchar(7)),
     SField8 varchar(20) collate enu default ''SField8 row ''||cast(ID as 
varchar(7)),
     Stamp timestamp,
     constraint pk_ATable primary key (ID)
   )
 ';
 execute immediate 'create index ix_ATable_SField1 on ATable (SField1)';

 prepare stmt from 'insert into ATable (Stamp) values(?)';
 set i = 0;
 repeat
   execute stmt using ts;
   set i = i+1;
 until i = 10
 end repeat;

 prepare stmt from
 '
   insert into ATable (Stamp)
   select Stamp
   from ATable
   where ID <= 10
 ';
 set i = 0;
 repeat
   execute stmt;
   set i = i+1;
 until i = 9
 end repeat;

 prepare stmt from
 '
   insert into ATable (Stamp)
   select Stamp
   from ATable
   where ID <= 100
 ';
 set i = 0;
 repeat
   execute stmt;
   set i = i+1;
 until i = 9
 end repeat;

 prepare stmt from
 '
   insert into ATable (Stamp)
   select Stamp
   from ATable
   where ID <= 1000
 ';
 set i = 0;
 repeat
   execute stmt;
   set i = i+1;
 until i = 9
 end repeat;

 prepare stmt from
 '
   insert into ATable (Stamp)
   select Stamp
   from ATable
   where ID <= 10000
 ';
 set i = 0;
 repeat
   execute stmt;
   set i = i+1;
 until i = 9
 end repeat;

 unprepare stmt;
end

---

 Comments

---

The issue was that the sub-query used for the INSERT was generating an insensitive (static) result set to a temporary table when it should have been just using a sensitive result set.

---

 Resolution

---

Fixed Problem on 5/28/2007 in version 1.04 build 1

---

 Products Affected

---

ElevateDB Additional Software and Utilities
ElevateDB VCL Client-Server
ElevateDB VCL Client-Server with Source
ElevateDB VCL Standard
ElevateDB VCL Standard with Source
ElevateDB VCL Trial