Procédure NON stockées [Résolu]

[figolu47]

Bonjour,

J'ai la version 7.2 OS 390 de DB2 et j'ai un petit problème : il n'y a pas de ON UPDATE CASCADE et je dois donc faire ma propagation de mise à jour de clé par des chemins détournés.

Histoire de compliquer le tout, je n'ai pas les droits pour faire une procédure stockée!

Je voudrais donc envoyé un script mais je ne suis pas sur de la syntaxe, j'ai d'ailleurs des erreurs

Voici le script :

BEGIN
DECLARE VARCHAR(50) jrTitleTxt ;
DECLARE CURSOR C_JRSS IS
SELECT Jr_ID, EE_ID,JRSS_DESC
FROM T_TEST_JRSS
WHERE JR_ID='040683';

SELECT JR_TITLE_TXT INTO jrTitleTxt
FROM T_TEST_JOB_ROLE
WHERE JR_ID='040683';

INSERT INTO T_TEST_JOB_ROLE VALUES('M40683',jrtitletxt,'M49');

FOR CUR IN C_JRSS LOOP
INSERT INTO T_TEST_JRSS VALUES('M40683',CUR.Ee_Id,CUR.JRSS_DESC,tax);
END LOOP;

UPDATE T_TEST_JRSS_SKILL SET JR_ID='M40683' WHERE JR_ID='040683';

-- DELETE FROM T_TEST_JRSS WHERE Jr_Id='040683';
-- DELETE FROM T_TEST_JOB_ROLE WHERE Jr_Id='040683';
END;

et voici les erreurs :

BEGIN DECLARE VARCHAR(50) jrTitleTxt
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0199N The use of the reserved word "VARCHAR" following "" is not valid.
Expected tokens may include: "SECTION". SQLSTATE=42601

DECLARE CURSOR C_JRSS IS SELECT Jr_ID, EE_ID,JRSS_DESC FROM T_TEST_JRSS WHERE JR_ID='040683'
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 "C_JRSS" was found following "". Expected
tokens may include: "STATEMENT". SQLSTATE=42601

SELECT JR_TITLE_TXT INTO jrTitleTxt FROM T_TEST_JOB_ROLE WHERE JR_ID='040683'
SQL0104N An unexpected token "JRTITLETXT" was found following "". Expected
tokens may include: ":". SQLSTATE=42601

INSERT INTO T_TEST_JOB_ROLE VALUES('M40683',jrtitletxt,'M49')
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0206N "JRTITLETXT " is not valid in the context where it is used.
SQLSTATE=42703

FOR CUR IN C_JRSS LOOP INSERT INTO T_TEST_JRSS VALUES('M40683',CUR.Ee_Id,CUR.JRSS_DESC,tax)
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0199N The use of the reserved word "IN" following "" is not valid.
Expected tokens may include: "IS <HEXSTRING> <CHARSTRING> <GRAPHSTRING> ".
SQLSTATE=42601

END LOOP
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 "".
Expected tokens may include: "<IDENTIFIER> ". SQLSTATE=42601

UPDATE T_TEST_JRSS_SKILL SET JR_ID='M40683' WHERE JR_ID='040683'
SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a
query is an empty table. SQLSTATE=02000

END
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 "".
Expected tokens may include: "<IDENTIFIER> ". SQLSTATE=42601

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

Explanation:

A syntax error in the SQL statement was detected at the specified
token following the text "<text>". The "<text>" field indicates
the 20 characters of the SQL statement that preceded the token
that is not valid.

As an aid to the programmer, a partial list of valid tokens is
provided in the SQLERRM field of the SQLCA as "<token-list>".
This list assumes the statement is correct to that point.

The statement cannot be processed.

User Response:

Examine and correct the statement in the area of the specified
token.

sqlcode : -104

sqlstate : 42601

en un mot HEEEEEEEEELP


merci

[Luc Orient]

Et pourquoi ne pas faire un programme ?
Qu'appelez-vous "script" (ce n'est pas un terme naturel en z/OS ou OS/390) et comment l'exécutez vous ?

[jab]

Ce genre de commande peuvent-elle vraiment être utilisée en dehors d'une procédure stockée !?