SQL/MX 3.2.1 Guide to Stored Procedures in Java (H06.26+, J06.15+)

• Qualify the procedure name with an existing catalog and schema. If you do not fully qualify
the procedure name, NonStop SQL/MX qualifies it according to the current settings of
CATALOG and SCHEMA in the SYSTEM_DEFAULTS table. If the NAMETYPE attribute is set
to NSK instead of ANSI and you do not fully qualify the procedure name, NonStop SQL/MX
returns an error. For more information, see the SQL/MX Reference Manual.
• Note that NonStop SQL/MX does not support the overloading of procedure names and
disallows the use of the same procedure name with different signatures. That is, you cannot
register the same procedure name more than once with different underlying SPJ methods.
Specifying SQL Parameters
If the SPJ method does not accept arguments, like the lowerPrice() method, specify the procedure
name with empty parentheses, as shown:
samdbcat.sales.lowerprice()
If the SPJ method accepts arguments, specify the corresponding SQL parameters. Each SQL
parameter consists of a parameter mode, an optional SQL identifier, and an SQL data type, as
shown:
Parameter Mode
The parameter mode specifies the input and output mode of an SQL parameter:
• IN specifies a parameter that passes a single value to an SPJ.
• INOUT specifies a parameter that passes a single value to and accepts a single value from
an SPJ.
• OUT specifies a parameter that accepts a single value from an SPJ.
The default mode is IN if you do not specify the parameter mode. For a parameter mode of OUT
or INOUT, you must explicitly specify the parameter mode for the SQL parameter.
In this example, all three parameters pass data to the SPJ, and the last parameter accepts data
from the SPJ:
samdbcat.sales.totalprice(IN qty NUMERIC(18),
IN rate VARCHAR(10),
INOUT price NUMERIC(18,2))
SQL Identifier
The SQL identifier is an optional name you can use to describe each SQL parameter and to enhance
the readability of the CREATE PROCEDURE statement. For example, these identifiers describe the
SQL parameters of the ADJUSTSALARY procedure:
samdbcat.persnl.adjustsalary(IN empnum NUMERIC(4),
IN percent FLOAT,
OUT newsalary NUMERIC(8,2))
The identifier conforms to the syntax of other SQL identifiers. For information about identifiers, see
the SQL/MX Reference Manual.
Using the CREATE PROCEDURE Statement 61