catch exception in procedure FOR EACH ROW

[jane_lj]

Hi,

In Teiid procedure FOR EACH ROW, is there any way to catch exception or put debug information in Teiid Designer?

Because in runtime,  when something is wrong in the procedure, Teiid doesnt invoke the call to real data source, we have no idea what's wrong.

Thanks.

[shawkins]

What do you need more information about - a source or a Teiid exception?  Teiid 8.2+ with https://issues.jboss.org/browse/TEIID-1386 supports exception handling.  See https://docs.jboss.org/author/display/TEIID/Procedure+Language

Steve

[jane_lj]

Thank you, Steve.

I followed the link you mentioned, and wrote this exception handling in my procedure, but always got syntax errors:

FOR EACH ROW

BEGIN ATOMIC

    DECLARE integer x;

    DECLARE EXCEPTION e;

          INSERT INTO SM.table1 (...) VALUES (...);

           VARIABLES.x = (SELECT COUNT(column1I) FROM SM.table1);

    IF (x <= 0) THEN

      RAISE e;

    END IF

END

I got this error:

ERROR: TEIID31100 Parsing error: Encountered "<= 0) [*]THEN[*] RAISE e" at line 8, column 17.

Was expecting: "alter" | "begin" | "break" | "call" | "continue" | "create" | "declare" | "delete" | "drop" | "error" ...

Tried so many things, can't fix this error. Do you know where is wrong?

And where can I find the Teiid syntax  of exception handling and the Teiid predefined exception, like NO_DATA_FOUND?

Thanks a lot!

[shawkins]

THEN is not used in an if statement.  Did you see an errant example somewhere?  Otherwise you should follow the Reference and you can always just check the grammar - https://docs.jboss.org/author/display/TEIID/BNF+for+SQL+Grammar#BNFforSQLGrammar-ifStatement

Steve