-- This is a CLP script that catalogs the stored procedures
-- contained in spserver.sqc.

-- 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 C 
PARAMETER STYLE GENERAL
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 C 
PARAMETER STYLE GENERAL
NO DBINFO
FENCED
MODIFIES SQL DATA
PROGRAM TYPE SUB
EXTERNAL NAME 'spserver!out_param'@

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 C 
PARAMETER STYLE GENERAL
NO DBINFO
FENCED
MODIFIES SQL DATA
PROGRAM TYPE SUB
EXTERNAL NAME 'spserver!in_params'@

CREATE PROCEDURE INOUT_PARAM (INOUT medianSalary DOUBLE, OUT errorCode INTEGER, OUT errorLabel CHAR(32))
DYNAMIC RESULT SETS 0
LANGUAGE C 
PARAMETER STYLE GENERAL WITH NULLS
NO DBINFO
FENCED
MODIFIES SQL DATA
PROGRAM TYPE SUB
EXTERNAL NAME 'spserver!inout_param'@

CREATE PROCEDURE ONE_RESULT_SET (IN salValue DOUBLE, OUT sqlCode INTEGER)
DYNAMIC RESULT SETS 1
LANGUAGE C 
PARAMETER STYLE GENERAL
NO DBINFO
FENCED
READS SQL DATA
PROGRAM TYPE SUB
EXTERNAL NAME 'spserver!one_result_set_to_client'@

CREATE PROCEDURE TWO_RESULT_SETS (IN salary DOUBLE, out sqlCode INTEGER)
DYNAMIC RESULT SETS 2
LANGUAGE C 
PARAMETER STYLE GENERAL
NO DBINFO
FENCED
READS SQL DATA
PROGRAM TYPE SUB
EXTERNAL NAME 'spserver!two_result_sets'@

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

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 C 
PARAMETER STYLE GENERAL
NO DBINFO
FENCED
READS SQL DATA
PROGRAM TYPE SUB
EXTERNAL NAME 'spserver!all_data_types'@

CREATE PROCEDURE DB2SQL_EXAMPLE (IN job CHAR(8), OUT salary DOUBLE)
DYNAMIC RESULT SETS 0
LANGUAGE C 
PARAMETER STYLE DB2SQL
NO DBINFO
FENCED
READS SQL DATA
PROGRAM TYPE SUB
EXTERNAL NAME 'spserver!db2sql_example'@

CREATE PROCEDURE DBINFO_EXAMPLE (IN job CHAR(8), OUT salary DOUBLE,
OUT dbname CHAR(128), OUT dbversion CHAR(8), OUT errorcode INTEGER)
DYNAMIC RESULT SETS 0
LANGUAGE C 
PARAMETER STYLE GENERAL
DBINFO
FENCED
READS SQL DATA
PROGRAM TYPE SUB
EXTERNAL NAME 'spserver!dbinfo_example'@

CREATE PROCEDURE MAIN_EXAMPLE (IN job CHAR(8), OUT salary DOUBLE, OUT errorcode INTEGER)
DYNAMIC RESULT SETS 0
LANGUAGE C 
PARAMETER STYLE GENERAL
NO DBINFO
FENCED
READS SQL DATA
PROGRAM TYPE MAIN
EXTERNAL NAME 'spserver!main_example'@