This appendix contains sample applications that can be used with the gateway.
This appendix includes the following examples:
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)); }
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); }