How is a prepared cursor supposed to work ?

I assumed that closing and reopening the cursor would refresh the result
set, i.e. close and reopen the underlying query.

First I tried the following:

1. Prepare the cursor statement.
2. Open the cursor.
3. Iterate through the cursor.
4. Update the underlying table based on the current row values.
5. Close the cursor.
6. Open the cursor.

The second time the cursor was opened, it contained only 1 row - the last
row from the original result.

Next I tried the following:

1. Prepare the cursor statement.
2. Open the cursor.
3. Iterate through the cursor.
4. Update the underlying table based on the current row values.
5. Close the cursor.
6. Prepare the cursor statement.
7. Open the cursor.

The second time the cursor was opened, it now contains exactly the same rows
as the original result. The cursor was not refreshed.

Finally I tried the following:

1. Prepare the cursor statement.
2. Open the cursor.
3. Iterate through the cursor.
4. Update the underlying table based on the current row values.
5. Close the cursor.
6. Unprepare the cursor statement.
7. Prepare the cursor statement.
8. Open the cursor.

The second time the cursor was opened, the result set was refreshed and
showed the updates.

Ole Willy Tuv

Ole,

<< How is a prepared cursor supposed to work ?

I assumed that closing and reopening the cursor would refresh the result
set, i.e. close and reopen the underlying query. >>

Yes, that is correct.

<< First I tried the following: >>

Could you send me the SQL/PSM that you're using ?  I don't want to miss
something little.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<< Could you send me the SQL/PSM that you're using ?  I don't want to miss
something little. >>

I'm attaching sp_Sudoko_EDB_debug1.zip (used for the first approach) and
sp_Sudoko_debug2.zip (used for the second approach). sp_Sudoko_debug3.zip
(used for the third approach) was posted in another reply.

Check out the SolveSingleCells procedure in each version of the Sudoko
database.

To check the effect of cursor issue:

1. Create and execute the sp_Sudoko_EDB procedure
2. Execute the sp_default_Sudoko_puzzle procedure
3. Execute the sp_solve_Sudoko_puzzle procedure

For the debug1 and debug2 versions, you need to kill the client, since the
WHILE loop never ends because of the cursor issue.

Ole Willy Tuv

Ole,

<< To check the effect of cursor issue:

1. Create and execute the sp_Sudoko_EDB procedure
2. Execute the sp_default_Sudoko_puzzle procedure
3. Execute the sp_solve_Sudoko_puzzle procedure

For the debug1 and debug2 versions, you need to kill the client, since the
WHILE loop never ends because of the cursor issue. >>

Okay, the issue has to do with refreshing of cursors using queries on views
that generate static result sets several levels deep, which is what you're
using.   For now, you'll have to use the prepare/unprepare approach to
control when the views referred to by the queries are refreshed.  I'm going
to have to develop a fine-grained refresh mechanism for queries on views
that refer to other views, etc.   Simply re-executing the views on refresh
won't cut it because it will cause performance to go in the toilet.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<< I'm going to have to develop a fine-grained refresh mechanism for queries
on views that refer to other views, etc.   Simply re-executing the views on
refresh won't cut it because it will cause performance to go in the toilet.
>>

Yes, I think it's important to address this issue. Performance isn't worth
much if the results are plain wrong.

Ole Willy Tuv

Ole,

<< Yes, I think it's important to address this issue. Performance isn't
worth much if the results are plain wrong. >>

The results aren't wrong, the views (generating static result sets) that the
queries are referencing are simply not refreshed when you open/close the
cursor, which re-executes the actual query but does not re-execute any of
the referenced views.   You've got views that are up to 3 levels deep, and
they're all static result sets.  If I simply re-execute the queries that
make up the views every time a query on them is executed, then it takes
forever to complete.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<< The results aren't wrong, the views (generating static result sets) that
the queries are referencing are simply not refreshed when you open/close the
cursor, which re-executes the actual query but does not re-execute any of
the referenced views. >>

Are you saying that referenced tables are refreshed, but referenced views
are not refreshed ?

If so, the results are definitely wrong. There's no difference between
tables and views in the table expression of a query.

<< You've got views that are up to 3 levels deep, and they're all static
result sets.  If I simply re-execute the queries that make up the views
every time a query on them is executed, then it takes forever to complete.
>>

Views need to be re-evaluated each time they're queried, of course, such
that the result reflects the current data.

You can't simply produce an incorrect result set just because producing the
correct result takes a longer time.

Have you checked what other database engines do when closing and opening a
cursor ?

Ole Willy Tuv

Ole,

<< Are you saying that referenced tables are refreshed, but referenced views
are not refreshed ? >>

Yep.

<< If so, the results are definitely wrong. There's no difference between
tables and views in the table expression of a query. >>

There is in EDB, especially if you're talking about a view that generates a
static result.

<< Views need to be re-evaluated each time they're queried, of course, such
that the result reflects the current data.

You can't simply produce an incorrect result set just because producing the
correct result takes a longer time. >>

That's not what I said.  What I said was that I changed it to refresh the
views, and it took forever (actually, it never even completed - I had to
kill it).   I didn't say that we intentionally made it this way for
performance reasons.   And as I said *in my first message*, I am going to
have to look at a way to develop a more fine-grained refresh mechanism for
views that generate static result sets.  So, please, let's not beat a dead
horse here.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<< And as I said *in my first message*, I am going to have to look at a way
to develop a more fine-grained refresh mechanism for views that generate
static result sets.  >>

That's fine.

<< So, please, let's not beat a dead horse here. >>

I would never beat a horse - dead or alive

Ole Willy Tuv

Tim,

<< What I said was that I changed it to refresh the views, and it took
forever (actually, it never even completed - I had to kill it). >>

I don't quite understand how changing it to refresh the views should take
any longer time than unprepare/prepare the cursor statement, which does the
same thing.

Did you by any chance test the change using the Sudoko SolveSingleCells
procedure ? If so, the reason that it never completed could be that the
cursor didn't return the correct result after the change, such that the
WHILE condition stayed true and the loop never ended.

You could restrict the WHILE loop to a few iterations, e.g.:

drop procedure SolveSingleCells;

create procedure SolveSingleCells(p boolean)
begin
 declare _rowID smallint;
 declare _columnID smallint;
 declare _cellValue smallint;
 declare _rowCount integer;
 declare loopCount integer;
 declare recordCount1 integer;
 declare recordCount2 integer;
 declare totalCount integer;
 declare stmt4 statement;
 declare stmt5 statement;
 declare cur1 cursor for stmt1;
 declare cur2 cursor for stmt2;
 declare cur3 cursor for stmt3;

 prepare stmt1 from
 '
   select
     SD.rowID,
     SD.columnID,
     DT.cellValue
   from
     Sudoko as SD
     inner join PossibleValuesStage2_DT2 as DT on
       SD.rowID = DT.rowID
       and SD.columnID = DT.columnID
   order by rowID, columnID
 ';

 prepare stmt2 from
 '
   select
     SD.rowID,
     SD.columnID,
     PV.cellValue
   from
     Sudoko as SD
     inner join PossibleValuesStage2 as PV on
       SD.rowID = PV.rowID
       and SD.columnID = PV.columnID
     inner join PossibleValuesByCell as PC on
       PC.rowCellID = floor((PV.rowID-1)/3)+1
       and PC.columnCellID = floor((PV.columnID-1)/3)+1
       and PC.cellValue = PV.cellValue
   where PC.occurrences = 1
   order by rowID, columnID
 ';

 prepare stmt3 from
 '
   select count(cellValue) as count_cellValue
   from Sudoko
 ';

 prepare stmt4 from
 '
   update Sudoko
   set cellValue = ?
   where
     rowID = ?
     and columnID = ?
 ';

 prepare stmt5 from
 '
   select 1
   from information.tables
   where name = ?
 ';
 execute stmt5 using 'iterations';
 if (rowsaffected(stmt5) = 1) then
   execute immediate 'drop table iterations';
 end if;
 execute immediate
 '
   create table iterations
   (
     iteration integer,
     query char(2),
     rowID smallint,
     columnID smallint,
     cellValue smallint,
     sudoko_rows integer
   )
 ';

 prepare stmt5 from 'insert into iterations values (?,?,?,?,?,?)';

 set loopCount = 0;
 set totalCount = 10;

 while (totalCount > 0) do
   set loopCount = loopCount+1;

   -- Update the Sudoko square where only a single
   -- solution exists for any one cell
   open cur1;
   set recordCount1 = rowcount(cur1);
   fetch first from cur1 (rowID,columnID,cellValue) into
    _rowID,_columnID,_cellValue;
   while not eof(cur1) do
     execute stmt4 using _cellvalue,_rowID,_columnID;
     open cur3;
     fetch first from cur3 (count_cellValue) into _rowCount;
     close cur3;
     execute stmt5 using
     loopCount,'Q1',_rowID,_columnID,_cellValue,_rowCount;
     fetch next from cur1 (rowID,columnID,cellValue) into
    _rowID,_columnID,_cellValue;
   end while;
   close cur1;

   open cur2;
   set recordCount2 = rowcount(cur2);
   fetch first from cur2 (rowID,columnID,cellValue) into
   _rowID,_columnID,_cellValue;
   while not eof(cur2) do
     execute stmt4 using _cellvalue,_rowID,_columnID;
     open cur3;
     fetch first from cur3 (count_cellValue) into _rowCount;
     close cur3;
     execute stmt5 using
     loopCount,'Q2',_rowID,_columnID,_cellValue,_rowCount;
     fetch next from cur2 (rowID,columnID,cellValue) into
     _rowID,_columnID,_cellValue;
   end while;
   close cur2;

   set totalCount = totalCount-1;
 end while;

 unprepare stmt1;
 unprepare stmt2;
 unprepare stmt3;
 unprepare stmt4;
 unprepare stmt5;
end;

Ole Willy Tuv