00001 {*********************************************************}
00002 { }
00003 { Zeos Database Objects }
00004 { Generic Cached Resolver }
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 ZDbcGenericResolver;
00055
00056 interface
00057
00058 {$I ZDbc.inc}
00059
00060 uses
00061 {$IFNDEF VER130BELOW}
00062 Types,
00063 {$ENDIF}
00064 Classes, SysUtils, Contnrs, ZVariant, ZDbcIntfs,
00065 ZDbcCache, ZDbcCachedResultSet, ZCompatibility, ZSelectSchema;
00066
00067 type
00068
00069 {** Implements a resolver parameter object. }
00070 TZResolverParameter = class (TObject)
00071 private
00072 FColumnIndex: Integer;
00073 FColumnName: string;
00074 FColumnType: TZSQLType;
00075 FNewValue: Boolean;
00076 FDefaultValue: string;
00077 public
00078 constructor Create(ColumnIndex: Integer; ColumnName: string;
00079 ColumnType: TZSQLType; NewValue: Boolean; DefaultValue: string);
00080
00081 property ColumnIndex: Integer read FColumnIndex write FColumnIndex;
00082 property ColumnName: string read FColumnName write FColumnName;
00083 property ColumnType: TZSQLType read FColumnType write FColumnType;
00084 property NewValue: Boolean read FNewValue write FNewValue;
00085 property DefaultValue: string read FDefaultValue write FDefaultValue;
00086 end;
00087
00088 {**
00089 Implements a generic cached resolver object which generates
00090 DML SQL statements and posts resultset updates to database.
00091 }
00092 TZGenericCachedResolver = class (TInterfacedObject, IZCachedResolver)
00093 private
00094 FConnection: IZConnection;
00095 FMetadata: IZResultSetMetadata;
00096 FDatabaseMetadata: IZDatabaseMetadata;
00097 FIdentifierConvertor: IZIdentifierConvertor;
00098
00099 FInsertColumns: TObjectList;
00100 FUpdateColumns: TObjectList;
00101 FWhereColumns: TObjectList;
00102
00103 FCalcDefaults: Boolean;
00104 FWhereAll: Boolean;
00105 FUpdateAll: Boolean;
00106
00107 protected
00108 procedure CopyResolveParameters(FromList, ToList: TObjectList);
00109 function ComposeFullTableName(Catalog, Schema, Table: string): string;
00110 function DefineTableName: string;
00111
00112 procedure DefineCalcColumns(Columns: TObjectList;
00113 RowAccessor: TZRowAccessor);
00114 procedure DefineInsertColumns(Columns: TObjectList);
00115 procedure DefineUpdateColumns(Columns: TObjectList;
00116 OldRowAccessor, NewRowAccessor: TZRowAccessor);
00117 procedure DefineWhereKeyColumns(Columns: TObjectList);
00118 procedure DefineWhereAllColumns(Columns: TObjectList);
00119 function CheckKeyColumn(ColumnIndex: Integer): Boolean; virtual;
00120
00121 procedure FillStatement(Statement: IZPreparedStatement;
00122 Params: TObjectList; OldRowAccessor, NewRowAccessor: TZRowAccessor);
00123
00124 property Connection: IZConnection read FConnection write FConnection;
00125 property Metadata: IZResultSetMetadata read FMetadata write FMetadata;
00126 property DatabaseMetadata: IZDatabaseMetadata read FDatabaseMetadata
00127 write FDatabaseMetadata;
00128 property IdentifierConvertor: IZIdentifierConvertor
00129 read FIdentifierConvertor write FIdentifierConvertor;
00130
00131 property InsertColumns: TObjectList read FInsertColumns;
00132 property UpdateColumns: TObjectList read FUpdateColumns;
00133 property WhereColumns: TObjectList read FWhereColumns;
00134
00135 property CalcDefaults: Boolean read FCalcDefaults write FCalcDefaults;
00136 property WhereAll: Boolean read FWhereAll write FWhereAll;
00137 property UpdateAll: Boolean read FUpdateAll write FUpdateAll;
00138
00139 public
00140 constructor Create(Statement: IZStatement; Metadata: IZResultSetMetadata);
00141 destructor Destroy; override;
00142
00143 function FormWhereClause(Columns: TObjectList;
00144 OldRowAccessor: TZRowAccessor): string;
00145 function FormInsertStatement(Columns: TObjectList;
00146 NewRowAccessor: TZRowAccessor): string;
00147 function FormUpdateStatement(Columns: TObjectList;
00148 OldRowAccessor, NewRowAccessor: TZRowAccessor): string;
00149 function FormDeleteStatement(Columns: TObjectList;
00150 OldRowAccessor: TZRowAccessor): string;
00151 function FormCalculateStatement(Columns: TObjectList): string; virtual;
00152
00153 procedure CalculateDefaults(Sender: IZCachedResultSet;
00154 RowAccessor: TZRowAccessor);
00155 procedure PostUpdates(Sender: IZCachedResultSet;
00156 UpdateType: TZRowUpdateType;
00157 OldRowAccessor, NewRowAccessor: TZRowAccessor); virtual;
00158 {BEGIN of PATCH [1185969]: Do tasks after posting updates. ie: Updating AutoInc fields in MySQL }
00159 procedure UpdateAutoIncrementFields(Sender: IZCachedResultSet;
00160 UpdateType: TZRowUpdateType;
00161 OldRowAccessor, NewRowAccessor: TZRowAccessor; Resolver: IZCachedResolver); virtual;
00162 {END of PATCH [1185969]: Do tasks after posting updates. ie: Updating AutoInc fields in MySQL }
00163 end;
00164
00165 implementation
00166
00167 uses ZMessages, ZSysUtils, ZDbcMetadata, ZDbcUtils;
00168
00169 { TZResolverParameter }
00170
00171 {**
00172 Constructs this resolver parameter and assignes the main properties.
00173 @param ColumnIndex a result set column index.
00174 @param ColumnName a result set column name.
00175 @param NewValue <code>True</code> for new value and <code>False</code>
00176 for an old one.
00177 @param DefaultValue a default column value to evalute on server.
00178 }
00179 constructor TZResolverParameter.Create(ColumnIndex: Integer;
00180 ColumnName: string; ColumnType: TZSQLType; NewValue: Boolean; DefaultValue: string);
00181 begin
00182 FColumnType := ColumnType;
00183 FColumnIndex := ColumnIndex;
00184 FColumnName := ColumnName;
00185 FNewValue := NewValue;
00186 FDefaultValue := DefaultValue;
00187 end;
00188
00189 { TZGenericCachedResolver }
00190
00191 {**
00192 Creates a cached resolver and assignes the main properties.
00193 @param ResultSet a related ResultSet object.
00194 }
00195 constructor TZGenericCachedResolver.Create(Statement: IZStatement;
00196 Metadata: IZResultSetMetadata);
00197 begin
00198 FConnection := Statement.GetConnection;
00199 FMetadata := Metadata;
00200 FDatabaseMetadata := Statement.GetConnection.GetMetadata;
00201 FIdentifierConvertor := TZDefaultIdentifierConvertor.Create(
00202 FDatabaseMetadata);
00203
00204 FInsertColumns := TObjectList.Create;
00205 FWhereColumns := TObjectList.Create;
00206 FUpdateColumns := TObjectList.Create;
00207
00208 FCalcDefaults := StrToBoolEx(DefineStatementParameter(Statement,
00209 'defaults', 'true'));
00210 FUpdateAll := UpperCase(DefineStatementParameter(Statement,
00211 'update', 'changed')) = 'ALL';
00212 FWhereAll := UpperCase(DefineStatementParameter(Statement,
00213 'where', 'keyonly')) = 'ALL';
00214 end;
00215
00216 {**
00217 Destroys this object and cleanups the memory.
00218 }
00219 destructor TZGenericCachedResolver.Destroy;
00220 begin
00221 FMetadata := nil;
00222 FDatabaseMetadata := nil;
00223
00224 FInsertColumns.Free;
00225 FUpdateColumns.Free;
00226 FWhereColumns.Free;
00227
00228 inherited Destroy;
00229 end;
00230
00231 {**
00232 Copies resolver parameters from source list to destination list.
00233 @param FromList the source object list.
00234 @param ToList the destination object list.
00235 }
00236 procedure TZGenericCachedResolver.CopyResolveParameters(
00237 FromList: TObjectList; ToList: TObjectList);
00238 var
00239 I: Integer;
00240 Current: TZResolverParameter;
00241 begin
00242 for I := 0 to FromList.Count - 1 do
00243 begin
00244 Current := TZResolverParameter(FromList[I]);
00245 if Current.ColumnName <> '' then
00246 ToList.Add(TZResolverParameter.Create(Current.ColumnIndex,
00247 Current.ColumnName, Current.ColumnType, Current.NewValue, ''));
00248 end;
00249 end;
00250
00251 {**
00252 Composes a fully quilified table name.
00253 @param Catalog a table catalog name.
00254 @param Schema a table schema name.
00255 @param Table a table name.
00256 @return a fully qualified table name.
00257 }
00258 function TZGenericCachedResolver.ComposeFullTableName(Catalog, Schema,
00259 Table: string): string;
00260 begin
00261 if Table <> '' then
00262 begin
00263 Result := IdentifierConvertor.Quote(Table);
00264 if Schema <> '' then
00265 Result := IdentifierConvertor.Quote(Schema) + '.' + Result;
00266 if Catalog <> '' then
00267 Result := IdentifierConvertor.Quote(Catalog) + '.' + Result;
00268 end else
00269 Result := '';
00270 end;
00271
00272 {**
00273 Defines a table name from the select statement.
00274 }
00275 function TZGenericCachedResolver.DefineTableName: string;
00276 var
00277 I: Integer;
00278 Temp: string;
00279 begin
00280 Result := '';
00281 for I := 1 to Metadata.GetColumnCount do
00282 begin
00283 Temp := ComposeFullTableName(Metadata.GetCatalogName(I),
00284 Metadata.GetSchemaName(I), Metadata.GetTableName(I));
00285 if (Result = '') and (Temp <> '') then
00286 Result := Temp
00287 else if (Result <> '') and (Temp <> '') and (Temp <> Result) then
00288 raise EZSQLException.Create(SCanNotUpdateComplexQuery);
00289 end;
00290 if Result = '' then
00291 raise EZSQLException.Create(SCanNotUpdateThisQueryType);
00292 end;
00293
00294 {**
00295 Gets a collection of data columns for INSERT statements.
00296 @param Columns a collection of columns.
00297 }
00298 procedure TZGenericCachedResolver.DefineInsertColumns(Columns: TObjectList);
00299 var
00300 I: Integer;
00301 begin
00302 { Precache insert parameters. }
00303 if InsertColumns.Count = 0 then
00304 begin
00305 for I := 1 to Metadata.GetColumnCount do
00306 begin
00307 if (Metadata.GetTableName(I) <> '') and (Metadata.GetColumnName(I) <> '')
00308 and Metadata.IsWritable(I) then
00309 begin
00310 InsertColumns.Add(TZResolverParameter.Create(I,
00311 Metadata.GetColumnName(I), Metadata.GetColumnType(I), True, ''));
00312 end;
00313 end;
00314 end;
00315 { Use cached insert parameters }
00316 CopyResolveParameters(InsertColumns, Columns);
00317 end;
00318
00319 {**
00320 Gets a collection of data columns for UPDATE statements.
00321 @param Columns a collection of columns.
00322 @param OldRowAccessor an accessor object to old column values.
00323 @param NewRowAccessor an accessor object to new column values.
00324 }
00325 procedure TZGenericCachedResolver.DefineUpdateColumns(
00326 Columns: TObjectList; OldRowAccessor, NewRowAccessor: TZRowAccessor);
00327 var
00328 I: Integer;
00329 ColumnIndices: TIntegerDynArray;
00330 ColumnDirs: TBooleanDynArray;
00331 begin
00332 { Use precached parameters. }
00333 if UpdateAll and (UpdateColumns.Count > 0) then
00334 begin
00335 CopyResolveParameters(UpdateColumns, Columns);
00336 Exit;
00337 end;
00338
00339 { Defines parameters for UpdateAll mode. }
00340 if UpdateAll then
00341 begin
00342 for I := 1 to Metadata.GetColumnCount do
00343 begin
00344 if (Metadata.GetTableName(I) <> '') and (Metadata.GetColumnName(I) <> '')
00345 and Metadata.IsWritable(I) then
00346 begin
00347 UpdateColumns.Add(TZResolverParameter.Create(I,
00348 Metadata.GetColumnName(I), Metadata.GetColumnType(I), True, ''));
00349 end;
00350 end;
00351 CopyResolveParameters(UpdateColumns, Columns);
00352 end
00353 { Defines parameters for UpdateChanged mode. }
00354 else
00355 begin
00356 SetLength(ColumnIndices, 1);
00357 SetLength(ColumnDirs, 1);
00358 ColumnDirs[0] := True;
00359 for I := 1 to Metadata.GetColumnCount do
00360 begin
00361 ColumnIndices[0] := I;
00362 if (Metadata.GetTableName(I) <> '') and (Metadata.GetColumnName(I) <> '')
00363 and Metadata.IsWritable(I) and (OldRowAccessor.CompareBuffers(
00364 OldRowAccessor.RowBuffer, NewRowAccessor.RowBuffer, ColumnIndices,
00365 ColumnDirs) <> 0)then
00366 begin
00367 Columns.Add(TZResolverParameter.Create(I,
00368 Metadata.GetColumnName(I), Metadata.GetColumnType(I), True, ''));
00369 end;
00370 end;
00371 end;
00372 end;
00373
00374 {**
00375 Gets a collection of where key columns for DELETE or UPDATE DML statements.
00376 @param Columns a collection of key columns.
00377 }
00378 procedure TZGenericCachedResolver.DefineWhereKeyColumns(Columns: TObjectList);
00379 var
00380 I: Integer;
00381 Found: Boolean;
00382 ColumnName: string;
00383 Catalog, Schema, Table: string;
00384 PrimaryKeys: IZResultSet;
00385 begin
00386 { Use precached values. }
00387 if WhereColumns.Count > 0 then
00388 begin
00389 CopyResolveParameters(WhereColumns, Columns);
00390 Exit;
00391 end;
00392
00393 { Defines catalog, schema and a table. }
00394 Table := DefineTableName;
00395 for I := 1 to Metadata.GetColumnCount do
00396 begin
00397 Table := Metadata.GetTableName(I);
00398 if Table <> '' then
00399 begin
00400 Schema := Metadata.GetSchemaName(I);
00401 Catalog := Metadata.GetCatalogName(I);
00402 Break;
00403 end;
00404 end;
00405
00406 { Tryes to define primary keys. }
00407 if not WhereAll then
00408 begin
00409 PrimaryKeys := DatabaseMetadata.GetPrimaryKeys(Catalog, Schema, Table);
00410 while PrimaryKeys.Next do
00411 begin
00412 ColumnName := PrimaryKeys.GetString(4);
00413 Found := False;
00414 for I := 1 to Metadata.GetColumnCount do
00415 begin
00416 if (ColumnName = Metadata.GetColumnName(I))
00417 and (Table = Metadata.GetTableName(I)) then
00418 begin
00419 Found := True;
00420 Break;
00421 end;
00422 end;
00423 if not Found then
00424 begin
00425 WhereColumns.Clear;
00426 Break;
00427 end;
00428 WhereColumns.Add(TZResolverParameter.Create(I, ColumnName,
00429 stUnknown, False, ''));
00430 end;
00431 end;
00432
00433 if WhereColumns.Count > 0 then
00434 CopyResolveParameters(WhereColumns, Columns)
00435 else DefineWhereAllColumns(Columns);
00436 end;
00437
00438 {**
00439 Gets a collection of where all columns for DELETE or UPDATE DML statements.
00440 @param Columns a collection of key columns.
00441 }
00442 procedure TZGenericCachedResolver.DefineWhereAllColumns(Columns: TObjectList);
00443 var
00444 I: Integer;
00445 begin
00446 { Use precached values. }
00447 if WhereColumns.Count > 0 then
00448 begin
00449 CopyResolveParameters(WhereColumns, Columns);
00450 Exit;
00451 end;
00452
00453 { Takes a a key all non-blob fields. }
00454 for I := 1 to Metadata.GetColumnCount do
00455 begin
00456 if CheckKeyColumn(I) then
00457 begin
00458 WhereColumns.Add(TZResolverParameter.Create(I,
00459 Metadata.GetColumnName(I), Metadata.GetColumnType(I), False, ''));
00460 end;
00461 end;
00462
00463 { Copy defined parameters to target columns }
00464 CopyResolveParameters(WhereColumns, Columns);
00465 end;
00466
00467 {**
00468 Checks is the specified column can be used in where clause.
00469 @param ColumnIndex an index of the column.
00470 @returns <code>true</code> if column can be included into where clause.
00471 }
00472 function TZGenericCachedResolver.CheckKeyColumn(ColumnIndex: Integer): Boolean;
00473 begin
00474 Result := (Metadata.GetTableName(ColumnIndex) <> '')
00475 and (Metadata.GetColumnName(ColumnIndex) <> '')
00476 and Metadata.IsSearchable(ColumnIndex)
00477 and not (Metadata.GetColumnType(ColumnIndex)
00478 in [stUnknown, stAsciiStream, stBinaryStream, stUnicodeStream]);
00479 end;
00480
00481 {**
00482 Gets a collection of data columns to initialize before INSERT statements.
00483 @param Columns a collection of columns.
00484 @param RowAccessor an accessor object to column values.
00485 }
00486 procedure TZGenericCachedResolver.DefineCalcColumns(Columns: TObjectList;
00487 RowAccessor: TZRowAccessor);
00488 var
00489 I: Integer;
00490 begin
00491 for I := 1 to Metadata.GetColumnCount do
00492 begin
00493 if RowAccessor.IsNull(I) and (Metadata.GetTableName(I) <> '')
00494 and (Metadata.GetDefaultValue(I) <> '') then
00495 begin
00496 Columns.Add(TZResolverParameter.Create(I,
00497 Metadata.GetColumnName(I), Metadata.GetColumnType(I),
00498 True, Metadata.GetDefaultValue(I)));
00499 end;
00500 end;
00501 end;
00502
00503 {**
00504 Fills the specified statement with stored or given parameters.
00505 @param ResultSet a source result set object.
00506 @param Statement a DBC statement object.
00507 @param Config an UpdateStatement configuration.
00508 @param OldRowAccessor an accessor object to old column values.
00509 @param NewRowAccessor an accessor object to new column values.
00510 }
00511 procedure TZGenericCachedResolver.FillStatement(Statement: IZPreparedStatement;
00512 Params: TObjectList; OldRowAccessor, NewRowAccessor: TZRowAccessor);
00513 var
00514 I: Integer;
00515 ColumnIndex: Integer;
00516 Current: TZResolverParameter;
00517 RowAccessor: TZRowAccessor;
00518 WasNull: Boolean;
00519 begin
00520 WasNull := False;
00521 for I := 0 to Params.Count - 1 do
00522 begin
00523 Current := TZResolverParameter(Params[I]);
00524 if Current.NewValue then
00525 RowAccessor := NewRowAccessor
00526 else RowAccessor := OldRowAccessor;
00527 ColumnIndex := Current.ColumnIndex;
00528
00529 if FCalcDefaults then
00530 Statement.SetDefaultValue(I + 1, Metadata.GetDefaultValue(ColumnIndex));
00531
00532 case Metadata.GetColumnType(ColumnIndex) of
00533 stBoolean:
00534 Statement.SetBoolean(I + 1,
00535 RowAccessor.GetBoolean(ColumnIndex, WasNull));
00536 stByte:
00537 Statement.SetByte(I + 1, RowAccessor.GetByte(ColumnIndex, WasNull));
00538 stShort:
00539 Statement.SetShort(I + 1, RowAccessor.GetShort(ColumnIndex, WasNull));
00540 stInteger:
00541 Statement.SetInt(I + 1, RowAccessor.GetInt(ColumnIndex, WasNull));
00542 stLong:
00543 Statement.SetLong(I + 1, RowAccessor.GetLong(ColumnIndex, WasNull));
00544 stFloat:
00545 Statement.SetFloat(I + 1, RowAccessor.GetFloat(ColumnIndex, WasNull));
00546 stDouble:
00547 Statement.SetDouble(I + 1, RowAccessor.GetDouble(ColumnIndex, WasNull));
00548 stBigDecimal:
00549 Statement.SetBigDecimal(I + 1,
00550 RowAccessor.GetBigDecimal(ColumnIndex, WasNull));
00551 stString:
00552 Statement.SetString(I + 1, RowAccessor.GetString(ColumnIndex, WasNull));
00553 stUnicodeString:
00554 Statement.SetUnicodeString(I + 1,
00555 RowAccessor.GetUnicodeString(ColumnIndex, WasNull));
00556 stBytes:
00557 Statement.SetBytes(I + 1, RowAccessor.GetBytes(ColumnIndex, WasNull));
00558 stDate:
00559 Statement.SetDate(I + 1, RowAccessor.GetDate(ColumnIndex, WasNull));
00560 stTime:
00561 Statement.SetTime(I + 1, RowAccessor.GetTime(ColumnIndex, WasNull));
00562 stTimestamp:
00563 Statement.SetTimestamp(I + 1,
00564 RowAccessor.GetTimestamp(ColumnIndex, WasNull));
00565 stAsciiStream:
00566 Statement.SetBlob(I + 1, stAsciiStream,
00567 RowAccessor.GetBlob(ColumnIndex, WasNull));
00568 stUnicodeStream:
00569 Statement.SetBlob(I + 1, stUnicodeStream,
00570 RowAccessor.GetBlob(ColumnIndex, WasNull));
00571 stBinaryStream:
00572 Statement.SetBlob(I + 1, stBinaryStream,
00573 RowAccessor.GetBlob(ColumnIndex, WasNull));
00574 end;
00575 if WasNull then
00576 Statement.SetNull(I + 1, Metadata.GetColumnType(ColumnIndex))
00577 end;
00578 end;
00579
00580 {**
00581 Forms a where clause for UPDATE or DELETE DML statements.
00582 @param Columns a collection of key columns.
00583 @param OldRowAccessor an accessor object to old column values.
00584 }
00585 function TZGenericCachedResolver.FormWhereClause(Columns: TObjectList;
00586 OldRowAccessor: TZRowAccessor): string;
00587 var
00588 I, N: Integer;
00589 Current: TZResolverParameter;
00590 begin
00591 Result := '';
00592 N := Columns.Count - WhereColumns.Count;
00593
00594 for I := 0 to WhereColumns.Count - 1 do
00595 begin
00596 Current := TZResolverParameter(WhereColumns[I]);
00597 if Result <> '' then
00598 Result := Result + ' AND ';
00599
00600 Result := Result + IdentifierConvertor.Quote(Current.ColumnName);
00601 if OldRowAccessor.IsNull(Current.ColumnIndex) then
00602 begin
00603 Result := Result + ' IS NULL ';
00604 Columns.Delete(N);
00605 end
00606 else
00607 begin
00608 Result := Result + '=?';
00609 Inc(N);
00610 end;
00611 end;
00612
00613 if Result <> '' then
00614 Result := ' WHERE ' + Result;
00615 end;
00616
00617 {**
00618 Forms a where clause for INSERT statements.
00619 @param Columns a collection of key columns.
00620 @param NewRowAccessor an accessor object to new column values.
00621 }
00622 function TZGenericCachedResolver.FormInsertStatement(Columns: TObjectList;
00623 NewRowAccessor: TZRowAccessor): string;
00624 var
00625 I: Integer;
00626 Current: TZResolverParameter;
00627 TableName: string;
00628 Temp1, Temp2: string;
00629 begin
00630 TableName := DefineTableName;
00631 DefineInsertColumns(Columns);
00632 if Columns.Count = 0 then
00633 begin
00634 Result := '';
00635 Exit;
00636 end;
00637
00638 Temp1 := '';
00639 Temp2 := '';
00640 for I := 0 to Columns.Count - 1 do
00641 begin
00642 Current := TZResolverParameter(Columns[I]);
00643 if Temp1 <> '' then
00644 Temp1 := Temp1 + ',';
00645 Temp1 := Temp1 + IdentifierConvertor.Quote(Current.ColumnName);
00646 if Temp2 <> '' then
00647 Temp2 := Temp2 + ',';
00648 Temp2 := Temp2 + '?';
00649 end;
00650
00651 Result := Format('INSERT INTO %s (%s) VALUES (%s)', [TableName, Temp1, Temp2]);
00652 end;
00653
00654 {**
00655 Forms a where clause for UPDATE statements.
00656 @param Columns a collection of key columns.
00657 @param OldRowAccessor an accessor object to old column values.
00658 @param NewRowAccessor an accessor object to new column values.
00659 }
00660 function TZGenericCachedResolver.FormUpdateStatement(Columns: TObjectList;
00661 OldRowAccessor, NewRowAccessor: TZRowAccessor): string;
00662 var
00663 I: Integer;
00664 Current: TZResolverParameter;
00665 TableName: string;
00666 Temp: string;
00667 begin
00668 TableName := DefineTableName;
00669 DefineUpdateColumns(Columns, OldRowAccessor, NewRowAccessor);
00670 if Columns.Count = 0 then
00671 begin
00672 Result := '';
00673 Exit;
00674 end;
00675
00676 Temp := '';
00677 for I := 0 to Columns.Count - 1 do
00678 begin
00679 Current := TZResolverParameter(Columns[I]);
00680 if Temp <> '' then
00681 Temp := Temp + ',';
00682 Temp := Temp + IdentifierConvertor.Quote(Current.ColumnName) + '=?';
00683 end;
00684
00685 Result := Format('UPDATE %s SET %s', [TableName, Temp]);
00686 DefineWhereKeyColumns(Columns);
00687 Result := Result + FormWhereClause(Columns, OldRowAccessor);
00688 end;
00689
00690 {**
00691 Forms a where clause for DELETE statements.
00692 @param Columns a collection of key columns.
00693 @param OldRowAccessor an accessor object to old column values.
00694 }
00695 function TZGenericCachedResolver.FormDeleteStatement(Columns: TObjectList;
00696 OldRowAccessor: TZRowAccessor): string;
00697 var
00698 TableName: string;
00699 begin
00700 TableName := DefineTableName;
00701 Result := Format('DELETE FROM %s', [TableName]);
00702 DefineWhereKeyColumns(Columns);
00703 Result := Result + FormWhereClause(Columns, OldRowAccessor);
00704 end;
00705
00706 {**
00707 Forms a where clause for SELECT statements to calculate default values.
00708 @param Columns a collection of key columns.
00709 @param OldRowAccessor an accessor object to old column values.
00710 }
00711 function TZGenericCachedResolver.FormCalculateStatement(
00712 Columns: TObjectList): string;
00713 var
00714 I: Integer;
00715 Current: TZResolverParameter;
00716 begin
00717 Result := '';
00718 if Columns.Count = 0 then Exit;
00719
00720 for I := 0 to Columns.Count - 1 do
00721 begin
00722 Current := TZResolverParameter(Columns[I]);
00723 if Result <> '' then
00724 Result := Result + ',';
00725 if Current.DefaultValue <> '' then
00726 Result := Result + Current.DefaultValue
00727 else Result := Result + 'NULL';
00728 end;
00729
00730 Result := 'SELECT ' + Result;
00731 end;
00732
00733 {**
00734 Posts updates to database.
00735 @param Sender a cached result set object.
00736 @param UpdateType a type of updates.
00737 @param OldRowAccessor an accessor object to old column values.
00738 @param NewRowAccessor an accessor object to new column values.
00739 }
00740 procedure TZGenericCachedResolver.PostUpdates(Sender: IZCachedResultSet;
00741 UpdateType: TZRowUpdateType; OldRowAccessor, NewRowAccessor: TZRowAccessor);
00742 var
00743 Statement : IZPreparedStatement;
00744 SQL : string;
00745 SQLParams : TObjectList;
00746 lUpdateCount : Integer;
00747 lValidateUpdateCount : Boolean;
00748 begin
00749 if (UpdateType = utDeleted)
00750 and (OldRowAccessor.RowBuffer.UpdateType = utInserted) then
00751 Exit;
00752
00753 SQLParams := TObjectList.Create;
00754 try
00755 case UpdateType of
00756 utInserted:
00757 SQL := FormInsertStatement(SQLParams, NewRowAccessor);
00758 utDeleted:
00759 SQL := FormDeleteStatement(SQLParams, OldRowAccessor);
00760 utModified:
00761 SQL := FormUpdateStatement(SQLParams, OldRowAccessor, NewRowAccessor);
00762 else
00763 Exit;
00764 end;
00765
00766 if SQL <> '' then
00767 begin
00768 Statement := Connection.PrepareStatement(SQL);
00769 FillStatement(Statement, SQLParams, OldRowAccessor, NewRowAccessor);
00770 lValidateUpdateCount := StrToBoolEx(
00771 Sender.GetStatement.GetParameters.Values['ValidateUpdateCount']);
00772
00773 lUpdateCount := Statement.ExecuteUpdatePrepared;
00774 if (lValidateUpdateCount)
00775 and (lUpdateCount <> 1 ) then
00776 raise EZSQLException.Create(Format(SInvalidUpdateCount, [lUpdateCount]));
00777 end;
00778 finally
00779 SQLParams.Free;
00780 end;
00781 end;
00782
00783 {**
00784 Calculate default values for the fields.
00785 @param Sender a cached result set object.
00786 @param RowAccessor an accessor object to column values.
00787 }
00788 procedure TZGenericCachedResolver.CalculateDefaults(
00789 Sender: IZCachedResultSet; RowAccessor: TZRowAccessor);
00790 var
00791 I: Integer;
00792 SQL: string;
00793 SQLParams: TObjectList;
00794 Statement: IZStatement;
00795 ResultSet: IZResultSet;
00796 Metadata: IZResultSetMetadata;
00797 Current: TZResolverParameter;
00798 begin
00799 if not FCalcDefaults then Exit;
00800
00801 SQLParams := TObjectList.Create;
00802 try
00803 DefineCalcColumns(SQLParams, RowAccessor);
00804 SQL := FormCalculateStatement(SQLParams);
00805 if SQL = '' then Exit;
00806
00807 { Executes statement and fills default fields. }
00808 Statement := Connection.CreateStatement;
00809 try
00810 ResultSet := Statement.ExecuteQuery(SQL);
00811 if ResultSet.Next then
00812 begin
00813 Metadata := ResultSet.GetMetadata;
00814 for I := 1 to Metadata.GetColumnCount do
00815 begin
00816 Current := TZResolverParameter(SQLParams[I - 1]);
00817 try
00818 case Current.ColumnType of
00819 stBoolean:
00820 RowAccessor.SetBoolean(Current.ColumnIndex,
00821 ResultSet.GetBoolean(I));
00822 stByte:
00823 RowAccessor.SetByte(Current.ColumnIndex, ResultSet.GetByte(I));
00824 stShort:
00825 RowAccessor.SetShort(Current.ColumnIndex, ResultSet.GetShort(I));
00826 stInteger:
00827 RowAccessor.SetInt(Current.ColumnIndex, ResultSet.GetInt(I));
00828 stLong:
00829 RowAccessor.SetLong(Current.ColumnIndex, ResultSet.GetLong(I));
00830 stFloat:
00831 RowAccessor.SetFloat(Current.ColumnIndex, ResultSet.GetFloat(I));
00832 stDouble:
00833 RowAccessor.SetDouble(Current.ColumnIndex,
00834 ResultSet.GetDouble(I));
00835 stBigDecimal:
00836 RowAccessor.SetBigDecimal(Current.ColumnIndex,
00837 ResultSet.GetBigDecimal(I));
00838 stString, stAsciiStream:
00839 RowAccessor.SetString(Current.ColumnIndex,
00840 ResultSet.GetString(I));
00841 stUnicodeString:
00842 RowAccessor.SetUnicodeString(Current.ColumnIndex,
00843 ResultSet.GetUnicodeString(I));
00844 stBytes:
00845 RowAccessor.SetBytes(Current.ColumnIndex, ResultSet.GetBytes(I));
00846 stDate:
00847 RowAccessor.SetDate(Current.ColumnIndex, ResultSet.GetDate(I));
00848 stTime:
00849 RowAccessor.SetTime(Current.ColumnIndex, ResultSet.GetTime(I));
00850 stTimestamp:
00851 RowAccessor.SetTimestamp(Current.ColumnIndex,
00852 ResultSet.GetTimestamp(I));
00853 end;
00854
00855 if ResultSet.WasNull then
00856 RowAccessor.SetNull(Current.ColumnIndex);
00857 except
00858 { Supress any errors in default fields. }
00859 end;
00860 end;
00861 end;
00862 ResultSet.Close;
00863 finally
00864 Statement.Close;
00865 end;
00866 finally
00867 SQLParams.Free;
00868 end;
00869 end;
00870
00871 {BEGIN of PATCH [1185969]: Do tasks after posting updates. ie: Updating AutoInc fields in MySQL }
00872 procedure TZGenericCachedResolver.UpdateAutoIncrementFields(
00873 Sender: IZCachedResultSet; UpdateType: TZRowUpdateType; OldRowAccessor,
00874 NewRowAccessor: TZRowAccessor; Resolver: IZCachedResolver);
00875 begin
00876
00877 end;
00878 {END of PATCH [1185969]: Do tasks after posting updates. ie: Updating AutoInc fields in MySQL }
00879
00880 end.
00881