-- This is a CLP script that catalogs the stored procedures
-- contained in the Spserver class.

-- To run this script from the CLP, perform the following steps:
-- 1. connect to the database
-- 2. issue the command "db2 -td@ -vf <script-name>"
--    where <script-name> represents the name of this script

CREATE PROCEDURE OUT_LANGUAGE (OUT LANGUAGE CHAR(8))
DYNAMIC RESULT SETS 0
LANGUAGE JAVA
PARAMETER STYLE JAVA
NO DBINFO
FENCED
MODIFIES SQL DATA
PROGRAM TYPE SUB
EXTERNAL NAME 'Spserver.outLanguage'@

CREATE PROCEDURE OUT_PARAM (OUT medianSalary DOUBLE, OUT errorCode INTEGER, OUT errorPoint CHAR(32))
DYNAMIC RESULT SETS 0
LANGUAGE JAVA
PARAMETER STYLE JAVA
NO DBINFO
FENCED
MODIFIES SQL DATA
PROGRAM TYPE SUB
EXTERNAL NAME 'Spserver.outParameter'@

CREATE PROCEDURE IN_PARAMS (IN lowsal DOUBLE, IN medsal DOUBLE, IN highsal DOUBLE, IN department CHAR(3), OUT errorCode INTEGER, OUT errorLabel CHAR(32))
DYNAMIC RESULT SETS 0
LANGUAGE JAVA 
PARAMETER STYLE JAVA
NO DBINFO
FENCED
MODIFIES SQL DATA
PROGRAM TYPE SUB
EXTERNAL NAME 'Spserver.inParams'@

CREATE PROCEDURE INOUT_PARAM (INOUT medianSalary DOUBLE, OUT errorCode INTEGER, OUT errorLabel CHAR(32))
DYNAMIC RESULT SETS 0
LANGUAGE JAVA 
PARAMETER STYLE JAVA
NO DBINFO
FENCED
MODIFIES SQL DATA
PROGRAM TYPE SUB
EXTERNAL NAME 'Spserver.inoutParam'@

CREATE PROCEDURE ONE_RESULT_SET (IN salValue DOUBLE, OUT sqlCode INTEGER)
DYNAMIC RESULT SETS 1
LANGUAGE JAVA 
PARAMETER STYLE JAVA
NO DBINFO
FENCED
READS SQL DATA
PROGRAM TYPE SUB
EXTERNAL NAME 'Spserver.resultSetToClient'@

CREATE PROCEDURE TWO_RESULT_SETS (IN salary DOUBLE, out sqlCode INTEGER)
DYNAMIC RESULT SETS 2
LANGUAGE JAVA 
PARAMETER STYLE JAVA
NO DBINFO
FENCED
READS SQL DATA
PROGRAM TYPE SUB
EXTERNAL NAME 'Spserver.twoResultSets'@

CREATE PROCEDURE CLOB_EXTRACT (IN number CHAR(6), OUT buffer VARCHAR(1000), OUT errorCode INTEGER)
DYNAMIC RESULT SETS 0
LANGUAGE JAVA
PARAMETER STYLE JAVA
NO DBINFO
FENCED
READS SQL DATA
PROGRAM TYPE SUB
EXTERNAL NAME 'Spserver.clobExtract'@

CREATE PROCEDURE DECIMAL_TYPE (INOUT decimalIn DECIMAL(10,2),
  OUT sqlCode INTEGER, OUT errorLabel CHAR(32))
DYNAMIC RESULT SETS 0
LANGUAGE JAVA 
PARAMETER STYLE JAVA
NO DBINFO
FENCED
READS SQL DATA
PROGRAM TYPE SUB
EXTERNAL NAME 'Spserver.decimalType'@

CREATE PROCEDURE ALL_DATA_TYPES (INOUT small SMALLINT, 
  INOUT intIn INTEGER, INOUT bigIn BIGINT,
  INOUT realIn REAL, INOUT doubleIn DOUBLE,
  OUT charOut CHAR(1),
  OUT charsOut CHAR(15),
  OUT varcharOut VARCHAR(12), OUT dateOut DATE,
  OUT timeOut TIME, OUT sqlCode INTEGER, OUT errorLabel CHAR(32))
DYNAMIC RESULT SETS 0
LANGUAGE JAVA 
PARAMETER STYLE JAVA
NO DBINFO
FENCED
READS SQL DATA
PROGRAM TYPE SUB
EXTERNAL NAME 'Spserver.allDataTypes'@