E Sample Applications

This appendix contains sample applications that can be used with the gateway.

This appendix includes the following examples:

• DB2IND

• ORAIND

/*********************************************************************/
/* 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));
   }

/*********************************************************************/
/* 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);
}