|
1
|
DEFINE VARIABLE tth AS HANDLE NO-UNDO.
|
|
2
|
DEFINE VARIABLE bh AS HANDLE NO-UNDO.
|
|
3
|
DEFINE VARIABLE qh AS HANDLE NO-UNDO.
|
|
4
|
DEFINE VARIABLE buf-ord-hndl AS HANDLE NO-UNDO.
|
|
5
|
DEFINE VARIABLE buf-rep-hndl AS HANDLE NO-UNDO.
|
|
6
|
DEFINE VARIABLE fld-ordnum-hndl AS HANDLE NO-UNDO.
|
|
7
|
DEFINE VARIABLE fld-repname-hndl AS HANDLE NO-UNDO.
|
|
8
|
DEFINE VARIABLE fld-orderdate-hndl AS HANDLE NO-UNDO.
|
|
9
|
DEFINE VARIABLE cLogFile AS CHARACTER NO-UNDO.
|
|
10
|
DEFINE VARIABLE cTestID AS CHARACTER NO-UNDO.
|
|
11
|
DEFINE VARIABLE exit AS LOGICAL NO-UNDO.
|
|
12
|
DEFINE VARIABLE i AS INTEGER INITIAL 0 NO-UNDO.
|
|
13
|
DEFINE VARIABLE runs AS INTEGER INITIAL 500 NO-UNDO.
|
|
14
|
|
|
15
|
/* Use the GUID as the primary unique identifier for the test session */
|
|
16
|
cTestID = STRING(GUID(GENERATE-UUID)). /* Ensure GUID() output is handled correctly */
|
|
17
|
cLogFile = "TestLog-" + cTestID + ".txt".
|
|
18
|
|
|
19
|
/* --- Logging Helper Procedure --- */
|
|
20
|
PROCEDURE WriteLogEntry:
|
|
21
|
IF i > -1 THEN RETURN.
|
|
22
|
DEFINE INPUT PARAMETER pcMessage AS CHARACTER NO-UNDO.
|
|
23
|
OUTPUT TO VALUE(cLogFile) APPEND.
|
|
24
|
PUT UNFORMATTED pcMessage SKIP.
|
|
25
|
OUTPUT CLOSE.
|
|
26
|
END PROCEDURE.
|
|
27
|
|
|
28
|
PROCEDURE CheckErrors:
|
|
29
|
/* Check for query preparation error (Using ERROR-STATUS:ERROR for maximum detail) */
|
|
30
|
IF ERROR-STATUS:ERROR OR ERROR-STATUS:NUM-MESSAGES > 0 THEN DO:
|
|
31
|
DEFINE VARIABLE ix AS INTEGER NO-UNDO.
|
|
32
|
|
|
33
|
DO ix = 1 TO ERROR-STATUS:NUM-MESSAGES:
|
|
34
|
RUN WriteLogEntry("ERROR: " + ERROR-STATUS:GET-MESSAGE(ix)).
|
|
35
|
END. /* Ensure cleanup on failure */
|
|
36
|
exit = YES.
|
|
37
|
END.
|
|
38
|
END.
|
|
39
|
|
|
40
|
/* ---------------------------------------------------------------------- */
|
|
41
|
/* --- 1. REUSABLE QUERY EXECUTION PROCEDURE ---------------------------- */
|
|
42
|
/* ---------------------------------------------------------------------- */
|
|
43
|
PROCEDURE runSingleQuery:
|
|
44
|
DEFINE INPUT PARAMETER pcQueryString AS CHARACTER NO-UNDO.
|
|
45
|
DEFINE INPUT PARAMETER phBuffer AS HANDLE NO-UNDO.
|
|
46
|
|
|
47
|
DEFINE VARIABLE txt AS CHARACTER NO-UNDO.
|
|
48
|
|
|
49
|
RUN WriteLogEntry("------------------------------------------------------------------").
|
|
50
|
RUN WriteLogEntry("TESTING QUERY: " + pcQueryString).
|
|
51
|
|
|
52
|
/* Get field handles needed for logging */
|
|
53
|
fld-ordnum-hndl = phBuffer:BUFFER-FIELD("OrderNum").
|
|
54
|
fld-repname-hndl = phBuffer:BUFFER-FIELD("RepName").
|
|
55
|
|
|
56
|
CREATE QUERY qh.
|
|
57
|
qh:SET-BUFFERS(phBuffer).
|
|
58
|
|
|
59
|
exit = NO.
|
|
60
|
|
|
61
|
/* Prepare and Open the dynamic query */
|
|
62
|
qh:QUERY-PREPARE(pcQueryString) NO-ERROR.
|
|
63
|
RUN CheckErrors.
|
|
64
|
|
|
65
|
qh:QUERY-OPEN() NO-ERROR.
|
|
66
|
RUN CheckErrors.
|
|
67
|
|
|
68
|
IF exit THEN RETURN.
|
|
69
|
|
|
70
|
/* Log the header */
|
|
71
|
RUN WriteLogEntry(STRING("OrderNum", "X(10)") + " | " + STRING("RepName", "X(20)")).
|
|
72
|
|
|
73
|
/* Iterate and log results */
|
|
74
|
/* FIX: Added DO TRANSACTION block to satisfy EXCLUSIVE-LOCK requirement (Error 2819) */
|
|
75
|
DO TRANSACTION:
|
|
76
|
REPEAT:
|
|
77
|
qh:GET-NEXT().
|
|
78
|
IF qh:QUERY-OFF-END THEN LEAVE.
|
|
79
|
|
|
80
|
/* Get and format the values */
|
|
81
|
txt = STRING(fld-ordnum-hndl:BUFFER-VALUE()) + " | " +
|
|
82
|
STRING(fld-repname-hndl:BUFFER-VALUE()).
|
|
83
|
|
|
84
|
RUN WriteLogEntry(INPUT txt).
|
|
85
|
END.
|
|
86
|
END. /* END TRANSACTION */
|
|
87
|
|
|
88
|
RUN WriteLogEntry("Query finished. Total records found: " + STRING(qh:NUM-RESULTS)).
|
|
89
|
|
|
90
|
qh:QUERY-CLOSE().
|
|
91
|
END PROCEDURE.
|
|
92
|
|
|
93
|
/* ---------------------------------------------------------------------- */
|
|
94
|
/* --- 2. QUERY VARIATION HARNESS --------------------------------------- */
|
|
95
|
/* ---------------------------------------------------------------------- */
|
|
96
|
PROCEDURE runQueryTests:
|
|
97
|
DEFINE INPUT PARAMETER phBuffer AS HANDLE NO-UNDO.
|
|
98
|
|
|
99
|
/* === 1. Simple Filtering and Sorting (Base Tests) === */
|
|
100
|
|
|
101
|
/* 1. Simple Filter and Sort (Base Case) - Already ordered */
|
|
102
|
RUN runSingleQuery("FOR EACH ordx NO-LOCK WHERE ordx.OrderNum < 50 BY ordx.OrderNum", phBuffer).
|
|
103
|
|
|
104
|
/* 2. Inverse Sort Order (Descending) - Already ordered */
|
|
105
|
RUN runSingleQuery("FOR EACH ordx NO-LOCK WHERE ordx.OrderNum < 100 BY ordx.OrderNum DESCENDING", phBuffer).
|
|
106
|
|
|
107
|
/* 3. Multiple Sort Fields (Composite BY) - OrderNum as secondary sort */
|
|
108
|
RUN runSingleQuery("FOR EACH ordx NO-LOCK BY ordx.RepName BY ordx.OrderNum", phBuffer).
|
|
109
|
|
|
110
|
/* === 2. String Operators and Logic === */
|
|
111
|
|
|
112
|
/* 4. String Pattern Matching (MATCHES) - OrderNum as secondary sort */
|
|
113
|
RUN runSingleQuery("FOR EACH ordx NO-LOCK WHERE ordx.RepName MATCHES 'A*' BY ordx.RepName BY ordx.OrderNum", phBuffer).
|
|
114
|
|
|
115
|
/* 5. String Start Check (BEGINS) - OrderNum as secondary sort */
|
|
116
|
RUN runSingleQuery("FOR EACH ordx NO-LOCK WHERE ordx.RepName BEGINS 'J' BY ordx.RepName BY ordx.OrderNum", phBuffer).
|
|
117
|
|
|
118
|
/* === 3. Complex Logic and Ranges === */
|
|
119
|
|
|
120
|
/* 6. Range Filter using >= and <= - Already ordered */
|
|
121
|
RUN runSingleQuery("FOR EACH ordx NO-LOCK WHERE ordx.OrderNum >= 10 AND ordx.OrderNum <= 30 BY ordx.OrderNum", phBuffer).
|
|
122
|
|
|
123
|
/* 7. Complex AND/OR Logic - OrderNum as secondary sort */
|
|
124
|
RUN runSingleQuery("FOR EACH ordx NO-LOCK WHERE (ordx.RepName BEGINS 'S' OR ordx.RepName BEGINS 'B') AND ordx.OrderNum > 20 BY ordx.RepName BY ordx.OrderNum", phBuffer).
|
|
125
|
|
|
126
|
/* === 4. Advanced Clauses and Options === */
|
|
127
|
|
|
128
|
/* 8. NO-LOCK Clause Check - Already ordered */
|
|
129
|
RUN runSingleQuery("FOR EACH ordx NO-LOCK WHERE ordx.OrderNum > 5 BY ordx.OrderNum", phBuffer).
|
|
130
|
|
|
131
|
/* 9. MAX-ROWS Clause Check - Already ordered */
|
|
132
|
RUN runSingleQuery("FOR EACH ordx NO-LOCK WHERE ordx.OrderNum > 1 BY ordx.OrderNum MAX-ROWS 5", phBuffer).
|
|
133
|
|
|
134
|
/* 10. USE-INDEX Clause Check - Already ordered */
|
|
135
|
/* RUN runSingleQuery("FOR EACH ordx USE-INDEX PrimaryIndex WHERE ordx.OrderNum > 10 BY ordx.OrderNum", phBuffer).*/
|
|
136
|
|
|
137
|
/* 12. Complex Date Function - OrderNum as secondary sort */
|
|
138
|
RUN runSingleQuery("FOR EACH ordx NO-LOCK WHERE MONTH(ordx.OrderDate) = 11 AND YEAR(ordx.OrderDate) = 1997 BY ordx.OrderDate BY ordx.OrderNum", phBuffer).
|
|
139
|
|
|
140
|
/* === 5. Edge Cases and Error Handling === */
|
|
141
|
|
|
142
|
/* 13. Error Case (Invalid field/table name) - No need to add BY, the query will fail prepare */
|
|
143
|
RUN runSingleQuery("FOR EACH ordx NO-LOCK WHERE ordx.InvalidField > 0", phBuffer).
|
|
144
|
|
|
145
|
END PROCEDURE.
|
|
146
|
|
|
147
|
/* ---------------------------------------------------------------------- */
|
|
148
|
/* --- 3. MAIN EXECUTION PROCEDURE (Temp-Table Setup) ------------------- */
|
|
149
|
/* ---------------------------------------------------------------------- */
|
|
150
|
PROCEDURE createTempTableTest:
|
|
151
|
/* Get database table handles */
|
|
152
|
buf-ord-hndl = BUFFER Order:HANDLE.
|
|
153
|
buf-rep-hndl = BUFFER SalesRep:HANDLE.
|
|
154
|
|
|
155
|
/* Create an empty, undefined TEMP-TABLE */
|
|
156
|
CREATE TEMP-TABLE tth.
|
|
157
|
/* Give it Order table's fields & indexes */
|
|
158
|
tth:CREATE-LIKE(buf-ord-hndl).
|
|
159
|
/* Add field like SalesRep.RepName */
|
|
160
|
tth:ADD-LIKE-FIELD("RepName","SalesRep.RepName").
|
|
161
|
/* No more fields will be added */
|
|
162
|
tth:TEMP-TABLE-PREPARE("ordx").
|
|
163
|
|
|
164
|
/* Get the buffer handle for the temp-table */
|
|
165
|
bh = tth:DEFAULT-BUFFER-HANDLE.
|
|
166
|
|
|
167
|
/* Populate the temp-table from order */
|
|
168
|
FOR EACH Order NO-LOCK:
|
|
169
|
bh:BUFFER-CREATE.
|
|
170
|
bh:BUFFER-COPY(buf-ord-hndl).
|
|
171
|
/* Add the corresponding salesrep name */
|
|
172
|
FIND SalesRep NO-LOCK WHERE SalesRep.SalesRep = Order.SalesRep NO-ERROR.
|
|
173
|
IF AVAILABLE SalesRep THEN
|
|
174
|
bh:BUFFER-COPY(buf-rep-hndl,?,"RepName,repname").
|
|
175
|
END.
|
|
176
|
|
|
177
|
/* Run a query to access the TEMP-TABLE */
|
|
178
|
CREATE QUERY qh.
|
|
179
|
qh:SET-BUFFERS(bh).
|
|
180
|
qh:QUERY-PREPARE("FOR EACH ordx NO-LOCK WHERE ordx.OrderNum < 50 BY ordx.RepName BY ordx.OrderNum").
|
|
181
|
qh:QUERY-OPEN().
|
|
182
|
|
|
183
|
fld-ordnum-hndl = bh:BUFFER-FIELD("OrderNum").
|
|
184
|
fld-repname-hndl = bh:BUFFER-FIELD("RepName").
|
|
185
|
fld-orderdate-hndl = bh:BUFFER-FIELD("OrderDate").
|
|
186
|
|
|
187
|
/* Display the order number and the salesrep name */
|
|
188
|
REPEAT:
|
|
189
|
qh:GET-NEXT().
|
|
190
|
IF qh:QUERY-OFF-END THEN LEAVE.
|
|
191
|
RUN WriteLogEntry(STRING(fld-ordnum-hndl:BUFFER-VALUE()) + " | " + STRING(fld-repname-hndl:BUFFER-VALUE()) + " | " + STRING(fld-orderdate-hndl:BUFFER-VALUE())).
|
|
192
|
END.
|
|
193
|
|
|
194
|
qh:QUERY-CLOSE().
|
|
195
|
bh:BUFFER-RELEASE().
|
|
196
|
END PROCEDURE.
|
|
197
|
|
|
198
|
/* --- START THE TEST --- */
|
|
199
|
RUN WriteLogEntry("------------------- TEST START -------------------").
|
|
200
|
RUN createTempTableTest.
|
|
201
|
|
|
202
|
DO i = 0 TO runs:
|
|
203
|
RUN runQueryTests(INPUT bh).
|
|
204
|
END.
|
|
205
|
DELETE OBJECT tth.
|
|
206
|
DELETE OBJECT qh.
|
|
207
|
i = 0.
|
|
208
|
RUN WriteLogEntry("------------------- TEST END ---------------------").
|