Re: Stored Procedure on iSeries.

Misty Thornton wrote:

5. Re: Stored Procedure on iSeries. (Misty Thornton)

P1: BEGIN
IF EXISTS (SELECT * FROM MYDB.EMAIL WHERE EMAIL_ID = em)
THEN
UPDATE MYDB.EMAIL
SET EMAIL_DESC= 'YYY'
WHERE EMAIL_ID = em
ELSE
INSERT INTO MYDB.EMAIL(EMAIL_ID, EMAIL_DESC)
VALUES (em, 'YYY')
END IF
END P1

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 "ELSE" was found following "WHERE EMAIL_ID =
em
". Expected tokens may include: "<psm_semicolon>". LINE NUMBER=11.
SQLSTATE=42601


Hmm...what am I missing here....:).

Misty:

As far as I can tell, you're missing semi-colons at the ends of individual statements. I noted semi-colons you should keep, a couple semi-colons you should add and one semi-colon that needed to be removed. It looks as if you removed _all_ semi-colons. (I also noted the extra ELSE clause, and you properly removed that.)

The error appears to be telling you that a semi-colon is missing before the remaining ELSE clause. That semi-colon would mark the end of the first statement -- the UPDATE for MYDB.EMAIL. The next statement to mark would be the INSERT INTO MYDB.EMAIL. And then the entire IF statement is to be marked after END IF. Finally, END P1 is marked as the end of the BEGIN block although that one doesn't seem to be required.

You might also want to specify CREATE PROCEDURE ... LANGUAGE SQL. I'm not sure if any of the various interfaces allow that to be defaulted.

The SQL compiler found an ELSE clause but couldn't decide if you were trying to use it incorrectly as part of the UPDATE statement. I imagine that once it chose not to handle the word as an ELSE clause, it gave up trying to figure out where each subsequent statement started and ended. Maybe you had other error messages, but the semi-colon is the one you posted.

Tom Liotta

On 4/30/07, qsrvbas wrote:

(Apology for messing up the previous Subject...)

rpg400-l-request@xxxxxxxxxxxx wrote:

> 1. Stored Procedure on iSeries. (Misty Thornton)
>
> 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 ";" was found following "E WHERE EMAIL_ID
=
> em".
> Expected tokens may include: "<space>". LINE NUMBER=7. SQLSTATE=42601
>
> ***********
>
> CREATE PROCEDURE MYDB.MANAGE_EMAILS
>
> ( IN em VARCHAR(10))
> SPECIFIC MYDB.MANAGE_EMAILS
>
> DYNAMIC RESULT SETS 1
>
> P1: BEGIN
> IF EXISTS (SELECT * FROM MYDB.EMAIL WHERE EMAIL_ID = em;)
> THEN
> UPDATE MYDB.EMAIL
> SET EMAIL_DESC= 'YYY'
> WHERE EMAIL_ID = em;
> ELSE
> INSERT INTO MYDB.EMAIL(EMAIL_ID, EMAIL_DESC)
> VALUES (em, 'YYY')
> END IF;
> ELSE
> UPDATE MYDB.EMAIL
>
> SET EMAIL_DESC = 'YYY'
> WHERE EMAIL_ID = em;
> END IF
> END P1

Try this:

P1: BEGIN
IF EXISTS (SELECT * FROM MYDB.EMAIL WHERE EMAIL_ID = em)
<---- Removed semi-colon
THEN
UPDATE MYDB.EMAIL
SET EMAIL_DESC= 'YYY'
WHERE EMAIL_ID = em; <---- Keep semi-colon
ELSE
INSERT INTO MYDB.EMAIL(EMAIL_ID, EMAIL_DESC)
VALUES (em, 'YYY') ; <---- New semi-colon
END IF; <---- Keep semi-colon
-- ELSE <-- Commented
-- UPDATE MYDB.EMAIL <-- Commented
-- SET EMAIL_DESC = 'YYY' <-- Commented
-- WHERE EMAIL_ID = em; <-- Commented
-- END IF <-- Commented
END P1 ; <---- New semi-colon