- Previously, create .jar file. Example:
package function;
public class parameters {
private static String stcPrm1="";
public static String setPrm1(String dnString){
stcPrm1=dnString;
return stcPrm1;
}
public static String getPrm1(){return stcPrm1;}
}
Compile to .jar file (example: DDA_derby.jar).
- Register that .jar to Derby:
Using Derby GUI, run query:
CALL sqlj.install_jar ('C:/myfolder/DDA_derby.jar', 'APP.DDA_derby', 0);
- Set classpath:
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(
'derby.database.classpath', 'APP.DDA_derby');
- Create function:
CREATE FUNCTION APP.SETPARAM1(PARAM1 VARCHAR(200))
RETURNS VARCHAR(200)
PARAMETER STYLE JAVA
NO SQL LANGUAGE JAVA
EXTERNAL NAME 'function.parameters.setPrm1';
CREATE FUNCTION APP.GETPARAM1()
RETURNS VARCHAR(200)
PARAMETER STYLE JAVA
NO SQL LANGUAGE JAVA
EXTERNAL NAME 'function.parameters.getPrm1';
Deleting .jar:
CALL sqlj.remove_jar ('APP.DDA_derby', 0);
Replacing .jar:
CALL sqlj.replace_jar('C:/myfolder/DDA_derby.jar', 'APP.DDA_derby');
Test:
SELECT * FROM MYSCHEMA.MYTABLE WHERE MYFIELD=APP.SETPARAM1('ABCDE');
Parameterize View:
CREATE VIEW VW AS SELECT * FROM MYSCHEMA.MYTABLE WHERE MYFIELD=APP.GETPARAM1();
SELECT VW.*, (VALUES APP.SETPARAM1('ABCDE')) FROM VW;
Do you want to try?
