This section provides an example of an embedded SQL UAP written in C language. For details about the SQL syntax, see the HiRDB Version 8 SQL Reference manual.
Figures 7-2 and 7-3 show a flowchart of the program example.
Figure 7-2 Flowchart example of an embedded SQL UAP written in C
Figure 7-3 Flowchart example of an embedded SQL UAP written in C
A coding example of an embedded SQL UAP written in C follows:
1 #include <string.h>
2 #include <stdlib.h>
3
4 #define MAXCOLUMN 80 /* max column in one line */
5 #define INFILE "inputf1" /* input data file name */
6
7 /* declare functions */
8 void abnormalend();
9 void connecterror();
10
11 FILE *input = NULL;
12
13 main()
14 {
15 /* input data */
16 char indata[MAXCOLUMN + 1];
17
18 char in_userid[31];
19 char in_passwd[31];
20 char in_type;
21 char in_pcode[5];
22 char in_pname[17];
23 char in_color[3];
24 int in_price;
25 int in_stock;
26 char in_flux;
27
28 /* variables for SQL */
29 EXEC SQL BEGIN DECLARE SECTION; 1
30 char xuserid[31]; 1
31 char xpasswd[31]; 1
32 char xpcode[5]; 1
33 char xpname[17]; 1
34 char xcolor[3]; 1
35 int xprice; 1
36 int xstock; 1
37 EXEC SQL END DECLARE SECTION; 1
38
39 /* input file open */ 2
40 input = fopen(INFILE, "r"); 2
41 if (input == NULL) { 2
42 /* input file open error */ 2
43 fprintf(stderr, "can't open %s.", INFILE); 2
44 goto FIN; 2
45 } 2
46 2
47 /* get userid/passwd */ 2
48 fgets(indata, 81, input); 2
49 sscanf(indata, "%30s %30s", xuserid, xpasswd); 2
50 if (feof(input)) { 2
51 fprintf(stderr, "*** error *** no data for connect
***"); 2
52 goto FIN; 2
53 } 2
54 printf("connect start,\n"); 2
55 EXEC SQL WHENEVER SQLERROR PERFORM connecterror; (a) 2
56 EXEC SQL CONNECT USER :xuserid USING :xpasswd; (b) 2
57 printf("connected,\n"); 2
58
59 /* read data from inputfile */
60 EXEC SQL WHENEVER SQLERROR PERFORM abnormalend;
61 fgets(indata, MAXCOLUMN, input);
62
63 while (!feof(input)) {
64 sscanf(indata, "%c %4s %16s %2s %8d %8d %c",
65 &in_type, in_pcode, in_pname, in_color,
66 &in_price, &in_stock, &in_flux);
67 switch (in_type) {
68 case 'I':
69 strncpy(xpcode, in_pcode, 4);
70 strncpy(xpname, in_pname, 8);
71 strncpy(xcolor, in_color, 2);
72 xprice = in_price;
73 xstock = in_stock;
74 EXEC SQL 3
75 INSERT INTO
STOCK(PCODE,PNAME,COLOR,PRICE,SQUANTITY) 3
76 VALUES(:xpcode,:xpname,:xcolor,:xprice,:xstock);
3
77 break;
78 case 'U':
79 strncpy(xpcode, in_pcode, 4); 4
80 xstock = in_stock; 4
81 if (in_flux == '1') { 4
82 EXEC SQL (a) 4
83 UPDATE STOCK
SET SQUANTITY =SQUANTITY+:xstock (a) 4
84 WHERE PCODE=: xpcode; (a) 4
85 } else { 4
86 EXEC SQL (b) 4
87 UPDATE STOCK
SET SQUANTITY=SQUANTITY-:xstock (b) 4
88 WHERE PCODE=:xpcode; (b) 4
89 }
90 break;
91 case 'D':
92 strncpy(xpcode, in_pcode, 4);
93 EXEC SQL 5
94 DELETE FROM STOCK WHERE PCODE=:xpcode; 5
95 break;
96 }
97 fgets(indata, MAXCOLUMN, input);
98 }
99
100 /* print stock list */
101 EXEC SQL 6
102 DECLARE CR1 CURSOR FOR 6
103 SELECT PCODE,PNAME,COLOR,PRICE,SQUANTITY FROM
STOCK; 6
104 EXEC SQL OPEN CR1; 7
105
106 /* print title */
107 printf("\n\n");
108 printf(" ***** Stock Table List *****\n\n");
109 printf(" Product code Product name Color Price
Current stock\n");
110 printf(" ---- ---------------- -- --------
--------\n");
111
112 /* FETCH */
113 SQLCODE = 0;
114 while (SQLCODE <= 100) {
115 EXEC SQL WHENEVER NOT FOUND GO TO FINISH;
116 EXEC SQL 8
117 FETCH CR1 INTO :
xpcode,:xpname,:xcolor,:xprice,:xstock; 8
118 EXEC SQL WHENEVER NOT FOUND CONTINUE;
119 printf(" %4s %-16s %2s %8d %8d\n",
120 xpcode, xpname, xcolor, xprice, xstock);
121 }
122
123 FINISH:
124 /* finish */
125 EXEC SQL CLOSE CR1; (a) 9
126 EXEC SQL COMMIT; (b) 9
127 printf(" *** normal ended ***\n");
128
129 FIN:
130 if (input != NULL) {
131 fclose(input);
132 }
133 EXEC SQL WHENEVER SQLERROR CONTINUE;
134 EXEC SQL WHENEVER NOT FOUND CONTINUE;
135 EXEC SQL WHENEVER SQLWARNING CONTINUE;
136 EXEC SQL DISCONNECT; 10
137 return(0);
138 }
139
140
141 Void connecterror()
142 {
143
144 printf("\n************ error *** cannot connect ***\n");
145 fclose(input);
146 EXEC SQL DISCONNECT;
147 exit(1);
148 }
149
150
151 void abnormalend()
152 {
153 int wsqlcode;
154
155 wsqlcode = -SQLCODE;
156 printf("\n*** HiRDB SQL ERROR SQLCODE = %d \n", wsqlcode);
157 printf("SQLERRMC = %s\n", SQLERRMC);
158
159 EXEC SQL ROLLBACK; (a) 11
160 EXEC SQL DISCONNECT; (b) 11
161 exit(2);
162 }
Figures 7-4 through 7-7 show the PAD chart for program example 2.
Figure 7-4 PAD chart for program example 2 (1/4)
Figure 7-5 PAD chart for program example 2 (2/4)
Figure 7-6 PAD chart for program example 2 (3/4)
Figure 7-7 PAD chart for program example 2 (4/4)
A coding example for program example 2 follows:
1 /************************************************/
2 /* */
3 /* ALL RIGHTS RESERVED, COPYRIGHT (C) 1997, HITACHI, LTD. */
4 /* LICENSED MATERIAL OF HITACHI, LTD. */
5 /* */
6 /* SAMPLE OF FETCH WITH SQLDA */
7 /* */
8 /************************************************/
9
10
11 #include <stdio.h>
12 #include <stdlib.h>
13 #include <string.h>
14 #include "pdbsqlda.h" 1
15
16
17 static void Describe();
18 static void Fetch();
19 static void ClearSqlda(short);
20 static void errmsg();
21
22 /************************************************/
23 /* GLOBAL VARIABLE */
24 /************************************************/
25 short ErrFlg;
26
27 /************************************************/
28 /* GLOBAL VARIABLE */
29 /************************************************/
30
31 /* sqlda */
32 PDUSRSQLDA(10) xsqlda; 2
33
34 /* sqlcnda */ 3
35 struct { 3
36 short sqlnz; 3
37 struct { 3
38 short sqlnamel; 3
39 char sqlnamec[30]; 3
40 } SQLNAME[10]; 3
41 } ucnda; 3
42
43
44 /************************************************/
45 /* */
46 /* MAIN ROUTINE */
47 /* */
48 /************************************************/
49 int main(
50 int argc,
51 char *argv[])
52 {
53
54 /************************************************/
55 /* CONNECT */
56 /************************************************/
57 EXEC SQL
58 WHENEVER SQLERROR GOTO:ERR_EXIT
59
60 printf("***** connect start \n");
61 EXEC SQL
62 CONNECT; 4
63 printf("***** connect : END\n");
64
65 /************************************************/
66 /* DESCRIBE */
67 /************************************************/
68 Describe(); 5
69 if(ErrFlg <0){ 5
70 goto ERR_EXIT; 5
71 } 5
72 5
73 /*********************************************/ 5
74 /* FETCH */ 5
75 /*********************************************/ 5
76 Fetch(); 5
77 if(ErrFlg <0){ 5
78 goto ERR_EXIT; 5
79 } 5
80
81 /**********************************************/
82 /* END OF ALL */
83 /*********************************************/
84 ERR_EXIT :
85 if(SQLCODE <0){
86 errmsg();
87 ErrFlg = -1;
88 }
89
90 EXEC SQL
91 WHENEVER SQLERROR CONTINUE;
92 EXEC SQL
93 WHENEVER NOT FOUNT CONTINUE;
94 EXEC SQL
95 WHENEVER SQLWARNING CONTINUE;
96
97 EXEC SQL
98 DISCONNECT; 6
99
100 return (ErrFlg);
101 }
102
103
104 /*********************************************/
105 /* */
106 /* DYNAMIC CURSOR */
107 /* */
108 /*********************************************/
109 static void Fetch()
110 {
111 EXEC SQL BEGIN DECLARE SECTION;
112 char XCUSTOM_CD[6];
113 char XCUSTOM_NAME[31];
114 char XTELNO[13];
115 char XZIPCD[4];
116 char XADDRESS[31];
117 EXEC SQL END DECLARE SECTION;
118
119 EXEC SQL
120 WHENEVER SQLERROR GOTO :Exit_Fetch;
121
122 EXEC SQL
123 DECLARE CUR2 CURSOR FOR SEL1; 7
124
125 /*********************************************/
126 /* OPEN CURSOR */
127 /*********************************************/
128 printf("***** DYNAMIC CURSOR open start\n");
129 EXEC SQL
130 OPEN CUR2; 8
131 printf("***** DYNAMIC CURSOR open : END\n");
132
133
134 /*********************************************/
135 /* FETCH */
136 /*********************************************/
137 printf("***** fetch (use sqlda) start\n");
138
139
140 EXEC SQL
141 WHENEVER NOT FOUND GOTO FETCH2_END;
142
143 for(;;) {
144 ClearSqlda(5); 9
145 PDSQLDATA(xsqlda, 0) = (void *)
XCUSTOM_CD; (a) 9
146 PDSQLCOD(xsqlda, 0) = PDSQL_CHAR; (a) 9
147 PDSQLLEN(xsqlda, 0) = sizeof
(XCUSTOM_CD)-1; (a) 9
148 PDSQLDATA(xsqlda, 1) = (void *)
XCUSTOM_NAME; (b) 9
149 PDSQLCOD(xsqlda, 1) = PDSQL_CHAR; (b) 9
150 PDSQLLEN(xsqlda, 1) = sizeof
(XCUSTOM_NAME)-1; (b) 9
151 PDSQLDATA(xsqlda, 2) = (void *)XTELNO; (c) 9
152 PDSQLCOD(xsqlda, 2) = PDSQL_CHAR; (c) 9
153 PDSQLLEN(xsqlda, 2) = sizeof(XTELNO)-1; (c) 9
154 PDSQLDATA(xsqlda, 3) = (void *)XZIPCD; (d) 9
155 PDSQLCOD(xsqlda, 3) = PDSQL_CHAR; (d) 9
156 PDSQLLEN(xsqlda, 3) = sizeof(XZIPCD)-1; (d) 9
157 PDSQLDATA(xsqlda, 4) =
(void *)XADDRESS; (e) 9
158 PDSQLCOD(xsqlda, 4) = PDSQL_CHAR; (e) 9
159 PDSQLLEN(xsqlda, 4) =
sizeof(XADDRESS)-1; (e) 9
160
161 memset(XCUSTOM_CD, 0, sizeof(XCUSTOM_CD));
162 memset(XCUSTOM_NAME, 0, sizeof(XCUSTOM_NAME));
163 memset(XTELNO, 0, sizeof(XTELNO));
164 memset(XZIPCD, 0, sizeof(XZIPCD));
165 memset(XADDRESS, 0, sizeof(XADDRESS));
166
167 EXEC SQL FETCH CUR2
168 USING DESCRIPTOR :xsqlda; 10
169
170 printf("%s", XCUSTOM_CD);
171 printf("%s", XCUSTOM_NAME);
172 printf("%s", XTELNO);
173 printf("%s", XZIPCD);
174 printf("%s\n", XADDRESS);
175 }
176 FETCH2_END:
177 printf("***** fetch : END\n");
178
179 /*********************************************/
180 /* CLOSE CURSOR */
181 /*********************************************/
182 printf("***** close start\n");
183 EXEC SQL
184 WHENEVER NOT FOUND CONTINUE;
185 EXEC SQL
186 CLOSE CUR2; 11
187 printf("***** close : END\n");
188
189 /*********************************************/
190 /* */
191 /*********************************************/
192 Exit_Fetch:
193 if(SQLCODE <0){
194 errmsg();
195 ErrFlg = -1;
196 }
197 return;
198 }
199
200
201 /*********************************************/
202 /* DESCRIBE */
203 /*********************************************/
204 static void Describe()
205 {
206 short I;
207
208 EXEC SQL
209 WHENEVER SQLERROR GOTO :Exit_Describe;
210
211 /*********************************************/
212 /* PREPARE */
213 /*********************************************/
214 printf("***** prepare start\n");
215 EXEC SQL 12
216 PREPARE SEL1 12
217 FROM 'SELECT * FROM CUSTOM' 12
218 WITH SQLNAME OPTION; 12
219 printf("***** prepare : END\n");
220
221 /*********************************************/
222 /* DESCRIBE */
223 /*********************************************/
224 PDSQLN(xsqlda) = 10;
225 printf("***** describe start\n");
226 EXEC SQL
227 DESCRIBE SEL1 INTO :xsqlda :ucnda; 13
228 printf("***** describe : END\n");
229
230 printf(" describe result\n");
231 printf(" NUMBER OF DATA =%d\n",
PDSQLD(xsqlda));
232 printf(" NUMBER OF COLUMN NAME = %d\n",
ucnda.sqlnz);
233 for (i=0 ; i < ucnda.sqlnz ; i++) {
234 printf(" [%d]), i);
235 printf(" DATA TYPE(%d)", PDSQLCOD(xsqlda,
i));
236 printf(" DATA LENGTH(%d)",
PDSQLLEN(xsqlda, i));
237 printf(" COLUMN NAME(%s)\n",
ucnda.SQLNAME[i].sqlnamec);
238 }
239
240 /*********************************************/
241 /* */
242 /*********************************************/
243 Exit_Describe:
244 if(SQLCODE <0){
245 errmsg();
246 ErrFlg = -1;
247 }
248 return;
249 }
250
251
252 /*********************************************/
253 /* Clear SQLDA */
254 /*********************************************/
255 static void ClearSqlda (
256 short num)
257 {
258 PDSQLN(xsqlda) = num; 14
259 PDSQLD(ssqlda) = num; 14
260 while(numzueng016.tif0){
261 PDSQLDATA(xsqlda, num) = NULL; 15
262 PDSQLIND(xsqlda, num) = NULL; 15
263 PDSQLDIM(xsqlda, num) = 0; 15
264 PDSQLXDIM(xsqlda, num) = 1; 15
265 PDSQLSYS(xsqlda, num) = 0; 15
266 PDSQLCOD(xsqlda, num) = 0; 15
267 PDSQLLEN(xsqlda, num) = 0; 15
268 }
269 return;
270 }
271
272
273 /*********************************************/
274 /* */
275 /* WARNING */
276 /* */
277 /*********************************************/
278 static void errmsg()
279 {
280 int wsqlcode;
281
282 if(SQLCODE > 0){
283 printf(">>> warning\n");
284 }
285 if(SQLCODE <0){
286 printf(">>> error occurred\n");
287 }
288 wsqlcode = SQLCODE;
289 printf(">>> sqlcode = %d\n", SQLCODE);
290 printf(">>> sqlwarn = %c", SQLWARN0);
291 printf("%c", SQLWARN1);
292 printf("%c", SQLWARN2);
293 printf("%c", SQLWARN3);
294 printf("%c", SQLWARN4);
295 printf("%c", SQLWARN5);
296 printf("%c", SQLWARN6);
297 printf("%c", SQLWARN7);
298 printf("%c", SQLWARN8);
299 printf("%c", SQLWARN9);
300 printf("%c", SQLWARNA);
301 printf("%c", SQLWARNB);
302 printf("%c\n", SQLWARNC);
303
304 #if defined(HIUXWE2) || defined(WIN32)
305 printf(">>> message = %s\n", SQLERRMC);
306 #else
307 printf(">>> message = %Fs\n", SQLERRMC);
308 #endif
309 return;
310 }
Figures 7-8 through 7-10 show the PAD chart for program example 3.
Figure 7-8 PAD chart for program example 3 (1/3)
Figure 7-9 PAD chart for program example 3 (2/3)
Figure 7-10 PAD chart for program example 3 (3/3)
A coding example of program example 3 follows:
1 /***********************************************/
2 /* */
3 /* ALL RIGHTS RESERVED, COPYRIGHT (C) 1997,
HITACHI, LTD. */
4 /* LICENSED MATERIAL OF HITACHI, LTD. */
5 /* */
6 /***********************************************/
7
8
9 #include <stdio.h>
10 #include <stdlib.h>
11 #include <stddef.h>
12 #include <ctype.h>
13 #include <string.h>
14
15 static void InitTable();
16 static void TestBlob();
17 static void warning();
18
19
20 /***********************************************/
21 /* GLOBAL VARIABLE */
22 /***********************************************/
23 short ErrFlg;
24
25 EXEC SQL BEGIN DECLARE SECTION;
26 short XSINT_IN;
27 short XSINT_OUT;
28 long XINT_IN;
29 long XINT_OUT;
30 SQL TYPE IS BLOB(16K) XBLOB_IN; 1
31 SQL TYPE IS BLOB(16K) XBLOB_OUT; 1
32 EXEC SQL END DECLARE SECTION;
33
34 /*
35 * name = MAIN
36 * func = SAMPLE
37 * io = argc : i :
38 * argv : i :
39 * return = 0,-1
40 * note = This program needs "RDUSER02" rdarea
on Server.
41 * date = 98.04.24 by matsushiba
42 */
43 int main(
44 int argc,
45 char *argv[])
46 {
47 ErrFlg = 0;
48
49 /***********************************************/
50 /* */
51 /***********************************************/
52 EXEC SQL
53 WHENEVER SQLERROR goto ERR_EXIT;
54
55 EXEC SQL
56 WHENEVER SQLWARNING PERFORM :warning;
57
58 EXEC SQL CONNECT; 2
59
60
61 /***********************************************/
62 /* INIT */
63 /***********************************************/
64 InitTable(); 3
65 if(ErrFlg <0){ 3
66 goto ERREXIT; 3
67 } 3
68
69 /***********************************************/
70 /* */
71 /***********************************************/
72 TestBlob(); 4
73 if(ErrFlg <0){ 4
74 goto ERR_EXIT; 4
75 } 4
76
77 /***********************************************/
78 /* */
79 /***********************************************/
80 ERREXIT:
81 if(SQLCODE <0){
82 printf(":> ERROR HAPPENED!!\n");
83 warning();
84 ErrFlg = -1;
85 }
86
87 EXEC SQL
88 WHENEVER SQLERROR CONTINUE;
89 EXEC SQL
90 WHENEVER NOT FOUND CONTINUE;
91 EXEC SQL
92 WJEMEVER SQLWARNING CONTINUE;
93
94 EXEC SQL DISCONNECT; 5
95
96 return (ErrFlg);
97 }
98
99
100 /***********************************************/
101 /* INIT */
102 /***********************************************/
103 static void InitTable()
104 {
105
106 /***********************************************/
107 /* */
108 /***********************************************/
109 EXEC SQL
110 WHENEVER SQLERROR CONTINUE;
111
112 EXEC SQL 6
113 DROP TABLE SMPTBL; 6
114 6
115 EXEC SQL 6
116 CREATE SCHEMA; 6
117
118 printf("## CREATE TABLE\n");
119
120 EXEC SQL
121 WHENEVER SQLERROR GOTO INIT_ERROR;
122
123 printf("## CREATE SMPTBL\n");
124 EXEC SQL 7
125 CREATE TABLE SMPTBL(CLM1 BLOB(30K) IN
RDUSER02, 7
126 CLM2 SMALLINT, 7
127 CLM3 INTEGER); 7
128
129 return;
130
131 INIT_ERROR:
132 warning();
133 ErrFlg = -1;
134 return;
135 }
136
137
138
139 /************************************************/
140 /* TEST BLOB */
141 /************************************************/
142 static void TestBlob()
143 {
144 short cnt;
145
146 EXEC SQL
147 WHENEVER SQL ERROR goto :ExitTestBlob;
148
149 EXEC SQL
150 WHENEVER SQLWARNING PERFORM :warning;
151
152 /***********************************************/
153 /* INSERT */
154 /***********************************************/
155 memset(XBLOB_IN.XBLOB_IN_data,
156 0x55,
157 sizeof(XBLOB_IN.XBLOB_IN_data));
158 XBLOB_IN.XBLOB_IN_length =
sizeof(XBLOB_IN.XBLOB_IN_data);
159
160 printf("## INSERT \n");
161 for(cnt=1; cnt<5; cnt++){
162 XSINT_IN = cnt;
163 XINT_IN = 100+cnt;
164 EXEC SQL 8
165 INSERT INTO SMPTBL 8
166 VALUES(:XBLOB_IN, :XSINT_IN,
:XINT_IN); 8
167 }
168 EXEC SQL COMMIT;
169
170 /***********************************************/
171 /* FETCH */
172 /***********************************************/
173 printf("## FETCH \n");
174
175 EXEC SQL 9
176 DECLARE CUR_BLOB CURSOR FOR 9
177 SELECT * FROM SMPTBL; 9
178
179 EXEC SQL
180 OPEN CUR_BLOB; 10
181
182 EXEC SQL
183 WHENEVER NOT FOUND GOTO FETCH_END;
184
185 for(;;){
186 memset (XBLOB_OUT.XBLOB_OUT_data,
187 0
188 sizeof(XBLOB_OUT.XBLOB_OUT_data));
189 XBLOB_OUT.XBLOBL_OUT_length = 0;
190 EXEC SQL 11
191 FETCH CUR_BLOB INTO :XBLOB_OUT, 11
192 :XSINT_OUT, 11
193 :XINT_OUT; 11
194
195 printf("CLM1 XBLOB_length == %d\n",
196 XBLOB_OUT.XBLOB_OUT_length);
197 printf("CLM2 = %d\n", XSINT_OUT);
198 printf("CLM3 = %ld\n", XINT_OUT);
199 }
200 FETCH_END:
201 EXEC SQL
202 WHENEVER NOT FOUND CONTINUE;
203
204 EXEC SQL
205 CLOSE CUR_BLOB; 12
206
207 /***********************************************/
208 /* UPDATE */
209 /***********************************************/
210 memset(XBLOB_IN.XBLOB_IN_data,
211 0x38,
212 sizeof(XBLOB_IN.XBLOB_IN_data));
213 XBLOB_IN.XBLOB_IN_length =
sizeof(XBLOB_IN.XBLOB_IN_data);
214
215 printf("## UPDATE\n");
216 EXEC SQL
217 UPDATE SMPTBL SET CLM1=:XBLOB_IN; 13
218
219 EXEC SQL COMMIT
220
221 /***********************************************/
222 /* */
223 /***********************************************/
224 ExitTestBlob:
225 if(SQLCODE < 0){
226 warning();
227 ErrFlg = -1;
228 }
229 return;
230 )
231
232
233 /***********************************************/
234 /* WARNING */
235 /***********************************************/
236 static void warning()
237 {
238 if(SQLCODE <0){
239 printf(">>>ERROR\n");
240 printf(">>> sqlcode = %d\n", SQLCODE);
241 #if defined(HIUXWE2) || defined(WIN32)
242 printf(":> message = %s\n", SQLERRMC);
243 #else
244 printf(":> message = %Fs\n", SQLERRMC);
245 #endif
246 }
247 else{
248 printf(">>>WARNING\n");
249 printf(">>>sqlwarn = %c", SQLWARN0)
250 printf("%c", SQLWARN1);
251 printf("%c", SQLWARN2);
252 printf("%c", SQLWARN3);
253 printf("%c", SQLWARN4);
254 printf("%c", SQLWARN5);
255 printf("%c", SQLWARN6);
256 printf("%c", SQLWARN7);
257 printf("%c", SQLWARN8);
258 printf("%c", SQLWARN9);
259 printf("%c", SQLWARNA);
260 printf("%c", SQLWARNB);
261 printf("%c\n", SQLWARNC);
262 }
263 return
264 }