Tags: alli, append, build, code, create, cursor, database, db2, declare, error, following, mysql, oracle, procedure, raised, source, spb, sql, stored, varchar, writed

About "declare cursor" in SQL Stored Procedure

On Database » DB2

9,506 words with 4 Comments; publish: Sat, 01 Dec 2007 20:16:00 GMT; (25094.24, « »)

HI, all

I writed the following code. I build it in the SPB. But the error is raised.

create procedure append(in source varchar(100),

in target varchar(100),

in timevar date,

out error_code integer,

out error_label varchar(255))

language sql

begin

declare SQLCODE integer default 0;

declare stmt varchar(1024);

declare at_end int default 0;

declare col_name varchar(100);

declare all_col varchar(1024);

declare EXIT HANDLER FOR SQLEXCEPTION

set error_code = SQLCODE;

declare CONTINUE HANDLER FOR NOT FOUND

set at_end = 1;

set error_code = 0;

set stmt = 'delete from '||

target||

' where timevar="'||

char(last_day(current date), iso)||

'"';

set error_label = 'The position of the error raised is 0001.';

prepare ps from stmt;

set error_label = 'The position of the error raised is 0002.';

execute ps;

declare c1 cursor for

select name

from sysibm.syscolumns

where tbname = ucase(source)

order by colno;

open c1;

fetch c1 into col_name;

while at_end = 0 do

set all_col = all_col||col_name;

fetch c1 into col_name;

end while;

close c1;

set stmt = 'insert into '||

target||

' values '||

'select '||

all_col||

', current date from '||

source;

set error_label = 'The position of the error raised is 0005.';

prepare ps from stmt;

set error_label = 'The position of the error raised is 0006.';

execute ps;

set error_label = '';

end

The error message is:

DB2ADMIN.APPEND - Create stored procedure returns -104.

DB2ADMIN.APPEND: 34: [IBM][CLI Driver][DB2/NT] SQL0104N An unexpected token "<cursor declaration>" was found following "". Expected tokens may include: "<SQL statement>". LINE NUMBER=34. SQLSTATE=42601

DB2ADMIN.APPEND - Build failed.

DB2ADMIN.APPEND - Changes rolled back.

Please tell me how to resolve this problem.

All Comments

Leave a comment...

  • 4 Comments
    • SQL stored procedures have an exacting syntax. This means that certain things must be done in the proper order. Your declare cursor statement is in the wrong place. Please read the SQL Procedures chapter in the SQL Reference manual to get the correct syntax of compound statements.

      Try this:

      create procedure append(in source varchar(100),

      in target varchar(100),

      in timevar date,

      out error_code integer,

      out error_label varchar(255))

      language sql

      begin

      declare SQLCODE integer default 0;

      declare stmt varchar(1024);

      declare at_end int default 0;

      declare col_name varchar(100);

      declare all_col varchar(1024);

      -- DECLARE CURSOR STATEMENTS BELONG HERE

      declare c1 cursor for

      select name

      from sysibm.syscolumns

      where tbname = ucase(source)

      order by colno;

      declare EXIT HANDLER FOR SQLEXCEPTION

      set error_code = SQLCODE;

      declare CONTINUE HANDLER FOR NOT FOUND

      set at_end = 1;

      set error_code = 0;

      set stmt = 'delete from '||

      target||

      ' where timevar="'||

      char(last_day(current date), iso)||

      '"';

      set error_label = 'The position of the error raised is 0001.';

      prepare ps from stmt;

      set error_label = 'The position of the error raised is 0002.';

      execute ps;

      -- NOT DOWN HERE

      open c1;

      fetch c1 into col_name;

      while at_end = 0 do

      set all_col = all_col||col_name;

      fetch c1 into col_name;

      end while;

      close c1;

      set stmt = 'insert into '||

      target||

      ' values '||

      'select '||

      all_col||

      ', current date from '||

      source;

      set error_label = 'The position of the error raised is 0005.';

      prepare ps from stmt;

      set error_label = 'The position of the error raised is 0006.';

      execute ps;

      set error_label = '';

      end

      HTH

      Andy

      === Original Words ===

      zhouhaiming

      HI, all

      I writed the following code. I build it in the SPB. But the error is raised.

      create procedure append(in source varchar(100),

      in target varchar(100),

      in timevar date,

      out error_code integer,

      out error_label varchar(255))

      language sql

      begin

      declare SQLCODE integer default 0;

      declare stmt varchar(1024);

      declare at_end int default 0;

      declare col_name varchar(100);

      declare all_col varchar(1024);

      declare EXIT HANDLER FOR SQLEXCEPTION

      set error_code = SQLCODE;

      declare CONTINUE HANDLER FOR NOT FOUND

      set at_end = 1;

      set error_code = 0;

      set stmt = 'delete from '||

      target||

      ' where timevar="'||

      char(last_day(current date), iso)||

      '"';

      set error_label = 'The position of the error raised is 0001.';

      prepare ps from stmt;

      set error_label = 'The position of the error raised is 0002.';

      execute ps;

      declare c1 cursor for

      select name

      from sysibm.syscolumns

      where tbname = ucase(source)

      order by colno;

      open c1;

      fetch c1 into col_name;

      while at_end = 0 do

      set all_col = all_col||col_name;

      fetch c1 into col_name;

      end while;

      close c1;

      set stmt = 'insert into '||

      target||

      ' values '||

      'select '||

      all_col||

      ', current date from '||

      source;

      set error_label = 'The position of the error raised is 0005.';

      prepare ps from stmt;

      set error_label = 'The position of the error raised is 0006.';

      execute ps;

      set error_label = '';

      end

      The error message is:

      DB2ADMIN.APPEND - Create stored procedure returns -104.

      DB2ADMIN.APPEND: 34: [IBM][CLI Driver][DB2/NT] SQL0104N An unexpected token "<cursor declaration>" was found following "". Expected tokens may include: "<SQL statement>". LINE NUMBER=34. SQLSTATE=42601

      DB2ADMIN.APPEND - Build failed.

      DB2ADMIN.APPEND - Changes rolled back.

      Please tell me how to resolve this problem.

      #1; Tue, 11 Dec 2007 17:43:00 GMT
    • Thank you very much!
      #2; Tue, 11 Dec 2007 17:44:00 GMT
    • I have a similar problem.

      I am writing a stored proc that utilizes a global temporary table and a cursor to selct from that table.

      When writing the proc, the only way it will compile is if I place the DECLAR CURSOR statement BEFORE I declare the temporary table. As you know, that will not work and you get an UNDEFINED OBJECT error.

      How do you possibly get around this? Why can't DB2 just let you do a basic select from a table withiout having to go through the hassle of using cursors?!?

      #3; Tue, 11 Dec 2007 17:45:00 GMT
    • First, I will answer you last question. The reason you need to use the cursor is to tell DB2 what you want to do with the result set. There are way too many possibilities that can be done with result sets. The only way DB2 know you want to have the SP return a result set is through the cursor construct (note the WITH RETURN TO CALLER/CLIENT) clause. The way you get around you problem is to study the grammar of a Compound Statement. The declare statements nned to go first. So how do you declare something if it depends on something else that is declared later? Simple, you use another Compound statement. For example:

      BEGIN

      DECLARE Temporary table

      ...

      BEGIN

      DECLARE CURSOR on Temporary table

      ...

      END;

      END

      HTH

      Andy

      === Original Words ===

      ansonee

      I have a similar problem.

      I am writing a stored proc that utilizes a global temporary table and a cursor to selct from that table.

      When writing the proc, the only way it will compile is if I place the DECLAR CURSOR statement BEFORE I declare the temporary table. As you know, that will not work and you get an UNDEFINED OBJECT error.

      How do you possibly get around this? Why can't DB2 just let you do a basic select from a table withiout having to go through the hassle of using cursors?!?

      #4; Tue, 11 Dec 2007 17:46:00 GMT