Example: SQL statements in ILE C and C++ programs
This example program is written in the C programming language.
The same program would work in C++ if the following conditions are true:
- An SQL BEGIN DECLARE SECTION statement was added before line 18
- An SQL END DECLARE SECTION statement was added after line 42
Note: By using the code examples, you agree to the terms of the Code license and disclaimer information.
xxxxST1 VxRxMx yymmdd Create SQL ILE C Object CEX 08/06/07 15:52:26 Page 1
Source type...............C
Object name...............CORPDATA/CEX
Source file...............CORPDATA/SRC
Member....................CEX
To source file............QTEMP/QSQLTEMP
Options...................*XREF
Listing option............*PRINT
Target release............VxRxMx
INCLUDE file..............*SRCFILE
Commit....................*CHG
Allow copy of data........*YES
Close SQL cursor..........*ENDACTGRP
Allow blocking............*READ
Delay PREPARE.............*NO
Generation level..........10
Margins...................*SRCFILE
Printer file..............*LIBL/QSYSPRT
Date format...............*JOB
Date separator............*JOB
Time format...............*HMS
Time separator ...........*JOB
Replace...................*YES
Relational database.......*LOCAL
User .....................*CURRENT
RDB connect method........*DUW
Default collection........*NONE
Dynamic default
collection..............*NO
Package name..............*OBJLIB/*OBJ
Path......................*NAMING
SQL rules.................*DB2
Created object type.......*PGM
Debugging view............*NONE
User profile..............*NAMING
Dynamic user profile......*USER
Sort sequence.............*JOB
Language ID...............*JOB
IBM SQL flagging..........*NOFLAG
ANS flagging..............*NONE
Text......................*SRCMBRTXT
Source file CCSID.........65535
Job CCSID.................65535
Decimal result options:
Maximum precision.......31
Maximum scale...........31
Minimum divide scale....0
DECFLOAT rounding mode....*HALFEVEN
Compiler options..........*NONE
Source member changed on 06/06/00 17:15:17
xxxxST1 VxRxMx yymmdd Create SQL ILE C Object CEX 08/06/07 15:52:26 Page 2
Record *...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8 SEQNBR Last change
1 #include "string.h" 100
2 #include "stdlib.h" 200
3 #include "stdio.h" 300
4 400
5 main() 500
6 { 600
7 /* A sample program which updates the salaries for those employees */ 700
8 /* whose current commission total is greater than or equal to the */ 800
9 /* value of 'commission'. The salaries of those who qualify are */ 900
10 /* increased by the value of 'percentage', retroactive to 'raise_date'.*/ 1000
11 /* A report is generated showing the projects that these employees */ 1100
12 /* have contributed to, ordered by project number and employee ID. */ 1200
13 /* A second report shows each project having an end date occurring */ 1300
14 /* after 'raise_date' (is potentially affected by the retroactive */ 1400
15 /* raises) with its total salary expenses and a count of employees */ 1500
16 /* who contributed to the project. */ 1600
17 1700
18 short work_days = 253; /* work days during in one year */ 1800
19 float commission = 2000.00; /* cutoff to qualify for raise */ 1900
20 float percentage = 1.04; /* raised salary as percentage */ 2000
21 char raise_date??(12??) = "1982-06-01"; /* effective raise date */ 2100
22 2200
23 /* File declaration for qprint */ 2300
24 FILE *qprint; 2400
25 2500
26 /* Structure for report 1 */ 2600
27 1 #pragma mapinc ("project","CORPDATA/PROJECT(PROJECT)","both","p z") 2700
28 #include "project" 2800
29 struct { 2900
30 CORPDATA_PROJECT_PROJECT_both_t Proj_struct; 3000
31 char empno??(7??); 3100
32 char name??(30??); 3200
33 float salary; 3300
34 } rpt1; 3400
35 3500
36 /* Structure for report 2 */ 3600
37 struct { 3700
38 char projno??(7??); 3800
39 char project_name??(37??); 3900
40 short employee_count; 4000
41 double total_proj_cost; 4100
42 } rpt2; 4200
43 4300
44 2 exec sql include SQLCA; 4400
45 4500
46 qprint=fopen("QPRINT","w"); 4600
47 4700
48 /* Update the selected projects by the new percentage. If an error */ 4800
49 /* occurs during the update, ROLLBACK the changes. */ 4900
50 3 EXEC SQL WHENEVER SQLERROR GO TO update_error; 5000
51 4 EXEC SQL 5100
52 UPDATE CORPDATA/EMPLOYEE 5200
53 SET SALARY = SALARY * :percentage 5300
54 WHERE COMM >= :commission ; 5400
55 5500
56 /* Commit changes */ 5600
57 5 EXEC SQL 5700
58 COMMIT; 5800
59 EXEC SQL WHENEVER SQLERROR GO TO report_error; 5900
60 6000
xxxxST1 VxRxMx yymmdd Create SQL ILE C Object CEX 08/06/07 15:52:26 Page 3
Record *...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8 SEQNBR Last change
61 /* Report the updated statistics for each employee assigned to the */ 6100
62 /* selected projects. */ 6200
63 6300
64 /* Write out the header for Report 1 */ 6400
65 fprintf(qprint," REPORT OF PROJECTS AFFECTED \ 6500
66 BY RAISES"); 6600
67 fprintf(qprint,"\n\nPROJECT EMPID EMPLOYEE NAME "); 6700
68 fprintf(qprint, " SALARY\n"); 6800
69 6900
70 6 exec sql 7000
71 declare c1 cursor for 7100
72 select distinct projno, empprojact.empno, 7200
73 lastname||', '||firstnme, salary 7300
74 from corpdata/empprojact, corpdata/employee 7400
75 where empprojact.empno = employee.empno and comm >= :commission 7500
76 order by projno, empno; 7600
77 7 EXEC SQL 7700
78 OPEN C1; 7800
79 7900
80 /* Fetch and write the rows to QPRINT */ 8000
81 8 EXEC SQL WHENEVER NOT FOUND GO TO done1; 8100
82 8200
83 do { 8300
84 10 EXEC SQL 8400
85 FETCH C1 INTO :Proj_struct.PROJNO, :rpt1.empno, 8500
86 :rpt1.name,:rpt1.salary; 8600
87 fprintf(qprint,"\n%6s %6s %-30s %8.2f", 8700
88 rpt1.Proj_struct.PROJNO,rpt1.empno, 8800
89 rpt1.name,rpt1.salary); 8900
90 } 9000
91 while (SQLCODE==0); 9100
92 9200
93 done1: 9300
94 EXEC SQL 9400
95 CLOSE C1; 9500
96 9600
97 /* For all projects ending at a date later than the 'raise_date' * / 9700
98 /* (that is, those projects potentially affected by the salary raises), */ 9800
99 /* generate a report containing the project number, project name */ 9900
100 /* the count of employees participating in the project, and the */ 10000
101 /* total salary cost of the project. */ 10100
102 10200
103 /* Write out the header for Report 2 */ 10300
104 fprintf(qprint,"\n\n\n ACCUMULATED STATISTICS\ 10400
105 BY PROJECT"); 10500
106 fprintf(qprint, "\n\nPROJECT \ 10600
107 NUMBER OF TOTAL"); 10700
108 fprintf(qprint, "\nNUMBER PROJECT NAME \ 10800
109 EMPLOYEES COST\n"); 10900
110 11000
111 11 EXEC SQL 11100
112 DECLARE C2 CURSOR FOR 11200
113 SELECT EMPPROJACT.PROJNO, PROJNAME, COUNT(*), 11300
114 SUM ( ( DAYS(EMENDATE) - DAYS(EMSTDATE) ) * EMPTIME * 11400
115 (DECIMAL( SALARY / :work_days ,8,2))) 11500
116 FROM CORPDATA/EMPPROJACT, CORPDATA/PROJECT, CORPDATA/EMPLOYEE 11600
117 WHERE EMPPROJACT.PROJNO=PROJECT.PROJNO AND 11700
118 EMPPROJACT.EMPNO =EMPLOYEE.EMPNO AND 11800
119 PRENDATE > :raise_date 11900
120 GROUP BY EMPPROJACT.PROJNO, PROJNAME 12000
121 ORDER BY 1; 12100
122 EXEC SQL 12200
123 OPEN C2; 12300 xxxxST1 VxRxMx yymmdd Create SQL ILE C Object CEX 08/06/07 15:52:26 Page 4
Record *...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8 SEQNBR Last change
124 12400
125 /* Fetch and write the rows to QPRINT */ 12500
126 EXEC SQL WHENEVER NOT FOUND GO TO done2; 12600
127 12700
128 do { 12800
129 12 EXEC SQL 12900
130 FETCH C2 INTO :rpt2; 13000
131 fprintf(qprint,"\n%6s %-36s %6d %9.2f", 13100
132 rpt2.projno,rpt2.project_name,rpt2.employee_count, 13200
133 rpt2.total_proj_cost); 13300
134 } 13400
135 while (SQLCODE==0); 13500
136 13600
137 done2: 13700
138 EXEC SQL 13800
139 CLOSE C2; 13900
140 goto finished; 14000
141 14100
142 /* Error occurred while updating table. Inform user and rollback */ 14200
143 /* changes. */ 14300
144 update_error: 14400
145 13 EXEC SQL WHENEVER SQLERROR CONTINUE; 14500
146 fprintf(qprint,"*** ERROR Occurred while updating table. SQLCODE=" 14600
147 "%5d\n",SQLCODE); 14700
148 14 EXEC SQL 14800
149 ROLLBACK; 14900
150 goto finished; 15000
151 15100
152 /* Error occurred while generating reports. Inform user and exit. */ 15200
153 report_error: 15300
154 fprintf(qprint,"*** ERROR Occurred while generating reports. " 15400
155 "SQLCODE=%5d\n",SQLCODE); 15500
156 goto finished; 15600
157 15700
158 /* All done */ 15800
159 finished: 15900
160 fclose(qprint); 16000
161 exit(0); 16100
162 16200
163 } 16300
* * * * * E N D O F S O U R C E * * * * *
xxxxST1 VxRxMx yymmdd Create SQL ILE C Object CEX 08/06/07 15:52:26 Page 5
CROSS REFERENCE
Data Names Define Reference
commission 19 FLOAT(24)
54 75
done1 **** LABEL
81
done2 **** LABEL
126
employee_count 40 SMALL INTEGER PRECISION(4,0) IN rpt2
empno 31 VARCHAR(7) IN rpt1
85
name 32 VARCHAR(30) IN rpt1
86
percentage 20 FLOAT(24)
53
project_name 39 VARCHAR(37) IN rpt2
projno 38 VARCHAR(7) IN rpt2
raise_date 21 VARCHAR(12)
119
report_error **** LABEL
59
rpt1 34
rpt2 42 STRUCTURE
130
salary 33 FLOAT(24) IN rpt1
86
total_proj_cost 41 FLOAT(53) IN rpt2
update_error **** LABEL
50
work_days 18 SMALL INTEGER PRECISION(4,0)
115
ACTNO 74 SMALL INTEGER PRECISION(4,0) COLUMN (NOT NULL) IN CORPDATA.EMPPROJACT
BIRTHDATE 74 DATE(10) COLUMN IN CORPDATA.EMPLOYEE
BONUS 74 DECIMAL(9,2) COLUMN IN CORPDATA.EMPLOYEE
COMM **** COLUMN
54 75
COMM 74 DECIMAL(9,2) COLUMN IN CORPDATA.EMPLOYEE
CORPDATA **** SCHEMA
52 74 74 116 116 116
C1 71 CURSOR
78 85 95
C2 112 CURSOR
123 130 139
DEPTNO 27 VARCHAR(3) IN Proj_struct
DEPTNO 116 CHARACTER(3) COLUMN (NOT NULL) IN CORPDATA.PROJECT
EDLEVEL 74 SMALL INTEGER PRECISION(4,0) COLUMN (NOT NULL) IN CORPDATA.EMPLOYEE
EMENDATE 74 DATE(10) COLUMN IN CORPDATA.EMPPROJACT
EMENDATE **** COLUMN
114
EMPLOYEE **** TABLE IN CORPDATA
52 74 116
EMPLOYEE **** TABLE
75 118
EMPNO **** COLUMN IN EMPPROJACT
72 75 76 118
EMPNO **** COLUMN IN EMPLOYEE
75 118
EMPNO 74 CHARACTER(6) COLUMN (NOT NULL) IN CORPDATA.EMPPROJACT
EMPNO 74 CHARACTER(6) COLUMN (NOT NULL) IN CORPDATA.EMPLOYEE
EMPPROJACT **** TABLE
72 75 113 117 118 120
EMPPROJACT **** TABLE IN CORPDATA
74 116
xxxxST1 VxRxMx yymmdd Create SQL ILE C Object CEX 08/06/07 15:52:26 Page 6
CROSS REFERENCE
EMPTIME 74 DECIMAL(5,2) COLUMN IN CORPDATA.EMPPROJACT
EMPTIME **** COLUMN
114
EMSTDATE 74 DATE(10) COLUMN IN CORPDATA.EMPPROJACT
EMSTDATE **** COLUMN
114
FIRSTNME **** COLUMN
73
FIRSTNME 74 VARCHAR(12) COLUMN (NOT NULL) IN CORPDATA.EMPLOYEE
HIREDATE 74 DATE(10) COLUMN IN CORPDATA.EMPLOYEE
JOB 74 CHARACTER(8) COLUMN IN CORPDATA.EMPLOYEE
LASTNAME **** COLUMN
73
LASTNAME 74 VARCHAR(15) COLUMN (NOT NULL) IN CORPDATA.EMPLOYEE
MAJPROJ 27 VARCHAR(6) IN Proj_struct
MAJPROJ 116 CHARACTER(6) COLUMN IN CORPDATA.PROJECT
MIDINIT 74 CHARACTER(1) COLUMN (NOT NULL) IN CORPDATA.EMPLOYEE
Proj_struct 30 STRUCTURE IN rpt1
PHONENO 74 CHARACTER(4) COLUMN IN CORPDATA.EMPLOYEE
PRENDATE 27 DATE(10) IN Proj_struct
PRENDATE **** COLUMN
119
PRENDATE 116 DATE(10) COLUMN IN CORPDATA.PROJECT
PROJECT **** TABLE IN CORPDATA
116
PROJECT **** TABLE
117
PROJNAME 27 VARCHAR(24) IN Proj_struct
PROJNAME **** COLUMN
113 120
PROJNAME 116 VARCHAR(24) COLUMN (NOT NULL) IN CORPDATA.PROJECT
PROJNO 27 VARCHAR(6) IN Proj_struct
85
PROJNO **** COLUMN
72 76
PROJNO 74 CHARACTER(6) COLUMN (NOT NULL) IN CORPDATA.EMPPROJACT
PROJNO **** COLUMN IN EMPPROJACT
113 117 120
PROJNO **** COLUMN IN PROJECT
117
PROJNO 116 CHARACTER(6) COLUMN (NOT NULL) IN CORPDATA.PROJECT
PRSTAFF 27 DECIMAL(5,2) IN Proj_struct
PRSTAFF 116 DECIMAL(5,2) COLUMN IN CORPDATA.PROJECT
PRSTDATE 27 DATE(10) IN Proj_struct
PRSTDATE 116 DATE(10) COLUMN IN CORPDATA.PROJECT
RESPEMP 27 VARCHAR(6) IN Proj_struct
RESPEMP 116 CHARACTER(6) COLUMN (NOT NULL) IN CORPDATA.PROJECT
SALARY **** COLUMN
53 53 73 115
SALARY 74 DECIMAL(9,2) COLUMN IN CORPDATA.EMPLOYEE
SEX 74 CHARACTER(1) COLUMN IN CORPDATA.EMPLOYEE
WORKDEPT 74 CHARACTER(3) COLUMN IN CORPDATA.EMPLOYEE
No errors found in source
163 Source records processed
* * * * * E N D O F L I S T I N G * * * * *