You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "A Hunt (JIRA)" <ji...@apache.org> on 2009/10/10 14:00:31 UTC
[jira] Created: (DERBY-4404) Creating trigger throws error in
embedded derby
Creating trigger throws error in embedded derby
-----------------------------------------------
Key: DERBY-4404
URL: https://issues.apache.org/jira/browse/DERBY-4404
Project: Derby
Issue Type: Bug
Components: SQL
Affects Versions: 10.5.1.1
Environment: Ubuntu 8.04 (GNU/Linux), IcedTea6
Reporter: A Hunt
Trying to create a trigger throws an error on embedded Derby, the same trigger can be created fine using ij. When trying to create the trigger, the error thrown states:
Syntax error: Encountered "NEW" at line 1, column 69.
The trigger statement used:
CREATE TRIGGER trig_dGroup1 AFTER INSERT ON groups
REFERENCING NEW AS mod
FOR EACH ROW MODE DB2SQL
UPDATE groups SET isDefault = 0 WHERE (isDefault = 1) AND ((NOT (id = mod.id)) AND (mod.isDefault = 1))
I have created the database beforehand and set up the table groups using:
connect 'jdbc:derby:MyDbTest;create=true';
(con = DriverManager.getConnection(connectionURL) for the embedded program)
Then I run the statement:
CREATE TABLE groups (
id INT NOT NULL GENERATED ALWAYS AS IDENTITY CONSTRAINT WISH_PK PRIMARY KEY,
name VARCHAR(32) NOT NULL,
description VARCHAR(64),
colour INT NOT NULL,
isDefault INT NOT NULL,
isPermanent INT NOT NULL
)
In java the statements are run using s.execute(methodLoadingScriptFromFile());
I have also tried setting up a new statement for the running of the trigger creation statement, which didn't help.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4404) Creating trigger throws error in
embedded derby
Posted by "Dag H. Wanvik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4404?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12764412#action_12764412 ]
Dag H. Wanvik commented on DERBY-4404:
--------------------------------------
What does the method methodLoadingScriptFromFile? It can only be a single statement text.
execute can't run scripts.
These two statements worked for me:
s.execute("CREATE TABLE groups (" +
"id INT NOT NULL GENERATED ALWAYS AS IDENTITY CONSTRAINT WISH_PK PRIMARY KEY," +
"name VARCHAR(32) NOT NULL," +
"description VARCHAR(64)," +
"colour INT NOT NULL," +
"isDefault INT NOT NULL," +
"isPermanent INT NOT NULL" +
") ");
s.execute("CREATE TRIGGER trig_dGroup1 AFTER INSERT ON groups " +
"REFERENCING NEW ROW AS mod " +
"FOR EACH ROW MODE DB2SQL " +
"UPDATE groups SET isDefault = 0 WHERE (isDefault = 1) AND ((NOT (id = mod.id)) AND (mod.isDefault = 1))");
I do see the error if I neglect to add space at the end of first line (after "groups"). Could that be your problem if the statement is read from a file?
> Creating trigger throws error in embedded derby
> -----------------------------------------------
>
> Key: DERBY-4404
> URL: https://issues.apache.org/jira/browse/DERBY-4404
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.5.1.1
> Environment: Ubuntu 8.04 (GNU/Linux), IcedTea6
> Reporter: A Hunt
>
> Trying to create a trigger throws an error on embedded Derby, the same trigger can be created fine using ij. When trying to create the trigger, the error thrown states:
> Syntax error: Encountered "NEW" at line 1, column 69.
> The trigger statement used:
> CREATE TRIGGER trig_dGroup1 AFTER INSERT ON groups
> REFERENCING NEW AS mod
> FOR EACH ROW MODE DB2SQL
> UPDATE groups SET isDefault = 0 WHERE (isDefault = 1) AND ((NOT (id = mod.id)) AND (mod.isDefault = 1))
> I have created the database beforehand and set up the table groups using:
> connect 'jdbc:derby:MyDbTest;create=true';
> (con = DriverManager.getConnection(connectionURL) for the embedded program)
> Then I run the statement:
> CREATE TABLE groups (
> id INT NOT NULL GENERATED ALWAYS AS IDENTITY CONSTRAINT WISH_PK PRIMARY KEY,
> name VARCHAR(32) NOT NULL,
> description VARCHAR(64),
> colour INT NOT NULL,
> isDefault INT NOT NULL,
> isPermanent INT NOT NULL
> )
> In java the statements are run using s.execute(methodLoadingScriptFromFile());
> I have also tried setting up a new statement for the running of the trigger creation statement, which didn't help.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Closed: (DERBY-4404) Creating trigger throws error in
embedded derby
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4404?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen closed DERBY-4404.
-------------------------------------
Resolution: Invalid
> Creating trigger throws error in embedded derby
> -----------------------------------------------
>
> Key: DERBY-4404
> URL: https://issues.apache.org/jira/browse/DERBY-4404
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.5.1.1
> Environment: Ubuntu 8.04 (GNU/Linux), IcedTea6
> Reporter: A Hunt
>
> Trying to create a trigger throws an error on embedded Derby, the same trigger can be created fine using ij. When trying to create the trigger, the error thrown states:
> Syntax error: Encountered "NEW" at line 1, column 69.
> The trigger statement used:
> CREATE TRIGGER trig_dGroup1 AFTER INSERT ON groups
> REFERENCING NEW AS mod
> FOR EACH ROW MODE DB2SQL
> UPDATE groups SET isDefault = 0 WHERE (isDefault = 1) AND ((NOT (id = mod.id)) AND (mod.isDefault = 1))
> I have created the database beforehand and set up the table groups using:
> connect 'jdbc:derby:MyDbTest;create=true';
> (con = DriverManager.getConnection(connectionURL) for the embedded program)
> Then I run the statement:
> CREATE TABLE groups (
> id INT NOT NULL GENERATED ALWAYS AS IDENTITY CONSTRAINT WISH_PK PRIMARY KEY,
> name VARCHAR(32) NOT NULL,
> description VARCHAR(64),
> colour INT NOT NULL,
> isDefault INT NOT NULL,
> isPermanent INT NOT NULL
> )
> In java the statements are run using s.execute(methodLoadingScriptFromFile());
> I have also tried setting up a new statement for the running of the trigger creation statement, which didn't help.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4404) Creating trigger throws error in
embedded derby
Posted by "A Hunt (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4404?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12764367#action_12764367 ]
A Hunt commented on DERBY-4404:
-------------------------------
I also tried 10.5.3, with no change.
> Creating trigger throws error in embedded derby
> -----------------------------------------------
>
> Key: DERBY-4404
> URL: https://issues.apache.org/jira/browse/DERBY-4404
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.5.1.1
> Environment: Ubuntu 8.04 (GNU/Linux), IcedTea6
> Reporter: A Hunt
>
> Trying to create a trigger throws an error on embedded Derby, the same trigger can be created fine using ij. When trying to create the trigger, the error thrown states:
> Syntax error: Encountered "NEW" at line 1, column 69.
> The trigger statement used:
> CREATE TRIGGER trig_dGroup1 AFTER INSERT ON groups
> REFERENCING NEW AS mod
> FOR EACH ROW MODE DB2SQL
> UPDATE groups SET isDefault = 0 WHERE (isDefault = 1) AND ((NOT (id = mod.id)) AND (mod.isDefault = 1))
> I have created the database beforehand and set up the table groups using:
> connect 'jdbc:derby:MyDbTest;create=true';
> (con = DriverManager.getConnection(connectionURL) for the embedded program)
> Then I run the statement:
> CREATE TABLE groups (
> id INT NOT NULL GENERATED ALWAYS AS IDENTITY CONSTRAINT WISH_PK PRIMARY KEY,
> name VARCHAR(32) NOT NULL,
> description VARCHAR(64),
> colour INT NOT NULL,
> isDefault INT NOT NULL,
> isPermanent INT NOT NULL
> )
> In java the statements are run using s.execute(methodLoadingScriptFromFile());
> I have also tried setting up a new statement for the running of the trigger creation statement, which didn't help.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Reopened: (DERBY-4404) Creating trigger throws error in
embedded derby
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4404?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen reopened DERBY-4404:
---------------------------------------
> Creating trigger throws error in embedded derby
> -----------------------------------------------
>
> Key: DERBY-4404
> URL: https://issues.apache.org/jira/browse/DERBY-4404
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.5.1.1
> Environment: Ubuntu 8.04 (GNU/Linux), IcedTea6
> Reporter: A Hunt
>
> Trying to create a trigger throws an error on embedded Derby, the same trigger can be created fine using ij. When trying to create the trigger, the error thrown states:
> Syntax error: Encountered "NEW" at line 1, column 69.
> The trigger statement used:
> CREATE TRIGGER trig_dGroup1 AFTER INSERT ON groups
> REFERENCING NEW AS mod
> FOR EACH ROW MODE DB2SQL
> UPDATE groups SET isDefault = 0 WHERE (isDefault = 1) AND ((NOT (id = mod.id)) AND (mod.isDefault = 1))
> I have created the database beforehand and set up the table groups using:
> connect 'jdbc:derby:MyDbTest;create=true';
> (con = DriverManager.getConnection(connectionURL) for the embedded program)
> Then I run the statement:
> CREATE TABLE groups (
> id INT NOT NULL GENERATED ALWAYS AS IDENTITY CONSTRAINT WISH_PK PRIMARY KEY,
> name VARCHAR(32) NOT NULL,
> description VARCHAR(64),
> colour INT NOT NULL,
> isDefault INT NOT NULL,
> isPermanent INT NOT NULL
> )
> In java the statements are run using s.execute(methodLoadingScriptFromFile());
> I have also tried setting up a new statement for the running of the trigger creation statement, which didn't help.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4404) Creating trigger throws error in
embedded derby
Posted by "A Hunt (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4404?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12764425#action_12764425 ]
A Hunt commented on DERBY-4404:
-------------------------------
Thanks for your help and sorry for being so stupid. The missing space was causing the problem (i.e. groupsREFERENCING was run together...), so I've now corrected the method loading scripts to add a space where each newline was. Problem is solved now, thanks again.
> Creating trigger throws error in embedded derby
> -----------------------------------------------
>
> Key: DERBY-4404
> URL: https://issues.apache.org/jira/browse/DERBY-4404
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.5.1.1
> Environment: Ubuntu 8.04 (GNU/Linux), IcedTea6
> Reporter: A Hunt
>
> Trying to create a trigger throws an error on embedded Derby, the same trigger can be created fine using ij. When trying to create the trigger, the error thrown states:
> Syntax error: Encountered "NEW" at line 1, column 69.
> The trigger statement used:
> CREATE TRIGGER trig_dGroup1 AFTER INSERT ON groups
> REFERENCING NEW AS mod
> FOR EACH ROW MODE DB2SQL
> UPDATE groups SET isDefault = 0 WHERE (isDefault = 1) AND ((NOT (id = mod.id)) AND (mod.isDefault = 1))
> I have created the database beforehand and set up the table groups using:
> connect 'jdbc:derby:MyDbTest;create=true';
> (con = DriverManager.getConnection(connectionURL) for the embedded program)
> Then I run the statement:
> CREATE TABLE groups (
> id INT NOT NULL GENERATED ALWAYS AS IDENTITY CONSTRAINT WISH_PK PRIMARY KEY,
> name VARCHAR(32) NOT NULL,
> description VARCHAR(64),
> colour INT NOT NULL,
> isDefault INT NOT NULL,
> isPermanent INT NOT NULL
> )
> In java the statements are run using s.execute(methodLoadingScriptFromFile());
> I have also tried setting up a new statement for the running of the trigger creation statement, which didn't help.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Closed: (DERBY-4404) Creating trigger throws error in
embedded derby
Posted by "A Hunt (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4404?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
A Hunt closed DERBY-4404.
-------------------------
Resolution: Fixed
Bug behavior facts: (was: [Embedded/Client difference, Deviation from standard])
> Creating trigger throws error in embedded derby
> -----------------------------------------------
>
> Key: DERBY-4404
> URL: https://issues.apache.org/jira/browse/DERBY-4404
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.5.1.1
> Environment: Ubuntu 8.04 (GNU/Linux), IcedTea6
> Reporter: A Hunt
>
> Trying to create a trigger throws an error on embedded Derby, the same trigger can be created fine using ij. When trying to create the trigger, the error thrown states:
> Syntax error: Encountered "NEW" at line 1, column 69.
> The trigger statement used:
> CREATE TRIGGER trig_dGroup1 AFTER INSERT ON groups
> REFERENCING NEW AS mod
> FOR EACH ROW MODE DB2SQL
> UPDATE groups SET isDefault = 0 WHERE (isDefault = 1) AND ((NOT (id = mod.id)) AND (mod.isDefault = 1))
> I have created the database beforehand and set up the table groups using:
> connect 'jdbc:derby:MyDbTest;create=true';
> (con = DriverManager.getConnection(connectionURL) for the embedded program)
> Then I run the statement:
> CREATE TABLE groups (
> id INT NOT NULL GENERATED ALWAYS AS IDENTITY CONSTRAINT WISH_PK PRIMARY KEY,
> name VARCHAR(32) NOT NULL,
> description VARCHAR(64),
> colour INT NOT NULL,
> isDefault INT NOT NULL,
> isPermanent INT NOT NULL
> )
> In java the statements are run using s.execute(methodLoadingScriptFromFile());
> I have also tried setting up a new statement for the running of the trigger creation statement, which didn't help.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.