How Stored Procedure work in SQL/MX ?
In SQL/MX stored procedures are called as SPJ.Stands for Stored Procedures Java.The reason is SQL/MX supports stored procedures written in JAVA programming
What is SPJ ?
A SPJ is a JAVA method contained a java class,registered in SQL/MX system metadata tables, and invoked by Nonstop SQL/MX when an application issues a CALL
Benifits of SPJ ?
1.Common packaging technique
DIfferent method can invoke same SPJ contains a encapsulated specific logic.It directed to maintain consistent database operations and
avoid duplicating the same logic in the application.
Can conceal sensitive logic inside SPJ without instead of exposing that to a client application.And then can grant previledges to specific users.
When a business logic changes,it is only need to altered in SPJ.No need to change all the calling programmes for a scenario where
several applications use same business logic.
Due to SPJ is a java class.It is portable.
How to play with SPJ ?
1. Write a JAVA method and compile the JAVA class.
2. Register the JAVA method as an SPJ in NonStop SQL/MX
3. Invoke the SPJ in NonStop SQL/MX
Write a JAVA method and compile the JAVA class
The body of a stored procedure consists of a public, static Java method that returns void. These methods, called SPJ methods, are contained in class files in the HP NonStop Open System Services (OSS) file system, and a group of class files or packages can be stored in a Java archive (JAR) file. The SQL statement, CREATE PROCEDURE, registers a Java method as a stored procedure in the database by storing its name, parameter types, location, and other
in SQL/MX system metadata tables.
Compile the Java source file that contains the method to produce a class file:
-> javac Payroll.java
Register the JAVA method as an SPJ in NonStop SQL/MX
When you register an SPJ by using a CREATE PROCEDURE statement, NonStop SQL/MX verifies that the specified Java method exists and that its signature matches the SQL parameters of the stored procedure. After verifying the SPJ, NonStop SQL/MX stores information about the SPJ method, such as its name, location, and parameter
types, in the system metadata tables. After you register an SPJ in NonStop SQL/MX, any SQL/MX application or interface with the appropriate permissions can call the SPJ to execute the SPJ method.
Invoke the SPJ in NonStop SQL/MX
When an application issues a CALL statement, the Java method of the invoked SPJ executes inside a JVM in an SQL/MX UDR server process.
Please get the attachment JAVA stored procedures in SQLMX for further reference.
- Latest Posts