00001 {*********************************************************}
00002 { }
00003 { Zeos Database Objects }
00004 { Unidatabase UpdateSQL component }
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 ZSqlUpdate;
00055
00056 interface
00057
00058 {$I ZComponent.inc}
00059
00060 uses
00061 SysUtils, Classes, DB, ZDbcIntfs, ZDbcCachedResultSet, ZDbcCache, ZSqlStrings;
00062
00063 type
00064 {ADDED BY fduenas}
00065 TZBeforeSQLStatementEvent = procedure(const Sender: TObject;
00066 StatementIndex: Integer; out Execute: Boolean ) of object;
00067
00068 TZAfterSQLStatementEvent = procedure(const Sender: TObject;
00069 StatementIndex: Integer) of object;
00070
00071 TZAfterInsertSQLStatementEvent = procedure(const Sender: TObject;
00072 StatementIndex: Integer; out UpdateAutoIncFields: Boolean ) of object;
00073
00074 {**
00075 Implements an object which manages SQL DML statements to update TDatasets.
00076 }
00077 TZUpdateSQL = class(TComponent, IZCachedResolver)
00078 private
00079 FDataSet: TDataSet;
00080
00081 FDeleteSQL: TZSQLStrings;
00082 FInsertSQL: TZSQLStrings;
00083 FModifySQL: TZSQLStrings;
00084
00085 FRefreshSQL: TZSQLStrings;
00086
00087
00088 FParamCheck: Boolean;
00089 FParams: TParams;
00090 FMultiStatements: Boolean;
00091 FBeforeDeleteSQL: TNotifyEvent;
00092 FBeforeInsertSQL: TNotifyEvent;
00093 FBeforeModifySQL: TNotifyEvent;
00094 FAfterDeleteSQL: TNotifyEvent;
00095 FAfterInsertSQL: TNotifyEvent;
00096 FAfterModifySQL: TNotifyEvent;
00097 FUseSequenceFieldForRefreshSQL: Boolean;
00098 {New Statement Events added by Fduenas}
00099 FBeforeDeleteSQLStatement: TZBeforeSQLStatementEvent;
00100 FAfterDeleteSQLStatement: TZAfterSQLStatementEvent;
00101 FBeforeInsertSQLStatement: TZBeforeSQLStatementEvent;
00102 FAfterInsertSQLStatement: TZAfterInsertSQLStatementEvent;
00103 FBeforeModifySQLStatement: TZBeforeSQLStatementEvent;
00104 FAfterModifySQLStatement: TZAfterSQLStatementEvent;
00105
00106 procedure SetUseSequenceFieldForRefreshSQL(const Value: Boolean);
00107 procedure SetDataset(Value: TDataset);
00108 function GetSQL(UpdateKind: TUpdateKind): TStrings;
00109 procedure SetSQL(UpdateKind: TUpdateKind; Value: TStrings);
00110 function GetParamsCount: Word;
00111 procedure SetParamsList(Value: TParams);
00112 procedure SetParamCheck(Value: Boolean);
00113 procedure SetMultiStatements(Value: Boolean);
00114
00115 function GetDeleteSQL: TStrings;
00116 procedure SetDeleteSQL(Value: TStrings);
00117 function GetInsertSQL: TStrings;
00118 procedure SetInsertSQL(Value: TStrings);
00119 function GetModifySQL: TStrings;
00120 procedure SetModifySQL(Value: TStrings);
00121
00122
00123 function GetRefreshSQL: TStrings;
00124 procedure SetRefreshSQL(Value: TStrings);
00125
00126
00127 procedure ReadParamData(Reader: TReader);
00128 procedure WriteParamData(Writer: TWriter);
00129
00130 protected
00131 procedure DefineProperties(Filer: TFiler); override;
00132 procedure CalculateDefaults(Sender: IZCachedResultSet;
00133 RowAccessor: TZRowAccessor);
00134 procedure PostUpdates(Sender: IZCachedResultSet; UpdateType: TZRowUpdateType;
00135 OldRowAccessor, NewRowAccessor: TZRowAccessor);
00136 {BEGIN of PATCH [1185969]: Do tasks after posting updates. ie: Updating AutoInc fields in MySQL }
00137 procedure UpdateAutoIncrementFields(Sender: IZCachedResultSet; UpdateType: TZRowUpdateType;
00138 OldRowAccessor, NewRowAccessor: TZRowAccessor; Resolver: IZCachedResolver);
00139 {END of PATCH [1185969]: Do tasks after posting updates. ie: Updating AutoInc fields in MySQL }
00140 procedure Rebuild(SQLStrings: TZSQLStrings);
00141 procedure RebuildAll;
00142 procedure FillStatement(ResultSet: IZCachedResultSet;
00143 Statement: IZPreparedStatement; Config: TZSQLStatement;
00144 OldRowAccessor, NewRowAccessor: TZRowAccessor);
00145 procedure UpdateParams(Sender: TObject);
00146
00147 procedure DoBeforeDeleteSQL;
00148 procedure DoBeforeInsertSQL;
00149 procedure DoBeforeModifySQL;
00150 procedure DoAfterDeleteSQL;
00151 procedure DoAfterInsertSQL;
00152 procedure DoAfterModifySQL;
00153
00154 procedure DoBeforeDeleteSQLStatement(const Sender: TObject;
00155 StatementIndex: Integer; out Execute: Boolean);
00156 procedure DoBeforeInsertSQLStatement(const Sender: TObject;
00157 StatementIndex: Integer; out Execute: Boolean);
00158 procedure DoBeforeModifySQLStatement(const Sender: TObject;
00159 StatementIndex: Integer; out Execute: Boolean);
00160 procedure DoAfterDeleteSQLStatement(const Sender: TObject;
00161 StatementIndex: Integer);
00162 procedure DoAfterInsertSQLStatement(const Sender: TObject;
00163 StatementIndex: Integer; out UpdateAutoIncFields: Boolean) ;
00164 procedure DoAfterModifySQLStatement(const Sender: TObject;
00165 StatementIndex: Integer);
00166 public
00167 constructor Create(AOwner: TComponent); override;
00168 destructor Destroy; override;
00169
00170 property SQL[UpdateKind: TUpdateKind]: TStrings read GetSQL write SetSQL;
00171 property ParamCount: Word read GetParamsCount;
00172 property DataSet: TDataSet read FDataSet write SetDataSet;
00173
00174 published
00175 property DeleteSQL: TStrings read GetDeleteSQL write SetDeleteSQL;
00176 property InsertSQL: TStrings read GetInsertSQL write SetInsertSQL;
00177 property ModifySQL: TStrings read GetModifySQL write SetModifySQL;
00178
00179 property RefreshSQL: TStrings read GetRefreshSQL write SetRefreshSQL;
00180
00181 property UseSequenceFieldForRefreshSQL:Boolean read FUseSequenceFieldForRefreshSQL write SetUseSequenceFieldForRefreshSQL;
00182
00183
00184 property Params: TParams read FParams write SetParamsList stored False;
00185 property ParamCheck: Boolean read FParamCheck write SetParamCheck default True;
00186 property MultiStatements: Boolean read FMultiStatements write SetMultiStatements default True;
00187
00188 property BeforeDeleteSQL: TNotifyEvent
00189 read FBeforeDeleteSQL write FBeforeDeleteSQL;
00190 property BeforeInsertSQL: TNotifyEvent
00191 read FBeforeInsertSQL write FBeforeInsertSQL;
00192 property BeforeModifySQL: TNotifyEvent
00193 read FBeforeModifySQL write FBeforeModifySQL;
00194 property AfterDeleteSQL: TNotifyEvent
00195 read FAfterDeleteSQL write FAfterDeleteSQL;
00196 property AfterInsertSQL: TNotifyEvent
00197 read FAfterInsertSQL write FAfterInsertSQL;
00198 property AfterModifySQL: TNotifyEvent
00199 read FAfterModifySQL write FAfterModifySQL;
00200
00201 {New Events Fired by executed Statement}
00202 property BeforeDeleteSQLStatement: TZBeforeSQLStatementEvent
00203 read FBeforeDeleteSQLStatement write FBeforeDeleteSQLStatement;
00204 property BeforeInsertSQLStatement: TZBeforeSQLStatementEvent
00205 read FBeforeInsertSQLStatement write FBeforeInsertSQLStatement;
00206 property BeforeModifySQLStatement: TZBeforeSQLStatementEvent
00207 read FBeforeModifySQLStatement write FBeforeModifySQLStatement;
00208 property AfterDeleteSQLStatement: TZAfterSQLStatementEvent
00209 read FAfterDeleteSQLStatement write FAfterDeleteSQLStatement;
00210 property AfterInsertSQLStatement: TZAfterInsertSQLStatementEvent
00211 read FAfterInsertSQLStatement write FAfterInsertSQLStatement;
00212 property AfterModifySQLStatement: TZAfterSQLStatementEvent
00213 read FAfterModifySQLStatement write FAfterModifySQLStatement;
00214 end;
00215
00216 implementation
00217
00218 uses ZGenericSqlToken, ZDatasetUtils, ZAbstractRODataset,ZAbstractDataset,
00219 ZSysUtils, ZDbcUtils,ZMessages;
00220
00221 { TZUpdateSQL }
00222
00223 {**
00224 Constructs this object and assignes main properties.
00225 @param AOwner a component owner.
00226 }
00227 constructor TZUpdateSQL.Create(AOwner: TComponent);
00228 begin
00229 inherited Create(AOwner);
00230
00231 FDeleteSQL := TZSQLStrings.Create;
00232 FDeleteSQL.OnChange := UpdateParams;
00233 FInsertSQL := TZSQLStrings.Create;
00234 FInsertSQL.OnChange := UpdateParams;
00235 FModifySQL := TZSQLStrings.Create;
00236 FModifySQL.OnChange := UpdateParams;
00237
00238
00239 FRefreshSQL := TZSQLStrings.Create;
00240 FRefreshSQL.OnChange:= UpdateParams;
00241
00242
00243 FParams := TParams.Create(Self);
00244 FParamCheck := True;
00245 FMultiStatements := True;
00246 end;
00247
00248 {**
00249 Destroys this object and cleanups the memory.
00250 }
00251 destructor TZUpdateSQL.Destroy;
00252 begin
00253 FParams.Free;
00254 FDeleteSQL.Free;
00255 FInsertSQL.Free;
00256 FModifySQL.Free;
00257 FRefreshSQL.Free;
00258
00259 inherited Destroy;
00260 end;
00261
00262 {**
00263 Store the related dataset object for update sql editor
00264 }
00265 procedure TZUpdateSQL.SetDataset(Value: TDataset);
00266 begin
00267 FDataSet := Value;
00268 FDeleteSQL.Dataset := Value;
00269 FInsertSQL.Dataset := Value;
00270 FModifySQL.Dataset := Value;
00271 end;
00272
00273 {**
00274 Gets a DML statements for specified action.
00275 @param UpdateKind a type of the DML statements.
00276 @return a stored DML statement.
00277 }
00278 function TZUpdateSQL.GetSQL(UpdateKind: TUpdateKind): TStrings;
00279 begin
00280 case UpdateKind of
00281 ukModify: Result := FModifySQL;
00282 ukInsert: Result := FInsertSQL;
00283 else Result := FDeleteSQL;
00284 end;
00285 end;
00286
00287 {**
00288 Sets a DML statements for specified action.
00289 @param UpdateKind a type of the DML statements.
00290 @param Value a DML statements to be set.
00291 }
00292 procedure TZUpdateSQL.SetSQL(UpdateKind: TUpdateKind; Value: TStrings);
00293 begin
00294 case UpdateKind of
00295 ukModify: FModifySQL.Assign(Value);
00296 ukInsert: FInsertSQL.Assign(Value);
00297 ukDelete: FDeleteSQL.Assign(Value);
00298 end;
00299 end;
00300
00301 {**
00302 Get parameters count.
00303 @return a parameters count.
00304 }
00305 function TZUpdateSQL.GetParamsCount: Word;
00306 begin
00307 Result := FParams.Count;
00308 end;
00309
00310 function TZUpdateSQL.GetRefreshSQL: TStrings;
00311 begin
00312 Result := FRefreshSQL;
00313 end;
00314
00315 {**
00316 Sets parameters checking flag.
00317 @param Value a new parameters checking flag.
00318 }
00319 procedure TZUpdateSQL.SetParamCheck(Value: Boolean);
00320 begin
00321 if FParamCheck <> Value then
00322 begin
00323 FParamCheck := Value;
00324 FModifySQL.ParamCheck := Value;
00325 FInsertSQL.ParamCheck := Value;
00326 FDeleteSQL.ParamCheck := Value;
00327 RebuildAll;
00328 end;
00329 end;
00330
00331 {**
00332 Sets multiple statements flag.
00333 @param Value a new multiple statements flag.
00334 }
00335 procedure TZUpdateSQL.SetMultiStatements(Value: Boolean);
00336 begin
00337 if FMultiStatements <> Value then
00338 begin
00339 FMultiStatements := Value;
00340 FModifySQL.MultiStatements := Value;
00341 FInsertSQL.MultiStatements := Value;
00342 FDeleteSQL.MultiStatements := Value;
00343 RebuildAll;
00344 end;
00345 end;
00346
00347 {**
00348 Set a new list of SQL parameters.
00349 @param Value a new list of SQL parameters.
00350 }
00351 procedure TZUpdateSQL.SetParamsList(Value: TParams);
00352 begin
00353 FParams.AssignValues(Value);
00354 end;
00355
00356 procedure TZUpdateSQL.SetRefreshSQL(Value: TStrings);
00357 begin
00358 FRefreshSQL.Assign(Value);
00359 end;
00360
00361 procedure TZUpdateSQL.SetUseSequenceFieldForRefreshSQL(const Value: Boolean);
00362 begin
00363 FUseSequenceFieldForRefreshSQL := Value;
00364 end;
00365
00366 {**
00367 Defines a persistent dataset properties.
00368 @param Filer a persistent manager object.
00369 }
00370 procedure TZUpdateSQL.DefineProperties(Filer: TFiler);
00371
00372 function WriteData: Boolean;
00373 begin
00374 if Filer.Ancestor <> nil then
00375 Result := not FParams.IsEqual(TZUpdateSQL(Filer.Ancestor).FParams)
00376 else Result := FParams.Count > 0;
00377 end;
00378
00379 begin
00380 inherited DefineProperties(Filer);
00381 Filer.DefineProperty('ParamData', ReadParamData, WriteParamData, WriteData);
00382 end;
00383
00384 {**
00385 Reads parameter data from persistent storage.
00386 @param Reader an input data stream.
00387 }
00388 procedure TZUpdateSQL.ReadParamData(Reader: TReader);
00389 begin
00390 Reader.ReadValue;
00391 Reader.ReadCollection(FParams);
00392 end;
00393
00394 {**
00395 Writes parameter data from persistent storage.
00396 @param Writer an output data stream.
00397 }
00398 procedure TZUpdateSQL.WriteParamData(Writer: TWriter);
00399 begin
00400 Writer.WriteCollection(Params);
00401 end;
00402
00403 {**
00404 Gets strings with Delete statements.
00405 @return strings with Delete statements.
00406 }
00407 function TZUpdateSQL.GetDeleteSQL: TStrings;
00408 begin
00409 Result := FDeleteSQL;
00410 end;
00411
00412 {**
00413 Sets a new Delete SQL statement.
00414 @param Value a new Delete SQL statement.
00415 }
00416 procedure TZUpdateSQL.SetDeleteSQL(Value: TStrings);
00417 begin
00418 FDeleteSQL.Assign(Value);
00419 end;
00420
00421 {**
00422 Gets strings with Insert statements.
00423 @return strings with Insert statements.
00424 }
00425 function TZUpdateSQL.GetInsertSQL: TStrings;
00426 begin
00427 Result := FInsertSQL;
00428 end;
00429
00430 {**
00431 Sets a new Insert SQL statement.
00432 @param Value a new Insert SQL statement.
00433 }
00434 procedure TZUpdateSQL.SetInsertSQL(Value: TStrings);
00435 begin
00436 FInsertSQL.Assign(Value);
00437 end;
00438
00439 {**
00440 Gets strings with Modify statements.
00441 @return strings with Modify statements.
00442 }
00443 function TZUpdateSQL.GetModifySQL: TStrings;
00444 begin
00445 Result := FModifySQL;
00446 end;
00447
00448 {**
00449 Sets a new Modify SQL statement.
00450 @param Value a new Modify SQL statement.
00451 }
00452 procedure TZUpdateSQL.SetModifySQL(Value: TStrings);
00453 begin
00454 FModifySQL.Assign(Value);
00455 end;
00456
00457 {**
00458 Updates all parameters.
00459 @param Sender an event sender object.
00460 }
00461 procedure TZUpdateSQL.UpdateParams(Sender: TObject);
00462 begin
00463 RebuildAll;
00464 end;
00465
00466 {**
00467 Rebuilds parameters and inserts a new one from specified sql statements.
00468 @param SQLStrings a strings with SQL statements.
00469 }
00470 procedure TZUpdateSQL.Rebuild(SQLStrings: TZSQLStrings);
00471 var
00472 I: Integer;
00473 begin
00474 for I := 0 to SQLStrings.ParamCount - 1 do
00475 begin
00476 if FParams.FindParam(SQLStrings.ParamNames[I]) = nil then
00477 FParams.CreateParam(ftUnknown, SQLStrings.ParamNames[I], ptUnknown);
00478 end;
00479 end;
00480
00481 {**
00482 Rebuilds all internal structures including parameters from SQL statements.
00483 }
00484 procedure TZUpdateSQL.RebuildAll;
00485 var
00486 OldParams: TParams;
00487 begin
00488 OldParams := TParams.Create;
00489 OldParams.Assign(FParams);
00490 FParams.Clear;
00491 try
00492 Rebuild(FModifySQL);
00493 Rebuild(FInsertSQL);
00494 Rebuild(FDeleteSQL);
00495
00496 Rebuild(FRefreshSQL);
00497
00498 FParams.AssignValues(OldParams);
00499 finally
00500 OldParams.Free;
00501 end;
00502 end;
00503
00504 {**
00505 Fills the specified statement with stored or given parameters.
00506 @param ResultSet a source result set object.
00507 @param Statement a DBC statement object.
00508 @param Config a SQLStatement configuration.
00509 @param OldRowAccessor an accessor object to old column values.
00510 @param NewRowAccessor an accessor object to new column values.
00511 }
00512 procedure TZUpdateSQL.FillStatement(ResultSet: IZCachedResultSet;
00513 Statement: IZPreparedStatement; Config: TZSQLStatement;
00514 OldRowAccessor, NewRowAccessor: TZRowAccessor);
00515 var
00516 I, ColumnIndex: Integer;
00517 ParamValue: TParam;
00518 ParamName: string;
00519 OldParam: Boolean;
00520
00521 WasNull: Boolean;
00522 RowAccessor: TZRowAccessor;
00523 Stream: TStream;
00524 TempBlob: IZBlob;
00525 begin
00526 WasNull := False;
00527 for I := 0 to Config.ParamCount - 1 do begin
00528 ParamValue := Params.FindParam(Config.ParamNames[I]);
00529 ParamName := Config.ParamNames[I];
00530 OldParam := False;{Seqparam:=False;}
00531 if StrLIComp(PChar(ParamName), 'NEW_', 4) = 0 then begin
00532 ParamName := Copy(ParamName, 5, Length(ParamName) - 4)
00533 end else
00534 if StrLIComp(PChar(ParamName), 'OLD_', 4) = 0 then begin
00535 ParamName := Copy(ParamName, 5, Length(ParamName) - 4);
00536 OldParam := True;
00537 end;
00538
00539 ColumnIndex := ResultSet.FindColumn(ParamName);
00540 if ColumnIndex > 0 then begin
00541 if OldParam then
00542 RowAccessor := OldRowAccessor
00543 else RowAccessor := NewRowAccessor;
00544
00545 if StrToBoolEx(DefineStatementParameter(
00546 ResultSet.GetStatement, 'defaults', 'true')) then
00547 Statement.SetDefaultValue(I + 1,
00548 ResultSet.GetMetadata.GetDefaultValue(ColumnIndex));
00549
00550 case ResultSet.GetMetadata.GetColumnType(ColumnIndex) of
00551 stBoolean:
00552 Statement.SetBoolean(I + 1,
00553 RowAccessor.GetBoolean(ColumnIndex, WasNull));
00554 stByte:
00555 Statement.SetByte(I + 1, RowAccessor.GetByte(ColumnIndex, WasNull));
00556 stShort:
00557 Statement.SetShort(I + 1, RowAccessor.GetShort(ColumnIndex, WasNull));
00558 stInteger:
00559 Statement.SetInt(I + 1, RowAccessor.GetInt(ColumnIndex, WasNull));
00560 stLong:
00561 Statement.SetLong(I + 1, RowAccessor.GetLong(ColumnIndex, WasNull));
00562 stFloat:
00563 Statement.SetFloat(I + 1, RowAccessor.GetFloat(ColumnIndex, WasNull));
00564 stDouble:
00565 Statement.SetDouble(I + 1, RowAccessor.GetDouble(ColumnIndex, WasNull));
00566 stBigDecimal:
00567 Statement.SetBigDecimal(I + 1,
00568 RowAccessor.GetBigDecimal(ColumnIndex, WasNull));
00569 stString, stUnicodeString:
00570 Statement.SetString(I + 1, RowAccessor.GetString(ColumnIndex, WasNull));
00571 stBytes:
00572 Statement.SetBytes(I + 1, RowAccessor.GetBytes(ColumnIndex, WasNull));
00573 stDate:
00574 Statement.SetDate(I + 1, RowAccessor.GetDate(ColumnIndex, WasNull));
00575 stTime:
00576 Statement.SetTime(I + 1, RowAccessor.GetTime(ColumnIndex, WasNull));
00577 stTimestamp:
00578 Statement.SetTimestamp(I + 1,
00579 RowAccessor.GetTimestamp(ColumnIndex, WasNull));
00580 stAsciiStream:
00581 begin
00582 TempBlob := RowAccessor.GetBlob(ColumnIndex, WasNull);
00583 if not TempBlob.IsEmpty then
00584 Statement.SetBlob(I + 1, stAsciiStream, TempBlob)
00585 else
00586 Statement.SetNull(I + 1, stAsciiStream);
00587 end;
00588 stUnicodeStream:
00589 begin
00590 TempBlob := RowAccessor.GetBlob(ColumnIndex, WasNull);
00591 if not TempBlob.IsEmpty then
00592 Statement.SetBlob(I + 1, stUnicodeStream, TempBlob)
00593 else
00594 Statement.SetNull(I + 1, stUnicodeStream);
00595 end;
00596 stBinaryStream:
00597 begin
00598 TempBlob := RowAccessor.GetBlob(ColumnIndex, WasNull);
00599 if not TempBlob.IsEmpty then
00600 Statement.SetBlob(I + 1, stBinaryStream, TempBlob)
00601 else
00602 Statement.SetNull(I + 1, stBinaryStream);
00603 end;
00604 end;
00605 if WasNull then
00606 begin
00607 Statement.SetNull(I + 1,
00608 ResultSet.GetMetadata.GetColumnType(ColumnIndex))
00609 end;
00610 end else begin
00611 if ParamValue.IsNull then
00612 Statement.SetNull(I + 1, ConvertDatasetToDbcType(ParamValue.DataType))
00613 else begin
00614 case ParamValue.DataType of
00615 ftBoolean:
00616 Statement.SetBoolean(I + 1, ParamValue.AsBoolean);
00617 ftSmallInt:
00618 Statement.SetShort(I + 1, ParamValue.AsSmallInt);
00619 ftInteger, ftAutoInc:
00620 Statement.SetInt(I + 1, ParamValue.AsInteger);
00621 ftFloat:
00622 Statement.SetFloat(I + 1, ParamValue.AsFloat);
00623 ftLargeInt:
00624 Statement.SetInt(I + 1, ParamValue.AsInteger);
00625 ftString:
00626 Statement.SetString(I + 1, ParamValue.AsString);
00627 ftBytes:
00628 Statement.SetString(I + 1, ParamValue.AsString);
00629 ftDate:
00630 Statement.SetDate(I + 1, ParamValue.AsDate);
00631 ftTime:
00632 Statement.SetTime(I + 1, ParamValue.AsTime);
00633 ftDateTime:
00634 Statement.SetTimestamp(I + 1, ParamValue.AsDateTime);
00635 ftMemo:
00636 begin
00637 Stream := TStringStream.Create(ParamValue.AsMemo);
00638 try
00639 Statement.SetAsciiStream(I + 1, Stream);
00640 finally
00641 Stream.Free;
00642 end;
00643 end;
00644 ftBlob, ftGraphic:
00645 begin
00646 Stream := TStringStream.Create(ParamValue.AsBlob);
00647 try
00648 Statement.SetBinaryStream(I + 1, Stream);
00649 finally
00650 Stream.Free;
00651 end;
00652 end;
00653 end;
00654 end;
00655 end;
00656 end;
00657 end;
00658
00659 {**
00660 Calculate default values for the fields.
00661 @param Sender a cached result set object.
00662 @param RowAccessor an accessor object to column values.
00663 }
00664 procedure TZUpdateSQL.CalculateDefaults(Sender: IZCachedResultSet;
00665 RowAccessor: TZRowAccessor);
00666 begin
00667 {BEGIN PATCH [1214009] TZUpdateSQL - implemented feature to Calculate default values}
00668 Sender.GetNativeResolver.CalculateDefaults(Sender, RowAccessor);
00669 {END PATCH [1214009] TZUpdateSQL - implemented feature to Calculate default values}
00670 end;
00671
00672 {**
00673 Posts updates to database.
00674 @param Sender a cached result set object.
00675 @param UpdateType a type of updates.
00676 @param OldRowAccessor an accessor object to old column values.
00677 @param NewRowAccessor an accessor object to new column values.
00678 }
00679 procedure TZUpdateSQL.PostUpdates(Sender: IZCachedResultSet;
00680 UpdateType: TZRowUpdateType; OldRowAccessor, NewRowAccessor: TZRowAccessor);
00681 var I: Integer;
00682 Statement: IZPreparedStatement;
00683 Config: TZSQLStrings;
00684 CalcDefaultValues,
00685 ExecuteStatement,
00686 UpdateAutoIncFields: Boolean;
00687
00688 RefreshResultSet: IZResultSet;
00689 RefreshRowAccessor: TZRowAccessor;
00690 RefreshColumnIndex:integer;
00691 RefreshColumnName:String;
00692 Refresh_OldSQL:String;
00693 RefreshColumnType:TZSQLType;
00694
00695 lValidateUpdateCount : Boolean;
00696 lUpdateCount : Integer;
00697 procedure Apply_RefreshResultSet;
00698 var
00699 I: Integer;
00700 begin
00701 if Assigned(RefreshResultSet) then begin
00702 if not RefreshResultSet.First then begin
00703 raise EZDatabaseError.Create(SUpdateSQLNoResult);
00704 end;
00705 for I := 1 to RefreshResultSet.GetMetadata.GetColumnCount do begin
00706 RefreshColumnName:=RefreshResultSet.GetMetadata.GetColumnLabel(I);
00707 RefreshColumnIndex := Sender.FindColumn(RefreshColumnName);
00708 if RefreshColumnIndex=0 then begin
00709 continue;
00710 end;
00711 if RefreshResultSet.IsNull(I) then begin
00712 RefreshRowAccessor.SetNull(RefreshColumnIndex);
00713 end else begin
00714 RefreshColumnType := RefreshResultSet.GetMetadata.GetColumnType(I);
00715 case RefreshColumnType of
00716 stBoolean: RefreshRowAccessor.SetBoolean(RefreshColumnIndex, RefreshResultSet.GetBoolean(I));
00717 stByte: RefreshRowAccessor.SetByte(RefreshColumnIndex, RefreshResultSet.GetByte(I));
00718 stShort: RefreshRowAccessor.SetShort(RefreshColumnIndex, RefreshResultSet.GetShort(I));
00719 stInteger: RefreshRowAccessor.SetInt(RefreshColumnIndex, RefreshResultSet.GetInt(I));
00720 stLong: RefreshRowAccessor.SetLong(RefreshColumnIndex, RefreshResultSet.GetLong(I));
00721 stFloat: RefreshRowAccessor.SetFloat(RefreshColumnIndex, RefreshResultSet.GetFloat(I));
00722 stDouble: RefreshRowAccessor.SetDouble(RefreshColumnIndex, RefreshResultSet.GetDouble(I));
00723 stBigDecimal: RefreshRowAccessor.SetBigDecimal(RefreshColumnIndex, RefreshResultSet.GetBigDecimal(I));
00724 stString: RefreshRowAccessor.SetPChar(RefreshColumnIndex, RefreshResultSet.GetPChar(I));
00725 stUnicodeString: RefreshRowAccessor.SetUnicodeString(RefreshColumnIndex, RefreshResultSet.GetUnicodeString(I));
00726 stBytes: RefreshRowAccessor.SetBytes(RefreshColumnIndex, RefreshResultSet.GetBytes(I));
00727 stDate: RefreshRowAccessor.SetDate(RefreshColumnIndex, RefreshResultSet.GetDate(I));
00728 stTime: RefreshRowAccessor.SetTime(RefreshColumnIndex, RefreshResultSet.GetTime(I));
00729 stTimestamp: RefreshRowAccessor.SetTimestamp(RefreshColumnIndex, RefreshResultSet.GetTimestamp(I));
00730 stAsciiStream, stUnicodeStream, stBinaryStream:RefreshRowAccessor.SetBlob(RefreshColumnIndex, RefreshResultSet.GetBlob(I));
00731 end;
00732 end;
00733 end;
00734 end;
00735 end;
00736
00737 begin
00738 if (UpdateType = utDeleted)
00739 and (OldRowAccessor.RowBuffer.UpdateType = utInserted) then
00740 Exit;
00741
00742 case UpdateType of
00743 utInserted:
00744 Config := FInsertSQL;
00745 utDeleted:
00746 Config := FDeleteSQL;
00747 utModified:
00748 Config := FModifySQL;
00749 else
00750 Exit;
00751 end;
00752
00753 case UpdateType of
00754 utInserted:
00755 DoBeforeInsertSQL;
00756 utDeleted:
00757 DoBeforeDeleteSQL;
00758 utModified:
00759 DoBeforeModifySQL;
00760 end;
00761
00762 if Dataset is TZAbstractRODataset then
00763 (Dataset as TZAbstractRODataset).Connection.ShowSqlHourGlass;
00764 CalcDefaultValues :=
00765 ZSysUtils.StrToBoolEx(DefineStatementParameter(Sender.GetStatement,'defaults','true'));
00766 try
00767 for I := 0 to Config.StatementCount - 1 do
00768 begin
00769 Statement := Sender.GetStatement.GetConnection.
00770 PrepareStatement(Config.Statements[I].SQL);
00771 FillStatement(Sender, Statement, Config.Statements[I],
00772 OldRowAccessor, NewRowAccessor);
00773 {BEGIN of PATCH [1185969]: Do tasks after posting updates. ie: Updating AutoInc fields in MySQL }
00774 {Update AutoInc Field Tasks will be only executed if the UpdateAutoIncFields
00775 in the AfterInsertSQLStatement event returns true
00776 }
00777 ExecuteStatement := true;
00778 UpdateAutoIncFields := false;
00779 case UpdateType of
00780 utDeleted:
00781 DoBeforeDeleteSQLStatement(Self, I, ExecuteStatement);
00782 utInserted:
00783 DoBeforeInsertSQLStatement(Self, I, ExecuteStatement);
00784 utModified:
00785 DoBeforeModifySQLStatement(Self, I, ExecuteStatement);
00786 end;
00787 if ExecuteStatement then
00788 begin
00789 lValidateUpdateCount := StrToBoolEx(
00790 Sender.GetStatement.GetParameters.Values['ValidateUpdateCount']);
00791
00792 lUpdateCount := Statement.ExecuteUpdatePrepared;
00793 if (lValidateUpdateCount) and (lUpdateCount <> 1) then
00794 raise EZSQLException.Create(Format(SInvalidUpdateCount, [lUpdateCount]));
00795
00796 case UpdateType of
00797 utDeleted:
00798 DoAfterDeleteSQLStatement(Self, I);
00799 utInserted:
00800 begin
00801 DoAfterInsertSQLStatement(Self, I, UpdateAutoIncFields);
00802 if CalcDefaultValues and UpdateAutoIncFields then
00803 UpdateAutoIncrementFields(Sender, UpdateType,
00804 OldRowAccessor, NewRowAccessor, Self);
00805 end;
00806 utModified:
00807 DoAfterModifySQLStatement(Self,I);
00808 end;
00809 end;
00810 {END of PATCH [1185969]: Do tasks after posting updates. ie: Updating AutoInc fields in MySQL }
00811 end;
00812
00813 RefreshRowAccessor := NewRowAccessor;
00814 case UpdateType of
00815 utInserted,utModified: begin
00816 if FRefreshSql.Text<>'' then begin
00817 Refresh_OldSQL:=FRefreshSql.Text;
00818 try
00819 Config:=FRefreshSQL;
00820 if UpdateType=utInserted then begin
00821 if Dataset is TZAbstractDataset then begin
00822 if FUseSequenceFieldForRefreshSQL then begin
00823 if assigned(TZAbstractDataset(DataSet).Sequence) and (TZAbstractDataset(DataSet).SequenceField<>'') then begin
00824 Config.Text :=
00825 StringReplace(UpperCase(Config.Text),
00826 ':OLD_'+UpperCase(TZAbstractDataset(DataSet).SequenceField),
00827 TZAbstractDataset(DataSet).Sequence.GetCurrentValueSQL,[rfReplaceAll]);
00828 end;
00829 end;
00830 end;
00831 end;
00832 if CONFIG.StatementCount=1 then
00833 begin
00834 Statement := Sender.GetStatement.GetConnection.PrepareStatement(Config.Statements[0].SQL);
00835 FillStatement(Sender, Statement, Config.Statements[0],OldRowAccessor, NewRowAccessor);
00836 RefreshResultSet:=Statement.ExecuteQueryPrepared;
00837 Apply_RefreshResultSet;
00838 end;
00839 finally
00840 FRefreshSQL.Text:=Refresh_OldSQL;
00841 end;
00842 end;
00843 end;
00844 end;
00845
00846
00847 finally
00848 if Dataset is TZAbstractRODataset then
00849 (Dataset as TZAbstractRODataset).Connection.HideSQLHourGlass;
00850 end;
00851
00852 case UpdateType of
00853 utInserted:
00854 DoAfterInsertSQL;
00855 utDeleted:
00856 DoAfterDeleteSQL;
00857 utModified:
00858 DoAfterModifySQL;
00859 end;
00860 end;
00861
00862 {**
00863 Fires an event before delete Statement
00864 }
00865 procedure TZUpdateSQL.DoBeforeDeleteSQL;
00866 begin
00867 if Assigned(FBeforeDeleteSQL) then
00868 FBeforeDeleteSQL(Self);
00869 end;
00870
00871 {**
00872 Fires an event before insert Statement
00873 }
00874 procedure TZUpdateSQL.DoBeforeInsertSQL;
00875 begin
00876 if Assigned(BeforeInsertSQL) then
00877 FBeforeInsertSQL(Self);
00878 end;
00879
00880 {**
00881 Fires an event before modify Statement
00882 }
00883 procedure TZUpdateSQL.DoBeforeModifySQL;
00884 begin
00885 if Assigned(FBeforeModifySQL) then
00886 FBeforeModifySQL(Self);
00887 end;
00888
00889 {**
00890 Fires an event after delete Statement
00891 }
00892 procedure TZUpdateSQL.DoAfterDeleteSQL;
00893 begin
00894 if Assigned(FAfterDeleteSQL) then
00895 FAfterDeleteSQL(Self);
00896 end;
00897
00898 {**
00899 Fires an event after insert Statement
00900 }
00901 procedure TZUpdateSQL.DoAfterInsertSQL;
00902 begin
00903 if Assigned(FAfterInsertSQL) then
00904 FAfterInsertSQL(Self);
00905 end;
00906
00907 {**
00908 Fires an event after modify Statement
00909 }
00910 procedure TZUpdateSQL.DoAfterModifySQL;
00911 begin
00912 if Assigned(FAfterModifySQL) then
00913 FAfterModifySQL(Self);
00914 end;
00915
00916 {BEGIN of PATCH [1185969]: Do tasks after posting updates. ie: Updating AutoInc fields in MySQL }
00917 procedure TZUpdateSQL.UpdateAutoIncrementFields(Sender: IZCachedResultSet;
00918 UpdateType: TZRowUpdateType; OldRowAccessor,
00919 NewRowAccessor: TZRowAccessor; Resolver: IZCachedResolver);
00920 begin
00921 with Sender.GetNativeResolver do
00922 begin
00923 UpdateAutoIncrementFields(Sender, UpdateType,
00924 OldRowAccessor, NewRowAccessor, Resolver);
00925 end;
00926 end;
00927 {END of PATCH [1185969]: Do tasks after posting updates. ie: Updating AutoInc fields in MySQL }
00928
00929 {NEW Methods for Events to validate at Statement level }
00930 procedure TZUpdateSQL.DoAfterDeleteSQLStatement(const Sender: TObject;
00931 StatementIndex: Integer);
00932 begin
00933 if Assigned(FAfterDeleteSQLStatement) then
00934 FAfterDeleteSQLStatement(Self, StatementIndex);
00935 end;
00936
00937 procedure TZUpdateSQL.DoAfterInsertSQLStatement(const Sender: TObject;
00938 StatementIndex: Integer; out UpdateAutoIncFields: Boolean);
00939 begin
00940 if Assigned(FAfterInsertSQLStatement) then
00941 FAfterInsertSQLStatement(Self, StatementIndex, UpdateAutoIncFields);
00942 end;
00943
00944 procedure TZUpdateSQL.DoAfterModifySQLStatement(const Sender: TObject;
00945 StatementIndex: Integer);
00946 begin
00947 if Assigned(FAfterModifySQLStatement) then
00948 FAfterModifySQLStatement(Self, StatementIndex);
00949 end;
00950
00951 procedure TZUpdateSQL.DoBeforeDeleteSQLStatement(const Sender: TObject;
00952 StatementIndex: Integer; out Execute: Boolean);
00953 begin
00954 if Assigned(FBeforeDeleteSQLStatement) then
00955 FBeforeDeleteSQLStatement(Self, StatementIndex, Execute);
00956 end;
00957
00958 procedure TZUpdateSQL.DoBeforeInsertSQLStatement(const Sender: TObject;
00959 StatementIndex: Integer; out Execute: Boolean);
00960 begin
00961 if Assigned(FBeforeInsertSQLStatement) then
00962 FBeforeInsertSQLStatement(Self, StatementIndex, Execute);
00963 end;
00964
00965 procedure TZUpdateSQL.DoBeforeModifySQLStatement(const Sender: TObject;
00966 StatementIndex: Integer; out Execute: Boolean);
00967 begin
00968 if Assigned(FBeforeModifySQLStatement) then
00969 FBeforeModifySQLStatement(Self, StatementIndex, Execute);
00970 end;
00971
00972 end.