*************************************************************************
**
** Source File Name = outcli.sqb
**
** Licensed Materials - Property of IBM
**
** (C) COPYRIGHT International Business Machines Corp. 1995, 2000
** All Rights Reserved.
**
** US Government Users Restricted Rights - Use, duplication or
** disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
**
** PURPOSE: This sample program demonstrates stored procedures.
**
** There are two parts to this program:
** - the outcli executable (placed on the client)
** - the outsrv library (placed on the server)
**
** Instead of connecting to a remote database
**
** The outcli routine will allocate and initialize a one
** variable output SQLDA. The outcli routine will then
** call the sqlgproc API passing it the output SQLDA.
**
** The sqlgproc API will call the outsrv routine stored
** in the outsrv library.
**
** The outsrv routine will obtain the median salary of
** employees in the "staff" table of the "sample" database.
** This value will be placed in the output SQLDA and
** returned to the outcli routine. The outcli routine will
** then print out the median salary.
**
** For more information about these samples see the README file.
**
** For more information on Programming in COBOL, see the:
** - "Programming in COBOL" section of the Application Development Guide.
**
** For more information on Building COBOL Applications, see the:
** - "Building COBOL Applications" section of the Application Building Guide.
**
** For more information on the SQL language see the SQL Reference.
**
*************************************************************************
Identification Division.
Program-ID. "outcli".
Data Division.
Working-Storage Section.
* Copy Files for Constants and Structures.
copy "sql.cbl". 1
copy "sqlenv.cbl".
copy "sqlca.cbl".
01 decimal-sqllen pic s9(4) comp-5.
01 decimal-parts redefines decimal-sqllen.
05 precision pic x.
05 scale pic x.
* Declare an Output SQLDA Structure.
01 io-sqlda sync. 2
05 io-sqldaid pic x(8) value "O-DA ".
05 io-sqldabc pic s9(9) comp-5.
05 io-sqln pic s9(4) comp-5.
05 io-sqld pic s9(4) comp-5.
05 io-sqlvar occurs 1 to 99 times
depending on io-sqld.
10 io-sqltype pic s9(4) comp-5.
10 io-sqllen pic s9(4) comp-5.
10 io-sqldata usage is pointer.
10 io-sqlind usage is pointer.
10 io-sqlname.
15 io-sqlnamel pic s9(4) comp-5.
15 io-sqlnamec pic x(30).
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 dbname pic x(8). 3
01 userid pic x(8).
01 passwd.
49 passwd-length pic s9(4) comp-5 value 0.
49 passwd-name pic x(18).
* Declare Variables for the SQL CALL.
77 prog-name pic x(19) value "outsrv".
* Declare Local Variables for Holding Actual Data.
77 salary pic s9(5)v99 comp-3.
77 sal-ind pic s9(4) comp-5.
EXEC SQL END DECLARE SECTION END-EXEC.
* Declare Output Mask for Salary
77 sal-out pic z9(5).99-.
* Declare a Null Pointer Variable.
77 null-ptr-int pic s9(9) comp-5.
77 null-ptr redefines null-ptr-int pointer.
77 errloc pic x(80).
Procedure Division.
Main Section.
* Initialize the Input/Output SQLDA Structure
move 1 to io-sqln. 4
move 1 to io-sqld.
move sql-typ-ndecimal to io-sqltype(1).
* Length = 7 digits precision and 2 digits scale
move x"07" to precision.
move x"02" to scale.
move decimal-sqllen to io-sqllen(1).
set io-sqldata(1) to address of salary.
set io-sqlind(1) to address of sal-ind.
* CONNECT TO DATABASE
display "Enter in the database name : " with no advancing.
accept dbname.
display "Enter your user id (default none): "
with no advancing.
accept userid.
if userid = spaces
EXEC SQL CONNECT TO sample END-EXEC
else
display "Enter your password : " with no advancing
accept passwd-name.
* Passwords in a CONNECT statement must be entered in a VARCHAR format
* with the length of the input string.
inspect passwd-name tallying passwd-length for characters
before initial " ".
EXEC SQL CONNECT TO :dbname USER :userid USING :passwd 5
END-EXEC.
move "CONNECT TO" to errloc.
call "checkerr" using SQLCA errloc.
* Call the Remote Procedure.
display "Use CALL with Host Var. to invoke Server Procedure."
EXEC SQL CALL :prog-name (:SALARY:SAL-IND) END-EXEC. 6a
move "CALL HV" to errloc.
call "checkerr" using SQLCA errloc.
display "Server Procedure Complete.".
* Print Salary Returned in the host variable.
move salary to sal-out.
display "Median Salary = " sal-out.
display " ".
* Call the Remote Procedure.
display "Use CALL with SQLDA to invoke Server Procedure."
EXEC SQL CALL :prog-name USING DESCRIPTOR 6b
:IO-SQLDA END-EXEC.
move "CALL DA" to errloc.
call "checkerr" using SQLCA errloc.
DISPLAY "Server Procedure Complete.".
* Print Salary Returned in IO-SQLDA.
move salary to sal-out.
display "Median Salary = " sal-out.
* Disconnect from Remote Database.
EXEC SQL CONNECT RESET END-EXEC. 7
stop run.
exit.