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
language.

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
statement.

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.

2.Security

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.

4.Portability

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.

image_2

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.

image_3

Please get the attachment JAVA stored procedures in SQLMX for further reference.

Thank you

Chanaka

  • Bio
  • Latest Posts

Leave a Reply

Your email address will not be published. Required fields are marked *

*