015. JAVADB-DERBY: CREATE FUNCTION

Trust me, it is complicated. But, it's cool.

• 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?