Cursor not found - table maintenance tripping me up

[#1]

I'm attempting to do some datamart loading in a proc using a cursor. I'm tripped up on my table creates, truncates, and drops. They seem to be causing a 'cursor not found' error.
I read through my table of tables and process:

1. create proxy table to other db
2. clear datamart table
3. load datamart table from proxy
4. drop proxy

The natural question might be why am I just not using proxy tables in my datamart. But for performance issues, we prefer to load from the remote db in nightly processing. Is there a way to define my cursor to allow this?

begin
  declare @sql varchar(500);
  /*create temporary proxies to reggie database to load data*/
    declare SourceTab scroll cursor for select tabledatabase, tabletable, tableproxyprefix, tableremoteserver, tableremoteowner, tablelocalowner
     from dmartstu.sourcetable where tabledatabase=@dbselect
        order by tabledatabase, tableorder asc;
    /****************/
    /*temp variables*/
    /****************/
    declare tdatabase char(50);
    declare ttable char(100);
    declare tproxprefix char(50);
    declare tremoteserver char(50);
    declare tremoteowner char(50);
    declare tlocalowner char(50);
    declare @remotetablename varchar(100);
    declare @sourcetablename varchar(100);
    declare @receivetablename varchar(100);

    open SourceTab;

    TabLoop: loop
      fetch next SourceTab into tdatabase, ttable, tproxprefix, tremoteserver, tremoteowner, tlocalowner;
      if sqlstate <> 0 then
        message 'sqlstate:  ' || sqlstate to client;
        leave TabLoop
      end if;
      set @remotetablename=trim(tremoteserver)||';;'||trim(tremoteowner)||';'||ttable;      /*remote server table name - ex: RO_Reggie;;dba;classes*/
      set @sourcetablename=trim(tlocalowner)||'.'||trim(tproxprefix)||'_'||trim(ttable);    /*proxy table name - ex: dmartstu.ProxReg_classes*/
      set @receivetablename=trim(tlocalowner)||'.'||trim(tdatabase)||'_'||trim(ttable);     /*actual (receiving) table name ex: dmartstu.registrar_classes*/
      set @sql='drop table if exists '||@sourcetablename||';';
      execute immediate @sql;
      set @sql='create existing table '||@sourcetablename||' AT '''||@remotetablename||''' ;';
      execute immediate @sql;
      set @sql='truncate table '||@receivetablename||';';
      execute immediate @sql;
      set @sql='insert into '||@receivetablename||' select * from '||@sourcetablename||';';
      execute immediate @sql; 
      set @sql='drop table '||@sourcetablename||';';
      execute immediate @sql;

     message 'loop end or back' to client

    end loop TabLoop;

    close SourceTab;
end

[former_SQLA_member1694892]

I did some more searching in this forum (yay!) and found that an Open with Hold will fix my problems.

Thanks!

Becky Snyder Bradley University

[Former Member]

Ah, you beat me to it by answering your own question so quickly. I'll add a couple of other points for completeness in case others are interested.

1. Some of the operations (e.g., drop table) have an automatic commit. These cause cursors on the connection to be closed unless they are opened WITH HOLD as you noted.
2. The FOR statement uses WITH HOLD implicitly, so if you are used to using FOR you might expect the cursor to stay open.
3. Normally when connection A has a cursor open on a table T, it has a schema lock on it and other connections block when trying to alter or drop T. However, if A itself does the alter or drop, then all cursors that A has open that reference table T are closed automatically (even if they are opened WITH HOLD). This can be a hidden gotcha for cursors being closed behind the scenes. This gotcha also shows up with materialized views (referencing cursors closed when manually refreshed) and regular views (cursors referencing dependent views are closed when a base underlying view is invalidated.

[former_SQLA_member1694892]

Thank-you for the additional information. Very good info! Becky

[Breck_Carter]

> "if A itself does the alter or drop, then all cursors that A has open that reference table T are closed automatically (even if they are opened WITH HOLD)"

It is hard to imagine that is anything other than a logic error (not in SQL Anywhere, but in the application); i.e., a connection that alters or drops object X while holding open a cursor that references X.

Silently closing the cursor seems like modern-day forgiveness for a crime that deserves punishment; e.g., an exception 🙂

[VolkerBarth]

Becky, feel free to accept your own answer, as it apparently has solved your problem:)