SQL/MX Guide to Stored Procedures in Java (G06.24+, H06.03+)

Registering SPJs in NonStop SQL/MX

HP NonStop SQL/MX Guide to Stored Procedures in Java—523727-004

4-14

Changes to the External Path

The external path is the OSS directory or JAR file path of the Java class file that

contains the SPJ method. If you change the external path of the underlying SPJ

method, you must drop the associated SPJ and re-create it.

For example, suppose that you move the class, Sales.class, which contains the

SPJ method, numMonthlyOrders(), from the /usr/mydir/myclasses directory

into a JAR file, myJar.jar.

The SPJ was originally registered in system metadata as:

CREATE PROCEDURE samdbcat.sales.monthlyorders(IN INT,

OUT number INT)

EXTERNAL NAME 'pkg.subpkg.Sales.numMonthlyOrders'

EXTERNAL PATH '/usr/mydir/myclasses'

LANGUAGE JAVA

PARAMETER STYLE JAVA

READS SQL DATA;

To drop the SPJ, issue this DROP PROCEDURE statement:

DROP PROCEDURE samdbcat.sales.monthlyorders;

To re-create the SPJ, issue this CREATE PROCEDURE statement:

CREATE PROCEDURE samdbcat.sales.monthlyorders(IN INT,

OUT number INT)

EXTERNAL NAME 'pkg.subpkg.Sales.numMonthlyOrders'

EXTERNAL PATH '/usr/myapps/myJar.jar'

LANGUAGE JAVA

PARAMETER STYLE JAVA

READS SQL DATA;

If a statically compiled program calls this SPJ, you should explicitly recompile the

module of that application. If you do not, NonStop SQL/MX tries to automatically

recompile the SQL plan of the CALL statement at run time.

You can now successfully execute the relocated SPJ method by issuing the existing

CALL statements.