[jira] [Closed] (DERBY-6876) Can't create triggers on a table - error 42X94
[ https://issues.apache.org/jira/browse/DERBY-6876?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Aleksei Kovura closed DERBY-6876. - Resolution: Won't Fix > Can't create triggers on a table - error 42X94 > -- > > Key: DERBY-6876 > URL: https://issues.apache.org/jira/browse/DERBY-6876 > Project: Derby > Issue Type: Bug > Environment: Linux x86_64, Java 1.8.0_74 >Reporter: Aleksei Kovura > Attachments: trigger_bug.zip > > > I previously shared this on dev mailing list - > http://thread.gmane.org/gmane.comp.apache.db.derby.devel/115427. > I'm reworking triggers in my database and getting this message while trying > to create one: > > Error: StoredPreparedStatement '19ba803c-014e-b216-6d98-0650b418' does > not exist. > SQLState: 42X94 > ErrorCode: 3 > > This database is a couple years old, started out as 10.10.1.1, was upgraded > to stable releases as they appeared (10.11.1.1 -> 10.12.1.1). Some DDL was > changed in between upgrades, unfortunately I can't track which ones and when. > SYSCS_UTIL.SYSCS_INVALIDATE_STORED_STATEMENTS() doesn't help. > I tried my best to create a replicable test case through creating database on > earlier versions, upgrading and playing with DDL - no such luck, so I'm > attaching compressed database (with data deleted and tables compressed). Bug > replication procedure is as follows: > 1) Unpack attached file (it has a "trigger_bug" root directory); > 2) Boot the db_trigger database in embedded mode, include "trigger_bug" > directory in the classpath - there is one Java class that is referenced in > Stored Procedure; > 3) Try to run this SQL: > CREATE TRIGGER APP."test" > AFTER UPDATE OF description ON APP."ACTIONS" > REFERENCING NEW ROW AS updated_row > FOR EACH ROW > UPDATE APP."ACTIONS" SET description = 'testing' > WHERE id=updated_row.id > 4) Get a 42X94 error. > I'm hoping someone with enough knowledge can poke around in system tables and > figure out what's going on. -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Comment Edited] (DERBY-6882) Add functionality to ALTER TABLE: switch from GENERATED ALWAYS to GENERATED BY DEFAULT
[
https://issues.apache.org/jira/browse/DERBY-6882?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15252362#comment-15252362
]
Aleksei Kovura edited comment on DERBY-6882 at 4/23/16 4:17 AM:
Some assistance would be nice actually. I tried checking out derby code:
mkdir derby ; svn co https://svn.apache.org/repos/asf/db/derby/code/trunk derby
... and switching to a revision you specified in the next post (r1740299):
svn up -r1740299
It builds successfully, but I end up with "10.13.0.0 alpha" version of jars in
jars/sane/. It's supposed to be some sort of 10.12.xx, right?
- EDIT: -
It seems JIRA is still in Lockdown, so I'll edit this comment because I can't
post a new one.
I've built 10.12 branch. Below is an example where this new functionality
doesn't quite work as expected. If after this example I do ALTER TABLE ACTIONS
ALTER COLUMN id RESTART WITH 5; then the table starts working normally. It
would be more handy if Derby automatically handled ID gaps and skipped already
existing IDs though. If that won't be implemented for whatever reason, then it
should be documented that users should restart ID generation according to their
current max ID value, increment and so on (I can contribute to documention).
What do you think?
--- USAGE SCENARIO
java -jar derbyrun.jar ij
ij version 10.12
ij> connect 'jdbc:derby:sample_alter;create=true';
ij> CREATE TABLE APP."ACTIONS" (id INTEGER NOT NULL GENERATED ALWAYS AS
IDENTITY CONSTRAINT PK_ACTION_ID PRIMARY KEY,description VARCHAR(128) NOT NULL);
0 rows inserted/updated/deleted
ij> INSERT INTO ACTIONS (description) VALUES ('action1');
1 row inserted/updated/deleted
ij> INSERT INTO ACTIONS (description) VALUES ('action2');
1 row inserted/updated/deleted
ij> select * from actions;
ID |DESCRIPTION
1 |action1
2 |action2
2 rows selected
ij> ALTER TABLE ACTIONS ALTER COLUMN id SET GENERATED BY DEFAULT;
0 rows inserted/updated/deleted
ij> INSERT INTO ACTIONS (id, description) VALUES (4, 'action4');
1 row inserted/updated/deleted
ij> select * from actions;
ID |DESCRIPTION
1 |action1
2 |action2
4 |action4
3 rows selected
ij> ALTER TABLE ACTIONS ALTER COLUMN id SET GENERATED ALWAYS;
0 rows inserted/updated/deleted
ij> INSERT INTO ACTIONS (description) VALUES ('action2');
1 row inserted/updated/deleted
ij> select * from actions;
ID |DESCRIPTION
1 |action1
2 |action2
4 |action4
3 |action2
4 rows selected
ij> INSERT INTO ACTIONS (description) VALUES ('action2');
ERROR 23505: The statement was aborted because it would have caused a duplicate
key value in a unique or primary key constraint or unique index identified by
'PK_ACTION_ID' defined on 'ACTIONS'.
was (Author: alex k.):
Some assistance would be nice actually. I tried checking out derby code:
mkdir derby ; svn co https://svn.apache.org/repos/asf/db/derby/code/trunk derby
... and switching to a revision you specified
[jira] [Commented] (DERBY-6882) Add functionality to ALTER TABLE: switch from GENERATED ALWAYS to GENERATED BY DEFAULT
[ https://issues.apache.org/jira/browse/DERBY-6882?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15252362#comment-15252362 ] Aleksei Kovura commented on DERBY-6882: --- Some assistance would be nice actually. I tried checking out derby code: mkdir derby ; svn co https://svn.apache.org/repos/asf/db/derby/code/trunk derby ... and switching to a revision you specified in the next post (r1740299): svn up -r1740299 It builds successfully, but I end up with "10.13.0.0 alpha" version of jars in jars/sane/. It's supposed to be some sort of 10.12.xx, right? > Add functionality to ALTER TABLE: switch from GENERATED ALWAYS to GENERATED > BY DEFAULT > -- > > Key: DERBY-6882 > URL: https://issues.apache.org/jira/browse/DERBY-6882 > Project: Derby > Issue Type: Improvement > Components: SQL >Affects Versions: 10.12.1.1 >Reporter: Aleksei Kovura >Assignee: Rick Hillegas > Labels: features > Attachments: derby-6882-01-aa-initialCode.diff, > derby-6882-01-bb-withTests.diff, derby-6882-02-aa-port_1738368_to_10.12.diff > > > I'm trying to import data from another Derby database with foreignViews tool > because system tables got corrupted somehow (see > https://issues.apache.org/jira/browse/DERBY-6876). Tables contain generated > ID columns (created as "GENERATED ALWAYS"). Importing data and allowing Derby > to generate new IDs in generated columns will break relationships between > tables (old tables have counter gaps there due to deletes - IDs won't match). > For a clean import without breaking DDL information in DB version control I > would like to be able to switch between generated types as follows: > > ALTER TABLE table1 ALTER COLUMN col1 SET GENERATED BY DEFAULT [ AS IDENTITY] > ALTER TABLE table1 ALTER COLUMN col1 SET GENERATED ALWAYS [ AS IDENTITY] -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (DERBY-6882) Add functionality to ALTER TABLE: switch from GENERATED ALWAYS to GENERATED BY DEFAULT
[ https://issues.apache.org/jira/browse/DERBY-6882?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15249709#comment-15249709 ] Aleksei Kovura commented on DERBY-6882: --- Hi Rick, I didn't find any other workaround, so please port it. Thanks! > Add functionality to ALTER TABLE: switch from GENERATED ALWAYS to GENERATED > BY DEFAULT > -- > > Key: DERBY-6882 > URL: https://issues.apache.org/jira/browse/DERBY-6882 > Project: Derby > Issue Type: Improvement > Components: SQL >Affects Versions: 10.12.1.1 >Reporter: Aleksei Kovura >Assignee: Rick Hillegas > Labels: features > Attachments: derby-6882-01-aa-initialCode.diff, > derby-6882-01-bb-withTests.diff > > > I'm trying to import data from another Derby database with foreignViews tool > because system tables got corrupted somehow (see > https://issues.apache.org/jira/browse/DERBY-6876). Tables contain generated > ID columns (created as "GENERATED ALWAYS"). Importing data and allowing Derby > to generate new IDs in generated columns will break relationships between > tables (old tables have counter gaps there due to deletes - IDs won't match). > For a clean import without breaking DDL information in DB version control I > would like to be able to switch between generated types as follows: > > ALTER TABLE table1 ALTER COLUMN col1 SET GENERATED BY DEFAULT [ AS IDENTITY] > ALTER TABLE table1 ALTER COLUMN col1 SET GENERATED ALWAYS [ AS IDENTITY] -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Updated] (DERBY-6882) Add functionality to ALTER TABLE: switch from GENERATED ALWAYS to GENERATED BY DEFAULT
[ https://issues.apache.org/jira/browse/DERBY-6882?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Aleksei Kovura updated DERBY-6882: -- Description: I'm trying to import data from another Derby database with foreignViews tool because system tables got corrupted somehow (see https://issues.apache.org/jira/browse/DERBY-6876). Tables contain generated ID columns (created as "GENERATED ALWAYS"). Importing data and allowing Derby to generate new IDs in generated columns will break relationships between tables (old tables have counter gaps there due to deletes - IDs won't match). For a clean import without breaking DDL information in DB version control I would like to be able to switch between generated types as follows: ALTER TABLE table1 ALTER COLUMN col1 SET GENERATED BY DEFAULT [ AS IDENTITY] ALTER TABLE table1 ALTER COLUMN col1 SET GENERATED ALWAYS [ AS IDENTITY] was: I'm trying to import data from another Derby database with foreignViews tool because system tables got corrupted somehow (see https://issues.apache.org/jira/browse/DERBY-6876). Tables contain generated ID columns (created as "GENERATED ALWAYS"). Importing data and allowing Derby to generate new IDs in generated columns will break relationships between tables (old tables have counter gaps there due to deletes - IDs won't match). For a clean import without breaking DDL information in DB version control I would like to be able to switch between generated types as follows: ALTER TABLE table1 ALTER COLUMN col1 SET GENERATED BY DEFAULT ALTER TABLE table1 ALTER COLUMN col1 SET GENERATED ALWAYS > Add functionality to ALTER TABLE: switch from GENERATED ALWAYS to GENERATED > BY DEFAULT > -- > > Key: DERBY-6882 > URL: https://issues.apache.org/jira/browse/DERBY-6882 > Project: Derby > Issue Type: Improvement > Components: SQL >Affects Versions: 10.12.1.1 >Reporter: Aleksei Kovura > Labels: features > > I'm trying to import data from another Derby database with foreignViews tool > because system tables got corrupted somehow (see > https://issues.apache.org/jira/browse/DERBY-6876). Tables contain generated > ID columns (created as "GENERATED ALWAYS"). Importing data and allowing Derby > to generate new IDs in generated columns will break relationships between > tables (old tables have counter gaps there due to deletes - IDs won't match). > For a clean import without breaking DDL information in DB version control I > would like to be able to switch between generated types as follows: > > ALTER TABLE table1 ALTER COLUMN col1 SET GENERATED BY DEFAULT [ AS IDENTITY] > ALTER TABLE table1 ALTER COLUMN col1 SET GENERATED ALWAYS [ AS IDENTITY] -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Updated] (DERBY-6882) Add functionality to ALTER TABLE: switch from GENERATED ALWAYS to GENERATED BY DEFAULT
[ https://issues.apache.org/jira/browse/DERBY-6882?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Aleksei Kovura updated DERBY-6882: -- Description: I'm trying to import data from another Derby database with foreignViews tool because system tables got corrupted somehow (see https://issues.apache.org/jira/browse/DERBY-6876). Tables contain generated ID columns (created as "GENERATED ALWAYS"). Importing data and allowing Derby to generate new IDs in generated columns will break relationships between tables (old tables have counter gaps there due to deletes - IDs won't match). For a clean import without breaking DDL information in DB version control I would like to be able to switch between generated types as follows: ALTER TABLE table1 ALTER COLUMN col1 SET GENERATED BY DEFAULT ALTER TABLE table1 ALTER COLUMN col1 SET GENERATED ALWAYS was: I'm trying to import data from another Derby database with foreignViews tool because system tables got corrupted somehow (see https://issues.apache.org/jira/browse/DERBY-6876). Tables contain generated ID columns (created as "GENERATED ALWAYS"). Importing data and allowing Derby to generate new IDs in generated columns will break relationships between tables (old tables have counter gaps there due to deletes - IDs won't match). For a clean import without breaking DDL information in DB version control I would like to be able to switch between generated types as follows: ALTER TABLE ALTER COLUMN col1 SET GENERATED BY DEFAULT ALTER TABLE ALTER COLUMN col1 SET GENERATED ALWAYS > Add functionality to ALTER TABLE: switch from GENERATED ALWAYS to GENERATED > BY DEFAULT > -- > > Key: DERBY-6882 > URL: https://issues.apache.org/jira/browse/DERBY-6882 > Project: Derby > Issue Type: Improvement > Components: SQL >Affects Versions: 10.12.1.1 >Reporter: Aleksei Kovura > Labels: features > > I'm trying to import data from another Derby database with foreignViews tool > because system tables got corrupted somehow (see > https://issues.apache.org/jira/browse/DERBY-6876). Tables contain generated > ID columns (created as "GENERATED ALWAYS"). Importing data and allowing Derby > to generate new IDs in generated columns will break relationships between > tables (old tables have counter gaps there due to deletes - IDs won't match). > For a clean import without breaking DDL information in DB version control I > would like to be able to switch between generated types as follows: > > ALTER TABLE table1 ALTER COLUMN col1 SET GENERATED BY DEFAULT > ALTER TABLE table1 ALTER COLUMN col1 SET GENERATED ALWAYS -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Created] (DERBY-6882) Add functionality to ALTER TABLE: switch from GENERATED ALWAYS to GENERATED BY DEFAULT
Aleksei Kovura created DERBY-6882: - Summary: Add functionality to ALTER TABLE: switch from GENERATED ALWAYS to GENERATED BY DEFAULT Key: DERBY-6882 URL: https://issues.apache.org/jira/browse/DERBY-6882 Project: Derby Issue Type: Improvement Components: SQL Affects Versions: 10.12.1.1 Reporter: Aleksei Kovura I'm trying to import data from another Derby database with foreignViews tool because system tables got corrupted somehow (see https://issues.apache.org/jira/browse/DERBY-6876). Tables contain generated ID columns (created as "GENERATED ALWAYS"). Importing data and allowing Derby to generate new IDs in generated columns will break relationships between tables (old tables have counter gaps there due to deletes - IDs won't match). For a clean import without breaking DDL information in DB version control I would like to be able to switch between generated types as follows: ALTER TABLE ALTER COLUMN col1 SET GENERATED BY DEFAULT ALTER TABLE ALTER COLUMN col1 SET GENERATED ALWAYS -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (DERBY-6876) Can't create triggers on a table - error 42X94
[ https://issues.apache.org/jira/browse/DERBY-6876?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15217926#comment-15217926 ] Aleksei Kovura commented on DERBY-6876: --- Thanks Rick, I didn't think of that. I'm still trying to avoid messing with DDL, because it's under version control by flywaydb. [~bpendleton] Just to get some clarity on this: is there absolutely no hope of figuring out corruption issue and repairing this database? You wrote "I don't think there is any easy way to repair a damaged SYS.SYSDEPENDS table" - does that mean there's a hard way? :) > Can't create triggers on a table - error 42X94 > -- > > Key: DERBY-6876 > URL: https://issues.apache.org/jira/browse/DERBY-6876 > Project: Derby > Issue Type: Bug > Environment: Linux x86_64, Java 1.8.0_74 >Reporter: Aleksei Kovura > Attachments: trigger_bug.zip > > > I previously shared this on dev mailing list - > http://thread.gmane.org/gmane.comp.apache.db.derby.devel/115427. > I'm reworking triggers in my database and getting this message while trying > to create one: > > Error: StoredPreparedStatement '19ba803c-014e-b216-6d98-0650b418' does > not exist. > SQLState: 42X94 > ErrorCode: 3 > > This database is a couple years old, started out as 10.10.1.1, was upgraded > to stable releases as they appeared (10.11.1.1 -> 10.12.1.1). Some DDL was > changed in between upgrades, unfortunately I can't track which ones and when. > SYSCS_UTIL.SYSCS_INVALIDATE_STORED_STATEMENTS() doesn't help. > I tried my best to create a replicable test case through creating database on > earlier versions, upgrading and playing with DDL - no such luck, so I'm > attaching compressed database (with data deleted and tables compressed). Bug > replication procedure is as follows: > 1) Unpack attached file (it has a "trigger_bug" root directory); > 2) Boot the db_trigger database in embedded mode, include "trigger_bug" > directory in the classpath - there is one Java class that is referenced in > Stored Procedure; > 3) Try to run this SQL: > CREATE TRIGGER APP."test" > AFTER UPDATE OF description ON APP."ACTIONS" > REFERENCING NEW ROW AS updated_row > FOR EACH ROW > UPDATE APP."ACTIONS" SET description = 'testing' > WHERE id=updated_row.id > 4) Get a 42X94 error. > I'm hoping someone with enough knowledge can poke around in system tables and > figure out what's going on. -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (DERBY-6876) Can't create triggers on a table - error 42X94
[ https://issues.apache.org/jira/browse/DERBY-6876?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15216352#comment-15216352 ] Aleksei Kovura commented on DERBY-6876: --- Is there a way to retain values of generated column somehow when restoring data (with foreignViews or otherwise)? Using foreignViews turned out to be trickier than I expected due to the fact that tables have auto-generated IDs and refer to each other by them. So importing data will result in changed IDs for many objects (there are counter gaps in "corrupted" database due to deletes), which will wreak havoc in relationships between tables; it's a lot of work to sort out all that data... If you gentlemen had any bright ideas on repairing this DB, that would be most welcome :) > Can't create triggers on a table - error 42X94 > -- > > Key: DERBY-6876 > URL: https://issues.apache.org/jira/browse/DERBY-6876 > Project: Derby > Issue Type: Bug > Environment: Linux x86_64, Java 1.8.0_74 >Reporter: Aleksei Kovura > Attachments: trigger_bug.zip > > > I previously shared this on dev mailing list - > http://thread.gmane.org/gmane.comp.apache.db.derby.devel/115427. > I'm reworking triggers in my database and getting this message while trying > to create one: > > Error: StoredPreparedStatement '19ba803c-014e-b216-6d98-0650b418' does > not exist. > SQLState: 42X94 > ErrorCode: 3 > > This database is a couple years old, started out as 10.10.1.1, was upgraded > to stable releases as they appeared (10.11.1.1 -> 10.12.1.1). Some DDL was > changed in between upgrades, unfortunately I can't track which ones and when. > SYSCS_UTIL.SYSCS_INVALIDATE_STORED_STATEMENTS() doesn't help. > I tried my best to create a replicable test case through creating database on > earlier versions, upgrading and playing with DDL - no such luck, so I'm > attaching compressed database (with data deleted and tables compressed). Bug > replication procedure is as follows: > 1) Unpack attached file (it has a "trigger_bug" root directory); > 2) Boot the db_trigger database in embedded mode, include "trigger_bug" > directory in the classpath - there is one Java class that is referenced in > Stored Procedure; > 3) Try to run this SQL: > CREATE TRIGGER APP."test" > AFTER UPDATE OF description ON APP."ACTIONS" > REFERENCING NEW ROW AS updated_row > FOR EACH ROW > UPDATE APP."ACTIONS" SET description = 'testing' > WHERE id=updated_row.id > 4) Get a 42X94 error. > I'm hoping someone with enough knowledge can poke around in system tables and > figure out what's going on. -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (DERBY-6876) Can't create triggers on a table - error 42X94
[ https://issues.apache.org/jira/browse/DERBY-6876?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15197119#comment-15197119 ] Aleksei Kovura commented on DERBY-6876: --- Thanks, I figured that much out, but I was hoping for a proper fix - because triggers are cool. > Can't create triggers on a table - error 42X94 > -- > > Key: DERBY-6876 > URL: https://issues.apache.org/jira/browse/DERBY-6876 > Project: Derby > Issue Type: Bug > Environment: Linux x86_64, Java 1.8.0_74 >Reporter: Aleksei Kovura > Attachments: trigger_bug.zip > > > I previously shared this on dev mailing list - > http://thread.gmane.org/gmane.comp.apache.db.derby.devel/115427. > I'm reworking triggers in my database and getting this message while trying > to create one: > > Error: StoredPreparedStatement '19ba803c-014e-b216-6d98-0650b418' does > not exist. > SQLState: 42X94 > ErrorCode: 3 > > This database is a couple years old, started out as 10.10.1.1, was upgraded > to stable releases as they appeared (10.11.1.1 -> 10.12.1.1). Some DDL was > changed in between upgrades, unfortunately I can't track which ones and when. > SYSCS_UTIL.SYSCS_INVALIDATE_STORED_STATEMENTS() doesn't help. > I tried my best to create a replicable test case through creating database on > earlier versions, upgrading and playing with DDL - no such luck, so I'm > attaching compressed database (with data deleted and tables compressed). Bug > replication procedure is as follows: > 1) Unpack attached file (it has a "trigger_bug" root directory); > 2) Boot the db_trigger database in embedded mode, include "trigger_bug" > directory in the classpath - there is one Java class that is referenced in > Stored Procedure; > 3) Try to run this SQL: > CREATE TRIGGER APP."test" > AFTER UPDATE OF description ON APP."ACTIONS" > REFERENCING NEW ROW AS updated_row > FOR EACH ROW > UPDATE APP."ACTIONS" SET description = 'testing' > WHERE id=updated_row.id > 4) Get a 42X94 error. > I'm hoping someone with enough knowledge can poke around in system tables and > figure out what's going on. -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Created] (DERBY-6876) Can't create triggers on a table - error 42X94
Aleksei Kovura created DERBY-6876: - Summary: Can't create triggers on a table - error 42X94 Key: DERBY-6876 URL: https://issues.apache.org/jira/browse/DERBY-6876 Project: Derby Issue Type: Bug Environment: Linux x86_64, Java 1.8.0_74 Reporter: Aleksei Kovura Attachments: trigger_bug.zip I previously shared this on dev mailing list - http://thread.gmane.org/gmane.comp.apache.db.derby.devel/115427. I'm reworking triggers in my database and getting this message while trying to create one: Error: StoredPreparedStatement '19ba803c-014e-b216-6d98-0650b418' does not exist. SQLState: 42X94 ErrorCode: 3 This database is a couple years old, started out as 10.10.1.1, was upgraded to stable releases as they appeared (10.11.1.1 -> 10.12.1.1). Some DDL was changed in between upgrades, unfortunately I can't track which ones and when. SYSCS_UTIL.SYSCS_INVALIDATE_STORED_STATEMENTS() doesn't help. I tried my best to create a replicable test case through creating database on earlier versions, upgrading and playing with DDL - no such luck, so I'm attaching compressed database (with data deleted and tables compressed). Bug replication procedure is as follows: 1) Unpack attached file (it has a "trigger_bug" root directory); 2) Boot the db_trigger database in embedded mode, include "trigger_bug" directory in the classpath - there is one Java class that is referenced in Stored Procedure; 3) Try to run this SQL: CREATE TRIGGER APP."test" AFTER UPDATE OF description ON APP."ACTIONS" REFERENCING NEW ROW AS updated_row FOR EACH ROW UPDATE APP."ACTIONS" SET description = 'testing' WHERE id=updated_row.id 4) Get a 42X94 error. I'm hoping someone with enough knowledge can poke around in system tables and figure out what's going on. -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Updated] (DERBY-6876) Can't create triggers on a table - error 42X94
[ https://issues.apache.org/jira/browse/DERBY-6876?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Aleksei Kovura updated DERBY-6876: -- Attachment: trigger_bug.zip > Can't create triggers on a table - error 42X94 > -- > > Key: DERBY-6876 > URL: https://issues.apache.org/jira/browse/DERBY-6876 > Project: Derby > Issue Type: Bug > Environment: Linux x86_64, Java 1.8.0_74 >Reporter: Aleksei Kovura > Attachments: trigger_bug.zip > > > I previously shared this on dev mailing list - > http://thread.gmane.org/gmane.comp.apache.db.derby.devel/115427. > I'm reworking triggers in my database and getting this message while trying > to create one: > > Error: StoredPreparedStatement '19ba803c-014e-b216-6d98-0650b418' does > not exist. > SQLState: 42X94 > ErrorCode: 3 > > This database is a couple years old, started out as 10.10.1.1, was upgraded > to stable releases as they appeared (10.11.1.1 -> 10.12.1.1). Some DDL was > changed in between upgrades, unfortunately I can't track which ones and when. > SYSCS_UTIL.SYSCS_INVALIDATE_STORED_STATEMENTS() doesn't help. > I tried my best to create a replicable test case through creating database on > earlier versions, upgrading and playing with DDL - no such luck, so I'm > attaching compressed database (with data deleted and tables compressed). Bug > replication procedure is as follows: > 1) Unpack attached file (it has a "trigger_bug" root directory); > 2) Boot the db_trigger database in embedded mode, include "trigger_bug" > directory in the classpath - there is one Java class that is referenced in > Stored Procedure; > 3) Try to run this SQL: > CREATE TRIGGER APP."test" > AFTER UPDATE OF description ON APP."ACTIONS" > REFERENCING NEW ROW AS updated_row > FOR EACH ROW > UPDATE APP."ACTIONS" SET description = 'testing' > WHERE id=updated_row.id > 4) Get a 42X94 error. > I'm hoping someone with enough knowledge can poke around in system tables and > figure out what's going on. -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Comment Edited] (DERBY-6783) WHEN clause in CREATE TRIGGER for UPDATE is not working for the sql script below
[ https://issues.apache.org/jira/browse/DERBY-6783?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14632738#comment-14632738 ] Aleksei Kovura edited comment on DERBY-6783 at 7/23/15 7:00 PM: Thanks Abhinav and Bryan. I'll try to build Derby on the specified revision and test triggers with my real world use cases as soon as I can. Edit: It works for all my use cases! was (Author: alex k.): Thanks Abhinav and Bryan. I'll try to build Derby on the specified revision and test triggers with my real world cases as soon as I can. > WHEN clause in CREATE TRIGGER for UPDATE is not working for the sql script > below > > > Key: DERBY-6783 > URL: https://issues.apache.org/jira/browse/DERBY-6783 > Project: Derby > Issue Type: Bug > Components: SQL >Affects Versions: 10.11.1.1 >Reporter: Mamta A. Satoor >Assignee: Abhinav Gupta > Fix For: 10.12.0.0 > > Attachments: 6783.diff, 6783_allTestsPass.diff, 6783_moreTests.diff, > 6783_moreTests_bryan.diff, 6783_newTest.diff, 6783_newTest_bryan.diff, > 6783_newTests.diff, cleanedUpDiff.patch, diagnostics.diff, > error-stacktrace.out, sortFunction.diff, testTriggerWhenClause.diff, > workingPatch.patch > > > Following sql script was shared on > derby-user(http://mail-archives.apache.org/mod_mbox/db-derby-user/201412.mbox/%3c548aba6d.8000...@zoho.com%3e). > The UPDATE TRIGGER with the WHEN clause below does not fire as expected. > Same script works fine on DB2. > ij version 10.11 > ij> connect 'jdbc:derby:MyDbTest;create=true'; > ij> CREATE TABLE t1 (id INTEGER, done_date DATE, status CHAR(1)); > 0 rows inserted/updated/deleted > ij> CREATE TRIGGER tr1 AFTER UPDATE OF status ON t1 REFERENCING NEW AS > newrow FOR EACH ROW WHEN (newrow.status='d') UPDATE t1 SET > done_date=current_date WHERE id=newrow.id; > 0 rows inserted/updated/deleted > ij> insert into t1 values (1, null, 'a'); > 1 row inserted/updated/deleted > ij> SELECT * FROM t1; > ID |DONE_DATE |STA& > --- > 1 |NULL |a > > 1 row selected > ij> UPDATE t1 SET status='d'; > 1 row inserted/updated/deleted > ij> SELECT * FROM t1; > ID |DONE_DATE |STA& > --- > 1 |NULL |d > > 1 row selected > ij> exit; -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (DERBY-6783) WHEN clause in CREATE TRIGGER for UPDATE is not working for the sql script below
[ https://issues.apache.org/jira/browse/DERBY-6783?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14632738#comment-14632738 ] Aleksei Kovura commented on DERBY-6783: --- Thanks Abhinav and Bryan. I'll try to build Derby on the specified revision and test triggers with my real world cases as soon as I can. > WHEN clause in CREATE TRIGGER for UPDATE is not working for the sql script > below > > > Key: DERBY-6783 > URL: https://issues.apache.org/jira/browse/DERBY-6783 > Project: Derby > Issue Type: Bug > Components: SQL >Affects Versions: 10.11.1.1 >Reporter: Mamta A. Satoor >Assignee: Abhinav Gupta > Fix For: 10.12.0.0 > > Attachments: 6783.diff, 6783_allTestsPass.diff, 6783_moreTests.diff, > 6783_moreTests_bryan.diff, 6783_newTest.diff, 6783_newTest_bryan.diff, > 6783_newTests.diff, cleanedUpDiff.patch, diagnostics.diff, > error-stacktrace.out, sortFunction.diff, testTriggerWhenClause.diff, > workingPatch.patch > > > Following sql script was shared on > derby-user(http://mail-archives.apache.org/mod_mbox/db-derby-user/201412.mbox/%3c548aba6d.8000...@zoho.com%3e). > The UPDATE TRIGGER with the WHEN clause below does not fire as expected. > Same script works fine on DB2. > ij version 10.11 > ij> connect 'jdbc:derby:MyDbTest;create=true'; > ij> CREATE TABLE t1 (id INTEGER, done_date DATE, status CHAR(1)); > 0 rows inserted/updated/deleted > ij> CREATE TRIGGER tr1 AFTER UPDATE OF status ON t1 REFERENCING NEW AS > newrow FOR EACH ROW WHEN (newrow.status='d') UPDATE t1 SET > done_date=current_date WHERE id=newrow.id; > 0 rows inserted/updated/deleted > ij> insert into t1 values (1, null, 'a'); > 1 row inserted/updated/deleted > ij> SELECT * FROM t1; > ID |DONE_DATE |STA& > --- > 1 |NULL |a > > 1 row selected > ij> UPDATE t1 SET status='d'; > 1 row inserted/updated/deleted > ij> SELECT * FROM t1; > ID |DONE_DATE |STA& > --- > 1 |NULL |d > > 1 row selected > ij> exit; -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (DERBY-4611) Add new datatypes: TIME WITH TIMEZONE and TIMESTAMP WITH TIMEZONE
[ https://issues.apache.org/jira/browse/DERBY-4611?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14141899#comment-14141899 ] Aleksei Kovura commented on DERBY-4611: --- Are there plans for this to be implemented? > Add new datatypes: TIME WITH TIMEZONE and TIMESTAMP WITH TIMEZONE > - > > Key: DERBY-4611 > URL: https://issues.apache.org/jira/browse/DERBY-4611 > Project: Derby > Issue Type: Improvement > Components: SQL >Reporter: Rick Hillegas > Labels: derby_triage10_10 > > The SQL Standard defines two timezone-stamped datatypes: TIME WITH TIMEZONE > and TIMESTAMP WITH TIMEZONE. Relevant sections in part 2 of the Standard > include 4.6 (Datetimes and intervals), 6.1 (), 6.12 ( specification>), and 8.2 (). > I think that we could implement these two datatypes today. Note that there is > a general cleanup of the Java datetime classes underway in JSR 310 ( > https://jsr-310.dev.java net/ ), which is supposed to be delivered in Java 7. > It's possible that the next rev of JDBC will provide better support for these > datatypes. However, there is currently no JSR covering the next JDBC > increment. > For the moment, the existing JDBC TIME and TIMESTAMP types would have to be > overloaded to cover the new datatypes. Disambiguating the WITH TIMEZONE > variants might have to be accomplished by parsing the type names returned by > ResultSetMetaData.getColumnTypeName() and > DatabaseMetaData.getColumns().TYPE_NAME. > Note also that DRDA does not provide WITH TIMEZONE variants of its TIME and > TIMEZONE types. Some creativity would be required here too. > Users are welcome to vote for this issue in order to boost its chance of > being addressed. -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Issue Comment Deleted] (DERBY-4611) Add new datatypes: TIME WITH TIMEZONE and TIMESTAMP WITH TIMEZONE
[
https://issues.apache.org/jira/browse/DERBY-4611?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Aleksei Kovura updated DERBY-4611:
--
Comment: was deleted
(was: I need to have a default value for a column to be current_timestamp *in
UTC* (regardless of local system TZ):
{code:SQL}
ALTER TABLE "APP"."ACTIONS" ADD COLUMN last_modified TIMESTAMP WITH TIMEZONE
("UTC") NOT NULL WITH DEFAULT current_timestamp;
{code}
Will this ticket provide this functionality, or do I need to create a separate
ticket?)
> Add new datatypes: TIME WITH TIMEZONE and TIMESTAMP WITH TIMEZONE
> -
>
> Key: DERBY-4611
> URL: https://issues.apache.org/jira/browse/DERBY-4611
> Project: Derby
> Issue Type: Improvement
> Components: SQL
>Reporter: Rick Hillegas
> Labels: derby_triage10_10
>
> The SQL Standard defines two timezone-stamped datatypes: TIME WITH TIMEZONE
> and TIMESTAMP WITH TIMEZONE. Relevant sections in part 2 of the Standard
> include 4.6 (Datetimes and intervals), 6.1 (), 6.12 ( specification>), and 8.2 ().
> I think that we could implement these two datatypes today. Note that there is
> a general cleanup of the Java datetime classes underway in JSR 310 (
> https://jsr-310.dev.java net/ ), which is supposed to be delivered in Java 7.
> It's possible that the next rev of JDBC will provide better support for these
> datatypes. However, there is currently no JSR covering the next JDBC
> increment.
> For the moment, the existing JDBC TIME and TIMESTAMP types would have to be
> overloaded to cover the new datatypes. Disambiguating the WITH TIMEZONE
> variants might have to be accomplished by parsing the type names returned by
> ResultSetMetaData.getColumnTypeName() and
> DatabaseMetaData.getColumns().TYPE_NAME.
> Note also that DRDA does not provide WITH TIMEZONE variants of its TIME and
> TIMEZONE types. Some creativity would be required here too.
> Users are welcome to vote for this issue in order to boost its chance of
> being addressed.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Comment Edited] (DERBY-4611) Add new datatypes: TIME WITH TIMEZONE and TIMESTAMP WITH TIMEZONE
[
https://issues.apache.org/jira/browse/DERBY-4611?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14141891#comment-14141891
]
Aleksei Kovura edited comment on DERBY-4611 at 9/20/14 9:23 AM:
I need to have a default value for a column to be current_timestamp *in UTC*
(regardless of local system TZ):
{code:SQL}
ALTER TABLE "APP"."ACTIONS" ADD COLUMN last_modified TIMESTAMP WITH TIMEZONE
("UTC") NOT NULL WITH DEFAULT current_timestamp;
{code}
Will this ticket provide this functionality, or do I need to create a separate
ticket?
was (Author: alex k.):
I need to have a default value for a column to be current_timestamp *in UTC*:
{code:SQL}
ALTER TABLE "APP"."ACTIONS" ADD COLUMN last_modified TIMESTAMP WITH TIMEZONE
("UTC") NOT NULL WITH DEFAULT current_timestamp;
{code}
Will this ticket provide this functionality, or do I need to create a separate
ticket?
> Add new datatypes: TIME WITH TIMEZONE and TIMESTAMP WITH TIMEZONE
> -
>
> Key: DERBY-4611
> URL: https://issues.apache.org/jira/browse/DERBY-4611
> Project: Derby
> Issue Type: Improvement
> Components: SQL
>Reporter: Rick Hillegas
> Labels: derby_triage10_10
>
> The SQL Standard defines two timezone-stamped datatypes: TIME WITH TIMEZONE
> and TIMESTAMP WITH TIMEZONE. Relevant sections in part 2 of the Standard
> include 4.6 (Datetimes and intervals), 6.1 (), 6.12 ( specification>), and 8.2 ().
> I think that we could implement these two datatypes today. Note that there is
> a general cleanup of the Java datetime classes underway in JSR 310 (
> https://jsr-310.dev.java net/ ), which is supposed to be delivered in Java 7.
> It's possible that the next rev of JDBC will provide better support for these
> datatypes. However, there is currently no JSR covering the next JDBC
> increment.
> For the moment, the existing JDBC TIME and TIMESTAMP types would have to be
> overloaded to cover the new datatypes. Disambiguating the WITH TIMEZONE
> variants might have to be accomplished by parsing the type names returned by
> ResultSetMetaData.getColumnTypeName() and
> DatabaseMetaData.getColumns().TYPE_NAME.
> Note also that DRDA does not provide WITH TIMEZONE variants of its TIME and
> TIMEZONE types. Some creativity would be required here too.
> Users are welcome to vote for this issue in order to boost its chance of
> being addressed.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Comment Edited] (DERBY-4611) Add new datatypes: TIME WITH TIMEZONE and TIMESTAMP WITH TIMEZONE
[
https://issues.apache.org/jira/browse/DERBY-4611?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14141891#comment-14141891
]
Aleksei Kovura edited comment on DERBY-4611 at 9/20/14 9:17 AM:
I need to have a default value for a column to be current_timestamp *in UTC*:
{code:SQL}
ALTER TABLE "APP"."ACTIONS" ADD COLUMN last_modified TIMESTAMP WITH TIMEZONE
("UTC") NOT NULL WITH DEFAULT current_timestamp;
{code}
Will this ticket provide this functionality, or do I need to create a separate
ticket?
was (Author: alex k.):
I need to have a default value for a column to be current_timestamp *in UTC*.
Will this ticket provide this functionality?
> Add new datatypes: TIME WITH TIMEZONE and TIMESTAMP WITH TIMEZONE
> -
>
> Key: DERBY-4611
> URL: https://issues.apache.org/jira/browse/DERBY-4611
> Project: Derby
> Issue Type: Improvement
> Components: SQL
>Reporter: Rick Hillegas
> Labels: derby_triage10_10
>
> The SQL Standard defines two timezone-stamped datatypes: TIME WITH TIMEZONE
> and TIMESTAMP WITH TIMEZONE. Relevant sections in part 2 of the Standard
> include 4.6 (Datetimes and intervals), 6.1 (), 6.12 ( specification>), and 8.2 ().
> I think that we could implement these two datatypes today. Note that there is
> a general cleanup of the Java datetime classes underway in JSR 310 (
> https://jsr-310.dev.java net/ ), which is supposed to be delivered in Java 7.
> It's possible that the next rev of JDBC will provide better support for these
> datatypes. However, there is currently no JSR covering the next JDBC
> increment.
> For the moment, the existing JDBC TIME and TIMESTAMP types would have to be
> overloaded to cover the new datatypes. Disambiguating the WITH TIMEZONE
> variants might have to be accomplished by parsing the type names returned by
> ResultSetMetaData.getColumnTypeName() and
> DatabaseMetaData.getColumns().TYPE_NAME.
> Note also that DRDA does not provide WITH TIMEZONE variants of its TIME and
> TIMEZONE types. Some creativity would be required here too.
> Users are welcome to vote for this issue in order to boost its chance of
> being addressed.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (DERBY-4611) Add new datatypes: TIME WITH TIMEZONE and TIMESTAMP WITH TIMEZONE
[ https://issues.apache.org/jira/browse/DERBY-4611?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14141891#comment-14141891 ] Aleksei Kovura commented on DERBY-4611: --- I need to have a default value for a column to be current_timestamp *in UTC*. Will this ticket provide this functionality? > Add new datatypes: TIME WITH TIMEZONE and TIMESTAMP WITH TIMEZONE > - > > Key: DERBY-4611 > URL: https://issues.apache.org/jira/browse/DERBY-4611 > Project: Derby > Issue Type: Improvement > Components: SQL >Reporter: Rick Hillegas > Labels: derby_triage10_10 > > The SQL Standard defines two timezone-stamped datatypes: TIME WITH TIMEZONE > and TIMESTAMP WITH TIMEZONE. Relevant sections in part 2 of the Standard > include 4.6 (Datetimes and intervals), 6.1 (), 6.12 ( specification>), and 8.2 (). > I think that we could implement these two datatypes today. Note that there is > a general cleanup of the Java datetime classes underway in JSR 310 ( > https://jsr-310.dev.java net/ ), which is supposed to be delivered in Java 7. > It's possible that the next rev of JDBC will provide better support for these > datatypes. However, there is currently no JSR covering the next JDBC > increment. > For the moment, the existing JDBC TIME and TIMESTAMP types would have to be > overloaded to cover the new datatypes. Disambiguating the WITH TIMEZONE > variants might have to be accomplished by parsing the type names returned by > ResultSetMetaData.getColumnTypeName() and > DatabaseMetaData.getColumns().TYPE_NAME. > Note also that DRDA does not provide WITH TIMEZONE variants of its TIME and > TIMEZONE types. Some creativity would be required here too. > Users are welcome to vote for this issue in order to boost its chance of > being addressed. -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Updated] (DERBY-6723) Allow derived table as a source in MERGE statement
[
https://issues.apache.org/jira/browse/DERBY-6723?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Aleksei Kovura updated DERBY-6723:
--
Priority: Major (was: Minor)
> Allow derived table as a source in MERGE statement
> --
>
> Key: DERBY-6723
> URL: https://issues.apache.org/jira/browse/DERBY-6723
> Project: Derby
> Issue Type: Improvement
> Components: SQL
>Affects Versions: 10.11.1.1
>Reporter: Aleksei Kovura
>
> I'm trying to utilize MERGE to do an upsert operation like this:
> MERGE INTO "APP"."ST_KEY_VAL" trg USING (SELECT * FROM (VALUES ('key1',
> 'val1')) as t1 (st_key, st_val)) as src
> ON trg.st_key = src.st_key
> WHEN MATCHED THEN UPDATE SET st_val = src.st_val
> WHEN NOT MATCHED THEN INSERT VALUES (src.st_key, src.st_val)
> ;
> Derby returns an error:
> Error: The source table of a MERGE statement must be a base table or table
> function.
> SQLState: 42XAL
> ErrorCode: 3
> MERGE is used this way on other databases, unfortunately I can't find whether
> it is standard compliant or not. If this gets implemented, I would use it in
> PreparedStatement, replacing values with "?"-s.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Updated] (DERBY-6723) Allow derived table as a source in MERGE statement
[
https://issues.apache.org/jira/browse/DERBY-6723?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Aleksei Kovura updated DERBY-6723:
--
Description:
I'm trying to utilize MERGE to do an upsert operation like this:
MERGE INTO "APP"."ST_KEY_VAL" trg USING (SELECT * FROM (VALUES ('key1',
'val1')) as t1 (st_key, st_val)) as src
ON st.st_key = src.st_key
WHEN MATCHED THEN UPDATE SET st_val = src.st_val
WHEN NOT MATCHED THEN INSERT VALUES (src.st_key, src.st_val)
;
Derby returns an error:
Error: The source table of a MERGE statement must be a base table or table
function.
SQLState: 42XAL
ErrorCode: 3
MERGE is used this way on other databases, unfortunately I can't find whether
it is standard compliant or not. If this gets implemented, I would use it in
PreparedStatement, replacing values with "?"-s.
was:
I'm trying to utilize MERGE to do an upsert operation like this:
MERGE INTO "APP"."ST_KEY_VAL" trg USING (SELECT * FROM (VALUES ('key1',
'val1')) as t1 (st_key, st_val)) as src
ON st.state_key = src.state_key
WHEN MATCHED THEN UPDATE SET st_val = src.st_val
WHEN NOT MATCHED THEN INSERT VALUES (src.st_key, src.st_val)
;
Derby returns an error:
Error: The source table of a MERGE statement must be a base table or table
function.
SQLState: 42XAL
ErrorCode: 3
MERGE is used this way on other databases, unfortunately I can't find whether
it is standard compliant or not. If this gets implemented, I would use it in
PreparedStatement, replacing values with "?"-s.
> Allow derived table as a source in MERGE statement
> --
>
> Key: DERBY-6723
> URL: https://issues.apache.org/jira/browse/DERBY-6723
> Project: Derby
> Issue Type: Improvement
> Components: SQL
>Affects Versions: 10.11.1.1
>Reporter: Aleksei Kovura
>Priority: Minor
>
> I'm trying to utilize MERGE to do an upsert operation like this:
> MERGE INTO "APP"."ST_KEY_VAL" trg USING (SELECT * FROM (VALUES ('key1',
> 'val1')) as t1 (st_key, st_val)) as src
> ON st.st_key = src.st_key
> WHEN MATCHED THEN UPDATE SET st_val = src.st_val
> WHEN NOT MATCHED THEN INSERT VALUES (src.st_key, src.st_val)
> ;
> Derby returns an error:
> Error: The source table of a MERGE statement must be a base table or table
> function.
> SQLState: 42XAL
> ErrorCode: 3
> MERGE is used this way on other databases, unfortunately I can't find whether
> it is standard compliant or not. If this gets implemented, I would use it in
> PreparedStatement, replacing values with "?"-s.
--
This message was sent by Atlassian JIRA
(v6.2#6252)
[jira] [Updated] (DERBY-6723) Allow derived table as a source in MERGE statement
[
https://issues.apache.org/jira/browse/DERBY-6723?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Aleksei Kovura updated DERBY-6723:
--
Description:
I'm trying to utilize MERGE to do an upsert operation like this:
MERGE INTO "APP"."ST_KEY_VAL" trg USING (SELECT * FROM (VALUES ('key1',
'val1')) as t1 (st_key, st_val)) as src
ON trg.st_key = src.st_key
WHEN MATCHED THEN UPDATE SET st_val = src.st_val
WHEN NOT MATCHED THEN INSERT VALUES (src.st_key, src.st_val)
;
Derby returns an error:
Error: The source table of a MERGE statement must be a base table or table
function.
SQLState: 42XAL
ErrorCode: 3
MERGE is used this way on other databases, unfortunately I can't find whether
it is standard compliant or not. If this gets implemented, I would use it in
PreparedStatement, replacing values with "?"-s.
was:
I'm trying to utilize MERGE to do an upsert operation like this:
MERGE INTO "APP"."ST_KEY_VAL" trg USING (SELECT * FROM (VALUES ('key1',
'val1')) as t1 (st_key, st_val)) as src
ON st.st_key = src.st_key
WHEN MATCHED THEN UPDATE SET st_val = src.st_val
WHEN NOT MATCHED THEN INSERT VALUES (src.st_key, src.st_val)
;
Derby returns an error:
Error: The source table of a MERGE statement must be a base table or table
function.
SQLState: 42XAL
ErrorCode: 3
MERGE is used this way on other databases, unfortunately I can't find whether
it is standard compliant or not. If this gets implemented, I would use it in
PreparedStatement, replacing values with "?"-s.
> Allow derived table as a source in MERGE statement
> --
>
> Key: DERBY-6723
> URL: https://issues.apache.org/jira/browse/DERBY-6723
> Project: Derby
> Issue Type: Improvement
> Components: SQL
>Affects Versions: 10.11.1.1
>Reporter: Aleksei Kovura
>Priority: Minor
>
> I'm trying to utilize MERGE to do an upsert operation like this:
> MERGE INTO "APP"."ST_KEY_VAL" trg USING (SELECT * FROM (VALUES ('key1',
> 'val1')) as t1 (st_key, st_val)) as src
> ON trg.st_key = src.st_key
> WHEN MATCHED THEN UPDATE SET st_val = src.st_val
> WHEN NOT MATCHED THEN INSERT VALUES (src.st_key, src.st_val)
> ;
> Derby returns an error:
> Error: The source table of a MERGE statement must be a base table or table
> function.
> SQLState: 42XAL
> ErrorCode: 3
> MERGE is used this way on other databases, unfortunately I can't find whether
> it is standard compliant or not. If this gets implemented, I would use it in
> PreparedStatement, replacing values with "?"-s.
--
This message was sent by Atlassian JIRA
(v6.2#6252)
