dcl.out

derby database source code.good for you.

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, '[e-mail]');
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, '[e-mail]');
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, '[e-mail]');
0 rows inserted/updated/deleted
ij> CALL EMC.ADDCONTACT(2, '[e-mail]');
0 rows inserted/updated/deleted
ij> SELECT id, e_mail from EMC.CONTACTS;
ID         |E_MAIL                        
------------------------------------------
1          |[e-mail]         
2          |[e-mail]          
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          |[e-mail]         |NULL  
2          |[e-mail]          |NULL  
ij> -- well written application, INSERT used explicit column names
-- ok defaults to NULL
CALL EMC.ADDCONTACT(3, '[e-mail]');
0 rows inserted/updated/deleted
ij> SELECT id, e_mail, ok from EMC.CONTACTS;
ID         |E_MAIL                        |OK    
-------------------------------------------------
1          |[e-mail]         |NULL  
2          |[e-mail]          |NULL  
3          |[e-mail]                  |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, '[e-mail]');
0 rows inserted/updated/deleted
ij> SELECT id, e_mail, ok from EMC.CONTACTS;
ID         |E_MAIL                        |OK    
-------------------------------------------------
1          |[e-mail]         |NULL  
2          |[e-mail]          |NULL  
3          |[e-mail]                  |NULL  
99         |[e-mail]             |0     
ij> rollback;
ij> AUTOCOMMIT ON;
ij> SELECT id, e_mail, ok from EMC.CONTACTS;
ID         |E_MAIL                        |OK    
-------------------------------------------------
1          |[e-mail]         |NULL  
2          |[e-mail]          |NULL  
3          |[e-mail]                  |NULL  
ij> CALL EMC.ADDCONTACT(99, '[e-mail]');
0 rows inserted/updated/deleted
ij> SELECT id, e_mail, ok from EMC.CONTACTS;
ID         |E_MAIL                        |OK    
-------------------------------------------------
1          |[e-mail]         |NULL  
2          |[e-mail]          |NULL  
3          |[e-mail]                  |NULL  
99         |[e-mail]            |NULL  
ij> DELETE FROM EMC.CONTACTS WHERE ID = 99;