00001 {*********************************************************}
00002 { }
00003 { Zeos Database Objects }
00004 { DBLib Utility Functions }
00005 { }
00006 { Originally written by Janos Fegyverneki }
00007 { }
00008 {*********************************************************}
00009
00010 {@********************************************************}
00011 { Copyright (c) 1999-2006 Zeos Development Group }
00012 { }
00013 { License Agreement: }
00014 { }
00015 { This library is distributed in the hope that it will be }
00016 { useful, but WITHOUT ANY WARRANTY; without even the }
00017 { implied warranty of MERCHANTABILITY or FITNESS FOR }
00018 { A PARTICULAR PURPOSE. See the GNU Lesser General }
00019 { Public License for more details. }
00020 { }
00021 { The source code of the ZEOS Libraries and packages are }
00022 { distributed under the Library GNU General Public }
00023 { License (see the file COPYING / COPYING.ZEOS) }
00024 { with the following modification: }
00025 { As a special exception, the copyright holders of this }
00026 { library give you permission to link this library with }
00027 { independent modules to produce an executable, }
00028 { regardless of the license terms of these independent }
00029 { modules, and to copy and distribute the resulting }
00030 { executable under terms of your choice, provided that }
00031 { you also meet, for each linked independent module, }
00032 { the terms and conditions of the license of that module. }
00033 { An independent module is a module which is not derived }
00034 { from or based on this library. If you modify this }
00035 { library, you may extend this exception to your version }
00036 { of the library, but you are not obligated to do so. }
00037 { If you do not wish to do so, delete this exception }
00038 { statement from your version. }
00039 { }
00040 { }
00041 { The project web site is located on: }
00042 { http:
00043 { http:
00044 { svn:
00045 { }
00046 { http:
00047 { http:
00048 { }
00049 { }
00050 { }
00051 { Zeos Development Group. }
00052 {********************************************************@}
00053
00054 unit ZDbcDbLibUtils;
00055
00056 interface
00057
00058 {$I ZDbc.inc}
00059
00060 uses Classes, SysUtils, ZVariant, ZDbcIntfs;
00061
00062 {**
00063 Converts an ODBC native types into ZDBC SQL types.
00064 @param FieldType dblibc native field type.
00065 @return a SQL undepended type.
00066 }
00067 function ConvertODBCToSqlType(FieldType: SmallInt): TZSQLType;
00068
00069 {**
00070 Converts a DBLib native types into ZDBC SQL types.
00071 @param FieldType dblibc native field type.
00072 @return a SQL undepended type.
00073 }
00074 function ConvertDBLibToSqlType(FieldType: SmallInt): TZSQLType;
00075
00076 {**
00077 Convert string DBLib field type to SqlType
00078 @param string field type value
00079 @result the SqlType field type value
00080 }
00081 function ConvertDBLibTypeToSqlType(Value: string): TZSQLType;
00082
00083 {**
00084 Converts ZDBC SQL types into MS SQL native types.
00085 @param FieldType dblibc native field type.
00086 @return a SQL undepended type.
00087 }
00088 function ConvertSqlTypeToDBLibType(FieldType: TZSQLType): Integer;
00089
00090 {**
00091 Converts ZDBC SQL types into MS SQL native types.
00092 @param FieldType dblibc native field type.
00093 @return a SQL undepended type.
00094 }
00095 function ConvertSqlTypeToDBLibTypeName(FieldType: TZSQLType): string;
00096
00097 {**
00098 Converts a DBLib nullability value into ZDBC TZColumnNullableType.
00099 @param DBLibNullability dblibc native nullability.
00100 @return a SQL TZColumnNullableType.
00101 }
00102 function ConvertDBLibNullability(DBLibNullability: Byte): TZColumnNullableType;
00103
00104 {**
00105 Prepares an SQL parameter for the query.
00106 @param ParameterIndex the first parameter is 1, the second is 2, ...
00107 @return a string representation of the parameter.
00108 }
00109 function PrepareSQLParameter(Value: TZVariant; ParamType: TZSQLType): string;
00110
00111 implementation
00112
00113 uses ZCompatibility, ZSysUtils, ZPlainDBLibDriver;
00114
00115 {**
00116 Converts an ODBC native types into ZDBC SQL types.
00117 @param FieldType dblibc native field type.
00118 @return a SQL undepended type.
00119 }
00120 function ConvertODBCToSqlType(FieldType: SmallInt): TZSQLType;
00121 begin
00122 case FieldType of
00123 1, 12, -8, -9: Result := stString;
00124 -7: Result := stBoolean;
00125
00126
00127 -6: Result := stShort;
00128 5: Result := stShort;
00129 4: Result := stInteger;
00130 2, 3, 6, 7, 8: Result := stDouble;
00131 11, 93: Result := stTimestamp;
00132 -1, -10: Result := stAsciiStream;
00133 -3, -4, -11: Result := stBinaryStream;
00134 -2: Result := stBytes;
00135 else
00136 Result := stUnknown;
00137 end;
00138 end;
00139
00140 {**
00141 Converts a DBLib native types into ZDBC SQL types.
00142 @param FieldType dblibc native field type.
00143 @return a SQL undepended type.
00144 }
00145 function ConvertDBLibToSqlType(FieldType: SmallInt): TZSQLType;
00146 begin
00147 case FieldType of
00148 SQLCHAR: Result := stString;
00149 SQLBIT: Result := stBoolean;
00150
00151
00152 SQLINT1: Result := stShort;
00153 SQLINT2: Result := stShort;
00154 SQLINT4: Result := stInteger;
00155 SQLFLT4: Result := stDouble;
00156 SQLFLT8: Result := stDouble;
00157 SQLMONEY4: Result := stDouble;
00158 SQLMONEY: Result := stDouble;
00159 SQLDATETIM4: Result := stTimestamp;
00160 SQLDATETIME: Result := stTimestamp;
00161 SQLTEXT: Result := stAsciiStream;
00162 SQLIMAGE: Result := stBinaryStream;
00163 SQLBINARY: Result := stBinaryStream;
00164 else
00165 Result := stUnknown;
00166 end;
00167 end;
00168
00169 {**
00170 Convert string DBLib field type to SqlType
00171 @param string field type value
00172 @result the SqlType field type value
00173 }
00174 function ConvertDBLibTypeToSqlType(Value: string): TZSQLType;
00175 begin
00176 Result := stUnknown;
00177 end;
00178
00179 {**
00180 Converts ZDBC SQL types into DBLib native types.
00181 @param FieldType dblibc native field type.
00182 @return a SQL undepended type.
00183 }
00184 function ConvertSqlTypeToDBLibType(FieldType: TZSQLType): Integer;
00185 begin
00186 Result := -1;
00187 case FieldType of
00188 stBoolean: Result := SQLBIT;
00189 stByte: Result := SQLINT1;
00190 stShort: Result := SQLINT2;
00191 stInteger: Result := SQLINT4;
00192 stLong: Result := SQLFLT8;
00193 stFloat: Result := SQLFLT8;
00194 stDouble: Result := SQLFLT8;
00195 stBigDecimal: Result := SQLFLT8;
00196 stString: Result := SQLCHAR;
00197 stBytes: Result := SQLBINARY;
00198 stDate: Result := SQLDATETIME;
00199 stTime: Result := SQLDATETIME;
00200 stTimestamp: Result := SQLDATETIME;
00201 stAsciiStream: Result := SQLTEXT;
00202 stUnicodeStream: Result := SQLIMAGE;
00203 stBinaryStream: Result := SQLIMAGE;
00204 end;
00205 end;
00206
00207 {**
00208 Converts ZDBC SQL types into DBLib native types.
00209 @param FieldType dblibc native field type.
00210 @return a SQL undepended type.
00211 }
00212 function ConvertSqlTypeToDBLibTypeName(FieldType: TZSQLType): string;
00213 begin
00214 Result := '';
00215 case FieldType of
00216 stBoolean: Result := 'bit';
00217 stByte: Result := 'tinyint';
00218 stShort: Result := 'smallint';
00219 stInteger: Result := 'int';
00220 stLong: Result := 'int';
00221 stFloat: Result := 'float(24)';
00222 stDouble: Result := 'float(53)';
00223 stBigDecimal: Result := 'float(53)';
00224 stString: Result := 'varchar(8000)';
00225 stBytes: Result := 'varbinary(8000)';
00226 stDate: Result := 'datetime';
00227 stTime: Result := 'datetime';
00228 stTimestamp: Result := 'datetime';
00229 stAsciiStream: Result := 'text';
00230 stUnicodeStream: Result := 'ntext';
00231 stBinaryStream: Result := 'image';
00232 end;
00233 end;
00234
00235 {**
00236 Converts a DBLib nullability value into ZDBC TZColumnNullableType.
00237 @param DBLibNullability dblibc native nullability.
00238 @return a SQL TZColumnNullableType.
00239 }
00240 function ConvertDBLibNullability(DBLibNullability: Byte): TZColumnNullableType;
00241 const
00242 Nullability: array[0..2] of TZColumnNullableType =
00243 (ntNoNulls, ntNullable, ntNullableUnknown);
00244 begin
00245 Result := Nullability[DBLibNullability];
00246 end;
00247
00248 {**
00249 Prepares an SQL parameter for the query.
00250 @param ParameterIndex the first parameter is 1, the second is 2, ...
00251 @return a string representation of the parameter.
00252 }
00253 function PrepareSQLParameter(Value: TZVariant; ParamType: TZSQLType): string;
00254 var
00255 TempBytes: TByteDynArray;
00256 TempBlob: IZBlob;
00257 TempString: string;
00258 begin
00259 TempBytes := nil;
00260
00261 if DefVarManager.IsNull(Value) then
00262 Result := 'NULL'
00263 else begin
00264 case ParamType of
00265 stBoolean:
00266 if SoftVarManager.GetAsBoolean(Value) then
00267 Result := '1'
00268 else Result := '0';
00269 stByte, stShort, stInteger, stLong, stFloat, stDouble, stBigDecimal:
00270 Result := SoftVarManager.GetAsString(Value);
00271 stString:
00272 Result := AnsiQuotedStr(SoftVarManager.GetAsString(Value), '''');
00273 stBytes:
00274 begin
00275 TempBytes := StrToBytes(SoftVarManager.GetAsString(Value));
00276 if Length(TempBytes) = 0 then
00277 Result := 'NULL'
00278 else
00279 begin
00280 SetLength(Result, (2 * Length(TempBytes)));
00281 BinToHex(PChar(TempBytes), PChar(Result), Length(TempBytes));
00282 Result := '0x' + Result;
00283 end;
00284 end;
00285 stDate:
00286 Result := '''' + FormatDateTime('yyyymmdd',
00287 SoftVarManager.GetAsDateTime(Value)) + '''';
00288 stTime:
00289 Result := '''' + FormatDateTime('hh":"mm":"ss":"zzz',
00290 SoftVarManager.GetAsDateTime(Value)) + '''';
00291 stTimestamp:
00292 Result := '''' + FormatDateTime('yyyymmdd hh":"mm":"ss":"zzz',
00293 SoftVarManager.GetAsDateTime(Value)) + '''';
00294 stAsciiStream, stUnicodeStream:
00295 begin
00296 TempBlob := DefVarManager.GetAsInterface(Value) as IZBlob;
00297 if not TempBlob.IsEmpty then
00298 begin
00299 Result := AnsiQuotedStr(
00300 StringReplace(TempBlob.GetString, #0, '', [rfReplaceAll]), '''')
00301 end else
00302 Result := 'NULL';
00303 end;
00304 stBinaryStream:
00305 begin
00306 TempBlob := DefVarManager.GetAsInterface(Value) as IZBlob;
00307 if not TempBlob.IsEmpty then
00308 begin
00309 TempString := TempBlob.GetString;
00310 SetLength(Result, (2 * Length(TempString)));
00311 BinToHex(PChar(TempString), PChar(Result), Length(TempString));
00312 Result := '0x' + Result;
00313 end
00314 else
00315 Result := 'NULL';
00316 end;
00317 else
00318 Result := 'NULL';
00319 end;
00320 end;
00321 end;
00322
00323 end.