1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
|
/*
* Program type: Embedded Static SQL
*
* Description:
* This program declares and creates a new table.
* Some rows, describing a department structure,
* are added to the new table as a test.
*
* The table is created temporarily for figuring
* out which level in the department structure each
* department belongs too.
* The contents of this file are subject to the Interbase Public
* License Version 1.0 (the "License"); you may not use this file
* except in compliance with the License. You may obtain a copy
* of the License at http://www.Inprise.com/IPL.html
*
* Software distributed under the License is distributed on an
* "AS IS" basis, WITHOUT WARRANTY OF ANY KIND, either express
* or implied. See the License for the specific language governing
* rights and limitations under the License.
*
* The Original Code was created by Inprise Corporation
* and its predecessors. Portions created by Inprise Corporation are
* Copyright (C) Inprise Corporation.
*
* All Rights Reserved.
* Contributor(s): ______________________________________.
*/
#include "example.h"
#include <stdlib.h>
#include <stdio.h>
void build_tree (void);
void pr_error (void);
EXEC SQL
BEGIN DECLARE SECTION;
EXEC SQL
END DECLARE SECTION;
int main (void)
{
char dept[4];
int lvl = 1;
/* Describe the new table's structure. */
EXEC SQL
DECLARE tmp_dept_tree TABLE (
dept_no CHAR(3) NOT NULL PRIMARY KEY,
tree_level INTEGER);
/* Drop the table in case it exists. Ignore error */
EXEC SQL
DROP TABLE tmp_dept_tree;
EXEC SQL
WHENEVER SQLERROR GO TO Error1;
/* Create the new table. */
printf ("creating tmp_dept_tree\n");
EXEC SQL
CREATE TABLE tmp_dept_tree (
dept_no CHAR(3) NOT NULL PRIMARY KEY,
tree_level INTEGER);
/* Fill the new table. */
build_tree();
/* Look at it */
EXEC SQL DECLARE dc CURSOR FOR
SELECT dept_no, tree_level
FROM tmp_dept_tree;
EXEC SQL
OPEN dc;
EXEC SQL
FETCH dc INTO :dept, :lvl;
while (SQLCODE == 0)
{
printf ("Dept = %s: Level = %d\n", dept, lvl);
EXEC SQL
FETCH dc INTO :dept, :lvl;
}
EXEC SQL
COMMIT RELEASE;
return 0;
Error1:
pr_error();
return 1;
}
/*
* For each department find its sub-departments and mark them
* with the next level number.
*/
void build_tree (void)
{
char dept[4];
int lvl = 1;
EXEC SQL
WHENEVER SQLERROR GO TO Error2;
/* Initialize the department structure by adding the first level. */
EXEC SQL
INSERT INTO tmp_dept_tree VALUES ('000', :lvl);
/* Declare the cursor for selecting all departments with level 'lvl'. */
EXEC SQL
DECLARE ds CURSOR FOR
SELECT dept_no
FROM tmp_dept_tree
WHERE tree_level = :lvl;
/* For each department with level 'lvl', find its sub-departments. */
while (SQLCODE == 0)
{
EXEC SQL
OPEN ds;
/* Initialize the next level. */
lvl++;
/* Add all the sub-departments of the next level to the table. */
for (;;)
{
EXEC SQL
FETCH ds INTO :dept;
if (SQLCODE == 100)
break;
EXEC SQL
INSERT INTO tmp_dept_tree
SELECT dept_no, :lvl
FROM department
WHERE head_dept = :dept;
}
EXEC SQL
CLOSE ds;
EXEC SQL
COMMIT;
/* Done, if no next level was created by the INSERT above. */
EXEC SQL
SELECT tree_level
FROM tmp_dept_tree
WHERE tree_level = :lvl;
if (SQLCODE == 100)
return;
}
Error2:
pr_error();
return;
}
/*
* Print any error and exit.
*/
void pr_error (void)
{
isc_print_sqlerror((short)SQLCODE, gds__status);
}
|
