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.
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 |
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 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.
(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
|