HP Nonstop SQLMX (SPJ) Stored Procedure in Javas Getting Started

nics

Stored Procedure in Java (SPJ)

HP Nonstop SQLMX, we can code Java program and regist it as a DB Stored Procedure, and then calling it. SPJ is a stored procedure whose body is a static Java method. The body is known as an SPJ method.

1. install JDK JDBC ....... onto oss evn;
2. create a class, the fuction must be "static", becuase we can access the method in the class without instantiate.

• publicclassHelloWorld{
  
•     publicstaticvoid testFunc(int a,int b,String[] tst,int[] bigger){
  
• 
  
•         if(a > b)
  
•            bigger[0]= a;
  
•         else
  
•            bigger[0]= b;
  
• 
  
•         tst[0]="The bigger one is: ";
  
•     }
  
• }
  

3. FTP the .java file onto oss;
4. compile it with javac command;
5. Regist, To register SPJs into MX,
    You must be either the schema owner or the super ID. Run below script in mxci to reigist the stored procedure.

• >>?SECTION "CREATE ENDER SPJs"
  
• >>CREATE PROCEDURE sos.ecui.testFunc(IN param_a INT, IN param_b INT, OUT tst VARCHAR(20), OUT bigger INT)
  
• +>EXTERNAL NAME 'HelloWorld.testFunc'
  
• +>EXTERNAL PATH '/home/ecui/java/test'
  
• +>LANGUAGE JAVA
  
• +>PARAMETER STYLE JAVA
  
• +>MODIFIES SQL DATA;
  
• 
  
• --- SQL operation complete.
  
• 
  
• >> SHOWDDL SOS.ECUI.TESTFUNC;
  
• 
  
• CREATE PROCEDURE SOS.ECUI.TESTFUNC
  
•   (
  
•     IN PARAM_A INTEGER
  
•   , IN PARAM_B INTEGER
  
•   , OUT TST VARCHAR(20) CHARACTER SET ISO88591
  
•   , OUT BIGGER INTEGER
  
•   )
  
•   EXTERNAL NAME 'HelloWorld.testFunc (int,int,java.lang.String[],int[])'
  
•   EXTERNAL PATH '/home/ecui/java/test'
  
•   LOCATION \P3DEV.$DATA01.ZSDZSGJ5.MKN46G00
  
•   LANGUAGE JAVA
  
•   PARAMETER STYLE JAVA
  
•   MODIFIES SQL DATA
  
•   DYNAMIC RESULT SETS 0
  
•   NOT DETERMINISTIC
  
•   ISOLATE
  
•   ;
  
• 
  
• --- SQL operation complete.
  

6. Grant privileges for invoking the SPJs to other users.
7. Call (or invoke) the SPJs in SQL/MX.

• >>CALL sos.ecui.testFunc(2,4,?,?);
  
• 
  
• TST                   BIGGER
  
• --------------------  -----------
  
• 
  
• The bigger one is:              4
  
• 
  
• --- SQL operation complete.
  
• >>CALL sos.ecui.testFunc(20,4,?,?);
  
• 
  
• TST                   BIGGER
  
• --------------------  -----------
  
• 
  
• The bigger one is:             20
  
• 
  
• --- SQL operation complete.
  

8. delete this SPJ,

• >>DROP procedure SOS.ECUI.TESTFUNC;
  
• 
  
• --- SQL operation complete.
  

　　


来自为知笔记(Wiz)