[patch_done] Updating Same Record

[sandeep_c24]

Ok, we'll do it your way. I agree we should try and keep things simple.

Conclusion ( I think):
Every updatecount <> 0 is an error
In version 6.6 this can raise an error IF requested explicitly by setting a query property
In version 6.7 this should raise an error UNLESS this is disabled explicitly by setting a query property

Did you mean UpdateCount = 0 is an error?

Sandeep

[mdaems]

Oops. <>1...

[sandeep_c24]

What should I call this switch?

Should I use something like 'AllowZeroUpdateCount=False'? If this is found then exception is raised.

Sandeep

[sandeep_c24]

One more thing, It looks like there is no way to get hold of TZQuery.Properties in TZGenericCachedResolver.PostUpdates.

Any ideas how I should go about this?

Sandeep

[mdaems]

Can't they be retrieved in TZGenericCachedResolver.Create(Statement: IZStatement; Metadata: IZResultSetMetadata); from the statement parameter using IZStatement.GetParameters?
It's a wild guess. I didn't do a very deep investigation yet.

Mark

[sandeep_c24]

Hi

I have changed the following function in unit ZDbcGenericResolver to warn if update count <> 1.

Code: Select all

procedure TZGenericCachedResolver.PostUpdates%u28Sender%u3a IZCachedResultSet;
  UpdateType%u3a TZRowUpdateType; OldRowAccessor, NewRowAccessor%u3a TZRowAccessor%u29;
var
  Statement%u3a IZPreparedStatement;
  SQL%u3a string;
  SQLParams%u3a TObjectList;
  lUpdateCount%u3a Integer;
  lValidateUpdateCount%u3a string;
begin
  if %u28UpdateType = utDeleted%u29
    and %u28OldRowAccessor.RowBuffer.UpdateType = utInserted%u29 then
    Exit;

  SQLParams %u3a= TObjectList.Create;
  try
    case UpdateType of
      utInserted%u3a
        SQL %u3a= FormInsertStatement%u28SQLParams, NewRowAccessor%u29;
      utDeleted%u3a
        SQL %u3a= FormDeleteStatement%u28SQLParams, OldRowAccessor%u29;
      utModified%u3a
        SQL %u3a= FormUpdateStatement%u28SQLParams, OldRowAccessor, NewRowAccessor%u29;
      else
        Exit;
    end;

    if SQL <> '' then
    begin
      Statement %u3a= Connection.PrepareStatement%u28SQL%u29;
      FillStatement%u28Statement, SQLParams, OldRowAccessor, NewRowAccessor%u29;
      lValidateUpdateCount %u3a= UpperCase%u28
        Sender.GetStatement.GetParameters.Values%u5b'ValidateUpdateCount'%u5d%u29;

      lUpdateCount %u3a= Statement.ExecuteUpdatePrepared;
      if  %u28lValidateUpdateCount =  'TRUE'%u29
      and %u28lUpdateCount         <> 1     %u29 then
      begin
        raise Exception.Create%u28IntToStr%u28lUpdateCount%u29
          + ' record%u28s%u29 updated. Only one record should have been updated.'%u29;
      end;
    end;
  finally
    SQLParams.Free;
  end;
end;

I have not checked whether the return values of all the function in [Sender.GetStatement.GetParameters] have been assigned or not. Do you think I should check them to be on the safe side?

Sandeep

[mdaems]

Hi Sandeep,

If you checked the effect of this patch and can confirm the right warning is fired in case no or multiple updates are effectively done, I can add it and try to make it a little more standard. i.e. using StrToBoolEx(Sender.GetStatement.GetParameters.Values['ValidateUpdateCount']) and raise EZSQLException.Create(<standardmessageconstant>);
I don't think more checks are needed for the moment. So, I move this thread to the user patches forum. I'll tell you when the definitive patch is ready. Can you keep your test case somewhere? That way it will be easy to validate the definitive solution.

Mark

[sandeep_c24]

Hi Mark

I have done the changes.

Code: Select all

procedure TZGenericCachedResolver.PostUpdates(Sender: IZCachedResultSet;
  UpdateType: TZRowUpdateType; OldRowAccessor, NewRowAccessor: TZRowAccessor);
var
  Statement            : IZPreparedStatement;
  SQL                  : string;
  SQLParams            : TObjectList;
  lUpdateCount         : Integer;
  lValidateUpdateCount : Boolean;
begin
  if (UpdateType = utDeleted)
    and (OldRowAccessor.RowBuffer.UpdateType = utInserted) then
    Exit;

  SQLParams := TObjectList.Create;
  try
    case UpdateType of
      utInserted:
        SQL := FormInsertStatement(SQLParams, NewRowAccessor);
      utDeleted:
        SQL := FormDeleteStatement(SQLParams, OldRowAccessor);
      utModified:
        SQL := FormUpdateStatement(SQLParams, OldRowAccessor, NewRowAccessor);
      else
        Exit;
    end;

    if SQL <> '' then
    begin
      Statement := Connection.PrepareStatement(SQL);
      FillStatement(Statement, SQLParams, OldRowAccessor, NewRowAccessor);
      lValidateUpdateCount := StrToBoolEx(
        Sender.GetStatement.GetParameters.Values['ValidateUpdateCount']);

      lUpdateCount := Statement.ExecuteUpdatePrepared;
      if  (lValidateUpdateCount)
      and (lUpdateCount <> 1   ) then
        raise EZSQLException.Create(Format(SInvalidUpdateCount, [lUpdateCount]));
    end;
  finally
    SQLParams.Free;
  end;
end;

I have also added following to ZMessages

Code: Select all

  SInvalidUpdateCount     = '%d record(s) updated. Only one record should have been updated.';

Can you keep your test case somewhere? That way it will be easy to validate the definitive solution.

I am not quite sure what you mean.

I have tested the above and an exception is raised if NO / MULTIPLE records are updated.

Sandeep

[mdaems]

Committed in SVN rev. 357 and 358. Only 357 will be merged to trunk for release 6.6.x !! (I hope I don't merge it by accident)
If you use Testing branch the check will (should) be active by default.

Mark

[sandeep_c24]

Hi Mark

I have tested the branch 358 and it works fine with and without ValidateUpdateCount.

Sandeep