QualityCentral

Public Report

Report From: Delphi-BCB/Midas/TDataSetProvider [ Add a report in this area ]

Report #: 2338

Status: Closed

ApplyUpdates fails if only fields without pfInUpdate are modified

Project: Delphi

Build #: 6.240

Version: 7.0

Submitted By: Gabriel Corneanu

Report Type: Basic functionality failure

Date Reported: 9/12/2002 11:31:31 AM

Severity: Infrequently encountered problem

Last Updated: 12/15/2006 4:02:57 AM

Platform: 95, 98, 2000, NT, XP

Internal Tracking #: 82238

Resolution: Fixed (Resolution Comments)

Resolved in Build: : 7.0.8.1

Duplicate of: None

Voting and Rating

Overall Rating: (3 Total Ratings)

4.67 out of 5

Total Votes: 3

Description

It's common to have a result set from a join where I let the provider apply updates for the fields in the main table and make the changes myself for the joined table.
The fields from the joined table have provider flags set to [] because they must not be updated by the provider.
But what happens when only fields from the joined table are changed? The provider will try to apply changes even there is no modified field.
I tracked it down and it's an error in TSQLResolver.GenUpdateSQL method. It should just check to see if there are any fields added to the SQL statement.

Steps to Reproduce:

Open project attached
Run
Change Category field //which has pfnUpdate set To False
Click applyUpdates button
//error message//

In this case the resolver should simply do nothing instead of generating an invalid update SQL statement.

Workarounds

PROVIDER.PAS
(**) (* Delphi 6.0 - 7.0 *)
(* QC#2338 fix. Mods also reqd to TSQLResolver.GenUpdateSQL
   Incorrect SQL being generated when there are no fields requiring update *)
(* Dave Rowntree 05/29/03 *)
procedure TSQLResolver.InternalDoUpdate(Tree: TUpdateTree; UpdateKind: TUpdateKind);
var
  Alias: string;
begin
  if not IProviderSupport(Tree.Source).PSUpdateRecord(UpdateKind, Tree.Delta) then
  begin
    if (PSQLInfo(Tree.Data)^.QuotedTable = '') and not Tree.IsNested then
      DatabaseError(SNoTableName);
    if PSQLInfo(Tree.Data)^.HasObjects then Alias := DefAlias else Alias := '';
    FSQL.Clear;
    FParams.Clear;
    case UpdateKind of
      ukModify: GenUpdateSQL(Tree, FSQL, FParams, Alias);
      ukInsert: GenInsertSQL(Tree, FSQL, FParams);
      ukDelete: GenDeleteSQL(Tree, FSQL, FParams, Alias);
    end;
    if FSQL.Text <> '' then (* add this line *)
      DoExecSQL(FSQL, FParams);
  end;
end;

(**) (* Delphi 6.0 - 7.0 *)
(* QC#2338 fix. mods also reqd to TSQLResolver.InternalDoUpdate.
   Incorrect SQL being generated when there are no fields requiring update *)
(* Dave Rowntree 05/29/03 *)
procedure TSQLResolver.GenUpdateSQL(Tree: TUpdateTree; SQL: TStrings;
  Params: TParams; Alias: string);

  {procedure AddField(Field: TField; InObject, InArray: Boolean);} (* rem out this line *)
  function AddField(Field: TField; InObject, InArray: Boolean): boolean;   (* add this line *)
  var
    i: Integer;
    TempStr: string;
    Value: Variant;
    NoParam: Boolean;
  begin
    Result := False; (* add this line *)
    NoParam := False;
    with PSQLInfo(Tree.Data)^ do
    begin
      if Field.DataType = ftADT then
      begin
        if InArray then
          SQL.Add(Format(' %s(',[TObjectField(Field).ObjectType]));
        for i := 0 to TObjectField(Field).FieldCount - 1 do
          Result := Result or (* add this line *)
            AddField(TObjectField(Field).Fields[i], True, InArray);
        if InArray then
        begin
          TempStr := SQL[SQL.Count-1];
          SQL[SQL.Count-1] := Copy(TempStr, 1, Length(TempStr) - 1);
          SQL.Add('),');
        end;
      end
      else if Field.DataType = ftArray then
      begin
        SQL.Add(Format('%s = %s(',[Field.FullName, TObjectField(Field).ObjectType]));
        for i := 0 to TObjectField(Field).FieldCount - 1 do
          Result := Result or (* add this line *)
            AddField(TObjectField(Field).Fields[i], InObject, True);
        TempStr := SQL[SQL.Count-1];
        SQL[SQL.Count-1] := Copy(TempStr, 1, Length(TempStr) - 1);
        SQL.Add('),');
      end
      else if InArray then
      begin
        SQL.Add(' ?,');
        Value := Field.NewValue;
        if VarIsClear(Value) then Value := Field.OldValue;
        TParam(Params.Add).AssignFieldValue(Field, Value);
        Result := True;  (* add this line *)
      end
      else if UseFieldInUpdate(Field) then
      begin
        Result := True; (* add this line *)
        if (Field.DataType = ftOraClob) and (not InformixLob) then
        begin
          NoParam := True;
        ......................................

var
  I, J: integer;
  TempStr: string;
  OraLobs: Integer;
  Value: Variant;
  UpdateReqd: boolean; (* add this line*)
begin
  OraLobs := 0;
  UpdateReqd := False; (* add this line*)
  with PSQLInfo(Tree.Data)^ do
  begin
    if Tree.IsNested then
    begin
      Alias := NestAlias;
      SQL.Add(Format('update the (select %s FROM %s %s',[QuoteFullName(Tree.Name, QuoteChar),   { Do not

localize }
        PSQLInfo(Tree.Parent.Data).QuotedTable, DefAlias]));
      GenWhereSQL(Tree.Parent, SQL, Params, upWhereKeyOnly, DefAlias);
      SQL.Add(Format(') %s set',[Alias]));                                                      { Do not

localize }
    end else
      SQL.Add(Format('update %s %s set', [QuotedTable, Alias]));                                { Do not

localize }

    for I := 0 to Tree.Delta.FieldCount - 1 do
    begin
      if (Tree.Delta.Fields[i].DataType in [ftOraClob, ftOraBlob]) and
          UseFieldInUpdate(Tree.Delta.Fields[I]) then
          if (not InformixLob) then
            Inc(OraLobs);
      {AddField(Tree.Delta.Fields[i], Alias = NestAlias, False);} (* rem out this line *)
      UpdateReqd :=           (* add this line *)
        AddField(Tree.Delta.Fields[i], Alias = NestAlias, False) or (* add this line *)
        UpdateReqd;           (* add this line *)
    end;

    if not UpdateReqd then (* add this line *)
    begin                  (* add this line *)
      SQL.Clear;           (* add this line *)
      Exit;                (* add this line *)
    end;                   (* add this line *)

    { Remove last ',' }
    TempStr := SQL[SQL.Count-1];

---------

PROVIDER.PAS
(**) (* Delphi 6.0 - 7.0 *)
(* QC#2338 fix. Mods also reqd to TSQLResolver.GenUpdateSQL
   Incorrect SQL being generated when there are no fields requiring update *)
(* Dave Rowntree 05/29/03 *)
procedure TSQLResolver.InternalDoUpdate(Tree: TUpdateTree; UpdateKind: TUpdateKind);
var
  Alias: string;
begin
  if not IProviderSupport(Tree.Source).PSUpdateRecord(UpdateKind, Tree.Delta) then
  begin
    if (PSQLInfo(Tree.Data)^.QuotedTable = '') and not Tree.IsNested then
      DatabaseError(SNoTableName);
    if PSQLInfo(Tree.Data)^.HasObjects then Alias := DefAlias else Alias := '';
    FSQL.Clear;
    FParams.Clear;
    case UpdateKind of
      ukModify: GenUpdateSQL(Tree, FSQL, FParams, Alias);
      ukInsert: GenInsertSQL(Tree, FSQL, FParams);
      ukDelete: GenDeleteSQL(Tree, FSQL, FParams, Alias);
    end;
    if FSQL.Text <> '' then (* add this line *)
      DoExecSQL(FSQL, FParams);
  end;
end;

(**) (* Delphi 6.0 - 7.0 *)
(* QC#2338 fix. mods also reqd to TSQLResolver.InternalDoUpdate.
   Incorrect SQL being generated when there are no fields requiring update *)
(* Dave Rowntree 05/29/03 *)
procedure TSQLResolver.GenUpdateSQL(Tree: TUpdateTree; SQL: TStrings;
  Params: TParams; Alias: string);

  {procedure AddField(Field: TField; InObject, InArray: Boolean);} (* rem out this line *)
  function AddField(Field: TField; InObject, InArray: Boolean): boolean;   (* add this line *)
  var
    i: Integer;
    TempStr: string;
    Value: Variant;
    NoParam: Boolean;
  begin
    Result := False; (* add this line *)
    NoParam := False;
    with PSQLInfo(Tree.Data)^ do
    begin
      if Field.DataType = ftADT then
      begin
        if InArray then
          SQL.Add(Format(' %s(',[TObjectField(Field).ObjectType]));
        for i := 0 to TObjectField(Field).FieldCount - 1 do
          Result := Result or (* add this line *)
            AddField(TObjectField(Field).Fields[i], True, InArray);
        if InArray then
        begin
          TempStr := SQL[SQL.Count-1];
          SQL[SQL.Count-1] := Copy(TempStr, 1, Length(TempStr) - 1);
          SQL.Add('),');
        end;
      end
      else if Field.DataType = ftArray then
      begin
        SQL.Add(Format('%s = %s(',[Field.FullName, TObjectField(Field).ObjectType]));
        for i := 0 to TObjectField(Field).FieldCount - 1 do
          Result := Result or (* add this line *)
            AddField(TObjectField(Field).Fields[i], InObject, True);
        TempStr := SQL[SQL.Count-1];
        SQL[SQL.Count-1] := Copy(TempStr, 1, Length(TempStr) - 1);
        SQL.Add('),');
      end
      else if InArray then
      begin
        SQL.Add(' ?,');
        Value := Field.NewValue;
        if VarIsClear(Value) then Value := Field.OldValue;
        TParam(Params.Add).AssignFieldValue(Field, Value);
        Result := True;  (* add this line *)
      end
      else if UseFieldInUpdate(Field) then
      begin
        Result := True; (* add this line *)
        if (Field.DataType = ftOraClob) and (not InformixLob) then
        begin
          NoParam := True;
        ......................................

var
  I, J: integer;
  TempStr: string;
  OraLobs: Integer;
  Value: Variant;
  UpdateReqd: boolean; (* add this line*)
begin
  OraLobs := 0;
  UpdateReqd := False; (* add this line*)
  with PSQLInfo(Tree.Data)^ do
  begin
    if Tree.IsNested then
    begin
      Alias := NestAlias;
      SQL.Add(Format('update the (select %s FROM %s %s',[QuoteFullName(Tree.Name, QuoteChar),   { Do not

localize }
        PSQLInfo(Tree.Parent.Data).QuotedTable, DefAlias]));
      GenWhereSQL(Tree.Parent, SQL, Params, upWhereKeyOnly, DefAlias);
      SQL.Add(Format(') %s set',[Alias]));                                                      { Do not

localize }
    end else
      SQL.Add(Format('update %s %s set', [QuotedTable, Alias]));                                { Do not

localize }

    for I := 0 to Tree.Delta.FieldCount - 1 do
    begin
      if (Tree.Delta.Fields[i].DataType in [ftOraClob, ftOraBlob]) and
          UseFieldInUpdate(Tree.Delta.Fields[I]) then
          if (not InformixLob) then
            Inc(OraLobs);
      {AddField(Tree.Delta.Fields[i], Alias = NestAlias, False);} (* rem out this line *)
      UpdateReqd :=           (* add this line *)
        AddField(Tree.Delta.Fields[i], Alias = NestAlias, False) or (* add this line *)
        UpdateReqd;           (* add this line *)
    end;

    if not UpdateReqd then (* add this line *)
    begin                  (* add this line *)
      SQL.Clear;           (* add this line *)
      Exit;                (* add this line *)
    end;                   (* add this line *)

    { Remove last ',' }
    TempStr := SQL[SQL.Count-1];

Attachment

Project.zip

Comments

Dave Rowntree at 9/13/2002 9:20:20 AM -

This is a known problem. MIDAS generates invalid SQL if the only fields updated all have pfInUpdate set to False.

I changed the report Area to TDataSetProvider.

Gabriel Corneanu at 9/13/2002 10:31:26 PM -

Of course another workaround (what I am using right now) is to check updated fields in BeforeUpdateRecord and set Applied to true if necessary. But it seems too much.

Quality Central

Developer Tools

Database Tools