HP Nonstop SQLMX (SPJ) Stored Procedure in Javas Getting Started
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= a;
[*] else
[*] bigger= b;
[*]
[*] tst="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)
页:
[1]