Hi to all, could anyone tell me what's wrong with the following:

BEGIN ATOMIC
DECLARE st VARCHAR(1024);
DECLARE id INTEGER;
DECLARE cur CURSOR
FOR select r_mnemonic from resources.rt_resource
where r_mnemonic like 'ppp.element.media_url.%'
order by r_id;
DECLARE cur2 CURSOR for
select ec_id, ec_fileurl_mn from pe.ct_educ_content
order by ec_id
FOR UPDATE OF ec_fileurl_mn;
open cur;
open cur2;
fetch from cur into st;
fetch from cur2 into id;

while( SQLSTATE = '0000') DO
update pe.ct_educ_content set ec_file_url_mn=st
where current of cur2;
fetch from cur into st;
fetch from cur2 into id;
end while;
close cur;
close cur2;
end:

I get an error on the cursor declaration saying the FOR word is not expected:
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 "FOR" was found following " DECLARE cur CURSOR
". Expected tokens may include: "<SQL_variable_condition_declaration>".
LINE NUMBER=5. SQLSTATE=42601

Thanks

Is this a dynamic compound statement? Dynamic compound does not support
cursors. Only FOR loop.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Serge Rielau <srielau@ca.eye-be-em.com> wrote in message news:<c0b5u9$re1$1@hanover.torolab.ibm.com>...
> Is this a dynamic compound statement? Dynamic compound does not support
> cursors. Only FOR loop.
>
> Cheers
> Serge

Hello Serge,
I'm sorry, but I don't know what you mean by dynamic compound
statement.The same code works if I put it in a procedure. It this what
you mean by dynamic compound statement? What can't we use cursors out
of procedures. I have found that some things works in triggers but not
with procedures.

Thank you.

Ali,

SQL PL can be categorized into two technologies.
One is "inline" SQL PL, the other, what I like to call "packaged" or
"compiled" SQL PL.
Procedures are compiled once into static SQL when they are created.

SQL Functions, Methods and Triggers as well as a standalone BEGIN ATOMIC
.... END statement (a dynamic compound) are not compiled into separate
objects. A trigger or SQL function get macro expanded into the
surrounding statement and compield within it's context.
This is very powerful technology, but also very tricky.
Consequently inline SQL PL has only a subset of the statements at its
disposal.
These are:
FOR loop (which is very close to a CURSOR)
WHILE
SET
ITERATE
CONTINUE
SIGNAL
GET DIAGNOSTICs
DECLARE variable
DECLARE condition
IF THEN ELSE

I know of only two things that inline SQL PL can do today that packaged
SQL PL cannot:
Multi column set (SET (a, b, c) = (....))
and SELECT and VALUES without INTO clauses

The former is on the todo list, the later is a historical feature.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab