Skip to main content


Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » DTP » Instead of Trigger Error
Instead of Trigger Error [message #510094] Tue, 26 January 2010 14:25 Go to next message
K Milligan is currently offline K MilliganFriend
Messages: 10
Registered: January 2010
Junior Member
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.

Report message to a moderator

Re: Instead of Trigger Error [message #510271 is a reply to message #510094] Wed, 27 January 2010 02:03 Go to previous messageGo to next message
Brian Payton is currently offline Brian PaytonFriend
Messages: 154
Registered: July 2009
Senior Member
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.
>

Report message to a moderator

Re: Instead of Trigger Error [message #511796 is a reply to message #510271] Tue, 02 February 2010 22:09 Go to previous messageGo to next message
K Milligan is currently offline K MilliganFriend
Messages: 10
Registered: January 2010
Junior Member

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

Report message to a moderator

Re: Instead of Trigger Error [message #512454 is a reply to message #511796] Fri, 05 February 2010 08:32 Go to previous messageGo to next message
Brian Payton is currently offline Brian PaytonFriend
Messages: 154
Registered: July 2009
Senior Member
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?

Report message to a moderator

Re: Instead of Trigger Error [message #512650 is a reply to message #512454] Fri, 05 February 2010 19:00 Go to previous messageGo to next message
K Milligan is currently offline K MilliganFriend
Messages: 10
Registered: January 2010
Junior Member
Yes, the terminator is set to a semi-colon

Report message to a moderator

Re: Instead of Trigger Error [message #512666 is a reply to message #512650] Fri, 05 February 2010 18:00 Go to previous messageGo to next message
Brian Payton is currently offline Brian PaytonFriend
Messages: 154
Registered: July 2009
Senior Member
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

Report message to a moderator

Re: Instead of Trigger Error [message #513104 is a reply to message #512666] Mon, 08 February 2010 20:21 Go to previous messageGo to next message
K Milligan is currently offline K MilliganFriend
Messages: 10
Registered: January 2010
Junior Member
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.

Report message to a moderator

Re: Instead of Trigger Error [message #513156 is a reply to message #513104] Tue, 09 February 2010 05:22 Go to previous messageGo to next message
Brian Payton is currently offline Brian PaytonFriend
Messages: 154
Registered: July 2009
Senior Member
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.

Report message to a moderator

Re: Instead of Trigger Error [message #515069 is a reply to message #513156] Wed, 17 February 2010 16:05 Go to previous messageGo to next message
K Milligan is currently offline K MilliganFriend
Messages: 10
Registered: January 2010
Junior Member
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.

Report message to a moderator

Re: Instead of Trigger Error [message #597170 is a reply to message #510271] Tue, 02 February 2010 22:09 Go to previous messageGo to next message
K Milligan is currently offline K MilliganFriend
Messages: 10
Registered: January 2010
Junior Member
This does not seem user-friendly. Users can execute some SQL statements using Rational Developer for i SQL interface, but some will not work?

Report message to a moderator

Re: Instead of Trigger Error [message #597186 is a reply to message #597170] Fri, 05 February 2010 08:32 Go to previous messageGo to next message
Brian Payton is currently offline Brian PaytonFriend
Messages: 154
Registered: July 2009
Senior Member
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?

Report message to a moderator

Re: Instead of Trigger Error [message #597194 is a reply to message #512454] Fri, 05 February 2010 19:00 Go to previous messageGo to next message
K Milligan is currently offline K MilliganFriend
Messages: 10
Registered: January 2010
Junior Member
Yes, the terminator is set to a semi-colon

Report message to a moderator

Re: Instead of Trigger Error [message #597205 is a reply to message #597194] Fri, 05 February 2010 22:52 Go to previous messageGo to next message
Brian Payton is currently offline Brian PaytonFriend
Messages: 154
Registered: July 2009
Senior Member
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

Report message to a moderator

Re: Instead of Trigger Error [message #597236 is a reply to message #597205] Mon, 08 February 2010 20:21 Go to previous messageGo to next message
K Milligan is currently offline K MilliganFriend
Messages: 10
Registered: January 2010
Junior Member
That works, but other IBM i SQL interfaces let developers use just the semi-colon(;) as the only statement terminator in an SQL script.

Report message to a moderator

Re: Instead of Trigger Error [message #597244 is a reply to message #597236] Tue, 09 February 2010 05:22 Go to previous messageGo to next message
Brian Payton is currently offline Brian PaytonFriend
Messages: 154
Registered: July 2009
Senior Member
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.

Report message to a moderator

Re: Instead of Trigger Error [message #597277 is a reply to message #513156] Wed, 17 February 2010 16:05 Go to previous message
K Milligan is currently offline K MilliganFriend
Messages: 10
Registered: January 2010
Junior Member
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.

Report message to a moderator

Previous Topic:Re: Database Explorer
Next Topic:Re: Database Explorer
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Nov 29 23:46:46 GMT 2020

Powered by FUDForum. Page generated in 0.02562 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software

Back to the top

Sign up to our Newsletter

A fresh new issue delivered monthly