Calls the stored procedures defined by the DB2 sample Spserver.java. The documentation for each shows the CREATE PROCEDURE statement that must be executed. To create all the stored procedures, use the spcreate.db2 script in the dxx_sample directory. returns the language in which the stored procedure library is implemented

CREATE PROCEDURE SAMPLE.OUT_LANGUAGE (
  OUT LANGUAGE CHAR(8)
)
DYNAMIC RESULT SETS 0
LANGUAGE JAVA
PARAMETER STYLE JAVA
FENCED
MODIFIES SQL DATA
EXTERNAL NAME 'Spserver.outLanguage'

CALL SAMPLE.OUT_LANGUAGE(:language) returns the median salary of the EMPLOYEE table

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

CALL SAMPLE.OUT_PARAM (:medianSalary, :errorCode, :errorPoint) accepts 3 values and update salaries in the EMPLOYEE table accordingly

CREATE PROCEDURE SAMPLE.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
FENCED
MODIFIES SQL DATA
EXTERNAL NAME 'Spserver.inParams'

CALL SAMPLE.IN_PARAMS (:lowsal, :medsal, :highsal, :department, :errorCode, :errorLabel) accepts an input value and returns the median salary of the EMPLOYEE table for employees who make more than the input value

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

CALL SAMPLE.INOUT_PARAM (:medianSalary, :errorCode, :errorLabel) returns a result set to the client application

CREATE PROCEDURE SAMPLE.ONE_RESULT_SET (
  IN salValue DOUBLE,
  OUT sqlCode INTEGER
)
DYNAMIC RESULT SETS 1
LANGUAGE JAVA 
PARAMETER STYLE JAVA
FENCED
MODIFIES SQL DATA
EXTERNAL NAME 'Spserver.resultSetToClient'

CALL SAMPLE.ONE_RESULT_SET (:salValue, :sqlCode) returns a non-JDBC result set to the client application


CREATE PROCEDURE sample.testrs (IN parameter1 INTEGER) 
LANGUAGE SQL 
dynamic result sets 1                                               
begin        
DECLARE C1 CURSOR FOR SELECT EMPNO,FIRSTNME,MIDINIT,LASTNAME,
                             WORKDEPT,PHONENO,JOB,EDLEVEL,SEX,
                             SALARY,BONUS,COMM         
FROM sample.employee   
where salary > parameter1; 
OPEN C1; 
END

CALL SAMPLE.TESTRS (:salValue) returns two result sets to the client application

CREATE PROCEDURE SAMPLE.TWO_RESULT_SETS (
  IN salary DOUBLE,
  out sqlCode INTEGER
)
DYNAMIC RESULT SETS 2
LANGUAGE JAVA 
PARAMETER STYLE JAVA
FENCED
MODIFIES SQL DATA
EXTERNAL NAME 'Spserver.twoResultSets'

CALL SAMPLE.TWO_RESULT_SETS (:salary, :sqlCode) returns a resume for an employee number

CREATE PROCEDURE SAMPLE.CLOB_EXTRACT (
  IN number CHAR(6),
  OUT buffer VARCHAR(1000),
  OUT errorCode INTEGER
)
DYNAMIC RESULT SETS 0
LANGUAGE JAVA
PARAMETER STYLE JAVA
FENCED
MODIFIES SQL DATA
EXTERNAL NAME 'Spserver.clobExtract'

CALL SAMPLE.CLOB_EXTRACT (:number, :buffer, :errorCode) modifies a decimal

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

CALL SAMPLE.DECIMAL_TYPE(:decimalIn, :sqlCode, :errorLabel) uses all of the common data types in a stored procecure

CREATE PROCEDURE SAMPLE.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
FENCED
MODIFIES SQL DATA
EXTERNAL NAME 'Spserver.allDataTypes'

CALL SAMPLE.ALL_DATA_TYPES (:small, :intIn, :bigIn, :realIn, :doubleIn, :charOut, :charsOut, :varCharOut, :dateOut, :timeOut, :sqlCode, :errorLabel)