Skip Headers
Oracle® Transparent Gateway for DRDA Installation and User's Guide
10g Release 2 (10.2) for Microsoft Windows

Part Number B16218-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

G Sample Applications

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

DB2INS

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

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