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

asked 25 Apr '14, 14:10

rsnyder's gravatar image

rsnyder
2414616
accept rate: 0%

edited 25 Apr '14, 14:31

Breck%20Carter's gravatar image

Breck Carter
26.8k420580826


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

permanent link

answered 25 Apr '14, 14:22

rsnyder's gravatar image

rsnyder
2414616
accept rate: 0%

3

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.
(25 Apr '14, 14:34) Ivan T. Bowman
Replies hidden

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

(25 Apr '14, 14:44) rsnyder
Replies hidden

> "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 :)

(25 Apr '14, 14:59) Breck Carter

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

(27 Apr '14, 08:58) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×16

question asked: 25 Apr '14, 14:10

question was seen: 553 times

last updated: 27 Apr '14, 08:58