This appendix contains sample applications that can be used with the gateway.
E.1 DB2IND
DB2IND is a sample DB2 stored procedure that inserts a row into a DB2 table. This procedure uses the SIMPLE WITH NULLS linkage convention.
/*********************************************************************/
/* This DB2 stored procedure uses indicator variables to insert null */
/* values for DNAME and LOC columns of DB2 user table SCOTT.DEPT. */
/* SCOTT.DEPT table is defined to DB2 as DEPTNO INTEGER, DNAME */
/* CHAR(14), LOC VARCHAR(13). This procedure receives 3 input */
/* parameters from the calling program which contain the values to */
/* insert for DEPTNO, DNAME, and LOC. */
/* */
/* The linkage convention used for this stored procedure is SIMPLE */
/* WITH NULLS. */
/* */
/* The output parameter for this procedure contains the SQLCODE from */
/* the INSERT operation. */
/* */
/* The entry in the DB2 catalog table SYSIBM.SYSPROCEDURES for this */
/* stored procedure might look like this: */
/* */
/* INSERT INTO SYSIBM.SYSPROCEDURES */
/* (PROCEDURE, AUTHID, LUNAME, LOADMOD, LINKAGE, COLLID, LANGUAGE, */
/* ASUTIME, STAYRESIDENT, IBMREQD, RUNOPTS, PARMLIST) */
/* VALUES */
/* ('DB2IND', ' ', ' ', 'DB2IND', 'N', 'DB2DEV', 'C', '0', ' ', */
/* 'N', ' ', 'A INT IN, B CHAR(14) IN, C VARCHAR(13) IN, */
/* D INT OUT, E CHAR(10) OUT'); */
/*********************************************************************/
#pragma runopts(plist(os))
#include <stdlib.h>
EXEC SQL INCLUDE SQLCA;
/*********************************************************************/
/* Declare C variables for SQL operations on the parameters. These */
/* are local variables to the C program which you must copy to and */
/* from the parameter list provided to the stored procedure. */
/*********************************************************************/
EXEC SQL BEGIN DECLARE SECTION;
long dno; /* input parm - DEPTNO */
char dname[15]; /* input parm - DNAME */
char locale[14]; /* input parm - LOC */
struct INDICATORS {
short int i1;
short int i2;
short int i3;
short int o;
} indvar; /* indicator variable structure */
EXEC SQL END DECLARE SECTION;
main(argc,argv)
int argc;
char *argv[];
{
/*********************************************************************/
/* Copy the input parameters into the area reserved in the local */
/* program for SQL processing. */
/*********************************************************************/
dno = *(int *) argv[1];
strcpy(dname, argv[2]);
strcpy(locale, argv[3]);
/*********************************************************************/
/* Copy indicator variable values for the parameter list. */
/*********************************************************************/
memcpy(&indvar,(struct INDICATORS *) argv[6], sizeof(indvar));
/*********************************************************************/
/* Issue SQL INSERT to insert a row into SCOTT.DEPT */
/*********************************************************************/
EXEC SQL INSERT INTO SCOTT.DEPT VALUES
(:dno:indvar.i1, :dname:indvar.i2, :locale:indvar.i3);
/*********************************************************************/
/* Copy SQLCODE to the output parameter list. */
/*********************************************************************/
*(int *) argv[4] = SQLCODE;
indvar.o = 0;
/*********************************************************************/
/* Copy indicator variable values back to the output parameter list. */
/*********************************************************************/
memcpy( (struct INDICATORS *) argv[6], &indvar, sizeof(indvar));
}
E.2 ORAIND
ORAIND is a sample host program that calls a DB2 stored procedure (DB2IND) to insert a row into a DB2 table. Embedded PL/SQL is used to manipulate the indictor variables.
/*********************************************************************/
/* This sample ProC program calls DB2 stored procedure DB2IND to */
/* insert null values into DB2 user table SCOTT.DEPT. This calling */
/* program uses embedded PL/SQL to pass indicator variables in the */
/* parameter list of the DB2 stored procedure call. */
/*********************************************************************/
#include <stdio.h>
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR username[20];
VARCHAR password[20];
int dept_no;
char dept_name[14];
VARCHAR location[13];
int code;
char buf[11];
short ind1;
short ind2;
short ind3;
short oind;
int x;
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE SQLCA;
main()
{
/*********************************************************************/
/* Setup Oracle userid and password */
/*********************************************************************/
strcpy(username.arr, "SCOTT"); /* copy the username */
username.len = strlen(username.arr);
strcpy(password.arr, "TIGER"); /* copy the password */
password.len = strlen(password.arr);
EXEC SQL WHENEVER SQLERROR GOTO sqlerror;
/*********************************************************************/
/* Logon to Oracle */
/*********************************************************************/
EXEC SQL CONNECT :username IDENTIFIED BY :password;
printf("\nConnected to ORACLE as user: %s\n", username.arr);
/* Delete any existing rows from DB2 table */
EXEC SQL DELETE from SCOTT.DEPT@GTWLINK where LOC='INDVARS';
EXEC SQL COMMIT;
/*------------------------ begin pl/sql block -----------------------*/
/*********************************************************************/
/* Insert 5 rows into DB2 table SCOTT.DEPT by invoking DB2 stored */
/* procedure DB2IND. Use indicator variables to pass null values to */
/* the stored procedure. The DB2 stored procedure will perform the */
/* INSERT. */
/* */
/* SCOTT.DEPT table is defined on DB2 as: */
/* */
/* DEPTNO INTEGER; */
/* DNAME CHAR(14); */
/* LOC VARCHAR(13); */
/* */
/*********************************************************************/
EXEC SQL EXECUTE
DECLARE
buf char(10);
BEGIN
for i in 1 .. 5 loop
:dept_no:ind1 := 10 * i;
:dept_name:ind2 := null;
:location:ind3 := null;
SYSPROC.DB2IND@GTWLINK
(:dept_no:ind1, :dept_name:ind2, :location:ind3, :code:oind, buf);
end loop;
END;
END-EXEC;
/*------------------------- end pl/sql block ------------------------*/
/*********************************************************************/
/* Verify row insertion. Use indicator variables to check columns */
/* for null values. Update the column with a value if column is */
/* null. */
/*********************************************************************/
for (x = 10; x < 60; x = x + 10)
{
EXEC SQL SELECT deptno, dname, loc into
:dept_no:ind1, :dept_name:ind2, :location:ind3
from SCOTT.DEPT@GTWLINK where deptno = :x;
if ((ind2 == -1) && (ind3 == -1))
{
printf("\nAfter INSERT\n");
printf("\ndeptno = %d, dname = NULL, loc = NULL\n", dept_no);
EXEC SQL UPDATE SCOTT.DEPT@GTWLINK set dname = 'TESTING',
loc = 'INDVARS' where deptno = :x;
EXEC SQL COMMIT;
}
EXEC SQL SELECT deptno, dname, loc into
:dept_no:ind1, :dept_name:ind2, :location:ind3
from SCOTT.DEPT@GTWLINK where deptno = :x;
printf("\nAfter UPDATE:\n");
printf("\ndeptno = %d, dname = %s, loc = %s\n",
dept_no, dept_name, location.arr);
}
/*********************************************************************/
/* Logoff from Oracle */
/*********************************************************************/
EXEC SQL COMMIT RELEASE;
printf("\n\nHave a good day\n\n");
exit(0);
sqlerror:
printf("\n% .70s \n", sqlca.sqlerrm.sqlerrmc);
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL ROLLBACK RELEASE;
exit(1);
}
