32 |
|
{************************************************************************} |
33 |
|
|
34 |
|
unit IBUtils; |
35 |
+ |
{$IFDEF MSWINDOWS} |
36 |
+ |
{$DEFINE WINDOWS} |
37 |
+ |
{$ENDIF} |
38 |
|
|
39 |
|
{$IFDEF FPC} |
40 |
|
{$Mode Delphi} |
43 |
|
|
44 |
|
interface |
45 |
|
|
46 |
< |
uses |
44 |
< |
{$IFDEF WINDOWS } |
45 |
< |
Windows, |
46 |
< |
{$ELSE} |
47 |
< |
unix, |
48 |
< |
{$ENDIF} |
49 |
< |
Classes, SysUtils; |
46 |
> |
uses Classes, SysUtils; |
47 |
|
|
48 |
|
const |
49 |
|
CRLF = #13 + #10; |
52 |
|
TAB = #9; |
53 |
|
NULL_TERMINATOR = #0; |
54 |
|
|
55 |
< |
sqlReservedWords: array [0..166] of string = ( |
56 |
< |
'ADD','ADMIN','ALL','ALTER','AND','ANY','AS','AT','AVG','BEGIN','BETWEEN','BIGINT','BIT_LENGTH','BLOB','BOTH', |
57 |
< |
'BY','CASE','CAST','CHAR','CHAR_LENGTH','CHARACTER','CHARACTER_LENGTH','CHECK','CLOSE','COLLATE','COLUMN', |
58 |
< |
'COMMIT','CONNECT','CONSTRAINT','COUNT','CREATE','CROSS','CURRENT','CURRENT_CONNECTION','CURRENT_DATE', |
59 |
< |
'CURRENT_ROLE','CURRENT_TIME','CURRENT_TIMESTAMP','CURRENT_TRANSACTION','CURRENT_USER','CURSOR','DATE', |
60 |
< |
'DAY','DEC','DECIMAL','DECLARE','DEFAULT','DELETE','DISCONNECT','DISTINCT','DOUBLE','DROP','ELSE','END', |
61 |
< |
'ESCAPE','EXECUTE','EXISTS','EXTERNAL','EXTRACT','FETCH','FILTER','FLOAT','FOR','FOREIGN','FROM','FULL', |
62 |
< |
'FUNCTION','GDSCODE','GLOBAL','GRANT','GROUP','HAVING','HOUR','IN','INDEX','INNER','INSENSITIVE','INSERT', |
63 |
< |
'INT','INTEGER','INTO','IS','JOIN','LEADING','LEFT','LIKE','LONG','LOWER','MAX','MAXIMUM_SEGMENT','MERGE', |
64 |
< |
'MIN','MINUTE','MONTH','NATIONAL','NATURAL','NCHAR','NO','NOT','NULL','NUMERIC','OCTET_LENGTH','OF','ON', |
65 |
< |
'ONLY','OPEN','OR','ORDER','OUTER','PARAMETER','PLAN','POSITION','POST_EVENT','PRECISION','PRIMARY', |
66 |
< |
'PROCEDURE','RDB$DB_KEY','REAL','RECORD_VERSION','RECREATE','RECURSIVE','REFERENCES','RELEASE','RETURNING_VALUES', |
67 |
< |
'RETURNS','REVOKE','RIGHT','ROLLBACK','ROW_COUNT','ROWS','SAVEPOINT','SECOND','SELECT','SENSITIVE', |
68 |
< |
'SET','SIMILAR','SMALLINT','SOME','SQLCODE','SQLSTATE','START','SUM','TABLE','THEN','TIME', |
69 |
< |
'TIMESTAMP','TO','TRAILING','TRIGGER','TRIM','UNION','UNIQUE','UPDATE','UPPER','USER','USING', |
70 |
< |
'VALUE','VALUES','VARCHAR','VARIABLE','VARYING','VIEW','WHEN','WHERE','WHILE','WITH','YEAR'); |
55 |
> |
sqlReservedWords: array [0..197] of string = ( |
56 |
> |
'ADD', |
57 |
> |
'ADMIN', |
58 |
> |
'ALL', |
59 |
> |
'ALTER', |
60 |
> |
'AND', |
61 |
> |
'ANY', |
62 |
> |
'AS', |
63 |
> |
'AT', |
64 |
> |
'AVG', |
65 |
> |
'BEGIN', |
66 |
> |
'BETWEEN', |
67 |
> |
'BIGINT', |
68 |
> |
'BIT_LENGTH', |
69 |
> |
'BLOB', |
70 |
> |
'BOOLEAN', |
71 |
> |
'BOTH', |
72 |
> |
'BY', |
73 |
> |
'CASE', |
74 |
> |
'CAST', |
75 |
> |
'CHAR', |
76 |
> |
'CHAR_LENGTH', |
77 |
> |
'CHARACTER', |
78 |
> |
'CHARACTER_LENGTH', |
79 |
> |
'CHECK', |
80 |
> |
'CLOSE', |
81 |
> |
'COLLATE', |
82 |
> |
'COLUMN', |
83 |
> |
'COMMIT', |
84 |
> |
'CONNECT', |
85 |
> |
'CONSTRAINT', |
86 |
> |
'CORR', |
87 |
> |
'COUNT', |
88 |
> |
'COVAR_POP', |
89 |
> |
'COVAR_SAMP', |
90 |
> |
'CREATE', |
91 |
> |
'CROSS', |
92 |
> |
'CURRENT', |
93 |
> |
'CURRENT_CONNECTION', |
94 |
> |
'CURRENT_DATE', |
95 |
> |
'CURRENT_ROLE', |
96 |
> |
'CURRENT_TIME', |
97 |
> |
'CURRENT_TIMESTAMP', |
98 |
> |
'CURRENT_TRANSACTION', |
99 |
> |
'CURRENT_USER', |
100 |
> |
'CURSOR', |
101 |
> |
'DATE', |
102 |
> |
'DAY', |
103 |
> |
'DEC', |
104 |
> |
'DECIMAL', |
105 |
> |
'DECLARE', |
106 |
> |
'DEFAULT', |
107 |
> |
'DELETE', |
108 |
> |
'DELETING', |
109 |
> |
'DETERMINISTIC', |
110 |
> |
'DISCONNECT', |
111 |
> |
'DISTINCT', |
112 |
> |
'DOUBLE', |
113 |
> |
'DROP', |
114 |
> |
'ELSE', |
115 |
> |
'END', |
116 |
> |
'ESCAPE', |
117 |
> |
'EXECUTE', |
118 |
> |
'EXISTS', |
119 |
> |
'EXTERNAL', |
120 |
> |
'EXTRACT', |
121 |
> |
'FALSE', |
122 |
> |
'FETCH', |
123 |
> |
'FILTER', |
124 |
> |
'FLOAT', |
125 |
> |
'FOR', |
126 |
> |
'FOREIGN', |
127 |
> |
'FROM', |
128 |
> |
'FULL', |
129 |
> |
'FUNCTION', |
130 |
> |
'GDSCODE', |
131 |
> |
'GLOBAL', |
132 |
> |
'GRANT', |
133 |
> |
'GROUP', |
134 |
> |
'HAVING', |
135 |
> |
'HOUR', |
136 |
> |
'IN', |
137 |
> |
'INDEX', |
138 |
> |
'INNER', |
139 |
> |
'INSENSITIVE', |
140 |
> |
'INSERT', |
141 |
> |
'INSERTING', |
142 |
> |
'INT', |
143 |
> |
'INTEGER', |
144 |
> |
'INTO', |
145 |
> |
'IS', |
146 |
> |
'JOIN', |
147 |
> |
'LEADING', |
148 |
> |
'LEFT', |
149 |
> |
'LIKE', |
150 |
> |
'LONG', |
151 |
> |
'LOWER', |
152 |
> |
'MAX', |
153 |
> |
'MAXIMUM_SEGMENT', |
154 |
> |
'MERGE', |
155 |
> |
'MIN', |
156 |
> |
'MINUTE', |
157 |
> |
'MONTH', |
158 |
> |
'NATIONAL', |
159 |
> |
'NATURAL', |
160 |
> |
'NCHAR', |
161 |
> |
'NO', |
162 |
> |
'NOT', |
163 |
> |
'NULL', |
164 |
> |
'NUMERIC', |
165 |
> |
'OCTET_LENGTH', |
166 |
> |
'OF', |
167 |
> |
'OFFSET', |
168 |
> |
'ON', |
169 |
> |
'ONLY', |
170 |
> |
'OPEN', |
171 |
> |
'OR', |
172 |
> |
'ORDER', |
173 |
> |
'OUTER', |
174 |
> |
'OVER', |
175 |
> |
'PARAMETER', |
176 |
> |
'PLAN', |
177 |
> |
'POSITION', |
178 |
> |
'POST_EVENT', |
179 |
> |
'PRECISION', |
180 |
> |
'PRIMARY', |
181 |
> |
'PROCEDURE', |
182 |
> |
'RDB$DB_KEY', |
183 |
> |
'RDB$RECORD_VERSION', |
184 |
> |
'REAL', |
185 |
> |
'RECORD_VERSION', |
186 |
> |
'RECREATE', |
187 |
> |
'RECURSIVE', |
188 |
> |
'REFERENCES', |
189 |
> |
'REGR_AVGX', |
190 |
> |
'REGR_AVGY', |
191 |
> |
'REGR_COUNT', |
192 |
> |
'REGR_INTERCEPT', |
193 |
> |
'REGR_R2', |
194 |
> |
'REGR_SLOPE', |
195 |
> |
'REGR_SXX', |
196 |
> |
'REGR_SXY', |
197 |
> |
'REGR_SYY', |
198 |
> |
'RELEASE', |
199 |
> |
'RETURN', |
200 |
> |
'RETURNING_VALUES', |
201 |
> |
'RETURNS', |
202 |
> |
'REVOKE', |
203 |
> |
'RIGHT', |
204 |
> |
'ROLLBACK', |
205 |
> |
'ROW', |
206 |
> |
'ROW_COUNT', |
207 |
> |
'ROWS', |
208 |
> |
'SAVEPOINT', |
209 |
> |
'SCROLL', |
210 |
> |
'SECOND', |
211 |
> |
'SELECT', |
212 |
> |
'SENSITIVE', |
213 |
> |
'SET', |
214 |
> |
'SIMILAR', |
215 |
> |
'SMALLINT', |
216 |
> |
'SOME', |
217 |
> |
'SQLCODE', |
218 |
> |
'SQLSTATE', |
219 |
> |
'SQLSTATE', |
220 |
> |
'START', |
221 |
> |
'STDDEV_POP', |
222 |
> |
'STDDEV_SAMP', |
223 |
> |
'SUM', |
224 |
> |
'TABLE', |
225 |
> |
'THEN', |
226 |
> |
'TIME', |
227 |
> |
'TIMESTAMP', |
228 |
> |
'TO', |
229 |
> |
'TRAILING', |
230 |
> |
'TRIGGER', |
231 |
> |
'TRIM', |
232 |
> |
'TRUE', |
233 |
> |
'UNION', |
234 |
> |
'UNIQUE', |
235 |
> |
'UNKNOWN', |
236 |
> |
'UPDATE', |
237 |
> |
'UPDATING', |
238 |
> |
'UPPER', |
239 |
> |
'USER', |
240 |
> |
'USING', |
241 |
> |
'VALUE', |
242 |
> |
'VALUES', |
243 |
> |
'VAR_POP', |
244 |
> |
'VAR_SAMP', |
245 |
> |
'VARCHAR', |
246 |
> |
'VARIABLE', |
247 |
> |
'VARYING', |
248 |
> |
'VIEW', |
249 |
> |
'WHEN', |
250 |
> |
'WHERE', |
251 |
> |
'WHILE', |
252 |
> |
'WITH', |
253 |
> |
'YEAR' |
254 |
> |
); |
255 |
|
|
256 |
|
function Max(n1, n2: Integer): Integer; |
257 |
|
function Min(n1, n2: Integer): Integer; |
258 |
< |
function RandomString(iLength: Integer): String; |
258 |
> |
function RandomString(iLength: Integer): AnsiString; |
259 |
|
function RandomInteger(iLow, iHigh: Integer): Integer; |
260 |
< |
function StripString(st: String; CharsToStrip: String): String; |
261 |
< |
function FormatIdentifier(Dialect: Integer; Value: String): String; |
262 |
< |
function FormatIdentifierValue(Dialect: Integer; Value: String): String; |
263 |
< |
function FormatIdentifierValueNC(Dialect: Integer; Value: String): String; |
264 |
< |
function ExtractIdentifier(Dialect: Integer; Value: String): String; |
265 |
< |
function QuoteIdentifier(Dialect: Integer; Value: String): String; |
266 |
< |
function QuoteIdentifierIfNeeded(Dialect: Integer; Value: String): String; |
267 |
< |
function Space2Underscore(s: string): string; |
260 |
> |
function StripString(st: AnsiString; CharsToStrip: AnsiString): AnsiString; |
261 |
> |
function FormatIdentifier(Dialect: Integer; Value: AnsiString): AnsiString; |
262 |
> |
function FormatIdentifierValue(Dialect: Integer; Value: AnsiString): AnsiString; |
263 |
> |
function FormatIdentifierValueNC(Dialect: Integer; Value: AnsiString): AnsiString; |
264 |
> |
function ExtractIdentifier(Dialect: Integer; Value: AnsiString): AnsiString; |
265 |
> |
function QuoteIdentifier(Dialect: Integer; Value: AnsiString): AnsiString; |
266 |
> |
function QuoteIdentifierIfNeeded(Dialect: Integer; Value: AnsiString): AnsiString; |
267 |
> |
function Space2Underscore(s: AnsiString): AnsiString; |
268 |
> |
function SQLSafeString(const s: AnsiString): AnsiString; |
269 |
> |
function IsSQLIdentifier(Value: AnsiString): boolean; |
270 |
|
|
271 |
|
implementation |
272 |
|
|
286 |
|
result := n2; |
287 |
|
end; |
288 |
|
|
289 |
< |
function RandomString(iLength: Integer): String; |
289 |
> |
function RandomString(iLength: Integer): AnsiString; |
290 |
|
begin |
291 |
|
result := ''; |
292 |
|
while Length(result) < iLength do |
300 |
|
result := Trunc(Random(iHigh - iLow)) + iLow; |
301 |
|
end; |
302 |
|
|
303 |
< |
function StripString(st: String; CharsToStrip: String): String; |
303 |
> |
function StripString(st: AnsiString; CharsToStrip: AnsiString): AnsiString; |
304 |
|
var |
305 |
|
i: Integer; |
306 |
|
begin |
311 |
|
end; |
312 |
|
end; |
313 |
|
|
314 |
< |
function FormatIdentifier(Dialect: Integer; Value: String): String; |
314 |
> |
function FormatIdentifier(Dialect: Integer; Value: AnsiString): AnsiString; |
315 |
|
begin |
316 |
|
Value := Trim(Value); |
317 |
|
if Dialect = 1 then |
324 |
|
Result := Value; |
325 |
|
end; |
326 |
|
|
327 |
< |
function FormatIdentifierValue(Dialect: Integer; Value: String): String; |
327 |
> |
function FormatIdentifierValue(Dialect: Integer; Value: AnsiString): AnsiString; |
328 |
|
begin |
329 |
|
Value := Trim(Value); |
330 |
|
if Dialect = 1 then |
343 |
|
Result := Value; |
344 |
|
end; |
345 |
|
|
346 |
< |
function FormatIdentifierValueNC(Dialect: Integer; Value: String): String; |
346 |
> |
function FormatIdentifierValueNC(Dialect: Integer; Value: AnsiString): AnsiString; |
347 |
|
begin |
348 |
|
Value := Trim(Value); |
349 |
|
if Dialect = 1 then |
362 |
|
Result := Value; |
363 |
|
end; |
364 |
|
|
365 |
< |
function ExtractIdentifier(Dialect: Integer; Value: String): String; |
365 |
> |
function ExtractIdentifier(Dialect: Integer; Value: AnsiString): AnsiString; |
366 |
|
begin |
367 |
|
Value := Trim(Value); |
368 |
|
if Dialect = 1 then |
381 |
|
Result := Value; |
382 |
|
end; |
383 |
|
|
384 |
< |
function IsReservedWord(w: string): boolean; |
384 |
> |
function IsReservedWord(w: AnsiString): boolean; |
385 |
|
var i: integer; |
386 |
|
begin |
387 |
|
Result := true; |
391 |
|
Result := false; |
392 |
|
end; |
393 |
|
|
394 |
< |
function QuoteIdentifier(Dialect: Integer; Value: String): String; |
394 |
> |
function QuoteIdentifier(Dialect: Integer; Value: AnsiString): AnsiString; |
395 |
|
begin |
396 |
|
if Dialect = 1 then |
397 |
|
Value := AnsiUpperCase(Trim(Value)) |
400 |
|
Result := Value; |
401 |
|
end; |
402 |
|
|
403 |
< |
function QuoteIdentifierIfNeeded(Dialect: Integer; Value: String): String; |
403 |
> |
const |
404 |
> |
ValidSQLIdentifierChars = ['A'..'Z','a'..'z','0'..'9','_','$']; |
405 |
> |
|
406 |
> |
function IsSQLIdentifier(Value: AnsiString): boolean; |
407 |
> |
var i: integer; |
408 |
> |
begin |
409 |
> |
Result := false; |
410 |
> |
for i := 1 to Length(Value) do |
411 |
> |
if not (Value[i] in ValidSQLIdentifierChars) then Exit; |
412 |
> |
Result := true; |
413 |
> |
end; |
414 |
> |
|
415 |
> |
function QuoteIdentifierIfNeeded(Dialect: Integer; Value: AnsiString): AnsiString; |
416 |
|
begin |
417 |
|
if (Dialect = 3) and |
418 |
< |
((AnsiUpperCase(Value) <> Value) or IsReservedWord(Value)) then |
418 |
> |
(IsReservedWord(Value) or not IsSQLIdentifier(Value)) then |
419 |
|
Result := '"' + Value + '"' |
420 |
|
else |
421 |
|
Result := Value |
422 |
|
end; |
423 |
|
|
424 |
< |
function Space2Underscore(s: string): string; |
424 |
> |
function Space2Underscore(s: AnsiString): AnsiString; |
425 |
|
var |
426 |
|
k: integer; |
427 |
|
begin |
431 |
|
Result[k] := '_'; |
432 |
|
end; |
433 |
|
|
434 |
+ |
function SQLSafeString(const s: AnsiString): AnsiString; |
435 |
+ |
begin |
436 |
+ |
Result := StringReplace(s,'''','''''',[rfReplaceAll]); |
437 |
+ |
end; |
438 |
|
|
439 |
|
end. |