This appendix contains sample applications that can be used with the gateway:
DB2INS
is a sample DB2 stored procedure that inserts a row into a DB2 table. This procedure uses the SIMPLE linkage convention.
/*********************************************************************/ /* */ /* This DB2 stored procedure inserts values for the DNAME and LOC */ /* columns of DB2 user table SCOTT.DEPT. */ /* */ /* The 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. */ /* */ /* 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 */ /* ('DB2INS', ' ', ' ', 'DB2INS', ' ', '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 */ 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]); /*********************************************************************/ /* Issue SQL INSERT to insert a row into SCOTT.DEPT */ /*********************************************************************/ EXEC SQL INSERT INTO SCOTT.DEPT VALUES(:dno, :dname, :locale); /*********************************************************************/ /* Copy SQLCODE to the output parameter list. */ /*********************************************************************/ *(int *) argv[4] = SQLCODE; }
ORAIND
is a sample host program that calls a DB2 stored procedure (DB2INS) to insert a row into a DB2 table.
/*********************************************************************/ /* This sample ProC program calls DB2 stored procedure DB2INS to */ /* insert values into the DB2 user table SCOTT.DEPT. This calling */ /* program uses embedded PL/SQL to call the stored procedure. */ /*********************************************************************/ #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]; int x; EXEC SQL END DECLARE SECTION; EXEC SQL INCLUDE SQLCA; main() { /*********************************************************************/ /* Setup Oracle user id 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; EXEC SQL COMMIT; /*------------------------ begin pl/sql block -----------------------*/ /*********************************************************************/ /* Insert 1 row into DB2 table SCOTT.DEPT by invoking DB2 stored */ /* procedure DB2INS. 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 BEGIN :dept_no := 10; :dept_name := 'GATEWAY'; :location := 'ORACLE'; DB2INS@GTWLINK(:dept_no, :dept_name, :location, :code); END; END-EXEC; /*------------------------- end pl/sql block ------------------------*/ /*********************************************************************/ /* Check the SQLCODE returned from the stored procedures INSERT. */ /*********************************************************************/ if (code == 0) printf("DB2INS reports successful INSERT\n"); else { printf("DB2INS reports error on INSERT.\nSQLCODE=%d\n",code); goto sqlerror; } /*********************************************************************/ /* Verify row insertion. Query the data just inserted. */ /*********************************************************************/ EXEC SQL SELECT deptno, dname, loc INTO :dept_no, :dept_name, :location FROM SCOTT.DEPT@GTWLINK WHERE deptno = 10; printf("\nData INSERTed was:\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); }