00001 {*********************************************************}
00002 { }
00003 { Zeos Database Objects }
00004 { MySQL Database Connectivity Classes }
00005 { }
00006 { Originally written by Sergey Seroukhov }
00007 { and Sergey Merkuriev }
00008 { }
00009 {*********************************************************}
00010
00011 {@********************************************************}
00012 { Copyright (c) 1999-2006 Zeos Development Group }
00013 { }
00014 { License Agreement: }
00015 { }
00016 { This library is distributed in the hope that it will be }
00017 { useful, but WITHOUT ANY WARRANTY; without even the }
00018 { implied warranty of MERCHANTABILITY or FITNESS FOR }
00019 { A PARTICULAR PURPOSE. See the GNU Lesser General }
00020 { Public License for more details. }
00021 { }
00022 { The source code of the ZEOS Libraries and packages are }
00023 { distributed under the Library GNU General Public }
00024 { License (see the file COPYING / COPYING.ZEOS) }
00025 { with the following modification: }
00026 { As a special exception, the copyright holders of this }
00027 { library give you permission to link this library with }
00028 { independent modules to produce an executable, }
00029 { regardless of the license terms of these independent }
00030 { modules, and to copy and distribute the resulting }
00031 { executable under terms of your choice, provided that }
00032 { you also meet, for each linked independent module, }
00033 { the terms and conditions of the license of that module. }
00034 { An independent module is a module which is not derived }
00035 { from or based on this library. If you modify this }
00036 { library, you may extend this exception to your version }
00037 { of the library, but you are not obligated to do so. }
00038 { If you do not wish to do so, delete this exception }
00039 { statement from your version. }
00040 { }
00041 { }
00042 { The project web site is located on: }
00043 { http:
00044 { http:
00045 { svn:
00046 { }
00047 { http:
00048 { http:
00049 { }
00050 { }
00051 { }
00052 { Zeos Development Group. }
00053 {********************************************************@}
00054
00055 unit ZDbcMySqlUtils;
00056
00057 interface
00058
00059 {$I ZDbc.inc}
00060
00061 uses
00062 Classes, SysUtils, ZSysUtils, ZDbcIntfs, ZPlainMySqlDriver, ZDbcLogging;
00063
00064 const
00065 MAXBUF = 65535;
00066
00067 type
00068 {** Silent exception }
00069 EZMySQLSilentException = class(EAbort);
00070
00071 {**
00072 Converts a MySQL native types into ZDBC SQL types.
00073 @param PlainDriver a native MySQL plain driver.
00074 @param FieldHandle a handler to field description structure.
00075 @param FieldFlags field flags.
00076 @return a SQL undepended type.
00077 }
00078 function ConvertMySQLHandleToSQLType(PlainDriver: IZMySQLPlainDriver;
00079 FieldHandle: PZMySQLField; FieldFlags: Integer): TZSQLType;
00080
00081 {**
00082 Convert string mysql field type to SQLType
00083 @param string field type value
00084 @result the SQLType field type value
00085 }
00086 function ConvertMySQLTypeToSQLType(TypeName, TypeNameFull: string): TZSQLType;
00087
00088 {**
00089 Checks for possible sql errors.
00090 @param PlainDriver a MySQL plain driver.
00091 @param Handle a MySQL connection handle.
00092 @param LogCategory a logging category.
00093 @param LogMessage a logging message.
00094 }
00095 procedure CheckMySQLError(PlainDriver: IZMySQLPlainDriver;
00096 Handle: PZMySQLConnect; LogCategory: TZLoggingCategory; const LogMessage: string);
00097
00098 procedure EnterSilentMySQLError;
00099 procedure LeaveSilentMySQLError;
00100
00101 {**
00102 Decodes a MySQL Version Value encoded with format:
00103 (major_version * 10,000) + (minor_version * 100) + sub_version
00104 into separated major, minor and subversion values
00105 @param MySQLVersion an integer containing the MySQL Full Version to decode.
00106 @param MajorVersion an integer containing the Major Version decoded.
00107 @param MinorVersion an integer containing the Minor Version decoded.
00108 @param SubVersion an integer contaning the Sub Version (revision) decoded.
00109 }
00110 procedure DecodeMySQLVersioning(const MySQLVersion: Integer;
00111 out MajorVersion: Integer; out MinorVersion: Integer;
00112 out SubVersion: Integer);
00113
00114 {**
00115 Encodes major, minor and subversion (revision) values in MySQL format:
00116 (major_version * 10,000) + (minor_version * 100) + sub_version
00117 For example, 4.1.12 is returned as 40112.
00118 @param MajorVersion an integer containing the Major Version.
00119 @param MinorVersion an integer containing the Minor Version.
00120 @param SubVersion an integer containing the Sub Version (revision).
00121 @return an integer containing the full version.
00122 }
00123 function EncodeMySQLVersioning(const MajorVersion: Integer;
00124 const MinorVersion: Integer; const SubVersion: Integer): Integer;
00125
00126 {**
00127 Decodes a MySQL Version Value and Encodes it to a Zeos SQL Version format:
00128 (major_version * 1,000,000) + (minor_version * 1,000) + sub_version
00129 into separated major, minor and subversion values
00130 @param MySQLVersion an integer containing the Full Version to decode.
00131 @return Encoded Zeos SQL Version Value.
00132 }
00133 function ConvertMySQLVersionToSQLVersion( const MySQLVersion: Integer ): Integer;
00134
00135 implementation
00136
00137 uses ZMessages, ZPlainMySqlConstants;
00138
00139 threadvar
00140 SilentMySQLError: Integer;
00141
00142 procedure EnterSilentMySQLError;
00143 begin
00144 Inc(SilentMySQLError);
00145 end;
00146
00147 procedure LeaveSilentMySQLError;
00148 begin
00149 Dec(SilentMySQLError);
00150 end;
00151
00152 {**
00153 Converts a MySQL native types into ZDBC SQL types.
00154 @param PlainDriver a native MySQL plain driver.
00155 @param FieldHandle a handler to field description structure.
00156 @param FieldFlags a field flags.
00157 @return a SQL undepended type.
00158 }
00159 function ConvertMySQLHandleToSQLType(PlainDriver: IZMySQLPlainDriver;
00160 FieldHandle: PZMySQLField; FieldFlags: Integer): TZSQLType;
00161
00162 function Signed: Boolean;
00163 begin
00164 Result := (UNSIGNED_FLAG and FieldFlags) = 0;
00165 end;
00166
00167 begin
00168 case PlainDriver.GetFieldType(FieldHandle) of
00169 FIELD_TYPE_TINY:
00170 begin
00171 if Signed then Result := stByte
00172 else Result := stShort;
00173 end;
00174 FIELD_TYPE_YEAR, FIELD_TYPE_SHORT:
00175 begin
00176 if Signed then Result := stShort
00177 else Result := stInteger;
00178 end;
00179 FIELD_TYPE_INT24, FIELD_TYPE_LONG:
00180 begin
00181 if Signed then Result := stInteger
00182 else Result := stLong;
00183 end;
00184 FIELD_TYPE_LONGLONG:
00185 begin
00186 if Signed then Result := stLong
00187 else Result := stBigDecimal;
00188 end;
00189 FIELD_TYPE_FLOAT:
00190 Result := stDouble;
00191 FIELD_TYPE_DECIMAL, FIELD_TYPE_NEWDECIMAL: {ADDED FIELD_TYPE_NEWDECIMAL by fduenas 20-06-2006}
00192 begin
00193 if PlainDriver.GetFieldDecimals(FieldHandle) = 0 then
00194 begin
00195 if PlainDriver.GetFieldLength(FieldHandle) < 11 then
00196 Result := stInteger
00197 else Result := stLong;
00198 end else
00199 Result := stDouble;
00200 end;
00201 FIELD_TYPE_DOUBLE:
00202 Result := stDouble;
00203 FIELD_TYPE_DATE, FIELD_TYPE_NEWDATE:
00204 Result := stDate;
00205 FIELD_TYPE_TIME:
00206 Result := stTime;
00207 FIELD_TYPE_DATETIME, FIELD_TYPE_TIMESTAMP:
00208 Result := stTimestamp;
00209 FIELD_TYPE_TINY_BLOB, FIELD_TYPE_MEDIUM_BLOB,
00210 FIELD_TYPE_LONG_BLOB, FIELD_TYPE_BLOB:
00211 if (FieldFlags and BINARY_FLAG) = 0 then
00212 Result := stAsciiStream
00213 else Result := stBinaryStream;
00214 FIELD_TYPE_BIT:
00215 Result := stBinaryStream;
00216 FIELD_TYPE_VARCHAR:
00217 Result := stString;
00218 FIELD_TYPE_VAR_STRING:
00219 Result := stString;
00220 FIELD_TYPE_STRING:
00221 Result := stString;
00222 FIELD_TYPE_ENUM:
00223 Result := stString;
00224 FIELD_TYPE_SET:
00225 Result := stString;
00226 FIELD_TYPE_NULL:
00227
00228 Result := stString;
00229 FIELD_TYPE_GEOMETRY:
00230
00231 Result := stBinaryStream;
00232 else
00233 raise Exception.Create('Unknown MySQL data type!');
00234 end;
00235 { Fix by the HeidiSql team. - See their SVN repository rev.775 and 900}
00236 { SHOW FULL PROCESSLIST on 4.x servers can return veeery long FIELD_TYPE_VAR_STRINGs. The following helps avoid excessive row buffer allocation later on. }
00237 if (Result = stString) and (PlainDriver.GetFieldLength(FieldHandle) > 8192) then Result := stAsciiStream;
00238 end;
00239
00240 {**
00241 Convert string mysql field type to SQLType
00242 @param string field type value
00243 @result the SQLType field type value
00244 }
00245 function ConvertMySQLTypeToSQLType(TypeName, TypeNameFull: string): TZSQLType;
00246 const
00247 GeoTypes: array[0..7] of string = (
00248 'POINT','LINESTRING','POLYGON','GEOMETRY',
00249 'MULTIPOINT','MULTILINESTRING','MULTIPOLYGON','GEOMETRYCOLLECTION'
00250 );
00251 var
00252 IsUnsigned: Boolean;
00253 Posi, Len, i: Integer;
00254 Spec: string;
00255 begin
00256 TypeName := UpperCase(TypeName);
00257 TypeNameFull := UpperCase(TypeNameFull);
00258 Result := stUnknown;
00259
00260 Posi := FirstDelimiter(' ', TypeName);
00261 if Posi > 0 then
00262 TypeName := Copy(TypeName, 1, Posi - 1);
00263
00264 Spec := '';
00265 Posi := FirstDelimiter(' ', TypeNameFull);
00266 if Posi > 0 then
00267 Spec := Copy(TypeNameFull, Posi + 1, Length(TypeNameFull)-Posi);
00268
00269 IsUnsigned := Pos('UNSIGNED', Spec) > 0;
00270
00271 if TypeName = 'TINYINT' then
00272 begin
00273 if IsUnsigned then
00274 Result := stShort
00275 else Result := stByte;
00276 end
00277 else if TypeName = 'YEAR' then
00278 Result := stShort
00279 else if TypeName = 'SMALLINT' then
00280 begin
00281 if IsUnsigned then
00282 Result := stInteger
00283 else Result := stShort;
00284 end
00285 else if TypeName = 'MEDIUMINT' then
00286 Result := stInteger
00287 else if (TypeName = 'INT') or (TypeName = 'INTEGER') then
00288 begin
00289 if IsUnsigned then Result := stLong
00290 else Result := stInteger
00291 end
00292 else if TypeName = 'BIGINT' then
00293 Result := stLong
00294 else if TypeName = 'INT24' then
00295 Result := stLong
00296 else if TypeName = 'REAL' then
00297 begin
00298 if IsUnsigned then
00299 Result := stDouble
00300 else Result := stFloat;
00301 end
00302 else if TypeName = 'FLOAT' then
00303 begin
00304
00305 Result := stDouble
00306
00307 end
00308 else if TypeName = 'DECIMAL' then
00309 begin
00310 if EndsWith(TypeNameFull, ',0)') then
00311 begin
00312 Len := StrToInt(Copy(TypeNameFull, 9, Length(TypeNameFull) - 11));
00313 if Len < 10 then
00314 Result := stInteger
00315 else Result := stLong;
00316 end else
00317 Result := stDouble;
00318 end
00319 else if TypeName = 'DOUBLE' then
00320 Result := stDouble
00321 else if TypeName = 'CHAR' then
00322 Result := stString
00323 else if TypeName = 'VARCHAR' then
00324 Result := stString
00325 else if TypeName = 'VARBINARY' then
00326 Result := stBytes
00327 else if TypeName = 'BINARY' then
00328 Result := stBytes
00329 else if TypeName = 'DATE' then
00330 Result := stDate
00331 else if TypeName = 'TIME' then
00332 Result := stTime
00333 else if TypeName = 'TIMESTAMP' then
00334 Result := stTimestamp
00335 else if TypeName = 'DATETIME' then
00336 Result := stTimestamp
00337 else if TypeName = 'TINYBLOB' then
00338 Result := stBinaryStream
00339 else if TypeName = 'BLOB' then
00340 Result := stBinaryStream
00341 else if TypeName = 'MEDIUMBLOB' then
00342 Result := stBinaryStream
00343 else if TypeName = 'LONGBLOB' then
00344 Result := stBinaryStream
00345 else if TypeName = 'TINYTEXT' then
00346 Result := stAsciiStream
00347 else if TypeName = 'TEXT' then
00348 Result := stAsciiStream
00349 else if TypeName = 'MEDIUMTEXT' then
00350 Result := stAsciiStream
00351 else if TypeName = 'LONGTEXT' then
00352 Result := stAsciiStream
00353 else if TypeName = 'ENUM' then
00354 begin
00355 if (TypeNameFull = 'ENUM(''Y'',''N'')')
00356 or (TypeNameFull = 'ENUM(''N'',''Y'')') then
00357 Result := stBoolean
00358 else Result := stString;
00359 end
00360 else if TypeName = 'SET' then
00361 Result := stString
00362 else if TypeName = 'BIT' then
00363 Result := stBinaryStream
00364 else for i := 0 to Length(GeoTypes)-1 do if GeoTypes[i] = TypeName then Result := stBinaryStream;
00365
00366 if Result = stUnknown then raise Exception.Create('Unknown MySQL data type!');
00367 end;
00368
00369 {**
00370 Checks for possible sql errors.
00371 @param PlainDriver a MySQL plain driver.
00372 @param Handle a MySQL connection handle.
00373 @param LogCategory a logging category.
00374 @param LogMessage a logging message.
00375 }
00376 procedure CheckMySQLError(PlainDriver: IZMySQLPlainDriver;
00377 Handle: PZMySQLConnect; LogCategory: TZLoggingCategory; const LogMessage: string);
00378 var
00379 ErrorMessage: string;
00380 ErrorCode: Integer;
00381 begin
00382 ErrorMessage := Trim(StrPas(PlainDriver.GetLastError(Handle)));
00383 ErrorCode := PlainDriver.GetLastErrorCode(Handle);
00384 if (ErrorCode <> 0) and (ErrorMessage <> '') then
00385 begin
00386 if SilentMySQLError > 0 then
00387 raise EZMySQLSilentException.CreateFmt(SSQLError1, [ErrorMessage]);
00388
00389 DriverManager.LogError(LogCategory, PlainDriver.GetProtocol, LogMessage,
00390 ErrorCode, ErrorMessage);
00391 raise EZSQLException.CreateWithCode(ErrorCode,
00392 Format(SSQLError1, [ErrorMessage]));
00393 end;
00394 end;
00395
00396 {**
00397 Decodes a MySQL Version Value encoded with format:
00398 (major_version * 10,000) + (minor_version * 100) + sub_version
00399 into separated major, minor and subversion values
00400 @param MySQLVersion an integer containing the MySQL Full Version to decode.
00401 @param MajorVersion an integer containing the Major Version decoded.
00402 @param MinorVersion an integer containing the Minor Version decoded.
00403 @param SubVersion an integer contaning the Sub Version (revision) decoded.
00404 }
00405 procedure DecodeMySQLVersioning(const MySQLVersion: Integer;
00406 out MajorVersion: Integer; out MinorVersion: Integer;
00407 out SubVersion: Integer);
00408 begin
00409 MajorVersion := MySQLVersion DIV 10000;
00410 MinorVersion := (MySQLVersion-(MajorVersion*10000)) DIV 100;
00411 SubVersion := MySQLVersion-(MajorVersion*10000)-(MinorVersion*100);
00412 end;
00413
00414 {**
00415 Encodes major, minor and subversion (revision) values in MySQL format:
00416 (major_version * 10,000) + (minor_version * 100) + sub_version
00417 For example, 4.1.12 is returned as 40112.
00418 @param MajorVersion an integer containing the Major Version.
00419 @param MinorVersion an integer containing the Minor Version.
00420 @param SubVersion an integer containing the Sub Version (revision).
00421 @return an integer containing the full version.
00422 }
00423 function EncodeMySQLVersioning(const MajorVersion: Integer;
00424 const MinorVersion: Integer; const SubVersion: Integer): Integer;
00425 begin
00426 Result := (MajorVersion * 10000) + (MinorVersion * 100) + SubVersion;
00427 end;
00428
00429 {**
00430 Decodes a MySQL Version Value and Encodes it to a Zeos SQL Version format:
00431 (major_version * 1,000,000) + (minor_version * 1,000) + sub_version
00432 into separated major, minor and subversion values
00433 So it transforms a version in format XYYZZ to XYYYZZZ where:
00434 X = major_version
00435 Y = minor_version
00436 Z = sub version
00437 @param MySQLVersion an integer containing the Full MySQL Version to decode.
00438 @return Encoded Zeos SQL Version Value.
00439 }
00440 function ConvertMySQLVersionToSQLVersion( const MySQLVersion: Integer ): integer;
00441 var MajorVersion, MinorVersion, SubVersion: Integer;
00442 begin
00443 DecodeMySQLVersioning(MySQLVersion,MajorVersion,MinorVersion,SubVersion);
00444 Result := EncodeSQLVersioning(MajorVersion,MinorVersion,SubVersion);
00445 end;
00446
00447 end.