I just start to use Db2 Cursor, I was trying to do a simple exemple,


DECLARE C1 Cursor for
select CHANGE_ID from CHANGES
ORDER BY START_DATE;

OPEN C1;

FETCH C1 INTO :ch_id;

But it give me this error, what's wrong??

FETCH C1 INTO :ch_id
SQL0104N An unexpected token "INTO" was found following "<identifier>".
Expected tokens may include: "END-OF-STATEMENT". SQLSTATE=42601

SQL0104N An unexpected token "INTO" was found following "<identifier>". Expected tokens may include: "END-OF-STATEMENT".

What DB2 version and OS are you using?

Andy

Db2 9
Windows XP

Syntactically it looks OK. Can you post the entire code? There maybe a problem elsewhere that is causing this.

Andy

Is this code in a stored procedure?

Andy

Take a look on your code:Where you use START_DATE on SELECT ?

Also, if you are coding for Mainfraime you have to use:

Lenny

That's the real question here. If it is in external code (e.g. C/C++), things are fine and should work. Otherwise (in a stored procedure or on the DB2 console), you must not have a ':'. The colon is just an indicator in embedded SQL to tell the DB2 precompiler that a host variable follows.

don't need it. vsn7 or 8 and above on mainframe.

It's in a stored procedure. What should I change??

Like this:

Andy

Removed because of my misunderstandings.

I think it's some problem in my version. I try to execute the procedure I got on the IBM site , DB2 Universal Database, on the command editor. I create the staff table, and I haven't change anything on the code.

But still gives me errors like that:
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "END-OF-STATEMENT" was found following "ecords
INT DEFAULT 1". Expected tokens may include: "<psm_semicolon>". LINE
NUMBER=5. SQLSTATE=42601

DECLARE c2 CURSOR WITH RETURN FOR SELECT name, job, CAST(salary AS INTEGER) FROM staff WHERE salary > medianSalary ORDER BY salary
SQL0104N An unexpected token "RETURN" was found following "WITH". Expected
tokens may include: "HOLD". SQLSTATE=42601


Here is the code

CREATE PROCEDURE MEDIAN_RESULT_SET (OUT medianSalary DOUBLE)
RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE v_numRecords INT DEFAULT 1;
DECLARE v_counter INT DEFAULT 0;

DECLARE c1 CURSOR FOR
SELECT CAST(salary AS DOUBLE)
FROM staff
ORDER BY salary;
DECLARE c2 CURSOR WITH RETURN FOR
SELECT name, job, CAST(salary AS INTEGER)
FROM staff
WHERE salary > medianSalary
ORDER BY salary;

DECLARE EXIT HANDLER FOR NOT FOUND
SET medianSalary = 6666;

SET medianSalary = 0;
SELECT COUNT(*) INTO v_numRecords
FROM STAFF;
OPEN c1;
WHILE v_counter < (v_numRecords / 2 + 1)
DO
FETCH c1 INTO medianSalary;
SET v_counter = v_counter + 1;
END WHILE;
CLOSE c1;
OPEN c2;
END e code:

What command are you issuing to create the stored procedure?

Andy

I am trying to run that on the command Editor

The command editor has a default terminator of ";", but within a SP there are many semi-colons that do not signify the end of the SP. What you want is one SP source code with semi-colons after certain lines of code, and a different terminator at the very end (such as "@"). The when you create the SP you will need to specify a different terminator, for example:

db2 -td@ -f input_file.sql > output_file.out