📄 dcl.out
字号:
ij> -- test database class loading.
maximumdisplaywidth 300;
ij> create schema emc;
0 rows inserted/updated/deleted
ij> set schema emc;
0 rows inserted/updated/deleted
ij> create table contacts (id int primary key, e_mail varchar(30));
0 rows inserted/updated/deleted
ij> create procedure EMC.ADDCONTACT(id INT, e_mail VARCHAR(30))
MODIFIES SQL DATA
external name 'org.apache.derbyTesting.databaseclassloader.emc.addContact'
language java parameter style java;
0 rows inserted/updated/deleted
ij> create function EMC.GETARTICLE(path VARCHAR(40)) RETURNS VARCHAR(256)
NO SQL
external name 'org.apache.derbyTesting.databaseclassloader.emc.getArticle'
language java parameter style java;
0 rows inserted/updated/deleted
ij> -- fails because no class in classpath,
CALL EMC.ADDCONTACT(1, 'bill@somecompany.com');
ERROR 42X51: The class 'org.apache.derbyTesting.databaseclassloader.emc' does not exist or is inaccessible. This can happen if the class is not public.
ERROR XJ001: Java exception: 'org.apache.derbyTesting.databaseclassloader.emc: java.lang.ClassNotFoundException'.
ij> -- install the jar, copied there by the magic of supportfiles
-- in the test harness (dcl_app.properties). The source for
-- the class is contained within the jar for reference.
CALL SQLJ.INSTALL_JAR('file:extin/dcl_emc1.jar', 'EMC.MAIL_APP', 0);
0 rows inserted/updated/deleted
ij> -- fails because no class not in classpath, jar file not in database classpath.
CALL EMC.ADDCONTACT(1, 'bill@somecompany.com');
ERROR 42X51: The class 'org.apache.derbyTesting.databaseclassloader.emc' does not exist or is inaccessible. This can happen if the class is not public.
ERROR XJ001: Java exception: 'org.apache.derbyTesting.databaseclassloader.emc: java.lang.ClassNotFoundException'.
ij> -- now add this into the database class path
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.classpath', 'EMC.MAIL_APP');
0 rows inserted/updated/deleted
ij> -- all should work now
CALL EMC.ADDCONTACT(1, 'bill@ruletheworld.com');
0 rows inserted/updated/deleted
ij> CALL EMC.ADDCONTACT(2, 'penguin@antartic.com');
0 rows inserted/updated/deleted
ij> SELECT id, e_mail from EMC.CONTACTS;
ID |E_MAIL
------------------------------------------
1 |bill@ruletheworld.com
2 |penguin@antartic.com
ij> -- Test resource loading from the jar file
-- Simple path should be prepended with the package name
-- of the class executing the code to find
-- /org/apache/derbyTesting/databaseclassloader/graduation.txt
VALUES EMC.GETARTICLE('graduate.txt');
1
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
The Apache Foundation has released the first version of the open-source Derby database, which also gained support from Sun Microsystems.
ij> -- now an absolute path
VALUES EMC.GETARTICLE('/article/release.txt');
1
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
The Apache Derby development community is pleased to announce its first release after graduating from the Apache Incubator, Apache Derby 10.1.1.0.
ij> -- no such resources
VALUES EMC.GETARTICLE('/article/fred.txt');
1
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
NULL
ij> VALUES EMC.GETARTICLE('barney.txt');
1
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
NULL
ij> -- try to read the class file should be disallowed
-- by returning null
VALUES EMC.GETARTICLE('emc.class');
1
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
NULL
ij> VALUES EMC.GETARTICLE('/org/apache/derbyTesting/databaseclassloader/emc.class');
1
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
NULL
ij> -- now the application needs to track if e-mails are valid
ALTER TABLE EMC.CONTACTS ADD COLUMN OK SMALLINT;
0 rows inserted/updated/deleted
ij> SELECT id, e_mail, ok from EMC.CONTACTS;
ID |E_MAIL |OK
-------------------------------------------------
1 |bill@ruletheworld.com |NULL
2 |penguin@antartic.com |NULL
ij> -- well written application, INSERT used explicit column names
-- ok defaults to NULL
CALL EMC.ADDCONTACT(3, 'big@blue.com');
0 rows inserted/updated/deleted
ij> SELECT id, e_mail, ok from EMC.CONTACTS;
ID |E_MAIL |OK
-------------------------------------------------
1 |bill@ruletheworld.com |NULL
2 |penguin@antartic.com |NULL
3 |big@blue.com |NULL
ij> -- check the roll back of class loading.
-- install a new jar in a transaction, see
-- that the new class is used and then rollback
-- the old class should be used after the rollback.
AUTOCOMMIT OFF;
ij> CALL SQLJ.REPLACE_JAR('file:extin/dcl_emc2.jar', 'EMC.MAIL_APP');
0 rows inserted/updated/deleted
ij> CALL EMC.ADDCONTACT(99, 'wormspam@soil.com');
0 rows inserted/updated/deleted
ij> SELECT id, e_mail, ok from EMC.CONTACTS;
ID |E_MAIL |OK
-------------------------------------------------
1 |bill@ruletheworld.com |NULL
2 |penguin@antartic.com |NULL
3 |big@blue.com |NULL
99 |wormspam@soil.com |0
ij> rollback;
ij> AUTOCOMMIT ON;
ij> SELECT id, e_mail, ok from EMC.CONTACTS;
ID |E_MAIL |OK
-------------------------------------------------
1 |bill@ruletheworld.com |NULL
2 |penguin@antartic.com |NULL
3 |big@blue.com |NULL
ij> CALL EMC.ADDCONTACT(99, 'wormspam2@soil.com');
0 rows inserted/updated/deleted
ij> SELECT id, e_mail, ok from EMC.CONTACTS;
ID |E_MAIL |OK
-------------------------------------------------
1 |bill@ruletheworld.com |NULL
2 |penguin@antartic.com |NULL
3 |big@blue.com |NULL
99 |wormspam2@soil.com |NULL
ij> DELETE FROM EMC.CONTACTS WHERE ID = 99;
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -