Stored Procedures in SQL/MX

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.

3.Increase productivity

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

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.

Thank you


  • Bio
  • Latest Posts