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

Invoking SPJs in NonStop SQL/MX
HP NonStop SQL/MX Guide to Stored Procedures in Java—523727-004
5-4
Listing the Arguments of the SPJ
Listing the Arguments of the SPJ
Each argument that you list in the CALL statement must correspond with the SQL
parameter that you specified in the CREATE PROCEDURE statement.
For example, if you registered the stored procedure with three SQL parameters (two IN
parameters and one OUT parameter), you must list three formal parameters, separated
by commas, in the CALL statement:
CALL samdbcat.persnl.adjustsalary(202, 5, ?);
If the SPJ does not accept arguments, you must specify empty parentheses, as shown:
CALL samdbcat.sales.lowerprice();
Data Conversion of Parameter Arguments
NonStop SQL/MX performs an implicit data conversion when the data type of a
parameter argument is compatible with but does not match the formal data type of the
stored procedure. For stored procedure input values, the conversion is from the actual
argument value to the formal parameter type. For stored procedure output values, the
conversion is from the actual output value, which has the data type of the formal
parameter, to the declared type of the host variable or dynamic parameter.
Input Parameter Arguments
To pass data to an IN or INOUT parameter of an SPJ, specify an SQL expression that
evaluates to a character, date-time, or numeric value. The SQL expression can
evaluate to NULL provided that the underlying Java parameter supports null values.
For more information, see Null Input and Output on page 3-4.
For an IN parameter argument, use one of these SQL expressions:
Type of Argument Examples
Literal CALL adjustsalary(202, 5.5, ?);
CALL dailyorders(DATE '2003-03-19', ?);
CALL totalprice(23, 'nextday', ?param);
SQL function
(including CASE and
CAST expressions)
CALL dailyorders(CURRENT_DATE, ?);
Arithmetic expression CALL adjustsalary(202, :percent * 0.25,
:OUT newsalary);
Concatenation
operation
CALL totalprice(23, 'next' || 'day', ?param);