G.1 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;
}
G.2 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);
}
