zeoslib  UNKNOWN
 All Files
ZDbcDbLibUtils.pas
Go to the documentation of this file.
1 {*********************************************************}
2 { }
3 { Zeos Database Objects }
4 { DBLib Utility Functions }
5 { }
6 { Originally written by Janos Fegyverneki }
7 { }
8 {*********************************************************}
9 
10 {@********************************************************}
11 { Copyright (c) 1999-2012 Zeos Development Group }
12 { }
13 { License Agreement: }
14 { }
15 { This library is distributed in the hope that it will be }
16 { useful, but WITHOUT ANY WARRANTY; without even the }
17 { implied warranty of MERCHANTABILITY or FITNESS FOR }
18 { A PARTICULAR PURPOSE. See the GNU Lesser General }
19 { Public License for more details. }
20 { }
21 { The source code of the ZEOS Libraries and packages are }
22 { distributed under the Library GNU General Public }
23 { License (see the file COPYING / COPYING.ZEOS) }
24 { with the following modification: }
25 { As a special exception, the copyright holders of this }
26 { library give you permission to link this library with }
27 { independent modules to produce an executable, }
28 { regardless of the license terms of these independent }
29 { modules, and to copy and distribute the resulting }
30 { executable under terms of your choice, provided that }
31 { you also meet, for each linked independent module, }
32 { the terms and conditions of the license of that module. }
33 { An independent module is a module which is not derived }
34 { from or based on this library. If you modify this }
35 { library, you may extend this exception to your version }
36 { of the library, but you are not obligated to do so. }
37 { If you do not wish to do so, delete this exception }
38 { statement from your version. }
39 { }
40 { }
41 { The project web site is located on: }
42 { http://zeos.firmos.at (FORUM) }
43 { http://sourceforge.net/p/zeoslib/tickets/ (BUGTRACKER)}
44 { svn://svn.code.sf.net/p/zeoslib/code-0/trunk (SVN) }
45 { }
46 { http://www.sourceforge.net/projects/zeoslib. }
47 { }
48 { }
49 { Zeos Development Group. }
50 {********************************************************@}
51 
52 unit ZDbcDbLibUtils;
53 
54 interface
55 
56 {$I ZDbc.inc}
57 
58 uses Classes, SysUtils, ZVariant, ZDbcIntfs, ZPlainDBLibDriver, ZCompatibility;
59 
60 {**
61  Converts an ODBC native types into ZDBC SQL types.
62  @param FieldType dblibc native field type.
63  @return a SQL undepended type.
64 }
65 function ConvertODBCToSqlType(FieldType: SmallInt; CtrlsCPType: TZControlsCodePage): TZSQLType;
66 
67 {**
68  Converts a DBLib native types into ZDBC SQL types.
69  @param FieldType dblibc native field type.
70  @return a SQL undepended type.
71 }
72 function ConvertDBLibToSqlType(FieldType: SmallInt; CtrlsCPType: TZControlsCodePage): TZSQLType;
73 function ConvertFreeTDSToSqlType(const FieldType: SmallInt;
74  const CtrlsCPType: TZControlsCodePage): TZSQLType;
75 
76 {**
77  Convert string DBLib field type to SqlType
78  @param string field type value
79  @result the SqlType field type value
80 }
81 function ConvertDBLibTypeToSqlType(Value: string): TZSQLType;
82 
83 {**
84  Converts ZDBC SQL types into MS SQL native types.
85  @param FieldType dblibc native field type.
86  @return a SQL undepended type.
87 }
88 function ConvertSqlTypeToDBLibType(FieldType: TZSQLType): Integer;
89 function ConvertSqlTypeToFreeTDSType(FieldType: TZSQLType): Integer;
90 
91 {**
92  Converts ZDBC SQL types into MS SQL native types.
93  @param FieldType dblibc native field type.
94  @return a SQL undepended type.
95 }
96 function ConvertSqlTypeToDBLibTypeName(FieldType: TZSQLType): string;
97 function ConvertSqlTypeToFreeTDSTypeName(FieldType: TZSQLType): string;
98 
99 {**
100  Converts a DBLib nullability value into ZDBC TZColumnNullableType.
101  @param DBLibNullability dblibc native nullability.
102  @return a SQL TZColumnNullableType.
103 }
104 function ConvertDBLibNullability(DBLibNullability: Byte): TZColumnNullableType;
105 
106 {**
107  Prepares an SQL parameter for the query.
108  @param ParameterIndex the first parameter is 1, the second is 2, ...
109  @return a string representation of the parameter.
110 }
111 function PrepareSQLParameter(Value: TZVariant; ParamType: TZSQLType;
112  ConSettings: PZConSettings; PlainDriver: IZDBLibPlainDriver;
113  const NChar: Boolean = False): RawByteString;
114 
115 implementation
116 
117 uses Types, ZSysUtils, ZPlainDbLibConstants, ZEncoding, ZDbcUtils
118  {$IFDEF WITH_UNITANSISTRINGS}, AnsiStrings{$ENDIF};
119 
120 {**
121  Converts an ODBC native types into ZDBC SQL types.
122  @param FieldType dblibc native field type.
123  @return a SQL undepended type.
124 }
125 function ConvertODBCToSqlType(FieldType: SmallInt;
126  CtrlsCPType: TZControlsCodePage): TZSQLType;
127 begin
128  case FieldType of
129  1, 12, -8, -9: Result := stString;
130  -7{bit}: Result := stBoolean;
131 //Bug #889223, bug with tinyint on mssql
132 // -6: Result := stByte;
133  -5: Result := stLong;
134  -6: Result := stShort;
135  5: Result := stShort;
136  4: Result := stInteger;
137  2, 3, 6, 7, 8: Result := stDouble;
138  11, 93: Result := stTimestamp;
139  -1, -10: Result := stAsciiStream;
140  -4{image}: Result := stBinaryStream;
141  -2{binary},-3{varbinary},-11{uniqueidentifier}: Result := stBytes;
142  else
143  Result := stUnknown;
144  end;
145  if CtrlsCPType = cCP_UTF16 then
146  case Result of
147  stString: Result := stUnicodeString;
148  stAsciiStream: Result := stUnicodeStream;
149  end;
150 end;
151 
152 {**
153  Converts a DBLib native types into ZDBC SQL types.
154  @param FieldType dblibc native field type.
155  @return a SQL undepended type.
156 }
157 function ConvertDBLibToSqlType(FieldType: SmallInt;
158  CtrlsCPType: TZControlsCodePage): TZSQLType;
159 begin
160  case FieldType of
161  DBLIBSQLCHAR: Result := stString;
162  DBLIBSQLBIT: Result := stBoolean;
163 //Bug #889223, bug with tinyint on mssql
164 // DBLIBSQLINT1: Result := stByte;
165  DBLIBSQLINT1: Result := stShort;
166  DBLIBSQLINT2: Result := stShort;
167  DBLIBSQLINT4: Result := stInteger;
168  DBLIBSQLFLT4: Result := stDouble;
169  DBLIBSQLFLT8: Result := stDouble;
170  DBLIBSQLMONEY4: Result := stDouble;
171  DBLIBSQLMONEY: Result := stDouble;
172  DBLIBSQLDATETIM4: Result := stTimestamp;
173  DBLIBSQLDATETIME: Result := stTimestamp;
174  DBLIBSQLTEXT: Result := stAsciiStream;
175  DBLIBSQLIMAGE: Result := stBinaryStream;
176  DBLIBSQLBINARY: Result := stBinaryStream;
177  else
178  Result := stUnknown;
179  end;
180  if CtrlsCPType = cCP_UTF16 then
181  case Result of
182  stString: Result := stUnicodeString;
183  stAsciiStream: Result := stUnicodeStream;
184  end;
185 end;
186 
187 {**
188  Converts a FreeTDS native types into ZDBC SQL types.
189  @param FieldType dblibc native field type.
190  @return a SQL undepended type.
191 }
192 function ConvertFreeTDSToSqlType(const FieldType: SmallInt;
193  const CtrlsCPType: TZControlsCodePage): TZSQLType;
194 begin
195  case FieldType of
196  SYBCHAR, SYBVARCHAR, XSYBCHAR, XSYBVARCHAR: Result := stString;
197  SYBINTN, SYBINT4: Result := stInteger;
198  SYBINT8: Result := stLong;
199  SYBNUMERIC: Result := stBigDecimal;
200  SYBINT1, SYBINT2: Result := stShort;
201  SYBFLT8, SYBFLTN, SYBREAL, SYBDECIMAL: Result := stDouble;
202  SYBDATETIME, SYBDATETIME4, SYBDATETIMN: Result := stTimestamp;
203  SYBBIT, SYBBITN: Result := stBoolean;
204  SYBTEXT: Result := stAsciiStream;
205  SYBNTEXT: Result := stUnicodeStream;
206  SYBIMAGE: Result := stBinaryStream;
207  SYBBINARY, SYBVARBINARY,
208  XSYBBINARY, XSYBVARBINARY: Result := stBytes;
209  SYBMONEY4, SYBMONEY, SYBMONEYN: Result := stDouble;
210  SYBVOID: Result := stUnknown;
211  SYBNVARCHAR, XSYBNCHAR, XSYBNVARCHAR: Result := stUnicodeString;
212  SYBMSXML: Result := stBinaryStream;
213  SYBUNIQUE: Result := stString;
214  SYBVARIANT: Result := stString;
215  SYBMSUDT: Result := stString;
216  else
217  Result := stUnknown;
218  end;
219  if CtrlsCPType = cCP_UTF16 then
220  case Result of
221  stString: Result := stUnicodeString;
222  stAsciiStream: Result := stUnicodeStream;
223  end;
224 end;
225 
226 {**
227  Convert string DBLib field type to SqlType
228  @param string field type value
229  @result the SqlType field type value
230 }
231 function ConvertDBLibTypeToSqlType(Value: string): TZSQLType;
232 begin
233  Result := stUnknown;
234 end;
235 
236 {**
237  Converts ZDBC SQL types into DBLib native types.
238  @param FieldType dblibc native field type.
239  @return a SQL undepended type.
240 }
241 function ConvertSqlTypeToDBLibType(FieldType: TZSQLType): Integer;
242 begin
243  Result := -1;
244  case FieldType of
245  stBoolean: Result := DBLIBSQLBIT;
246  stByte: Result := DBLIBSQLINT1;
247  stShort: Result := DBLIBSQLINT2;
248  stInteger: Result := DBLIBSQLINT4;
249  stLong: Result := DBLIBSQLFLT8;
250  stFloat: Result := DBLIBSQLFLT8;
251  stDouble: Result := DBLIBSQLFLT8;
252  stBigDecimal: Result := DBLIBSQLFLT8;
253  stString: Result := DBLIBSQLCHAR;
254  stBytes: Result := DBLIBSQLBINARY;
255  stDate: Result := DBLIBSQLDATETIME;
256  stTime: Result := DBLIBSQLDATETIME;
257  stTimestamp: Result := DBLIBSQLDATETIME;
258  stAsciiStream: Result := DBLIBSQLTEXT;
259  stUnicodeStream: Result := DBLIBSQLIMAGE;
260  stBinaryStream: Result := DBLIBSQLIMAGE;
261  end;
262 end;
263 
264 {**
265  Converts ZDBC SQL types into DBLib native types.
266  @param FieldType dblibc native field type.
267  @return a SQL undepended type.
268 }
269 function ConvertSqlTypeToDBLibTypeName(FieldType: TZSQLType): string;
270 begin
271  Result := '';
272  case FieldType of
273  stBoolean: Result := 'bit';
274  stByte: Result := 'tinyint';
275  stShort: Result := 'smallint';
276  stInteger: Result := 'int';
277  stLong: Result := 'bigint';
278  stFloat: Result := 'float(24)';
279  stDouble: Result := 'float(53)';
280  stBigDecimal: Result := 'float(53)';
281  stString: Result := 'varchar(8000)';
282  stBytes: Result := 'varbinary(8000)';
283  stDate: Result := 'datetime';
284  stTime: Result := 'datetime';
285  stTimestamp: Result := 'datetime';
286  stAsciiStream: Result := 'text';
287  stUnicodeStream: Result := 'ntext';
288  stBinaryStream: Result := 'image';
289  end;
290 end;
291 
292 {**
293  Converts ZDBC SQL types into FreeTDS native types.
294  @param FieldType dblibc native field type.
295  @return a SQL undepended type.
296 }
297 function ConvertSqlTypeToFreeTDSType(FieldType: TZSQLType): Integer;
298 begin
299  Result := -1;
300  case FieldType of
301  stBoolean: Result := SYBBIT;
302  stByte: Result := SYBINT1;
303  stShort: Result := SYBINT2;
304  stInteger: Result := SYBINT4;
305  stLong: Result := SYBFLT8;
306  stFloat: Result := SYBFLT8;
307  stDouble: Result := SYBFLT8;
308  stBigDecimal: Result := SYBFLT8;
309  stString: Result := SYBCHAR;
310  stUnicodeString: Result := SYBNVARCHAR;
311  stBytes: Result := SYBBINARY;
312  stDate: Result := SYBDATETIME;
313  stTime: Result := SYBDATETIME;
314  stTimestamp: Result := SYBDATETIME;
315  stAsciiStream: Result := SYBTEXT;
316  stUnicodeStream: Result := SYBNTEXT;
317  stBinaryStream: Result := SYBIMAGE;
318  end;
319 end;
320 
321 {**
322  Converts ZDBC SQL types into FreeTDS native types.
323  @param FieldType dblibc native field type.
324  @return a SQL undepended type.
325 }
326 function ConvertSqlTypeToFreeTDSTypeName(FieldType: TZSQLType): string;
327 begin
328  Result := '';
329  case FieldType of
330  stBoolean: Result := 'bit';
331  stByte: Result := 'tinyint';
332  stShort: Result := 'smallint';
333  stInteger: Result := 'int';
334  stLong: Result := 'bigint';
335  stFloat: Result := 'float(24)';
336  stDouble: Result := 'float(53)';
337  stBigDecimal: Result := 'float(53)';
338  stString: Result := 'varchar(8000)';
339  stUnicodeString: Result := 'nvarchar(4000)';
340  stBytes: Result := 'varbinary(8000)';
341  stDate: Result := 'datetime';
342  stTime: Result := 'datetime';
343  stTimestamp: Result := 'datetime';
344  stAsciiStream: Result := 'text';
345  stUnicodeStream: Result := 'ntext';
346  stBinaryStream: Result := 'image';
347  end;
348 end;
349 
350 
351 {**
352  Converts a DBLib nullability value into ZDBC TZColumnNullableType.
353  @param DBLibNullability dblibc native nullability.
354  @return a SQL TZColumnNullableType.
355 }
356 function ConvertDBLibNullability(DBLibNullability: Byte): TZColumnNullableType;
357 const
358  Nullability: array[0..2] of TZColumnNullableType =
359  (ntNoNulls, ntNullable, ntNullableUnknown);
360 begin
361  Result := Nullability[DBLibNullability];
362 end;
363 
364 {**
365  Prepares an SQL parameter for the query.
366  @param ParameterIndex the first parameter is 1, the second is 2, ...
367  @return a string representation of the parameter.
368 }
369 function PrepareSQLParameter(Value: TZVariant; ParamType: TZSQLType;
370  ConSettings: PZConSettings; PlainDriver: IZDBLibPlainDriver;
371  const NChar: Boolean = False): RawByteString;
372 var
373  TempBytes: TByteDynArray;
374  TempBlob: IZBlob;
375 begin
376  TempBytes := nil;
377 
378  if DefVarManager.IsNull(Value) then
379  Result := 'NULL'
380  else
381  begin
382  case ParamType of
383  stBoolean:
384  if SoftVarManager.GetAsBoolean(Value) then
385  Result := '1'
386  else
387  Result := '0';
388  stByte, stShort, stInteger, stLong, stFloat, stDouble, stBigDecimal:
389  Result := RawByteString(SoftVarManager.GetAsString(Value));
390  stString:
391  if NChar then
392  Result := {$IFDEF WITH_UNITANSISTRINGS}AnsiStrings.{$ENDIF}AnsiQuotedStr(PlainDriver.ZPlainString(SoftVarManager.GetAsString(Value), ConSettings, zCP_UTF8), '''')
393  else
394  Result := {$IFDEF WITH_UNITANSISTRINGS}AnsiStrings.{$ENDIF}AnsiQuotedStr(PlainDriver.ZPlainString(SoftVarManager.GetAsString(Value), ConSettings), '''');
395  stUnicodeString:
396  if NChar then
397  Result := {$IFDEF WITH_UNITANSISTRINGS}AnsiStrings.{$ENDIF}AnsiQuotedStr(UTF8Encode(SoftVarManager.GetAsUnicodeString(Value)),'''')
398  else
399  Result := {$IFDEF WITH_UNITANSISTRINGS}AnsiStrings.{$ENDIF}AnsiQuotedStr(PlainDriver.ZPlainString(SoftVarManager.GetAsUnicodeString(Value), ConSettings),'''');
400  stBytes:
401  begin
402  TempBytes := SoftVarManager.GetAsBytes(Value);
403  if Length(TempBytes) = 0 then
404  Result := 'NULL'
405  else
406  Result := GetSQLHexAnsiString(PAnsiChar(TempBytes), Length(TempBytes), True);
407  end;
408  stDate:
409  Result := RawByteString('''' + FormatDateTime(ConSettings^.DateFormat,
410  SoftVarManager.GetAsDateTime(Value)) + '''');
411  stTime:
412  Result := RawByteString('''' + FormatDateTime('hh":"mm":"ss":"zzz',
413  SoftVarManager.GetAsDateTime(Value)) + '''');
414  stTimestamp:
415  Result := RawByteString('''' + FormatDateTime(ConSettings^.DateFormat+' hh":"mm":"ss":"zzz',
416  SoftVarManager.GetAsDateTime(Value)) + '''');
417  stAsciiStream, stUnicodeStream, stBinaryStream:
418  begin
419  TempBlob := DefVarManager.GetAsInterface(Value) as IZBlob;
420  if not TempBlob.IsEmpty then
421  begin
422  if ParamType = stBinaryStream then
423  Result := GetSQLHexAnsiString(PAnsiChar(TempBlob.GetBuffer), TempBlob.Length, True)
424  else
425  if NChar then
426  {$IFDEF WITH_UNITANSISTRINGS}
427  Result := AnsiStrings.AnsiQuotedStr(AnsiStrings.StringReplace(
428  GetValidatedAnsiStringFromBuffer(TempBlob.GetBuffer,
429  TempBlob.Length, ConSettings, zCP_UTF8), #0, '', [rfReplaceAll]), '''')
430  else
431  Result := AnsiStrings.AnsiQuotedStr(AnsiStrings.StringReplace(
432  GetValidatedAnsiStringFromBuffer(TempBlob.GetBuffer,
433  TempBlob.Length, TempBlob.WasDecoded, ConSettings), #0, '', [rfReplaceAll]), '''')
434  {$ELSE}
435  Result := AnsiQuotedStr(StringReplace(
436  GetValidatedAnsiStringFromBuffer(TempBlob.GetBuffer,
437  TempBlob.Length, ConSettings, zCP_UTF8), #0, '', [rfReplaceAll]), '''')
438  else
439  Result := AnsiQuotedStr(StringReplace(
440  GetValidatedAnsiStringFromBuffer(TempBlob.GetBuffer,
441  TempBlob.Length, TempBlob.WasDecoded, ConSettings), #0, '', [rfReplaceAll]), '''')
442  {$ENDIF}
443  end
444  else
445  Result := 'NULL';
446  TempBlob := nil;
447  end;
448  else
449  Result := 'NULL';
450  end;
451  end;
452 end;
453 
454 end.