Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

Why does my cursor FETCH loop stop after one pass?

5
2

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

asked 15 Mar '10, 14:29

Tom%20Rolseth's gravatar image

Tom Rolseth
191●8●7●8
accept rate: 0%

edited 15 Mar '13, 19:41

Mark%20Culp's gravatar image

Mark Culp
24.9k●10●141●297

What is @Entity?

(15 Mar '10, 14:56) Breck Carter

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

(15 Mar '10, 15:22) Tom Rolseth

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

(15 Mar '10, 18:54) Breck Carter
2 Answers:
active answersoldest answersnewest answerspopular answers
6

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' ;
permanent link

answered 15 Mar '10, 15:24

Bruce%20Hay's gravatar image

Bruce Hay
2.6k●1●5●10
accept rate: 48%

1

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

(15 Mar '10, 18:59) Breck Carter
Comment Text Removed

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

(16 Mar '10, 08:52) Volker Barth
Comment Text Removed
Comment Text Removed

@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?)

(16 Mar '10, 08:58) Volker Barth
2

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.

(16 Mar '10, 13:36) Bruce Hay
1

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;
permanent link

answered 15 Mar '10, 15:35

Breck%20Carter's gravatar image

Breck Carter
32.5k●541●726●1050
accept rate: 20%

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

(15 Mar '10, 16:16) Tom Rolseth
2

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.

(15 Mar '10, 18:47) Breck Carter
1

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

(15 Mar '10, 18:56) Breck Carter

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

(17 Mar '10, 15:03) Tom Rolseth
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

learn more about Markdown

Question tags:

×125
×24
×12

question asked: 15 Mar '10, 14:29

question was seen: 7,065 times

last updated: 15 Mar '13, 19:41

Related questions

Execute statement in the loop

Runtime error when combining working stored procs in a trigger

execute immediate with arbitrary result set

Cursor in nested loop gives error after migration

Execute statement in stored procedure does not work in JDBC app any more

difference in exec vs call storeproc?

Protecting intellectual property

Calling A Procedure For Every Row In A Select Statement Inside A Stored Proc

DBISQL 12.0.1.3324 does not display the single result set of a stored procedure by default

Problems with cursors