Cursor not working

jeunne0000 · #1 (**permalink**) 02-08-10, 10:58

I have the following script. very simple just to see if this will work but unfortunately, I was not able to make it work. Care to tell me what is wrong?

==========================================
DECLARE GLOBAL TEMPORARY TABLE T1_TBL
( ID BIGINT,
NAME VARCHAR(200)
)
WITH REPLACE
ON COMMIT PRESERVE ROWS;

INSERT INTO session.T1_TBL
values(1,'grapes');
INSERT INTO session.T1_TBL
values(3,'apples');
INSERT INTO session.T1_TBL
values(3,'orange');

DECLARE C1 CURSOR FOR
SELECT name, id
FROM session.T1_TBL
FOR UPDATE OF name;
OPEN C1;
myLoop:
LOOP
fetch C1 INTO :name, :id;
if :id = 3 then
UPDATE session.T1_TBL
SET name = :name concat 'Alive';
end if;
END LOOP myLoop;
close C1;
commit;
==========================================

Running this script gave me the following error:

===============================

myLoop: LOOP fetch C1 INTO :name, :id
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 "myLoop" was found following
"BEGIN-OF-STATEMENT". Expected tokens may include: "<values>".
SQLSTATE=42601

SQL0104N An unexpected token "myLoop" was found following "BEGIN-OF-STATEMENT". Expected tokens may include: "<values> ".
===============================

I just want to make it work and see how cursor works in reading the resultset per row.

Please help. Many thanks.

n_i · #2 (**permalink**) 02-08-10, 11:23

I think LOOP cannot be used in the dynamic SQL context; it can only be a part of a procedure.

jeunne0000 · #3 (**permalink**) 02-09-10, 06:42

Thank you for your reply!

Ok I tried to put everything in a temp SP:

==============================================

CREATE PROCEDURE session.T1_SPx(OUT counter INTEGER)
LANGUAGE SQL
BEGIN
DECLARE C1 CURSOR FOR
SELECT name, id
FROM session.T1_TBL
FOR UPDATE OF name;
OPEN C1;
myLoop:
LOOP
fetch C1 INTO :name, :id;
if :id = 3 then
UPDATE session.T1_TBL
SET name = :name concat 'Alive';
end if;
END LOOP myLoop;
close C1;
commit;
END
==============================================

And it gives me an error

==============================================
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 "L FOR
UPDATE OF name". Expected tokens may include: "<psm_semicolon>". LINE
NUMBER=7. SQLSTATE=42601

SQL0104N An unexpected token "END-OF-STATEMENT" was found following "L
FOR UPDATE OF name". Expected tokens may include: "<psm_semicolon> ".
==============================================

I just want to know how I can make this FOR LOOP statement work. Many thanks!!!

n_i · #4 (**permalink**) 02-09-10, 07:10

See if this helps: bonehead question

jeunne0000 · #5 (**permalink**) 02-09-10, 07:31

Thank you for the prompt reply!

The problem is similar to mine. I did this the moment I received your reply

================================
CREATE PROCEDURE session.T1_SPx()
LANGUAGE SQL
BEGIN
DECLARE sname VARCHAR(50);

END@
================================

But still getting an error

================================
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 "RE sname
VARCHAR(50)". Expected tokens may include: "<psm_semicolon>". LINE
NUMBER=4. SQLSTATE=42601

SQL0104N An unexpected token "END-OF-STATEMENT" was found following "RE sname VARCHAR(50)". Expected tokens may include: "<psm_semicolon> ".

================================

Sorry but I am novice in using cursors in DB2. I have this script that should update a table and got a problem and my solution is I need to iterate the records in order for me to update the table correctly. Reason I want to use a cursor or control statements to achieve my goal.

The logic that I would like to do in my script is similar to this one below

================================
FOR v1 AS
c1 CURSOR FOR
SELECT name, id
FROM session.T1_TBL
DO
SET sname = name concat 'Hi';
UPDATE session.T1_TBL
SET name = sname;
END FOR;
================================

And as you know it is not working. I really need help how this cursor works in DB2.

Honestly, I really appreciate your help. And I hope sooner or later I can make this work with your help.

[-]

tonkuma · #6 (**permalink**) 02-09-10, 07:41

I did debug of your code
on Command Editor of DB2 9.7 on Windows/XP.

Code:

------------------------------ Commands Entered ------------------------------
CREATE PROCEDURE session.T1_SPx() 
LANGUAGE SQL 
BEGIN 
DECLARE GLOBAL TEMPORARY TABLE T1_TBL
( ID BIGINT,
NAME VARCHAR(200)
)
WITH REPLACE
ON COMMIT PRESERVE ROWS;

INSERT INTO session.T1_TBL
values(1,'grapes');
INSERT INTO session.T1_TBL
values(2,'apples');
INSERT INTO session.T1_TBL
values(3,'orange');

BEGIN
DECLARE v_name VARCHAR(200);
DECLARE v_id   BIGINT;
DECLARE C1 CURSOR FOR
SELECT name, id
  FROM session.T1_TBL
FOR UPDATE OF name;
OPEN C1;
myLoop:
LOOP
   fetch C1 INTO v_name, v_id;
   if v_id = 3 then
      UPDATE session.T1_TBL
         SET name = v_name concat 'Alive'
       WHERE CURRENT OF c1;
      LEAVE myLoop;
   end if;
END LOOP myLoop;
close C1;
commit;
END;

END@
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.

------------------------------ Commands Entered ------------------------------
CALL session.T1_SPx()@
------------------------------------------------------------------------------

  Return Status = 0

------------------------------ Commands Entered ------------------------------
SELECT * FROM session.T1_TBL@
------------------------------------------------------------------------------

ID                   NAME                                                                                                                                                                                                    
-------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                   1 grapes                                                                                                                                                                                                  
                   2 apples                                                                                                                                                                                                  
                   3 orangeAlive                                                                                                                                                                                             

  3 record(s) selected.

jeunne0000 · #7 (**permalink**) 02-09-10, 07:48

Hi Tonkuma!

Thank you for your reply!

I did try it and it gives me an error.

=================================
SQL0104N An unexpected token "END-OF-STATEMENT" was found following "COMMIT PRESERVE ROWS". Expected tokens may include: "<psm_semicolon> ".
=================================

I executed the script using the Query Command from the DB2 Control Center for Windows.

Thank you very much for future help!

[-]

tonkuma · #8 (**permalink**) 02-09-10, 07:57

See the link provided by Nick.

Quote:

See if this helps: bonehead question
__________________
===
Nick Ivanov

Then read the description and the following example...

Quote:

CREATE PROCEDURE is a single [compound] statement, up the the final END, so the first statement delimiter should appear right after the END, otherwise the statement will appear as syntactically incorrect to the parser. Now, you also have to delimit the statements _inside_ the CREATE PROCEDURE statement. The way out is to redefine the statement delimiter and place the _new_ delimiter at the end of the CREATE statement, while leaving alone the "internal" delimiters.

Thusly,
Code:
.......

jeunne0000 · #9 (**permalink**) 02-09-10, 08:34

@tonkuma:
Now, what I don't understand is that your script works fine when you try to execute it and not for me if all these things discussed here should solve my problem.

Is this a version limitation too? I am currently using DB2 v9.1.
I feel so hopeless.

[-]

tonkuma · #10 (**permalink**) 02-09-10, 09:05

I thought that it would be not version limitation, but not sure.

What error message(s) did you got by creating procedure on your Command Editor?

jeunne0000 · #11 (**permalink**) 02-09-10, 09:21

=================================
SQL0104N An unexpected token "END-OF-STATEMENT" was found following "COMMIT PRESERVE ROWS". Expected tokens may include: "<psm_semicolon> ".
=================================

And still couldn't figure out why the script is not working. If I can make this thing work, then I will be fine. I really need help on this.

Many Thanks again for the prompt reply. Super Thanks!

[-]

ARWinner · #12 (**permalink**) 02-09-10, 09:22

If you are using the Command Editor to create the Stored Procedure, you need to change the statement delimiter character. The default is the semicolon which will not work for stored procedures. The entire CREATE PROCEDURE is just one statement to DB2. All of the internal sql statements are still part of the CREATE PROCEDURE statement, so the semicolon on those are need and thus a different character is need for the CREATE PROCEDURE statement. If you leave it at the default value, you get the error you are getting because DB2 thinks the entire CREATE PROCEDURE statement ends at the first semicolon.

Andy

tonkuma · #13 (**permalink**) 02-09-10, 09:31

If you are using the Command Editor, follow this steps.
1) Tools ---> Tools Settings window ---> General tab ---> see "Use statement terminator character"
2) Change "Use statement terminator character"(it must be semicolon) to "@".
3) Close "Tools Settings" window.
4) Execute my sample...
CREATE PROCEDURE session.T1_SPx()
LANGUAGE SQL
BEGIN
.....
.....

END;

END@

jeunne0000 · #14 (**permalink**) 02-09-10, 09:31

@ARWinner

I changed the default delimiter value from semicolon(

to this sign (@). I tried to re-execute tonkuma's revised script and I was surprised.

It did work now! ^_^

============================================

DB20000I The SQL command completed successfully.
============================================

Thank you to all of you who helped me this quick!

To n_i, tonkuma and ARWinner: Many and Super Thanks !!!

[-]

jeunne0000 · #15 (**permalink**) 02-10-10, 06:12

Hi Again,

I have updated my script with the following. I was able to create the SP successfully but there was an error when trying to call it.

===============================================
CREATE PROCEDURE session.T1_SPx()
LANGUAGE SQL
BEGIN
declare v_parentid BIGINT;
declare v_name VARCHAR(50);
declare v_body CLOB;
declare c1 cursor for
select parentid, name, body
from session.CELL2_TMP
order by parentid
for update of body;
open c1;
cell_loop:
LOOP
fetch c1 into v_parentid, v_name, v_body;
UPDATE session.CELL2_TMP
SET body = replace(body,'R("' concat v_name concat '")','')
WHERE current of c1;
END LOOP cell_loop;
close c1;
commit;
END@
===============================================

The error is

===============================================

SQL0508N The cursor specified in the UPDATE or DELETE statement is not positioned on a row.

===============================================

Any idea what could be the problem?

Many thanks again for the help!

[-]

Internet Services

Web Development

Digital Marketing

Consumer Tech