Eclipse Community Forums: DTP » Instead of Trigger Error

Home » Eclipse Projects » DTP » Instead of Trigger Error

Instead of Trigger Error [message #510094]

Tue, 26 January 2010 09:25

Eclipse User

The following Trigger definition fails when executed as part of an SQL script that's creating DB2 for i database objects.

CREATE TRIGGER INACTIVATE_EMPLOYEE
INSTEAD OF UPDATE ON ACTIVE_EMPLOYEES
REFERENCING OLD Old_row NEW New_row FOR EACH ROW
BEGIN ATOMIC
INSERT IGNORE INTO inactive_employees (emp_mast_pk)
select emp_mast_pk
from emp_mast e
where old_row.empno = e.empno;
END
;

The statement fails with an SQL0104 error (Token <end of statement> was not valid.

This SQL statement works fine with other IBM i SQL interfaces.

Re: Instead of Trigger Error [message #510271 is a reply to message #510094]

Tue, 26 January 2010 21:03

Eclipse User

I suspect that the problem is that semi-colon is used to terminate the
INSERT IGNORE INTO statement inside the trigger definition. The run service is
probably dividing the statement into pieces based on the statement
terminator, so it's executing the statement only up to the first
semi-colon. The SQL editor and it's run service needs a
database-specific adapter for DB2 for i to handle this, which
unfortunately isn't included with DTP.

You might want to try the IBM Data Studio product, available here (free
download):
http://www-01.ibm.com/software/data/optim/data-studio/

K Milligan wrote:
> The following Trigger definition fails when executed as part of an SQL
> script that's creating DB2 for i database objects.
>
> CREATE TRIGGER INACTIVATE_EMPLOYEE INSTEAD OF UPDATE ON
> ACTIVE_EMPLOYEES REFERENCING OLD Old_row NEW New_row FOR EACH ROW
> BEGIN ATOMIC
> INSERT IGNORE INTO inactive_employees (emp_mast_pk) select
> emp_mast_pk from emp_mast e where old_row.empno =
> e.empno; END
> ;
>
> The statement fails with an SQL0104 error (Token <end of statement> was
> not valid.
> This SQL statement works fine with other IBM i SQL interfaces.
>

Re: Instead of Trigger Error [message #511796 is a reply to message #510271]

Tue, 02 February 2010 17:09

Eclipse User

This does not seem user-friendly. Users can execute some SQL statements using Rational Developer for i SQL interface, but some will not work?

Re: Instead of Trigger Error [message #512454 is a reply to message #511796]

Fri, 05 February 2010 03:32

Eclipse User

I didn't realize you were using RDi. A question: when you right-click
in the SQL editor, do you see a "Set statement terminator" action? (If
RDi is using the DTP SQL File Editor you won't, but if it is using the
IBM-specific SQL and XQuery Editor, you will.)

K Milligan wrote:
>
> This does not seem user-friendly. Users can execute some SQL statements
> using Rational Developer for i SQL interface, but some will not work?

Re: Instead of Trigger Error [message #512650 is a reply to message #512454]

Fri, 05 February 2010 14:00

Eclipse User

Yes, the terminator is set to a semi-colon

Re: Instead of Trigger Error [message #512666 is a reply to message #512650]

Fri, 05 February 2010 13:00

Eclipse User

OK, try changing the statement terminator to something else, such as !
Then change all the statement terminators outside of the CREATE TRIGGER
statement to !, but leave the statement terminators inside the trigger
definition as ;

K Milligan wrote:
> Yes, the terminator is set to a semi-colon

Re: Instead of Trigger Error [message #513104 is a reply to message #512666]

Mon, 08 February 2010 15:21

Eclipse User

That works, but other IBM i SQL interfaces let developers use just the semi-colon( Wink

as the only statement terminator in an SQL script.

Re: Instead of Trigger Error [message #513156 is a reply to message #513104]

Tue, 09 February 2010 00:22

Eclipse User

The Run SQL service that the SQL editor uses to execute a script runs
the script one statement at a time. It uses the statement terminator to
find the boundary of each statement to run. A CREATE TRIGGER or CREATE
PROCEDURE statement contains nested statements, but it needs to be
treated as one statement when it is executed by the Run SQL service. So
if the same statement terminator character is used, the Run SQL service
won't be able to find the statement boundary accurately. (It simply
scans for the statement terminator; it doesn't try to parse the
statements to find out whether they are compound statements.)

K Milligan wrote:
> That works, but other IBM i SQL interfaces let developers use just the
> semi-colon(;) as the only statement terminator in an SQL script.

Re: Instead of Trigger Error [message #515069 is a reply to message #513156]

Wed, 17 February 2010 11:05

Eclipse User

Are there any plans to enhance the Run SQL Service to make it smarter, so that users can simply use a single terminator?

The Rochester team has Java code that detects the compound statement and are willing to share it.

Re: Instead of Trigger Error [message #597170 is a reply to message #510271]

Tue, 02 February 2010 17:09

Eclipse User

This does not seem user-friendly. Users can execute some SQL statements using Rational Developer for i SQL interface, but some will not work?

Re: Instead of Trigger Error [message #597186 is a reply to message #597170]

Fri, 05 February 2010 03:32

Eclipse User