00001 {*********************************************************}
00002 { }
00003 { Zeos Database Objects }
00004 { MySQL Database Connectivity Classes }
00005 { }
00006 { Originally written by Sergey Seroukhov }
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 ZDbcMySqlStatement;
00055
00056 interface
00057
00058 {$I ZDbc.inc}
00059
00060 uses
00061 Classes, SysUtils, ZDbcIntfs, ZDbcStatement, ZPlainMySqlDriver,
00062 ZCompatibility, ZDbcLogging, ZVariant;
00063
00064 type
00065
00066 {** Represents a MYSQL specific connection interface. }
00067 IZMySQLStatement = interface (IZStatement)
00068 ['{A05DB91F-1E40-46C7-BF2E-25D74978AC83}']
00069
00070 function IsUseResult: Boolean;
00071 end;
00072
00073
00074 {** Implements Generic MySQL Statement. }
00075 TZMySQLStatement = class(TZAbstractStatement, IZMySQLStatement)
00076 private
00077 FHandle: PZMySQLConnect;
00078 FPlainDriver: IZMySQLPlainDriver;
00079 FUseResult: Boolean;
00080 FSQL: string;
00081
00082 function CreateResultSet(const SQL: string): IZResultSet;
00083 public
00084 constructor Create(PlainDriver: IZMySQLPlainDriver;
00085 Connection: IZConnection; Info: TStrings; Handle: PZMySQLConnect);
00086
00087 function ExecuteQuery(const SQL: string): IZResultSet; override;
00088 function ExecuteUpdate(const SQL: string): Integer; override;
00089 function Execute(const SQL: string): Boolean; override;
00090
00091 function GetMoreResults: Boolean; override;
00092
00093 function IsUseResult: Boolean;
00094 end;
00095
00096 {** Implements Prepared SQL Statement. }
00097 TZMySQLPreparedStatement = class(TZEmulatedPreparedStatement)
00098 private
00099 FHandle: PZMySQLConnect;
00100 FPlainDriver: IZMySQLPlainDriver;
00101 protected
00102 function CreateExecStatement: IZStatement; override;
00103 function GetEscapeString(const Value: string): string;
00104 function PrepareSQLParam(ParamIndex: Integer): string; override;
00105 public
00106 constructor Create(PlainDriver: IZMySQLPlainDriver;
00107 Connection: IZConnection; const SQL: string; Info: TStrings;
00108 Handle: PZMySQLConnect);
00109 end;
00110
00111 implementation
00112
00113 uses
00114 ZDbcMySql, ZDbcMySqlUtils, ZDbcMySqlResultSet, ZSysUtils,
00115 ZMessages, ZDbcCachedResultSet, ZDbcUtils{$IFNDEF VER130BELOW}, DateUtils{$ENDIF};
00116
00117 { TZMySQLStatement }
00118
00119 {**
00120 Constructs this object and assignes the main properties.
00121 @param PlainDriver a native MySQL plain driver.
00122 @param Connection a database connection object.
00123 @param Handle a connection handle pointer.
00124 @param Info a statement parameters.
00125 }
00126 constructor TZMySQLStatement.Create(PlainDriver: IZMySQLPlainDriver;
00127 Connection: IZConnection; Info: TStrings; Handle: PZMySQLConnect);
00128 var
00129 MySQLConnection: IZMySQLConnection;
00130 begin
00131 inherited Create(Connection, Info);
00132 FHandle := Handle;
00133 FPlainDriver := PlainDriver;
00134 ResultSetType := rtScrollInsensitive;
00135
00136 MySQLConnection := Connection as IZMySQLConnection;
00137 FUseResult := StrToBoolEx(DefineStatementParameter(Self, 'useresult', 'false'));
00138 end;
00139
00140 {**
00141 Checks is use result should be used in result sets.
00142 @return <code>True</code> use result in result sets,
00143 <code>False</code> store result in result sets.
00144 }
00145 function TZMySQLStatement.IsUseResult: Boolean;
00146 begin
00147 Result := FUseResult;
00148 end;
00149
00150 {**
00151 Creates a result set based on the current settings.
00152 @return a created result set object.
00153 }
00154 function TZMySQLStatement.CreateResultSet(const SQL: string): IZResultSet;
00155 var
00156 CachedResolver: TZMySQLCachedResolver;
00157 NativeResultSet: TZMySQLResultSet;
00158 CachedResultSet: TZCachedResultSet;
00159 begin
00160 NativeResultSet := TZMySQLResultSet.Create(FPlainDriver, Self, SQL, FHandle,
00161 FUseResult);
00162 NativeResultSet.SetConcurrency(rcReadOnly);
00163 if (GetResultSetConcurrency <> rcReadOnly) or (FUseResult
00164 and (GetResultSetType <> rtForwardOnly)) then
00165 begin
00166 CachedResolver := TZMySQLCachedResolver.Create(FPlainDriver, FHandle, Self,
00167 NativeResultSet.GetMetaData);
00168 CachedResultSet := TZCachedResultSet.Create(NativeResultSet, SQL,
00169 CachedResolver);
00170 CachedResultSet.SetConcurrency(GetResultSetConcurrency);
00171 Result := CachedResultSet;
00172 end else
00173 Result := NativeResultSet;
00174 end;
00175
00176 {**
00177 Executes an SQL statement that returns a single <code>ResultSet</code> object.
00178 @param sql typically this is a static SQL <code>SELECT</code> statement
00179 @return a <code>ResultSet</code> object that contains the data produced by the
00180 given query; never <code>null</code>
00181 }
00182 function TZMySQLStatement.ExecuteQuery(const SQL: string): IZResultSet;
00183 begin
00184 Result := nil;
00185 if FPlainDriver.ExecQuery(FHandle, PChar(SQL)) = 0 then
00186 begin
00187 DriverManager.LogMessage(lcExecute, FPlainDriver.GetProtocol, SQL);
00188 {$IFDEF ENABLE_MYSQL_DEPRECATED}
00189 if FPlainDriver.GetClientVersion < 32200 then
00190 begin
00191
00192 if FPlainDriver.GetStatus(FHandle) = MYSQL_STATUS_READY then
00193 raise EZSQLException.Create(SCanNotOpenResultSet);
00194 end
00195 else
00196 {$ENDIF ENABLE_MYSQL_DEPRECATED}
00197 if not FPlainDriver.ResultSetExists(FHandle) then
00198 raise EZSQLException.Create(SCanNotOpenResultSet);
00199 Result := CreateResultSet(SQL);
00200 end else
00201 CheckMySQLError(FPlainDriver, FHandle, lcExecute, SQL);
00202 end;
00203
00204 {**
00205 Executes an SQL <code>INSERT</code>, <code>UPDATE</code> or
00206 <code>DELETE</code> statement. In addition,
00207 SQL statements that return nothing, such as SQL DDL statements,
00208 can be executed.
00209
00210 @param sql an SQL <code>INSERT</code>, <code>UPDATE</code> or
00211 <code>DELETE</code> statement or an SQL statement that returns nothing
00212 @return either the row count for <code>INSERT</code>, <code>UPDATE</code>
00213 or <code>DELETE</code> statements, or 0 for SQL statements that return nothing
00214 }
00215 function TZMySQLStatement.ExecuteUpdate(const SQL: string): Integer;
00216 var
00217 QueryHandle: PZMySQLResult;
00218 HasResultset : Boolean;
00219 begin
00220 Result := -1;
00221 if FPlainDriver.ExecQuery(FHandle, PChar(SQL)) = 0 then
00222 begin
00223 DriverManager.LogMessage(lcExecute, FPlainDriver.GetProtocol, SQL);
00224 {$IFDEF ENABLE_MYSQL_DEPRECATED}
00225 if FPlainDriver.GetClientVersion < 32200 then
00226 HasResultSet := FPlainDriver.GetStatus(FHandle) <> MYSQL_STATUS_READY
00227 else
00228 HasResultSet := FPlainDriver.ResultSetExists(FHandle);
00229 {$ELSE}
00230 HasResultSet := FPlainDriver.ResultSetExists(FHandle);
00231 {$ENDIF ENABLE_MYSQL_DEPRECATED}
00232 { Process queries with result sets }
00233 if HasResultSet then
00234 begin
00235 QueryHandle := FPlainDriver.StoreResult(FHandle);
00236 if QueryHandle <> nil then
00237 begin
00238 Result := FPlainDriver.GetRowCount(QueryHandle);
00239 FPlainDriver.FreeResult(QueryHandle);
00240 end else
00241 Result := FPlainDriver.GetAffectedRows(FHandle);
00242 end
00243 { Process regular query }
00244 else Result := FPlainDriver.GetAffectedRows(FHandle);
00245 end else
00246 CheckMySQLError(FPlainDriver, FHandle, lcExecute, SQL);
00247 LastUpdateCount := Result;
00248 end;
00249
00250 {**
00251 Executes an SQL statement that may return multiple results.
00252 Under some (uncommon) situations a single SQL statement may return
00253 multiple result sets and/or update counts. Normally you can ignore
00254 this unless you are (1) executing a stored procedure that you know may
00255 return multiple results or (2) you are dynamically executing an
00256 unknown SQL string. The methods <code>execute</code>,
00257 <code>getMoreResults</code>, <code>getResultSet</code>,
00258 and <code>getUpdateCount</code> let you navigate through multiple results.
00259
00260 The <code>execute</code> method executes an SQL statement and indicates the
00261 form of the first result. You can then use the methods
00262 <code>getResultSet</code> or <code>getUpdateCount</code>
00263 to retrieve the result, and <code>getMoreResults</code> to
00264 move to any subsequent result(s).
00265
00266 @param sql any SQL statement
00267 @return <code>true</code> if the next result is a <code>ResultSet</code> object;
00268 <code>false</code> if it is an update count or there are no more results
00269 }
00270 function TZMySQLStatement.Execute(const SQL: string): Boolean;
00271 var
00272 HasResultset : Boolean;
00273 begin
00274 Result := False;
00275 FSQL := SQL;
00276 if FPlainDriver.ExecQuery(FHandle, PChar(SQL)) = 0 then
00277 begin
00278 DriverManager.LogMessage(lcExecute, FPlainDriver.GetProtocol, SQL);
00279 {$IFDEF ENABLE_MYSQL_DEPRECATED}
00280 if FPlainDriver.GetClientVersion < 32200 then
00281 HasResultSet := FPlainDriver.GetStatus(FHandle) <> MYSQL_STATUS_READY
00282 else
00283 HasResultSet := FPlainDriver.ResultSetExists(FHandle);
00284 {$ELSE}
00285 HasResultSet := FPlainDriver.ResultSetExists(FHandle);
00286 {$ENDIF ENABLE_MYSQL_DEPRECATED}
00287 { Process queries with result sets }
00288 if HasResultSet then
00289 begin
00290 Result := True;
00291 LastResultSet := CreateResultSet(SQL);
00292 end
00293 { Processes regular query. }
00294 else
00295 begin
00296 Result := False;
00297 LastUpdateCount := FPlainDriver.GetAffectedRows(FHandle);
00298 end;
00299 end else
00300 CheckMySQLError(FPlainDriver, FHandle, lcExecute, SQL);
00301 end;
00302
00303 {**
00304 Moves to a <code>Statement</code> object's next result. It returns
00305 <code>true</code> if this result is a <code>ResultSet</code> object.
00306 This method also implicitly closes any current <code>ResultSet</code>
00307 object obtained with the method <code>getResultSet</code>.
00308
00309 <P>There are no more results when the following is true:
00310 <PRE>
00311 <code>(!getMoreResults() && (getUpdateCount() == -1)</code>
00312 </PRE>
00313
00314 @return <code>true</code> if the next result is a <code>ResultSet</code> object;
00315 <code>false</code> if it is an update count or there are no more results
00316 @see #execute
00317 }
00318 function TZMySQLStatement.GetMoreResults: Boolean;
00319 var
00320 AStatus: integer;
00321 begin
00322 Result := inherited GetMoreResults;
00323 if FPlainDriver.GetClientVersion >= 40100 then
00324 begin
00325 AStatus := FPlainDriver.RetrieveNextRowset(FHandle);
00326 if AStatus > 0 then
00327 CheckMySQLError(FPlainDriver, FHandle, lcExecute, FSQL)
00328 else
00329 Result := (AStatus = 0);
00330
00331 if LastResultSet <> nil then
00332 LastResultSet.Close;
00333 LastResultSet := nil;
00334 LastUpdateCount := -1;
00335 if FPlainDriver.ResultSetExists(FHandle) then
00336 LastResultSet := CreateResultSet(FSQL)
00337 else
00338 LastUpdateCount := FPlainDriver.GetAffectedRows(FHandle);
00339 end;
00340 end;
00341
00342 { TZMySQLEmulatedPreparedStatement }
00343
00344 {**
00345 Constructs this object and assignes the main properties.
00346 @param PlainDriver a native MySQL Plain driver.
00347 @param Connection a database connection object.
00348 @param Info a statement parameters.
00349 @param Handle a connection handle pointer.
00350 }
00351 constructor TZMySQLPreparedStatement.Create(PlainDriver: IZMySQLPlainDriver;
00352 Connection: IZConnection; const SQL: string; Info: TStrings; Handle: PZMySQLConnect);
00353 begin
00354 inherited Create(Connection, SQL, Info);
00355 FHandle := Handle;
00356 FPlainDriver := PlainDriver;
00357 ResultSetType := rtScrollInsensitive;
00358 end;
00359
00360 {**
00361 Creates a temporary statement which executes queries.
00362 @param Info a statement parameters.
00363 @return a created statement object.
00364 }
00365 function TZMySQLPreparedStatement.CreateExecStatement: IZStatement;
00366 begin
00367 Result := TZMySQLStatement.Create(FPlainDriver, Connection, Info,FHandle);
00368 end;
00369
00370 {**
00371 Converts an string into escape MySQL format.
00372 @param Value a regular string.
00373 @return a string in MySQL escape format.
00374 }
00375 function TZMySQLPreparedStatement.GetEscapeString(const Value: string): string;
00376 var
00377 BufferLen: Integer;
00378 Buffer: PChar;
00379 begin
00380 BufferLen := Length(Value) * 2 + 1;
00381 GetMem(Buffer, BufferLen);
00382 If FHandle = nil then
00383 BufferLen := FPlainDriver.GetEscapeString(Buffer, PChar(Value), Length(Value))
00384 else
00385 BufferLen := FPlainDriver.GetRealEscapeString(FHandle, Buffer, PChar(Value), Length(Value));
00386 Result := '''' + BufferToStr(Buffer, BufferLen) + '''';
00387 FreeMem(Buffer);
00388 end;
00389
00390 {**
00391 Prepares an SQL parameter for the query.
00392 @param ParameterIndex the first parameter is 1, the second is 2, ...
00393 @return a string representation of the parameter.
00394 }
00395 function TZMySQLPreparedStatement.PrepareSQLParam(ParamIndex: Integer): string;
00396 var
00397 Value: TZVariant;
00398 TempBytes: TByteDynArray;
00399 TempBlob: IZBlob;
00400
00401 AYear, AMonth, ADay, AHour, AMinute, ASecond, AMilliSecond: Word;
00402 begin
00403 TempBytes := nil;
00404 if InParamCount <= ParamIndex then
00405 raise EZSQLException.Create(SInvalidInputParameterCount);
00406
00407 Value := InParamValues[ParamIndex];
00408 if DefVarManager.IsNull(Value) then
00409 if (InParamDefaultValues[ParamIndex] <> '') and
00410 StrToBoolEx(DefineStatementParameter(Self, 'defaults', 'true')) then
00411 Result := InParamDefaultValues[ParamIndex]
00412 else
00413 Result := 'NULL'
00414 else begin
00415 case InParamTypes[ParamIndex] of
00416 stBoolean:
00417 if SoftVarManager.GetAsBoolean(Value) then Result := '''Y'''
00418 else Result := '''N''';
00419 stByte, stShort, stInteger, stLong, stBigDecimal, stFloat, stDouble:
00420 Result := SoftVarManager.GetAsString(Value);
00421 stString, stBytes:
00422 Result := GetEscapeString(SoftVarManager.GetAsString(Value));
00423 stDate:
00424 begin
00425 {$IFNDEF VER130BELOW}
00426 DecodeDateTime(SoftVarManager.GetAsDateTime(Value),
00427 AYear, AMonth, ADay, AHour, AMinute, ASecond, AMilliSecond);
00428 {$ELSE}
00429 DecodeDate(SoftVarManager.GetAsDateTime(Value),
00430 AYear, AMonth, ADay);
00431 DecodeTime(SoftVarManager.GetAsDateTime(Value),
00432 AHour, AMinute, ASecond, AMilliSecond);
00433 {$ENDIF}
00434 Result := '''' + Format('%0.4d-%0.2d-%0.2d',
00435 [AYear, AMonth, ADay]) + '''';
00436 end;
00437 stTime:
00438 begin
00439 {$IFNDEF VER130BELOW}
00440 DecodeDateTime(SoftVarManager.GetAsDateTime(Value),
00441 AYear, AMonth, ADay, AHour, AMinute, ASecond, AMilliSecond);
00442 {$ELSE}
00443 DecodeDate(SoftVarManager.GetAsDateTime(Value),
00444 AYear, AMonth, ADay);
00445 DecodeTime(SoftVarManager.GetAsDateTime(Value),
00446 AHour, AMinute, ASecond, AMilliSecond);
00447 {$ENDIF}
00448 Result := '''' + Format('%0.2d:%0.2d:%0.2d',
00449 [AHour, AMinute, ASecond]) + '''';
00450 end;
00451 stTimestamp:
00452 begin
00453 {$IFNDEF VER130BELOW}
00454 DecodeDateTime(SoftVarManager.GetAsDateTime(Value),
00455 AYear, AMonth, ADay, AHour, AMinute, ASecond, AMilliSecond);
00456 {$ELSE}
00457 DecodeDate(SoftVarManager.GetAsDateTime(Value),
00458 AYear, AMonth, ADay);
00459 DecodeTime(SoftVarManager.GetAsDateTime(Value),
00460 AHour, AMinute, ASecond, AMilliSecond);
00461 {$ENDIF}
00462 Result := '''' + Format('%0.4d-%0.2d-%0.2d %0.2d:%0.2d:%0.2d',
00463 [AYear, AMonth, ADay, AHour, AMinute, ASecond]) + '''';
00464 end;
00465 stAsciiStream, stUnicodeStream, stBinaryStream:
00466 begin
00467 TempBlob := DefVarManager.GetAsInterface(Value) as IZBlob;
00468 if not TempBlob.IsEmpty then
00469 Result := GetEscapeString(TempBlob.GetString)
00470 else Result := 'NULL';
00471 end;
00472 end;
00473 end;
00474 end;
00475
00476 end.