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

Using MXCI with SPJ RS

In an MXCI session, invoking the SPJ named ORDERSUMMARY returns one LARGEINT value as

an output parameter and returns a maximum of two result sets. The output parameter row (which

in this case contains one column named NUM_ORDERS) is displayed first followed by two stored

procedure result sets. Prepare and run the following CALL statement:

CALL samdbcat.sales.order_summary('01/01/2001', ?);

The output of the prepared call statement is:

NUM_ORDERS

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

ORDERNUM NUM_PARTS AMOUNT Order/Date Last Name

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

100210 4 19020.00 2003-04-10 HUGHES

100250 4 22625.00 2003-01-23 HUGHES

101220 4 45525.00 2003-07-21 SCHNABL

200300 3 52000.00 2003-02-06 SCHAEFFER

200320 4 9195.00 2003-02-17 KARAJAN

200490 2 1065.00 2003-03-19 WEIGL

--- 13 row(s) selected.

Order/Num Part/Num Unit/Price Qty/Ord Part Description

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

100210 2001 1100.00 3 GRAPHIC PRINTER,M1

100210 2403 620.00 6 DAISY PRINTER,T2

100210 244 3500.00 3 PC GOLD, 30 MB

100210 5100 150.00 10 MONITOR BW, TYPE 1

100250 6500 95.00 10 DISK CONTROLLER

100250 6301 245.00 15 GRAPHIC CARD, HR

--- 70 row(s) selected.

--- SQL operation complete.

For more information, see ORDERSUMMARY Stored Procedure (page 121).

NOTE: The SPJ RS feature is supported on systems running J06.05 and later J-series RVUs or

H06.16 and later H-series RVUs.

Invoking SPJs Statically in an Embedded SQL Program in C, C++, or

COBOL

In an embedded SQL program in C, C++, or COBOL, you can invoke an SPJ in a statically compiled

CALL statement. Place a statically compiled CALL statement within an EXEC SQL directive:

EXEC SQL CALL procedure-name

([parameter [{, parameter}...]]);

EXEC SQL CALL procedure-name

([parameter [{, parameter}...]]) END-EXEC

For statically compiled CALL statements, which have static actual parameters, the embedded

program does not explicitly refer to input or output descriptors.

76 Invoking SPJs in NonStop SQL/MX