Following is a sample program demonstrating the use of an input SQLDA structure. The client application invokes a stored procedure that creates a table named Presidents and loads the table with data.
This program creates a table called Presidents in the SAMPLE database. It then inserts the values Washington, Jefferson, and Lincoln into the table.
Without using stored procedures, the sample program would have been designed to transmit data across the network in four separate requests in order to process each SQL statement, as shown in Figure 22.
Figure 22. Input-SQLDA Sample Without a Stored Procedure
|
Instead, the sample program makes use of the stored procedures technique to transmit all of the data across the network in one request, allowing the server procedure to execute the SQL statements as a group. This technique is shown in Figure 23.
Figure 23. Input-SQLDA Sample With a Stored Procedure
 |
A sample input-SQLDA client application and sample input-SQLDA stored procedure is shown on "How the Example Input-SQLDA Client Application Works".
The CHECKERR macro/function is an error checking utility which is external to the program. The location of this error checking utility depends upon the programming language used:
See "Using GET ERROR MESSAGE in Example Programs" for the source code for this error checking utility.
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlenv.h>
#include <sqlca.h>
#include <sqlda.h>
#include <sqlutil.h>
#include "util.h"
#define CHECKERR(CE_STR) if (check_error (CE_STR, &sqlca) != 0) return 1;
int main(int argc, char *argv[]) {
EXEC SQL BEGIN DECLARE SECTION;
char database[9];
char userid[9];
char passwd[19];
char procname[255] = "inpsrv";
char table_name[11] = "PRESIDENTS";
char data_item0[21] = "Washington";
char data_item1[21] = "Jefferson";
char data_item2[21] = "Lincoln";
short tableind, dataind0, dataind1, dataind2;
EXEC SQL END DECLARE SECTION;
/* Declare Variables for CALL USING */
struct sqlca sqlca;
struct sqlda *inout_sqlda = NULL;
if (argc != 4) {
printf ("\nUSAGE: inpcli remote_database userid passwd\n\n");
return 1;
}
strcpy (database, argv[1]);
strcpy (userid, argv[2]);
strcpy (passwd, argv[3]);
/* Connect to Remote Database */
printf("CONNECT TO Remote Database.\n");
EXEC SQL CONNECT TO :database USER :userid USING :passwd;
CHECKERR ("CONNECT TO SAMPLE");
/********************************************************\
* Call the Remote Procedure via CALL with Host Variables *
\********************************************************/
printf("Use CALL with Host Variable to invoke the Server Procedure"
" named inpsrv.\n");
tableind = dataind0 = dataind1 = dataind2 = 0;
EXEC SQL CALL :procname (:table_name:tableind, :data_item0:dataind0,
:data_item1:dataind1, :data_item2:dataind2); (2a)
CHECKERR ("CALL WITH HOST VARIABLE");
printf("Server Procedure Complete.\n\n");
/* Allocate and Initialize Input SQLDA */ (1)
inout_sqlda = (struct sqlda *)malloc( SQLDASIZE(4) );
inout_sqlda->sqln = 4;
inout_sqlda->sqld = 4;
inout_sqlda->sqlvar[0].sqltype = SQL_TYP_NCSTR;
inout_sqlda->sqlvar[0].sqldata = table_name;
inout_sqlda->sqlvar[0].sqllen = strlen( table_name ) + 1;
inout_sqlda->sqlvar[0].sqlind = &tableind;
inout_sqlda->sqlvar[1].sqltype = SQL_TYP_NCSTR;
inout_sqlda->sqlvar[1].sqldata = data_item0;
inout_sqlda->sqlvar[1].sqllen = strlen( data_item0 ) + 1;
inout_sqlda->sqlvar[1].sqlind = &dataind0;
inout_sqlda->sqlvar[2].sqltype = SQL_TYP_NCSTR;
inout_sqlda->sqlvar[2].sqldata = data_item1;
inout_sqlda->sqlvar[2].sqllen = strlen( data_item1 ) + 1;
inout_sqlda->sqlvar[2].sqlind = &dataind0;
inout_sqlda->sqlvar[3].sqltype = SQL_TYP_NCSTR;
inout_sqlda->sqlvar[3].sqldata = data_item2;
inout_sqlda->sqlvar[3].sqllen = strlen( data_item2 ) + 1;
inout_sqlda->sqlvar[3].sqlind = &dataind0;
/***********************************************\
* Call the Remote Procedure via CALL with SQLDA *
\***********************************************/
printf("Use CALL with SQLDA to invoke the Server Procedure named "
"inpsrv.\n");
tableind = dataind0 = dataind1 = dataind2 = 0;
EXEC SQL CALL :procname USING DESCRIPTOR :*inout_sqlda; (2b)
CHECKERR ("CALL WITH SQLDA");
printf("Server Procedure Complete.\n\n");
/* Free allocated memory */
free( inout_sqlda );
/* Disconnect from Remote Database */
EXEC SQL CONNECT RESET;
CHECKERR ("CONNECT RESET");
return 0;
}
/* end of program : inpcli.sqc */
Identification Division.
Program-ID. "inpcli".
Data Division.
Working-Storage Section.
* Copy Files for Constants and Structures.
copy "sql.cbl".
copy "sqlenv.cbl".
copy "sqlca.cbl".
* Declare an Input/Output SQLDA Structure.
01 io-sqlda sync.
05 io-sqldaid pic x(8) value "IN-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-entries occurs 0 to 99 times
depending on io-sqld.
10 io-sqlvar.
15 io-sqltype pic s9(4) comp-5.
15 io-sqllen pic s9(4) comp-5.
15 io-sqldata usage is pointer.
15 io-sqlind usage is pointer.
15 io-sqlname.
20 io-sqlnamel pic s9(4) comp-5.
20 io-sqlnamec pic x(30).
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 dbname pic x(8).
01 userid pic x(8).
01 passwd.
49 passwd-length pic s9(4) comp-5 value 0.
49 passwd-name pic x(18).
01 table-name pic x(10) value "PRESIDENTS".
01 io-data1 pic x(20) value "Washington ".
01 io-data2 pic x(20) value "Jefferson ".
01 io-data3 pic x(20) value "Lincoln ".
* Declare and Initialize Indicator Variables.
01 table-nameind pic s9(4) comp-5 value 0.
01 io-dataind1 pic s9(4) comp-5 value 0.
01 io-dataind2 pic s9(4) comp-5 value 0.
01 io-dataind3 pic s9(4) comp-5 value 0.
01 prog-name pic x(12) value "inpsrv".
EXEC SQL END DECLARE SECTION END-EXEC.
* 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.
* 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.
move 0 to passwd-length.
inspect passwd-name tallying passwd-length for characters
before initial " ".
EXEC SQL CONNECT TO :dbname USER :userid USING :passwd
END-EXEC.
move "CONNECT TO" to errloc.
call "checkerr" using SQLCA errloc.
* Call the Remote Procedure.
display "Use CALL with Host Variable to invoke the Server Pro
- "cedure".
EXEC SQL CALL :prog-name (2a)
(:table-name:table-nameind,
:io-data1:io-dataind1,
:io-data2:io-dataind2,
:io-data3:io-dataind3) END-EXEC.
move "SQLCALL HV" to errloc.
call "checkerr" using SQLCA errloc.
display "Server Procedure Complete.".
* Initialize the Input/Output SQLDA Structure
move 4 to io-sqln. (1)
move 4 to io-sqld.
move SQL-TYP-NCHAR to io-sqltype(1).
set io-sqldata(1) to address of table-name.
set io-sqlind(1) to address of table-nameind.
move 10 to io-sqllen(1).
move SQL-TYP-NCHAR to io-sqltype(2).
set io-sqldata(2) to address of io-data1.
set io-sqlind(2) to address of io-dataind1.
move 20 to io-sqllen(2).
move SQL-TYP-NCHAR to io-sqltype(3).
set io-sqldata(3) to address of io-data2.
set io-sqlind(3) to address of io-dataind2.
move 20 to io-sqllen(3).
move SQL-TYP-NCHAR to io-sqltype(4).
set io-sqldata(4) to address of io-data3.
set io-sqlind(4) to address of io-dataind3.
move 20 to io-sqllen(4).
* Call the Remote Procedure.
display "Use CALL with SQLDA to invoke the Server Procedure n
- "amed".
EXEC SQL CALL :prog-name USING DESCRIPTOR (2b)
:io-sqlda END-EXEC.
move "SQLCALL DA" to errloc.
call "checkerr" using SQLCA errloc.
display "Server Procedure Complete.".
* Disconnect from Remote Database.
EXEC SQL CONNECT RESET END-EXEC.
stop run.
exit.
program inpcli
implicit none
* Copy Files for Constants and Structures
include 'sql.f'
include 'sqlenv.f'
include 'sqlutil.f'
include 'sqldact.f'
* Declare an SQLCA
EXEC SQL INCLUDE SQLCA
* Declare host variables.
EXEC SQL BEGIN DECLARE SECTION
character*8 dbname
character*8 userid
character*18 passwd
character*11 procname /'inpsrv'/
character*10 table_name /'PRESIDENTS'/
character*20 data_item0 /'Washington'/
character*20 data_item1 /'Jefferson'/
character*20 data_item2 /'Lincoln'/
integer*2 tableind /0/
integer*2 dataind0 /0/
integer*2 dataind1 /0/
integer*2 dataind2 /0/
EXEC SQL END DECLARE SECTION
* Declare Variables Used to Create an SQLDA
integer*2 sqlvar1
parameter ( sqlvar1 = sqlda_header_sz + 0*sqlvar_struct_sz )
integer*2 sqlvar2
parameter ( sqlvar2 = sqlda_header_sz + 1*sqlvar_struct_sz )
integer*2 sqlvar3
parameter ( sqlvar3 = sqlda_header_sz + 2*sqlvar_struct_sz )
integer*2 sqlvar4
parameter ( sqlvar4 = sqlda_header_sz + 3*sqlvar_struct_sz )
* Declare an Input SQLDA Structure -- 4 Variables
character io_sqlda(sqlda_header_sz + 4*sqlvar_struct_sz)
character*8 io_sqldaid ! Header
integer*4 io_sqldabc
integer*2 io_sqln
integer*2 io_sqld
integer*2 io_sqltype1 ! First Variable
integer*2 io_sqllen1
integer*4 io_sqldata1
integer*4 io_sqlind1
integer*2 io_sqlnamel1
character*30 io_sqlnamec1
integer*2 io_sqltype2 ! Second Variable
integer*2 io_sqllen2
integer*4 io_sqldata2
integer*4 io_sqlind2
integer*2 io_sqlnamel2
character*30 io_sqlnamec2
integer*2 io_sqltype3 ! Third Variable
integer*2 io_sqllen3
integer*4 io_sqldata3
integer*4 io_sqlind3
integer*2 io_sqlnamel3
character*30 io_sqlnamec3
integer*2 io_sqltype4 ! Fourth Variable
integer*2 io_sqllen4
integer*4 io_sqldata4
integer*4 io_sqlind4
integer*2 io_sqlnamel4
character*30 io_sqlnamec4
equivalence( io_sqlda(sqlda_sqldaid_ofs), io_sqldaid )
equivalence( io_sqlda(sqlda_sqldabc_ofs), io_sqldabc )
equivalence( io_sqlda(sqlda_sqln_ofs), io_sqln )
equivalence( io_sqlda(sqlda_sqld_ofs), io_sqld )
equivalence( io_sqlda(sqlvar1+sqlvar_type_ofs), io_sqltype1 )
equivalence( io_sqlda(sqlvar1+sqlvar_len_ofs), io_sqllen1 )
equivalence( io_sqlda(sqlvar1+sqlvar_data_ofs), io_sqldata1 )
equivalence( io_sqlda(sqlvar1+sqlvar_ind_ofs), io_sqlind1 )
equivalence( io_sqlda(sqlvar1+sqlvar_name_length_ofs),
+ io_sqlnamel1 )
equivalence( io_sqlda(sqlvar1+sqlvar_name_data_ofs),
+ io_sqlnamec1 )
equivalence( io_sqlda(sqlvar2+sqlvar_type_ofs), io_sqltype2 )
equivalence( io_sqlda(sqlvar2+sqlvar_len_ofs), io_sqllen2 )
equivalence( io_sqlda(sqlvar2+sqlvar_data_ofs), io_sqldata2 )
equivalence( io_sqlda(sqlvar2+sqlvar_ind_ofs), io_sqlind2 )
equivalence( io_sqlda(sqlvar2+sqlvar_name_length_ofs),
+ io_sqlnamel2 )
equivalence( io_sqlda(sqlvar2+sqlvar_name_data_ofs),
+ io_sqlnamec2 )
equivalence( io_sqlda(sqlvar3+sqlvar_type_ofs), io_sqltype3 )
equivalence( io_sqlda(sqlvar3+sqlvar_len_ofs), io_sqllen3 )
equivalence( io_sqlda(sqlvar3+sqlvar_data_ofs), io_sqldata3 )
equivalence( io_sqlda(sqlvar3+sqlvar_ind_ofs), io_sqlind3 )
equivalence( io_sqlda(sqlvar3+sqlvar_name_length_ofs),
+ io_sqlnamel3 )
equivalence( io_sqlda(sqlvar3+sqlvar_name_data_ofs),
+ io_sqlnamec3 )
equivalence( io_sqlda(sqlvar4+sqlvar_type_ofs), io_sqltype4 )
equivalence( io_sqlda(sqlvar4+sqlvar_len_ofs), io_sqllen4 )
equivalence( io_sqlda(sqlvar4+sqlvar_data_ofs), io_sqldata4 )
equivalence( io_sqlda(sqlvar4+sqlvar_ind_ofs), io_sqlind4 )
equivalence( io_sqlda(sqlvar4+sqlvar_name_length_ofs),
+ io_sqlnamel4 )
equivalence( io_sqlda(sqlvar4+sqlvar_name_data_ofs),
+ io_sqlnamec4 )
character*80 errloc
integer*4 rc
* Program Logic
print *, 'Enter in the database name :'
read 100, dbname
100 format (a8)
print *, 'Enter your user id (default none):'
read 100, userid
if( userid(1:1) .eq. ' ' ) then
EXEC SQL CONNECT TO :dbname
else
print *, 'Enter your password :'
read 100, passwd
print *,'CONNECT to Remote Database.'
EXEC SQL CONNECT TO :dbname USER :userid USING :passwd
end if
* Connect to Remote Database
errloc = 'CONNECT'
call checkerr (sqlca, errloc, *999)
* Call the Remote Procedure via CALL with Host Variables (2)
print *,'Use CALL with Host Variable to invoke the ',
+ 'Server Procedure named inpsrv.'
EXEC SQL CALL :procname (:table_name:tableind,
+ :data_item0:dataind0,
+ :data_item1:dataind1,
+ :data_item2:dataind2)
errloc = 'CALL with HOST VARIABLES'
call checkerr (sqlca, errloc, *999)
print *,'Server Procedure Complete.'
* Initialize the Input SQLDA Structure (1)
io_sqldaid = 'IN_SQLDA'
io_sqldabc = sqlda_header_sz + 4*sqlvar_struct_sz
io_sqln = 4
io_sqld = 4
io_sqltype1 = SQL_TYP_NCHAR
io_sqllen1 = 10
rc = sqlgaddr(%ref(table_name), %ref(io_sqldata1))
rc = sqlgaddr(%ref(tableind), %ref(io_sqlind1))
io_sqltype2 = SQL_TYP_NCHAR
io_sqllen2 = 20
rc = sqlgaddr(%ref(data_item0), %ref(io_sqldata2))
rc = sqlgaddr(%ref(dataind0), %ref(io_sqlind2))
io_sqltype3 = SQL_TYP_NCHAR
io_sqllen3 = 20
rc = sqlgaddr(%ref(data_item1), %ref(io_sqldata3))
rc = sqlgaddr(%ref(dataind1), %ref(io_sqlind3))
io_sqltype4 = SQL_TYP_NCHAR
io_sqllen4 = 20
rc = sqlgaddr(%ref(data_item2), %ref(io_sqldata4))
rc = sqlgaddr(%ref(dataind2), %ref(io_sqlind4))
* Call the Remote Procedure via CALL with SQLDA (2)
print *,'Use CALL with SQLDA to invoke the Server ',
+ 'Procedure named inpsrv.'
EXEC SQL CALL :procname USING DESCRIPTOR :io_sqlda
errloc = 'CALL with SQLDA'
call checkerr (sqlca, errloc, *999)
print *,'Server Procedure Complete.'
* Disconnect from Remote Database.
EXEC SQL CONNECT RESET
errloc = 'CONNECT RESET'
call checkerr (sqlca, errloc, *999)
999 stop
end
/* REXX INPput CLIent */
/* this variable (SYSTEM) must be user defined */
SYSTEM = OS2
if SYSTEM = OS2 then do
if rxfuncquery('SQLDBS') <> 0 then
rcy = rxfuncadd( 'SQLDBS', 'DB2AR', 'SQLDBS' )
if rxfuncquery('SQLEXEC') <> 0 then
rcy = rxfuncadd( 'SQLEXEC', 'DB2AR', 'SQLEXEC' )
end
if SYSTEM = AIX then
rcy = SysAddFuncPkg("/usr/lpp/db2_05_00/lib/db2rexx")
/* pull in command line arguments */
parse arg dbname userid passwd .
/* check to see if the proper number of arguments have been passed in */
if passwd = '' then
do
say 'USAGE : inpcli dbname userid passwd'
exit
end
procname = 'inpsrv.cmd'
tablename = 'Presidents'
tableind = 0
dataitem.1 = 'Washington'
dataitem.1.ind = 0
dataitem.2 = 'Jefferson'
dataitem.2.ind = 0
dataitem.3 = 'Lincoln'
dataitem.3.ind = 0
io_sqlda.sqld = 4 (1)
io_sqlda.1.sqltype = 453
io_sqlda.1.sqldata = tablename
io_sqlda.1.sqllen = 10
io_sqlda.1.sqlind = tableind
io_sqlda.2.sqltype = 453
io_sqlda.2.sqldata = dataitem.1
io_sqlda.2.sqllen = 20
io_sqlda.2.sqlind = dataitem.1.ind
io_sqlda.3.sqltype = 453
io_sqlda.3.sqldata = dataitem.2
io_sqlda.3.sqllen = 20
io_sqlda.3.sqlind = dataitem.2.ind
io_sqlda.4.sqltype = 453
io_sqlda.4.sqldata = dataitem.3
io_sqlda.4.sqllen = 20
io_sqlda.4.sqlind = dataitem.3.ind
/* CONNECT TO REMOTE DATABASE */
say 'Connect to Remote Database.'
call SQLEXEC 'CONNECT TO 'dbname' USER 'userid' USING 'passwd
call CHECKERR 'CONNECT TO SAMPLE'
/* CALL THE REMOTE PROCEDURE USING SQL CALL AND HOST VARIABLES */
say 'Use CALL with Host Variables to invoke the Stored Procedure name inpsrv'
call SQLEXEC 'CALL :procname (:tablename:tableind,', (2a)
':dataitem.1 :dataitem.1.ind,',
':dataitem.2 :dataitem.2.ind,',
':dataitem.3 :dataitem.3.ind)'
call CHECKERR 'CALL USING HV'
say 'Server Procedure Complete'
say
/* CALL THE REMOTE PROCEDURE USING SQL CALL AND SQLDA */
say 'Use CALL with SQLDA to invoke the Stored Procedure name inpsrv'
call SQLEXEC 'CALL :procname USING DESCRIPTOR :io_sqlda' (2b)
call CHECKERR 'CALL USING SQLDA'
say 'Server Procedure Complete'
/* DISCONNECT FROM REMOTE DATABASE */
call SQLEXEC 'CONNECT RESET'
call CHECKERR 'CONNECT RESET'
exit 0
CHECKERR:
arg errloc
if ( SQLCA.SQLCODE = 0 ) then
return 0
else
say '--- error report ---'
say 'ERROR occured :' errloc
say 'SQLCODE :' SQLCA.SQLCODE
/******************************\
* GET ERROR MESSAGE API called *
\******************************/
call SQLDBS 'GET MESSAGE INTO :errmsg LINEWIDTH 80'
say errmsg
say '--- end error report ---'
if (SQLCA.SQLCODE < 0 ) then
exit
else
say 'WARNING - CONTINUING PROGRAM WITH ERRORS'
return 0
end
end
return 0
| Note: | Server procedures cannot be written in REXX on AIX systems. |
#include <memory.h>
#include <string.h>
#include <sqlenv.h>
#include <sqlutil.h>
#include "util.h"
SQL_API_RC SQL_API_FN inpsrv(void *reserved1, (1)
void *reserved2,
struct sqlda *inout_sqlda,
struct sqlca *ca)
{
/* Declare a local SQLCA */
EXEC SQL INCLUDE SQLCA;
/* Declare Host Variables */
EXEC SQL BEGIN DECLARE SECTION;
char table_stmt[80] = "CREATE TABLE ";
char insert_stmt[80] = "INSERT INTO ";
char insert_data[21];
EXEC SQL END DECLARE SECTION;
/* Declare Miscellanous Variables */
int cntr = 0;
char *table_name;
char *data_items[3];
short data_items_length[3];
int num_of_data = 0;
/*-----------------------------------------------------------------*/
/* Assign the data from the SQLDA to local variables so that we */
/* don't have to refer to the SQLDA structure further. This will */
/* provide better portability to other platforms such as DB2 MVS */
/* where they receive the parameter list differently. */
/*-----------------------------------------------------------------*/
table_name = inout_sqlda->sqlvar[0].sqldata;
num_of_data = inout_sqlda->sqld - 1;
for (cntr = 0; cntr < num_of_data; cntr++)
{
data_items[cntr] = inout_sqlda->sqlvar[cntr+1].sqldata;
data_items_length[cntr] = inout_sqlda->sqlvar[cntr+1].sqllen;
}
/*-----------------------------------------------------------------*/
/* Create President Table */
/* - For simplicity, we'll ignore any errors from the */
/* CREATE TABLE so that you can run this program even when the */
/* table already exists due to a previous run. */
/*-----------------------------------------------------------------*/
EXEC SQL WHENEVER SQLERROR CONTINUE;
strcat(table_stmt, table_name);
strcat(table_stmt, " (name CHAR(20))");
EXEC SQL EXECUTE IMMEDIATE :table_stmt; (2)
EXEC SQL WHENEVER SQLERROR GOTO ext;
/*-----------------------------------------------------------------*/
/* Generate and execute a PREPARE for an INSERT statement, and */
/* then insert the three presidents. */
/*-----------------------------------------------------------------*/
strcat(insert_stmt, table_name);
strcat(insert_stmt, " VALUES (?)"); (3)
EXEC SQL PREPARE S1 FROM :insert_stmt;
for (cntr = 0; cntr < num_of_data; cntr++)
{
strncpy(insert_data, data_items[cntr], data_items_length[cntr]);
insert_data[data_items_length[cntr]] = '\0';
EXEC SQL EXECUTE S1 USING :insert_data; (4)
}
/*-----------------------------------------------------------------*/
/* Return to caller */
/* - Copy the SQLCA */
/* - Update the output SQLDA. Since there's no output to */
/* return, we are setting the indicator values to -128 to */
/* return only a null value. */
/* - Commit or Rollback the inserts. */
/*-----------------------------------------------------------------*/
ext: (5)
memcpy(ca, &sqlca, sizeof(struct sqlca));
if (inout_sqlda != NULL)
{
for (cntr = 0; cntr < inout_sqlda->sqld; cntr++)
{
*(inout_sqlda->sqlvar[cntr].sqlind) = -128;
}
}
EXEC SQL WHENEVER SQLERROR CONTINUE;
/* Check SQLCA for errors */
if (SQLCODE == 0)
EXEC SQL COMMIT;
else
EXEC SQL ROLLBACK;
return(SQLZ_DISCONNECT_PROC);
}
Identification Division.
Program-ID. "inpsrv".
Data Division.
Working-Storage Section.
* Copy Files for Constants and Structures
copy "sql.cbl".
copy "sqlenv.cbl".
copy "sqlca.cbl".
* Declare Host Variables
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 insert-data pic x(12).
01 insert-statement pic x(40).
EXEC SQL END DECLARE SECTION END-EXEC.
* Declare and Initialize SQL Statement Strings
01 create-string sync.
05 stringc1 pic x(13) value "CREATE TABLE ".
05 var-string1 pic x(10).
05 stringc2 pic x(16) value " (NAME CHAR(20))".
01 insert-string sync.
05 stringi1 pic x(12) value "INSERT INTO ".
05 var-string2 pic x(11).
05 stringi2 pic x(11) value " VALUES (?)".
* Declare Miscellaneous Variables
77 idx pic 9(4) comp-5.
Linkage Section.
* Declare Parameters
01 input-data.
05 input-len pic 9(4) comp-5.
05 input-char pic x(80).
77 reserved1 pointer.
77 reserved2 pointer.
01 inout-sqlda sync.
05 inout-sqldaid pic x(8).
05 inout-sqldabc pic s9(9) comp-5.
05 inout-sqln pic s9(4) comp-5.
05 inout-sqld pic s9(4) comp-5.
05 inout-sqlvar occurs 1 to 1489 times
depending on inout-sqld.
10 inout-sqltype pic s9(4) comp-5.
10 inout-sqllen pic s9(4) comp-5.
10 inout-sqldata usage is pointer.
10 inout-sqlind usage is pointer.
10 inout-sqlname.
15 inout-sqlnamel pic s9(4) comp-5.
15 inout-sqlnamec pic x(30).
* Declare Miscellaneous Variables
77 temp-name pic x(20).
77 temp-ind pic s9(4) comp-5.
77 table-name pic x(10).
77 table-ind pic s9(4) comp-5.
01 O-SQLCA SYNC.
05 SQLCAID PIC X(8).
05 SQLCABC PIC S9(9) COMP-5.
05 SQLCODE PIC S9(9) COMP-5.
05 SQLERRM.
49 SQLERRML PIC S9(4) COMP-5.
49 SQLERRMC PIC X(70).
05 SQLERRP PIC X(8).
05 SQLERRD OCCURS 6 TIMES PIC S9(9) COMP-5.
05 SQLWARN.
10 SQLWARN0 PIC X.
10 SQLWARN1 PIC X.
10 SQLWARN2 PIC X.
10 SQLWARN3 PIC X.
10 SQLWARN4 PIC X.
10 SQLWARN5 PIC X.
10 SQLWARN6 PIC X.
10 SQLWARN7 PIC X.
10 SQLWARN8 PIC X.
10 SQLWARN9 PIC X.
10 SQLWARNA PIC X.
05 SQLSTATE PIC X(5).
Procedure Division using reserved1 reserved2 inout-sqlda O-SQLCA.(1)
Main Section.
EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC.
* Create "President" Table
set address of table-name to inout-sqldata(1).
move table-name to var-string1.
move create-string to insert-statement.
EXEC SQL EXECUTE IMMEDIATE :insert-statement END-EXEC. (2)
EXEC SQL WHENEVER SQLERROR GOTO :Error-Exit END-EXEC.
* Prepare for Insert
move table-name to var-string2.
move insert-string to insert-statement.
EXEC SQL PREPARE INSERTSTMT FROM :insert-statement END-EXEC. (3)
* Insert President Names stored in Input SQLDA into Newly Created Table
perform Add-Rows varying idx from 2 by 1 until idx > 4. (4)
* Return the SQLCA Information to the Calling Program.
move SQLCA to O-SQLCA.
EXEC SQL COMMIT END-EXEC.
move SQLZ-DISCONNECT-PROC to return-code
goback. (5)
Add-Rows.
set address of temp-name to inout-sqldata(idx).
move temp-name to insert-data.
EXEC SQL EXECUTE INSERTSTMT USING :insert-data END-EXEC.
* To minimize network flow, set the input-only variable to null
* by setting its indicator value to -128 so that they won"t
* be resent back to the client program.
set address of temp-ind to inout-sqlind(idx).
move -128 to temp-ind.
Error-Exit.
* An Error has Occurred -- ROLLBACK and Return to Calling Program.
EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC.
move SQLCA to O-SQLCA.
EXEC SQL ROLLBACK END-EXEC.
move SQLZ-DISCONNECT-PROC to return-code
goback.
integer*4 function inpsrv( reserved1, (1)
+ reserved2,
+ io_sqlda,
+ ca )
implicit none
* Include Files for Constants and Structures
include 'sql.f'
include 'sqlenv.f'
include 'sqlutil.f'
include 'sqldact.f'
* Declare Output SQLCA
EXEC SQL INCLUDE SQLCA
* Declare Dummy Parameters
integer*4 reserved1
integer*4 reserved2
character io_sqlda(sqlda_header_sz + 4*sqlvar_struct_sz)
character ca(sqlca_size)
* Declare Host Variables
EXEC SQL BEGIN DECLARE SECTION
character*20 insert_data
* character*80 insert_string
character*12 sqlname /' '/
character*200 sql_string /' '/
EXEC SQL END DECLARE SECTION
* Declare Local Variables to Hold Dummy Parameter Values
* They are in_sqlda, out_sqlda and sqlca
* Declare Variables Used to Create an SQLDA
integer*2 sqlvar1
parameter ( sqlvar1 = sqlda_header_sz + 0*sqlvar_struct_sz )
integer*2 sqlvar2
parameter ( sqlvar2 = sqlda_header_sz + 1*sqlvar_struct_sz )
integer*2 sqlvar3
parameter ( sqlvar3 = sqlda_header_sz + 2*sqlvar_struct_sz )
integer*2 sqlvar4
parameter ( sqlvar4 = sqlda_header_sz + 3*sqlvar_struct_sz )
* Declare an Input SQLDA Structure -- 4 Variables
character in_sqlda(sqlda_header_sz + 4*sqlvar_struct_sz)
character*8 in_sqldaid ! Header
integer*4 in_sqldabc
integer*2 in_sqln
integer*2 in_sqld
integer*2 in_sqltype1 ! First Variable
integer*2 in_sqllen1
integer*4 in_sqldata1
integer*4 in_sqlind1
integer*2 in_sqlnamel1
character*30 in_sqlnamec1
integer*2 in_sqltype2 ! Second Variable
integer*2 in_sqllen2
integer*4 in_sqldata2
integer*4 in_sqlind2
integer*2 in_sqlnamel2
character*30 in_sqlnamec2
integer*2 in_sqltype3 ! Third Variable
integer*2 in_sqllen3
integer*4 in_sqldata3
integer*4 in_sqlind3
integer*2 in_sqlnamel3
character*30 in_sqlnamec3
integer*2 in_sqltype4 ! Fourth Variable
integer*2 in_sqllen4
integer*4 in_sqldata4
integer*4 in_sqlind4
integer*2 in_sqlnamel4
character*30 in_sqlnamec4
equivalence( in_sqlda(sqlda_sqldaid_ofs), in_sqldaid )
equivalence( in_sqlda(sqlda_sqldabc_ofs), in_sqldabc )
equivalence( in_sqlda(sqlda_sqln_ofs), in_sqln )
equivalence( in_sqlda(sqlda_sqld_ofs), in_sqld )
equivalence( in_sqlda(sqlvar1+sqlvar_type_ofs), in_sqltype1 )
equivalence( in_sqlda(sqlvar1+sqlvar_len_ofs), in_sqllen1 )
equivalence( in_sqlda(sqlvar1+sqlvar_data_ofs), in_sqldata1 )
equivalence( in_sqlda(sqlvar1+sqlvar_ind_ofs), in_sqlind1 )
equivalence( in_sqlda(sqlvar1+sqlvar_name_length_ofs),
+ in_sqlnamel1 )
equivalence( in_sqlda(sqlvar1+sqlvar_name_data_ofs),
+ in_sqlnamec1 )
equivalence( in_sqlda(sqlvar2+sqlvar_type_ofs), in_sqltype2 )
equivalence( in_sqlda(sqlvar2+sqlvar_len_ofs), in_sqllen2 )
equivalence( in_sqlda(sqlvar2+sqlvar_data_ofs), in_sqldata2 )
equivalence( in_sqlda(sqlvar2+sqlvar_ind_ofs), in_sqlind2 )
equivalence( in_sqlda(sqlvar2+sqlvar_name_length_ofs),
+ in_sqlnamel2 )
equivalence( in_sqlda(sqlvar2+sqlvar_name_data_ofs),
+ in_sqlnamec2 )
equivalence( in_sqlda(sqlvar3+sqlvar_type_ofs), in_sqltype3 )
equivalence( in_sqlda(sqlvar3+sqlvar_len_ofs), in_sqllen3 )
equivalence( in_sqlda(sqlvar3+sqlvar_data_ofs), in_sqldata3 )
equivalence( in_sqlda(sqlvar3+sqlvar_ind_ofs), in_sqlind3 )
equivalence( in_sqlda(sqlvar3+sqlvar_name_length_ofs),
+ in_sqlnamel3 )
equivalence( in_sqlda(sqlvar3+sqlvar_name_data_ofs),
+ in_sqlnamec3 )
equivalence( in_sqlda(sqlvar4+sqlvar_type_ofs), in_sqltype4 )
equivalence( in_sqlda(sqlvar4+sqlvar_len_ofs), in_sqllen4 )
equivalence( in_sqlda(sqlvar4+sqlvar_data_ofs), in_sqldata4 )
equivalence( in_sqlda(sqlvar4+sqlvar_ind_ofs), in_sqlind4 )
equivalence( in_sqlda(sqlvar4+sqlvar_name_length_ofs),
+ in_sqlnamel4 )
equivalence( in_sqlda(sqlvar4+sqlvar_name_data_ofs),
+ in_sqlnamec4 )
* Declare and Initialize SQL Statement Strings
character create_string(39)
character*39 create_string_tmp
character*13 stringc1 /'CREATE TABLE '/
character*10 var_string1
character*16 stringc2 /' (NAME CHAR(20))'/
equivalence( create_string(1), stringc1 )
equivalence( create_string(14), var_string1 )
equivalence( create_string(24), stringc2 )
equivalence( create_string(1), create_string_tmp )
character insert_string(34)
character*34 insert_string_tmp
character*12 stringi1 /'INSERT INTO '/
character*10 var_string2
character*11 stringi2 /' VALUES (?)'/
equivalence( insert_string(1), stringi1 )
equivalence( insert_string(13), var_string2 )
equivalence( insert_string(23), stringi2 )
equivalence( insert_string(1), insert_string_tmp )
* Declare Miscellaneous Variables
integer*2 cntr
integer*2 rc
* Copy io_sqlda to in_sqlda
cntr = 0
do 20 while ( cntr .lt. (sqlda_header_sz + 4*sqlvar_struct_sz) )
cntr = cntr + 1
in_sqlda(cntr) = io_sqlda(cntr)
20 end do
* Program Logic
EXEC SQL WHENEVER SQLERROR CONTINUE
* Create "President" Table
rc = sqlgdref ( %val(in_sqllen1), %ref(var_string1),
+ %ref(in_sqldata1) )
sql_string = create_string_tmp
EXEC SQL EXECUTE IMMEDIATE :sql_string (2)
EXEC SQL WHENEVER SQLERROR GOTO 100
* Prepare for Insert
var_string2 = var_string1
sql_string = insert_string_tmp
EXEC SQL PREPARE insertstmt FROM :sql_string (3)
* Insert President Names stored in Input SQLDA into Newly Created Table
rc = sqlgdref( %val(in_sqllen2),%ref(insert_data),
+ %ref(in_sqldata2) )
EXEC SQL EXECUTE insertstmt USING :insert_data (4)
rc = sqlgdref( %val(in_sqllen3),%ref(insert_data),
+ %ref(in_sqldata3) )
EXEC SQL EXECUTE insertstmt USING :insert_data
rc = sqlgdref( %val(in_sqllen4),%ref(insert_data),
+ %ref(in_sqldata4) )
EXEC SQL EXECUTE insertstmt USING :insert_data
EXEC SQL COMMIT
* Copy Local Variables into Dummy Output Variables
* Copy sqlca to ca
cntr = 0
do 30 while ( cntr .lt. sqlca_size )
cntr = cntr + 1
ca(cntr) = sqlca(cntr)
30 end do
goto 200
100 continue ! An Error has Occurred
* Copy Local Variables into Dummy Output Variables
* Copy sqlca to ca
EXEC SQL WHENEVER SQLERROR CONTINUE
cntr = 0
do 40 while ( cntr .lt. sqlca_size )
cntr = cntr + 1
ca(cntr) = sqlca(cntr)
40 end do
EXEC SQL ROLLBACK
200 continue ! Exit Program
inpsrv = sqlz_disconnect_proc
return (5)
end
/* REXX INPut SeRVer */
/* this variable (SYSTEM) must be user defined */
SYSTEM = OS2
if SYSTEM = OS2 then do
if rxfuncquery('SQLDBS') <> 0 then
rcy = rxfuncadd( 'SQLDBS', 'DB2AR', 'SQLDBS' )
if rxfuncquery('SQLEXEC') <> 0 then
rcy = rxfuncadd( 'SQLEXEC', 'DB2AR', 'SQLEXEC' )
end
if SYSTEM = AIX then
rcy = SysAddFuncPkg("/usr/lpp/db2_05_00/lib/db2rexx")
table_stmt = 'CREATE TABLE ' || sqlrida.1.sqldata || ' (name CHAR(20))' (2)
call SQLEXEC 'EXECUTE IMMEDIATE :table_stmt'
if sqlca.sqlcode = -601 then
sqlca.sqlcode = 0
call CHECKERR 'EXECUTE IMMEDIATE CREATE TABLE'
insert_stmt = 'INSERT INTO ' || sqlrida.1.sqldata || ' VALUES (?)'
call SQLEXEC 'PREPARE s1 FROM :insert_stmt' (3)
call CHECKERR 'PREPARE INSERT STATEMENT'
cntr = 1
do while ( cntr < SQLRIDA.sqld & sqlca.sqlcode = 0 )
cntr = cntr + 1
insert_data = SQLRIDA.cntr.sqldata
call SQLEXEC 'EXECUTE s1 USING :insert_data' (4)
end
call CHECKERR 'EXECUTION OF INSERT STATEMENT'
call SQLEXEC 'COMMIT'
call CHECKERR 'COMMIT'
exit 0 (5)
CHECKERR:
arg errloc
if ( SQLCA.SQLCODE = 0 ) then
return 0
else
say '--- error report ---'
say 'ERROR occured :' errloc
say 'SQLCODE :' SQLCA.SQLCODE
/******************************\
* GET ERROR MESSAGE API called *
\******************************/
call SQLDBS 'GET MESSAGE INTO :errmsg LINEWIDTH 80'
say errmsg
say '--- end error report ---'
if (SQLCA.SQLCODE < 0 ) then
exit
else
say 'WARNING - CONTINUING PROGRAM WITH ERRORS'
return 0
end
end
return 0