Zeoslib Portal

Posted: **15.05.2014, 12:38**

There's a problem in TZGenericCachedResolver.DefineWhereKeyColumns when the current table is actually a VIEW. Currently the procedure tries to get primary keys by DatabaseMetadata.GetPrimaryKeys(%view_name%) but obviously fails because the key actually belongs to a table not to a view. So the wmWhereAll mode is used in all cases. In my case it results in update fail.

I presume TZGenericCachedResolver must regard the owner dataset's Properties.KeyFields setting and only retrieve primary keys from DB metadata when this setting is empty.

I use Firebird 2.5, Delphi 7 and Zeos 7.1.3 but 7.2 version seem not having changes in this area. Problem is straightforward but I could provide a test project if needed.

Posted: **15.09.2017, 15:30**

Seems like I did it.

src/dbc/ZDbcGenericResolver.pas

Code: Select all

 src/dbc/ZDbcGenericResolver.pas | 89 +++++++++++++++++++++++++++++------------
 1 file changed, 64 insertions(+), 25 deletions(-)

diff --git a/src/dbc/ZDbcGenericResolver.pas b/src/dbc/ZDbcGenericResolver.pas
index 3f0de89..8b63950 100644
--- a/src/dbc/ZDbcGenericResolver.pas
+++ b/src/dbc/ZDbcGenericResolver.pas
@@ -177,7 +177,8 @@ type
 
 implementation
 
-uses ZMessages, ZSysUtils, ZDbcMetadata, ZDbcUtils
+uses ZMessages, ZSysUtils, ZDbcMetadata, ZDbcUtils, ZDatasetUtils, ZTokenizer,
+  ZAbstractRODataset
   {$IFDEF FAST_MOVE}, ZFastCode{$ENDIF};
 
 { TZResolverParameter }
@@ -420,12 +421,38 @@ end;
   @param Columns a collection of key columns.
 }
 procedure TZGenericCachedResolver.DefineWhereKeyColumns(Columns: TObjectList);
+
+  function AddColumn(const Table, ColumnName: string; WhereColumns: TObjectList): Boolean;
+  var
+    ColIdx, I: Integer;
+  begin
+    Result := False;
+    ColIdx := InvalidDbcIndex;
+    for I := FirstDbcIndex to Metadata.GetColumnCount{$IFDEF GENERIC_INDEX}-1{$ENDIF} do
+      if (ColumnName = Metadata.GetColumnName(I))
+        and (Table = Metadata.GetTableName(I)) then
+      begin
+        ColIdx := I;
+        Break;
+      end;
+    if ColIdx = InvalidDbcIndex then
+    begin
+      WhereColumns.Clear;
+      Exit;
+    end;
+    WhereColumns.Add(TZResolverParameter.Create(I, ColumnName,
+      stUnknown, False, ''));
+    Result := True;
+  end;
+
 var
   I: Integer;
-  Found: Boolean;
-  ColumnName: string;
+  KeyFields: string;
   Catalog, Schema, Table: string;
   PrimaryKeys: IZResultSet;
+  Tokens: TStrings;
+  TokenType: TZTokenType;
+  TokenValue: string;
 begin
   { Use precached values. }
   if WhereColumns.Count > 0 then
@@ -450,31 +477,43 @@ begin
   { Tryes to define primary keys. }
   if not WhereAll then
   begin
-    {For exact results: quote all identifiers SEE: http://sourceforge.net/p/zeoslib/tickets/81/
-    If table names have mixed case ConstructNameCondition will return wrong results
-    and we fall back to WhereAll}
-    PrimaryKeys := DatabaseMetadata.GetPrimaryKeys(IdentifierConvertor.Quote(Catalog),
-      IdentifierConvertor.Quote(Schema), IdentifierConvertor.Quote(Table));
-    while PrimaryKeys.Next do
+    KeyFields := FStatement.GetParameters.Values['KeyFields'];
+    { Let user define key fields }
+    if KeyFields <> '' then
     begin
-      ColumnName := PrimaryKeys.GetString(ColumnNameIndex);
-      Found := False;
-      for I := FirstDbcIndex to Metadata.GetColumnCount{$IFDEF GENERIC_INDEX}-1{$ENDIF} do
-      begin
-        if (ColumnName = Metadata.GetColumnName(I))
-          and (Table = Metadata.GetTableName(I)) then
+      Tokens := CommonTokenizer.TokenizeBufferToList(KeyFields,
+        [toSkipEOF, toSkipWhitespaces, toUnifyNumbers, toDecodeStrings]);
+
+      try
+        for I := 0 to Tokens.Count - 1 do
         begin
-          Found := True;
-          Break;
+          TokenType := TZTokenType({$IFDEF oldFPC}Pointer({$ENDIF}
+            Tokens.Objects[I]{$IFDEF oldFPC}){$ENDIF});
+          TokenValue := Tokens[I];
+
+          if TokenType in [ttWord, ttQuoted] then
+          begin
+            if not AddColumn(Table, TokenValue, WhereColumns) then
+              Break;
+          end
+          else if (TokenValue <> ',') and (TokenValue <> ';') then
+            raise EZDatabaseError.Create(Format(SIncorrectSymbol, [TokenValue]));
         end;
+      finally
+        Tokens.Free;
       end;
-      if not Found then
-      begin
-        WhereColumns.Clear;
-        Break;
-      end;
-      WhereColumns.Add(TZResolverParameter.Create(I, ColumnName,
-        stUnknown, False, ''));
+    end
+    else
+    { Ask DB for key fields }
+    begin
+      {For exact results: quote all identifiers SEE: http://sourceforge.net/p/zeoslib/tickets/81/
+      If table names have mixed case ConstructNameCondition will return wrong results
+      and we fall back to WhereAll}
+      PrimaryKeys := DatabaseMetadata.GetPrimaryKeys(IdentifierConvertor.Quote(Catalog),
+        IdentifierConvertor.Quote(Schema), IdentifierConvertor.Quote(Table));
+      while PrimaryKeys.Next do
+        if not AddColumn(Table, PrimaryKeys.GetString(ColumnNameIndex), WhereColumns) then
+          Break;
     end;
   end;
 
@@ -854,7 +893,7 @@ begin
       begin
         if DeleteStatement = nil then
         begin
-        SQL := FormDeleteStatement(FDeleteParams, OldRowAccessor);
+          SQL := FormDeleteStatement(FDeleteParams, OldRowAccessor);
           If Assigned(DeleteStatement) and (SQL <> DeleteStatement.GetSQL) then
             DeleteStatement := nil;
           If not Assigned(DeleteStatement) then

or on GitHub

Zeoslib Portal

wmWhereKeyOnly fails for VIEWs

wmWhereKeyOnly fails for VIEWs

Re: wmWhereKeyOnly fails for VIEWs