Why does my cursor FETCH loop stop after one pass?

[Former Member]

I am using ASA 11.0.1 build 2044.

I have a procudure that dynamically builds a command string to 1) delete rows from a table and 2) run load table to re-populate it with new data. I am using EXECUTE IMMEDIATE to run the delete and load table commands. It works just fine for the first loop (there should be 6 fetches in total) but it seems like after the the 2nd call of Execute immediate, the whole thing just dies. I can't get any error messasges or other data that would give me a clue as to what is going on. When I comment out the 2 calls to execute immediate, the procedure works fine and goes through all six loops. I can write the syntax of the delete and load table statements to the log and they look fine. Below is my procedure code. Thanks, Tom

BEGIN

declare cmdLoadZZ long varchar;
declare WriteLine long varchar;
declare Delimiter char(20);
declare cmdDeleteZZ long varchar;
declare TableName char(100);
declare DataFile long varchar;
declare DemoYN char(1);
declare Entity char(10);
declare zzTableYN char(1);
declare LogFile long varchar;
declare @loopcounter integer;

DECLARE cEntity cursor FOR 
SELECT sy_table_name, sy_file, sy_demo_yn, use_zz_yn
  FROM sy_data_upload 
 WHERE fd_entity = @Entity;

SET @loopcounter = 0;
SET LogFile = '';
SET Delimiter = ' DELIMITED BY ' + Char(39) + '|' + Char(39);

OPEN cEntity;

FETCH cEntity INTO TableName, DataFile, DemoYN, zzTableYN;

WHILE SQLCode = 0 LOOP

SET @loopcounter = @loopcounter + 1;

SET cmdDeleteZZ = 'DELETE FROM zz_' + TableName + ' WHERE fd_entity = ' + Char(39) + @Entity + Char(39);
        SET cmdLoadZZ = 'LOAD TABLE ' || 'zz_' || TableName || ' FROM ' || '''c:\\\\donorcentral\\\\advisor\\\\' || @Entity || '\\\\history\\\\' || DataFile || '''' || Delimiter;

SET LogFile = LogFile + str(@loopcounter) + ' ' + cmdDeleteZZ + '  ' + cmdLoadZZ;

EXECUTE IMMEDIATE cmdDeleteZZ; 
        MESSAGE STRING(str(@loopcounter) + ' cmdDeleteZZ: ', cmdDeleteZZ, str(SQLSTATE)) TO LOG;

EXECUTE IMMEDIATE cmdLoadZZ;
        MESSAGE STRING(str(@loopcounter) + ' cmdLoadZZ: ', cmdLoadZZ, str(SQLSTATE)) TO LOG;

FETCH cEntity INTO TableName, DataFile, DemoYN, zzTableYN;

END LOOP ;

Close cEntity;

UNLOAD SELECT LogFile To 'c:\\loaddata_log.txt' ;

END

[Breck_Carter]

What is @Entity?

[Former Member]

It is a parameter I am passing to the procedure. Different entities (our clients) could have different tables that need to be processed. I should point out that the first time through the loop, it works fine. The DELETE and LOAD TABLE commands are successful. The problem is that the loop just ends right then and there and no other fetches occur. Tom

[Breck_Carter]

@Tom: I hope you don't mind the rewrite of your title to match the question you're asking... you can change it back if you want, but EXECUTE IMMEDIATE was not directly implicated. ( Yes, it creeps me out editing other people's stuff 🙂

[Former Member]

The LOAD TABLE statement causes an automatic commit, closing your cursor. You could open the cursor WITH HOLD, or use the FOR statement as shown here.

declare @cmd long varchar;
declare @filename long varchar;
declare @LogFile long varchar;
declare @loopcounter integer;

SET @loopcounter = 0;
SET @LogFile = '';
for l1 as cEntity cursor for 
    SELECT 'zz_' || sy_table_name as TableName, 
        sy_file as DataFile, 
        sy_demo_yn as DemoYN, 
        use_zz_yn as zzTableYN
    FROM sy_data_upload  
    WHERE fd_entity = @Entity
do
    SET @loopcounter = @loopcounter + 1;
    SET @cmd = string('DELETE FROM ', TableName, 
        ' WHERE fd_entity = ''', @Entity, '''');
    SET @LogFile = @LogFile || @loopcounter || ' ' || @cmd;
    EXECUTE IMMEDIATE @cmd;
    MESSAGE STRING(@loopcounter, ' cmd: ', @cmd, SQLSTATE) TO LOG;
    set @filename = string('''c:\\\\donorcentral\\\\advisor\\\\', @Entity, 
        '\\\\history\\\\', DataFile, '''' );
    SET @cmd = string('LOAD TABLE ', TableName, ' FROM @filename',
        ' DELIMITED BY ''|''' );
    set @LogFile = @LogFile || '  ' || @cmd;
    EXECUTE IMMEDIATE @cmd;
    MESSAGE STRING(@loopcounter, ' cmd: ', @cmd, SQLSTATE) TO LOG;
end for;
UNLOAD SELECT @LogFile To 'c:\\loaddata_log.txt' ;

[Breck_Carter]

@Bruce: Did you create the original code to support FOR? Whoever did, should have an annual parade in their honor 🙂

[VolkerBarth]

I second that proposal (though it would be quite a distance for me to participate). @Breck: Plans for a "SQLA Hall of Fame"?

[VolkerBarth]

@Bruce: Is the FOR statement generally opening a cursor WITH HOLD, as your answer suggests? (If so, is that behaviour documented in the SA 11.0.1 docs?)

[Former Member]

The FOR statement was implemented based on a preliminary draft of the ANSI standard for persistent stored modules, sometime back in the early 90's. The parade would have to honor those on the committee; I was not a member.

I have asked the doc team (via DCX) to add a note about the implicit WITH HOLD behaviour.

[Breck_Carter]

In addition to what Bruce says, the DELIMITED BY caused a problem in the first slightly-modified version of your code:

EXCEPTION in BEGIN block at 2010-03-15 11:13:27.284: 
SQLCODE = -638, SQLSTATE = 22001, 
ERRORMSG() = Right truncation of string data

Here's the first modified version; note the EXCEPTION handler and the @here variable for pinpointing problems...

-- version 1

BEGIN
   DROP TABLE t1;
   EXCEPTION WHEN OTHERS THEN
END;

CREATE TABLE t1 ( 
   fd_entity  VARCHAR ( 1 ),
   data1 integer,
   data2 integer );

INSERT t1 VALUES ( '1', 1, 1 );
INSERT t1 VALUES ( '1', 2, 2 );
COMMIT;

UNLOAD TABLE t1 TO 'c:/temp/1/history/t1.txt' DELIMITED BY '''|''';

BEGIN
   DROP TABLE zz_t1;
   EXCEPTION WHEN OTHERS THEN
END;

CREATE TABLE zz_t1 ( 
   fd_entity  VARCHAR ( 1 ),
   data1 integer,
   data2 integer );

BEGIN
   DROP TABLE sy_data_upload;
   EXCEPTION WHEN OTHERS THEN
END;

CREATE TABLE sy_data_upload ( 
   fd_entity  VARCHAR ( 1 ),
   sy_table_name  char(100),
   sy_file  long varchar,
   sy_demo_yn  char(1),
   use_zz_yn char(1) );

INSERT sy_data_upload VALUES ( '1', 't1', 't1.txt', 'Y', 'Y' );
COMMIT;

BEGIN

declare cmdLoadZZ long varchar;
declare WriteLine long varchar;
declare Delimiter char(20);
declare cmdDeleteZZ long varchar;
declare TableName char(100);
declare DataFile long varchar;
declare DemoYN char(1);
declare Entity char(10);
declare zzTableYN char(1);
declare LogFile long varchar;
declare @loopcounter integer;

DECLARE cEntity cursor FOR 
SELECT sy_table_name, sy_file, sy_demo_yn, use_zz_yn
  FROM sy_data_upload 
 WHERE fd_entity = @Entity;

DECLARE @Entity VARCHAR ( 1 );

DECLARE @sqlcode   INTEGER;
DECLARE @sqlstate  VARCHAR ( 5 );
DECLARE @errormsg  VARCHAR ( 32767 );

DECLARE @here VARCHAR ( 1 );

SET @here = '1';
SET @Entity = '1';

SET @loopcounter = 0;
SET LogFile = '';
SET Delimiter = ' DELIMITED BY ' + Char(39) + '|' + Char(39);

OPEN cEntity;

SET @here = '2';
FETCH cEntity INTO TableName, DataFile, DemoYN, zzTableYN;

WHILE SQLCode = 0 LOOP

SET @loopcounter = @loopcounter + 1;

SET cmdDeleteZZ = 'DELETE FROM zz_' + TableName + ' WHERE fd_entity = ' + Char(39) + @Entity + Char(39);
        SET cmdLoadZZ = 'LOAD TABLE ' || 'zz_' || TableName || ' FROM ' || '''c:\\\\temp\\\\' || @Entity || '\\\\history\\\\' || DataFile || '''' || Delimiter;

SET LogFile = LogFile + str(@loopcounter) + ' ' + cmdDeleteZZ + '  ' + cmdLoadZZ;

SET @here = '3';
        EXECUTE IMMEDIATE cmdDeleteZZ; 
        MESSAGE STRING(str(@loopcounter) + ' cmdDeleteZZ: ', cmdDeleteZZ, str(SQLSTATE)) TO CONSOLE;

SET @here = '4';
        EXECUTE IMMEDIATE cmdLoadZZ;
        MESSAGE STRING(str(@loopcounter) + ' cmdLoadZZ: ', cmdLoadZZ, str(SQLSTATE)) TO CONSOLE;

FETCH cEntity INTO TableName, DataFile, DemoYN, zzTableYN;

END LOOP ;

SET @here = '5';
Close cEntity;

SET @here = '6';
UNLOAD SELECT LogFile To 'c:/temp/loaddata_log.txt' ;

EXCEPTION
   WHEN OTHERS THEN
      SELECT SQLCODE, SQLSTATE, ERRORMSG() 
        INTO @sqlcode, @sqlstate, @errormsg;
      MESSAGE STRING ( 
         'EXCEPTION in BEGIN block at ', 
         CURRENT TIMESTAMP, 
         ': @here = ', @here,  
         ', SQLCODE = ', @sqlcode,  
         ', SQLSTATE = ', @sqlstate,  
         ', ERRORMSG() = ', @errormsg ) 
         TO CONSOLE;

END;

SELECT * FROM zz_t1;

The second modified version shows the problem Bruce mentions:

Starting checkpoint of "ddd11" (ddd11.db) at Mon Mar 15 2010 11:29
Finished checkpoint of "ddd11" (ddd11.db) at Mon Mar 15 2010 11:29
         1 cmdDeleteZZ: DELETE FROM zz_t1 WHERE fd_entity = '1'      2000
Starting checkpoint of "ddd11" (ddd11.db) at Mon Mar 15 2010 11:29
Finished checkpoint of "ddd11" (ddd11.db) at Mon Mar 15 2010 11:29
         1 cmdLoadZZ: LOAD TABLE zz_t1 FROM 'c:\\temp\\1\\history\\t1.txt'         0
EXCEPTION in BEGIN block at 2010-03-15 11:29:51.165: 
@here = 4, SQLCODE = -180, SQLSTATE = 24501, 
ERRORMSG() = Cursor not open

Here's the second modified version:

-- version 2

BEGIN
   DROP TABLE t1;
   EXCEPTION WHEN OTHERS THEN
END;

CREATE TABLE t1 ( 
   fd_entity  VARCHAR ( 1 ),
   data1 integer,
   data2 integer );

INSERT t1 VALUES ( '1', 1, 1 );
INSERT t1 VALUES ( '1', 2, 2 );
COMMIT;

UNLOAD TABLE t1 TO 'c:/temp/1/history/t1.txt'; -- DELIMITED BY '''|''';

BEGIN
   DROP TABLE zz_t1;
   EXCEPTION WHEN OTHERS THEN
END;

CREATE TABLE zz_t1 ( 
   fd_entity  VARCHAR ( 1 ),
   data1 integer,
   data2 integer );

BEGIN
   DROP TABLE sy_data_upload;
   EXCEPTION WHEN OTHERS THEN
END;

CREATE TABLE sy_data_upload ( 
   fd_entity  VARCHAR ( 1 ),
   sy_table_name  char(100),
   sy_file  long varchar,
   sy_demo_yn  char(1),
   use_zz_yn char(1) );

INSERT sy_data_upload VALUES ( '1', 't1', 't1.txt', 'Y', 'Y' );
COMMIT;

BEGIN

declare cmdLoadZZ long varchar;
declare WriteLine long varchar;
declare Delimiter char(20);
declare cmdDeleteZZ long varchar;
declare TableName char(100);
declare DataFile long varchar;
declare DemoYN char(1);
declare Entity char(10);
declare zzTableYN char(1);
declare LogFile long varchar;
declare @loopcounter integer;

DECLARE cEntity cursor FOR 
SELECT sy_table_name, sy_file, sy_demo_yn, use_zz_yn
  FROM sy_data_upload 
 WHERE fd_entity = @Entity;

DECLARE @Entity VARCHAR ( 1 );

DECLARE @sqlcode   INTEGER;
DECLARE @sqlstate  VARCHAR ( 5 );
DECLARE @errormsg  VARCHAR ( 32767 );

DECLARE @here VARCHAR ( 1 );

SET @here = '1';
SET @Entity = '1';

SET @loopcounter = 0;
SET LogFile = '';
SET Delimiter = ''; -- ' DELIMITED BY ' + Char(39) + '|' + Char(39);

OPEN cEntity;

SET @here = '2';
FETCH cEntity INTO TableName, DataFile, DemoYN, zzTableYN;

WHILE SQLCode = 0 LOOP

SET @loopcounter = @loopcounter + 1;

SET cmdDeleteZZ = 'DELETE FROM zz_' + TableName + ' WHERE fd_entity = ' + Char(39) + @Entity + Char(39);
        SET cmdLoadZZ = 'LOAD TABLE ' || 'zz_' || TableName || ' FROM ' || '''c:\\\\temp\\\\' || @Entity || '\\\\history\\\\' || DataFile || '''' || Delimiter;

SET LogFile = LogFile + str(@loopcounter) + ' ' + cmdDeleteZZ + '  ' + cmdLoadZZ;

SET @here = '3';
        EXECUTE IMMEDIATE cmdDeleteZZ; 
        MESSAGE STRING(str(@loopcounter) + ' cmdDeleteZZ: ', cmdDeleteZZ, str(SQLSTATE)) TO CONSOLE;

SET @here = '4';
        EXECUTE IMMEDIATE cmdLoadZZ;
        MESSAGE STRING(str(@loopcounter) + ' cmdLoadZZ: ', cmdLoadZZ, str(SQLSTATE)) TO CONSOLE;

FETCH cEntity INTO TableName, DataFile, DemoYN, zzTableYN;

END LOOP ;

SET @here = '5';
Close cEntity;

SET @here = '6';
UNLOAD SELECT LogFile To 'c:/temp/loaddata_log.txt' ;

EXCEPTION
   WHEN OTHERS THEN
      SELECT SQLCODE, SQLSTATE, ERRORMSG() 
        INTO @sqlcode, @sqlstate, @errormsg;
      MESSAGE STRING ( 
         'EXCEPTION in BEGIN block at ', 
         CURRENT TIMESTAMP, 
         ': @here = ', @here,  
         ', SQLCODE = ', @sqlcode,  
         ', SQLSTATE = ', @sqlstate,  
         ', ERRORMSG() = ', @errormsg ) 
         TO CONSOLE;

END;

SELECT * FROM zz_t1;

As Bruce says, adding WITH HOLD to the OPEN makes gets rid of the second exception. Here is version 3...

-- version 3

BEGIN
   DROP TABLE t1;
   EXCEPTION WHEN OTHERS THEN
END;

CREATE TABLE t1 ( 
   fd_entity  VARCHAR ( 1 ),
   data1 integer,
   data2 integer );

INSERT t1 VALUES ( '1', 1, 1 );
INSERT t1 VALUES ( '1', 2, 2 );
COMMIT;

UNLOAD TABLE t1 TO 'c:/temp/1/history/t1.txt'; -- DELIMITED BY '''|''';

BEGIN
   DROP TABLE zz_t1;
   EXCEPTION WHEN OTHERS THEN
END;

CREATE TABLE zz_t1 ( 
   fd_entity  VARCHAR ( 1 ),
   data1 integer,
   data2 integer );

BEGIN
   DROP TABLE sy_data_upload;
   EXCEPTION WHEN OTHERS THEN
END;

CREATE TABLE sy_data_upload ( 
   fd_entity  VARCHAR ( 1 ),
   sy_table_name  char(100),
   sy_file  long varchar,
   sy_demo_yn  char(1),
   use_zz_yn char(1) );

INSERT sy_data_upload VALUES ( '1', 't1', 't1.txt', 'Y', 'Y' );
COMMIT;

BEGIN

declare cmdLoadZZ long varchar;
declare WriteLine long varchar;
declare Delimiter char(20);
declare cmdDeleteZZ long varchar;
declare TableName char(100);
declare DataFile long varchar;
declare DemoYN char(1);
declare Entity char(10);
declare zzTableYN char(1);
declare LogFile long varchar;
declare @loopcounter integer;

DECLARE cEntity cursor FOR 
SELECT sy_table_name, sy_file, sy_demo_yn, use_zz_yn
  FROM sy_data_upload 
 WHERE fd_entity = @Entity;

DECLARE @Entity VARCHAR ( 1 );

DECLARE @sqlcode   INTEGER;
DECLARE @sqlstate  VARCHAR ( 5 );
DECLARE @errormsg  VARCHAR ( 32767 );

DECLARE @here VARCHAR ( 1 );

SET @here = '1';
SET @Entity = '1';

SET @loopcounter = 0;
SET LogFile = '';
SET Delimiter = ''; -- ' DELIMITED BY ' + Char(39) + '|' + Char(39);

OPEN cEntity WITH HOLD;

SET @here = '2';
FETCH cEntity INTO TableName, DataFile, DemoYN, zzTableYN;

WHILE SQLCode = 0 LOOP

SET @loopcounter = @loopcounter + 1;

SET cmdDeleteZZ = 'DELETE FROM zz_' + TableName + ' WHERE fd_entity = ' + Char(39) + @Entity + Char(39);
        SET cmdLoadZZ = 'LOAD TABLE ' || 'zz_' || TableName || ' FROM ' || '''c:\\\\temp\\\\' || @Entity || '\\\\history\\\\' || DataFile || '''' || Delimiter;

SET LogFile = LogFile + str(@loopcounter) + ' ' + cmdDeleteZZ + '  ' + cmdLoadZZ;

SET @here = '3';
        EXECUTE IMMEDIATE cmdDeleteZZ; 
        MESSAGE STRING(str(@loopcounter) + ' cmdDeleteZZ: ', cmdDeleteZZ, str(SQLSTATE)) TO CONSOLE;

SET @here = '4';
        EXECUTE IMMEDIATE cmdLoadZZ;
        MESSAGE STRING(str(@loopcounter) + ' cmdLoadZZ: ', cmdLoadZZ, str(SQLSTATE)) TO CONSOLE;

FETCH cEntity INTO TableName, DataFile, DemoYN, zzTableYN;

END LOOP ;

SET @here = '5';
Close cEntity;

SET @here = '6';
UNLOAD SELECT LogFile To 'c:/temp/loaddata_log.txt' ;

EXCEPTION
   WHEN OTHERS THEN
      SELECT SQLCODE, SQLSTATE, ERRORMSG() 
        INTO @sqlcode, @sqlstate, @errormsg;
      MESSAGE STRING ( 
         'EXCEPTION in BEGIN block at ', 
         CURRENT TIMESTAMP, 
         ': @here = ', @here,  
         ', SQLCODE = ', @sqlcode,  
         ', SQLSTATE = ', @sqlstate,  
         ', ERRORMSG() = ', @errormsg ) 
         TO CONSOLE;

END;

SELECT * FROM zz_t1;

[Former Member]

Thank you both -- this is working now. Question: what is the preferred way of doing things here -- using WITH HOLD or FOR?

[Breck_Carter]

The FOR loop is a lot easier to code... in particular you don't need to DECLARE any of the variables to use in the FETCH, they are implicitly created by the SELECT (using the "AS name" is recommended). You also don't need to OPEN or DECLARE the cursor, or FETCH, or CLOSE, or check SQLCODE... there's a lot to recommend it, besides the implicit WITH HOLD.

[Breck_Carter]

Personally, I like using @ to separate the FOR loop variables from actual column names, since there can be scope issues inside the loop. For example, FOR ... SELECT column_name AS @column_name makes the code clear... as long as @ isn't used inside actual tables 🙂

[Former Member]

great -- thanks makes sense. Again, many thanks. Love this new forum