C プログラムにおける動的および静的 SQL の例
Db2 にアクセスするプログラムには、静的 SQL、動的 SQL、またはその両方が含まれます。
この例では、静的 SQL と動的 SQL を両方とも含む C プログラムを示します。
次の図では、C プログラムに組み込まれている動的 SQL および静的 SQL を説明します。 プログラムの各セクションはコメントを付けて区別されています。 プログラムのセクション 1 は静的 SQL を示し、 セクション 2、3、4 は動的 SQL を示しています。 各セクションの機能は、プログラムのプロローグに詳しく説明されています。
/*********************************************************************/
/* Descriptive name = Dynamic SQL sample using C language */
/* */
/* Function = To show examples of the use of dynamic and static */
/* SQL. */
/* */
/* Notes = This example assumes that the EMP and DEPT tables are */
/* defined. They need not be the same as the DB2 Sample */
/* tables. */
/* */
/* Module type = C program */
/* Processor = DB2 precompiler, C compiler */
/* Module size = see link edit */
/* Attributes = not reentrant or reusable */
/* */
/* Input = */
/* */
/* symbolic label/name = DEPT */
/* description = arbitrary table */
/* symbolic label/name = EMP */
/* description = arbitrary table */
/* */
/* Output = */
/* */
/* symbolic label/name = SYSPRINT */
/* description = print results via printf */
/* */
/* Exit-normal = return code 0 normal completion */
/* */
/* Exit-error = */
/* */
/* Return code = SQLCA */
/* */
/* Abend codes = none */
/* */
/* External references = none */
/* */
/* Control-blocks = */
/* SQLCA - sql communication area */
/* */
/* Logic specification: */
/* */
/* There are four SQL sections. */
/* */
/* 1) STATIC SQL 1: using static cursor with a SELECT statement. */
/* Two output host variables. */
/* 2) Dynamic SQL 2: Fixed-list SELECT, using same SELECT statement */
/* used in SQL 1 to show the difference. The prepared string */
/* :iptstr can be assigned with other dynamic-able SQL statements.*/
/* 3) Dynamic SQL 3: Insert with parameter markers. */
/* Using four parameter markers which represent four input host */
/* variables within a host structure. */
/* 4) Dynamic SQL 4: EXECUTE IMMEDIATE */
/* A GRANT statement is executed immediately by passing it to DB2 */
/* via a varying string host variable. The example shows how to */
/* set up the host variable before passing it. */
/* */
/*********************************************************************/
#include "stdio.h"
#include "stdefs.h"
EXEC SQL INCLUDE SQLCA;
EXEC SQL INCLUDE SQLDA;
EXEC SQL BEGIN DECLARE SECTION;
short edlevel;
struct { short len;
char x1??(56??);
} stmtbf1, stmtbf2, inpstr;
struct { short len;
char x1??(15??);
} lname;
short hv1;
struct { char deptno??(4??);
struct { short len;
char x??(36??);
} deptname;
char mgrno??(7??);
char admrdept??(4??);
char location??(17??);
} hv2;
short ind??(4??);
EXEC SQL END DECLARE SECTION;
EXEC SQL DECLARE EMP TABLE
(EMPNO CHAR(6) ,
FIRSTNAME VARCHAR(12) ,
MIDINIT CHAR(1) ,
LASTNAME VARCHAR(15) ,
WORKDEPT CHAR(3) ,
PHONENO CHAR(4) ,
HIREDATE DECIMAL(6) ,
JOBCODE DECIMAL(3) ,
EDLEVEL SMALLINT ,
SEX CHAR(1) ,
BIRTHDATE DECIMAL(6) ,
SALARY DECIMAL(8,2) ,
FORFNAME VARGRAPHIC(12) ,
FORMNAME GRAPHIC(1) ,
FORLNAME VARGRAPHIC(15) ,
FORADDR VARGRAPHIC(256) ) ;
EXEC SQL DECLARE DEPT TABLE
(
DEPTNO CHAR(3) ,
DEPTNAME VARCHAR(36) ,
MGRNO CHAR(6) ,
ADMRDEPT CHAR(3) ,
LOCATION CHAR(16));
main ()
{
printf("??/n*** begin of program ***");
EXEC SQL WHENEVER SQLERROR GO TO HANDLERR;
EXEC SQL WHENEVER SQLWARNING GO TO HANDWARN;
EXEC SQL WHENEVER NOT FOUND GO TO NOTFOUND;
/******************************************************************/
/* Assign values to host variables which will be input to DB2 */
/******************************************************************/
strcpy(hv2.deptno,"M92");
strcpy(hv2.deptname.x,"DDL");
hv2.deptname.len = strlen(hv2.deptname.x);
strcpy(hv2.mgrno,"000010");
strcpy(hv2.admrdept,"A00");
/******************************************************************/
/* Static SQL 1: DECLARE CURSOR, OPEN, FETCH, CLOSE */
/* Select into :edlevel, :lname */
/******************************************************************/
printf("??/n*** begin declare ***");
EXEC SQL DECLARE C1 CURSOR FOR SELECT EDLEVEL, LASTNAME FROM EMP
WHERE EMPNO = '000010';
printf("??/n*** begin open ***");
EXEC SQL OPEN C1;
printf("??/n*** begin fetch ***");
EXEC SQL FETCH C1 INTO :edlevel, :lname;
printf("??/n*** returned values ***");
printf("??/n??/nedlevel = %d",edlevel);
printf("??/nlname = %s\n",lname.x1);
printf("??/n*** begin close ***");
EXEC SQL CLOSE C1;
/******************************************************************/
/* Dynamic SQL 2: PREPARE, DECLARE CURSOR, OPEN, FETCH, CLOSE */
/* Select into :edlevel, :lname */
/******************************************************************/
sprintf (inpstr.x1,
"SELECT EDLEVEL, LASTNAME FROM EMP WHERE EMPNO = '000010'");
inpstr.len = strlen(inpstr.x1);
printf("??/n*** begin prepare ***");
EXEC SQL PREPARE STAT1 FROM :inpstr;
printf("??/n*** begin declare ***");
EXEC SQL DECLARE C2 CURSOR FOR STAT1;
printf("??/n*** begin open ***");
EXEC SQL OPEN C2;
printf("??/n*** begin fetch ***");
EXEC SQL FETCH C2 INTO :edlevel, :lname;
printf("??/n*** returned values ***");
printf("??/n??/nedlevel = %d",edlevel);
printf("??/nlname = %s??/n",lname.x1);
printf("??/n*** begin close ***");
EXEC SQL CLOSE C2;
/******************************************************************/
/* Dynamic SQL 3: PREPARE with parameter markers */
/* Insert into with five values. */
/******************************************************************/
sprintf (stmtbf1.x1,
"INSERT INTO DEPT VALUES (?,?,?,?,?)");
stmtbf1.len = strlen(stmtbf1.x1);
printf("??/n*** begin prepare ***");
EXEC SQL PREPARE s1 FROM :stmtbf1;
printf("??/n*** begin execute ***");
EXEC SQL EXECUTE s1 USING :hv2:ind;
printf("??/n*** following are expected insert results ***");
printf("??/n hv2.deptno = %s",hv2.deptno);
printf("??/n hv2.deptname.len = %d",hv2.deptname.len);
printf("??/n hv2.deptname.x = %s",hv2.deptname.x);
printf("??/n hv2.mgrno = %s",hv2.mgrno);
printf("??/n hv2.admrdept = %s",hv2.admrdept);
printf("??/n hv2.location = %s",hv2.location);
EXEC SQL COMMIT;
/******************************************************************/
/* Dynamic SQL 4: EXECUTE IMMEDIATE */
/* Grant select */
/******************************************************************/
sprintf (stmtbf2.x1,
"GRANT SELECT ON EMP TO USERX");
stmtbf2.len = strlen(stmtbf2.x1);
printf("??/n*** begin execute immediate ***");
EXEC SQL EXECUTE IMMEDIATE :stmtbf2;
printf("??/n*** end of program ***");
goto progend;
HANDWARN: HANDLERR: NOTFOUND: ;
printf("??/n SQLCODE = %d",SQLCODE);
printf("??/n SQLWARN0 = %c",SQLWARN0);
printf("??/n SQLWARN1 = %c",SQLWARN1);
printf("??/n SQLWARN2 = %c",SQLWARN2);
printf("??/n SQLWARN3 = %c",SQLWARN3);
printf("??/n SQLWARN4 = %c",SQLWARN4);
printf("??/n SQLWARN5 = %c",SQLWARN5);
printf("??/n SQLWARN6 = %c",SQLWARN6);
printf("??/n SQLWARN7 = %c",SQLWARN7);
printf("??/n SQLERRMC = %s",sqlca.sqlerrmc);
progend: ;
}