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
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
|
--%ESQL at 0 : ***************************************************
--%ESQL at 0 : !!! D O N O T E D I T !!!
--%ESQL at 0 : This file is gnerated by GNADE ESQL translator
--%ESQL at 0 :
--%ESQL at 0 : ESQL VERSION : 1.1.4
--%ESQL at 0 : SOURCE FILE : simpler.adq
--%ESQL at 0 : OPTIONS :
--%ESQL at 0 : -nosqlstate
--%ESQL at 0 : -s
--%ESQL at 0 : -v
--%ESQL at 0 : -pedantic
--%ESQL at 0 : -debugcode
--%ESQL at 0 : -schema
--%ESQL at 0 : test.sql
--%ESQL at 0 : simpler.adq
--%ESQL at 0 : ***************************************************
with ada.text_io; use ada.text_io;
with Ada.Strings.Fixed; use Ada.Strings.Fixed;
with Ada.Strings; use Ada.Strings;
with sql_standard; use sql_standard;
with gnu.db.esql_Support; use gnu.db.esql_support;
use gnu.db;
with Ada.Exceptions; use Ada.Exceptions;
with Ada.Characters.Latin_1; use Ada.Characters;
WITH GNU.DB.ESQL_SUPPORT; USE GNU.DB.ESQL_SUPPORT;
WITH GNU.DB.ESQL_SUPPORT.ODBC;
USE GNU.DB.ESQL_SUPPORT.ODBC;
USE GNU.DB.ESQL_SUPPORT;
USE GNU.DB;
pragma Elaborate_All(GNU.DB.ESQL_SUPPORT.ODBC);
procedure Simpler is
name : String(1..20);
NAME_LEN : Integer;
-- declare host and program variables
GNADE_L_CONTEXT : ODBC.CONTEXT_TYPE;
ENAME : char(1..20);
DEPT_NAME : char(1..14);
SQLCODE : sql_standard.sqlcode_type; -- for ANSI mode
SQLSTATE : sql_standard.sqlstate_type; -- ANSI mode
LOCATION : char(1..13);
SQLCA : aliased ESQL_SUPPORT.SQLCA_TYPE;
GNADE_DB_DEFAULT : ODBC.CONNECTION_HANDLE := NULL;
GNADE_G_CONTEXT : ODBC.CONTEXT_TYPE;
SQL_ERROR : exception;
SQL_WARNING : exception;
-- Check to see if the last database
-- operation returned any rows.
-- (See Chapter 5 on error handling.)
function EMPLOYEE_EXISTS return boolean is
begin
return not (SQLCODE in SQL_STANDARD.NOT_FOUND);
end EMPLOYEE_EXISTS;
begin
-- Direct the precompiler to insert "if" logic that
-- checks the ORACLE return code and raises an exception
-- if needed.
-- Check for warnings, such as data truncation, also.
--- hellop
-- Connect to ORACLE
GNADE_DB_DEFAULT:=ODBC.Connect("DEMO_DB", "gnade", "gnade");
NEW_LINE;
PUT_LINE("Connected to database $DBSOURCE as $DBUSER " );
NEW_LINE;
PUT_LINE("*** ORACLE DEMO #1 ***");
NEW_LINE;
loop
PUT("Enter employee last name (CR to exit): ");
GET_LINE(name, NAME_LEN);
exit when NAME_LEN = 0;
ESQL_Support.Move( name, ename );
-- SELECT statements that return one row can use a
-- simple SELECT statement. Otherwise, a cursor must be
-- declared for the SELECT, and a FETCH statement is used.
--%ESQL at 77 : *** Query ***
declare
LOCATION_Indicator : aliased SQL_STANDARD.INDICATOR_TYPE := 0;
DEPT_NAME_Indicator : aliased SQL_STANDARD.INDICATOR_TYPE := 0;
ENAME_Indicator : aliased SQL_STANDARD.INDICATOR_TYPE := 0;
begin
-- Prepare
ODBC.PREPARE(GNADE_DB_DEFAULT,
GNADE_G_CONTEXT,
"SELECT Location , Departments . Name FROM Employees , Depart" &
"ments WHERE Departments . Deptno = Employees . deptno AND Em" &
"ployees . Name = ? ",
SQLCA'Address,
Is_Reopenable =>FALSE);
-- Evaluate_Result
ESQL_SUPPORT.DEBUG(
"Line 72 in package EMPLOYEE_EXISTS",
"SELECT Location , Departments . Name FROM Employees , Depart" &
"ments WHERE Departments . Deptno = Employees . deptno AND Em" &
"ployees . Name = ? ",
SQLCA'Address);
SQLCODE := SQLCA.SQLCODE;
SQLSTATE := SQLCA.STATE;
IF ( SQLSTATE(1..2) /= ESQL_SUPPORT.SUCCESS_CLASS AND
SQLSTATE(1..2) /= ESQL_SUPPORT.WARNING_CLASS AND
SQLSTATE(1..2) /= ESQL_SUPPORT.NOTFOUND_CLASS) OR
SQLCODE in SQL_STANDARD.SQL_ERROR THEN
Raise_Exception(SQL_ERROR'Identity,
"Line 72 in package EMPLOYEE_EXISTS failed " & Latin_1.LF & SQLCA.message);
END IF;
IF SQLSTATE(1..2) = ESQL_SUPPORT.WARNING_CLASS THEN
Raise_Exception(SQL_WARNING'Identity,
"Line 72 in package EMPLOYEE_EXISTS failed " & Latin_1.LF & SQLCA.message);
END IF;
-- Parameter Binding ENAME
ODBC.BINDPARAMETER(
GNADE_DB_DEFAULT,
1,
ENAME'Address,
ENAME'Size,
ISO92_CHAR_TYPE,
ENAME_Indicator'Access
);
ODBC.BINDCOLUMN(
GNADE_DB_DEFAULT,
1,
LOCATION'Address,
LOCATION'Size,
ISO92_CHAR_TYPE,
LOCATION_Indicator'Access
);
ODBC.BINDCOLUMN(
GNADE_DB_DEFAULT,
2,
DEPT_NAME'Address,
DEPT_NAME'Size,
ISO92_CHAR_TYPE,
DEPT_NAME_Indicator'Access
);
-- Execute
ODBC.EXECUTE(GNADE_DB_DEFAULT,SQLCA'Address);
ODBC.FETCH(GNADE_DB_DEFAULT,SQLCA'Address);
-- Evaluate_Result
ESQL_SUPPORT.DEBUG(
"Line 72 in package EMPLOYEE_EXISTS",
"SELECT Location , Departments . Name FROM Employees , Depart" &
"ments WHERE Departments . Deptno = Employees . deptno AND Em" &
"ployees . Name = ? ",
SQLCA'Address);
SQLCODE := SQLCA.SQLCODE;
SQLSTATE := SQLCA.STATE;
IF ( SQLSTATE(1..2) /= ESQL_SUPPORT.SUCCESS_CLASS AND
SQLSTATE(1..2) /= ESQL_SUPPORT.WARNING_CLASS AND
SQLSTATE(1..2) /= ESQL_SUPPORT.NOTFOUND_CLASS) OR
SQLCODE in SQL_STANDARD.SQL_ERROR THEN
Raise_Exception(SQL_ERROR'Identity,
"Line 72 in package EMPLOYEE_EXISTS failed " & Latin_1.LF & SQLCA.message);
END IF;
IF SQLSTATE(1..2) = ESQL_SUPPORT.WARNING_CLASS THEN
Raise_Exception(SQL_WARNING'Identity,
"Line 72 in package EMPLOYEE_EXISTS failed " & Latin_1.LF & SQLCA.message);
END IF;
end;
if EMPLOYEE_EXISTS then
NEW_LINE;
PUT("Employee ");
PUT(To_String(ENAME(1..NAME_LEN)));
PUT(" works for department " & To_String(DEPT_NAME));
PUT(" in " & To_String( LOCATION ));
else
PUT_LINE("Sorry, no such employee (try ALLEN or JONES)");
NEW_LINE;
-- The following lines illustrate the use of SQLSTATE and SQLCODE
--DEBUG("The SQLSTATE error code is: ", SQLCA'Unchecked_Access );
end if;
NEW_LINE; NEW_LINE;
end loop;
NEW_LINE;
PUT_LINE("Good bye.");
-- Disconnect from the database.
--%ESQL at 100 : *** Query ***
begin
-- Prepare
-- Execute
ODBC.EXECUTE(GNADE_DB_DEFAULT,SQLCA'Address,
Statement => "COMMIT RELEASE ");
-- Evaluate_Result
ESQL_SUPPORT.DEBUG(
"Line 99 in package EMPLOYEE_EXISTS",
"COMMIT RELEASE ",
SQLCA'Address);
SQLCODE := SQLCA.SQLCODE;
SQLSTATE := SQLCA.STATE;
IF ( SQLSTATE(1..2) /= ESQL_SUPPORT.SUCCESS_CLASS AND
SQLSTATE(1..2) /= ESQL_SUPPORT.WARNING_CLASS AND
SQLSTATE(1..2) /= ESQL_SUPPORT.NOTFOUND_CLASS) OR
SQLCODE in SQL_STANDARD.SQL_ERROR THEN
Raise_Exception(SQL_ERROR'Identity,
"Line 99 in package EMPLOYEE_EXISTS failed " & Latin_1.LF & SQLCA.message);
END IF;
IF SQLSTATE(1..2) = ESQL_SUPPORT.WARNING_CLASS THEN
Raise_Exception(SQL_WARNING'Identity,
"Line 99 in package EMPLOYEE_EXISTS failed " & Latin_1.LF & SQLCA.message);
END IF;
end;
exception
-- Turn off error checking, since we do not want
-- to raise an exception when logging out under
-- any circumstance.
when SQL_ERROR =>
PUT_LINE(" ** DATABASE ERROR OCCURRED **");
NEW_LINE;
PUT_LINE(Trim( SQLCA.Message, Right ));
--%ESQL at 115 : *** Query ***
begin
-- Prepare
-- Execute
ODBC.EXECUTE(GNADE_DB_DEFAULT,SQLCA'Address,
Statement => "ROLLBACK RELEASE ");
-- Evaluate_Result
ESQL_SUPPORT.DEBUG(
"Line 114 in package EMPLOYEE_EXISTS",
"ROLLBACK RELEASE ",
SQLCA'Address);
SQLCODE := SQLCA.SQLCODE;
SQLSTATE := SQLCA.STATE;
end;
when SQL_WARNING =>
PUT_LINE(" ** ORACLE WARNING OCCURRED **");
NEW_LINE;
--%ESQL at 120 : *** Query ***
begin
-- Prepare
-- Execute
ODBC.EXECUTE(GNADE_DB_DEFAULT,SQLCA'Address,
Statement => "ROLLBACK RELEASE ");
-- Evaluate_Result
ESQL_SUPPORT.DEBUG(
"Line 119 in package EMPLOYEE_EXISTS",
"ROLLBACK RELEASE ",
SQLCA'Address);
SQLCODE := SQLCA.SQLCODE;
SQLSTATE := SQLCA.STATE;
end;
end Simpler;
|
