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-6

Invoking SPJs in MXCI

In MXCI, you can invoke an SPJ by issuing a CALL statement directly or by preparing

and executing a CALL statement.

Use MXCI named or unnamed parameters anywhere in the argument list of an SPJ

invoked in MXCI. An MXCI named parameter is set by the SET PARAM command, and

an MXCI unnamed parameter is set by the USING clause of the EXECUTE statement.

You must use an MXCI parameter for an OUT or INOUT parameter argument. MXCI

displays all output parameter values after you issue the CALL statement. The

procedure call does not change the value of an MXCI named parameter that you use

as an OUT or INOUT parameter.

For more information about MXCI parameters, see the SQL/MX Reference Manual.

Using MXCI Named Parameters

In an MXCI session, invoke the SPJ named TOTALPRICE, which has two IN

parameters and one INOUT parameter. This SPJ accepts the quantity, shipping speed,

and price of an item, calculates the total price, including tax and shipping charges, and

returns the total price. For more information, see the Sales Class on page A-3.

Set the input value for the INOUT parameter by entering a SET PARAM command

before calling the SPJ:

SET PARAM ?p 10;

CALL samdbcat.sales.totalprice(23, 'standard', ?p);

The CALL statement returns the total price of the item:

The value of the MXCI named parameter, ?p, remains 10.

Using MXCI Unnamed Parameters

In an MXCI session, invoke the SPJ named TOTALPRICE by preparing and executing

a CALL statement. The INOUT parameter accepts a value that is set by the USING

clause of the EXECUTE statement and returns the total price:

SET SCHEMA samdbcat.sales;

PREPARE stmt1 FROM CALL totalprice(50,'nextday',?);

EXECUTE stmt1 USING 2.25;

PRICE

---------------------

253.96

--- SQL operation complete.