StrictUpdate problem

[ths]

Hi,

we have a problem with unexpected behaviour by update when StrictUpdate is set to TRUE on the query.
If there is a CLOB field included into the update and we use the following pattern:

update mytable set clobfield = EMPTY_MEMO() where id = 1 retruning memo into :memo

then there is no exception in the case when no rows found for update.

P.S. We have found that this problem exists for the combination CachedUpdates= true and Direct=true in the ODAC version 9.5.14 that we use. The problem seems to be corrected in the last version 9.5.16 supported for Delphi5. However, for this last version we do not have compiled installation from DevArt. The last compiled Installation for Delphi5 is 9.5.14.

Vladimir

[AlexP]

Hello,

If you are specifying this query in the TOraQuery.SQL property, then the StrictUpdate option won't be considered on query execution. You should specify the UPDATE query in the TOraQuery.UpdateSQL property. Then an error message will be returned on record not found.

[ths]

Hello,

we use TOraUpdateSQL component and its ModifySQL property for update command.
The behaviour is as I wrote in the P.S. section above.

Vladimir

[AlexP]

We can't reproduce the problem. The below code works with no errors. Please modify the code, so that the error is reproduced.

Code: Select all

CREATE TABLE SCOTT.T_CLOB (
  F_ID   NUMBER(10, 0),
  F_CLOB CLOB,
  CONSTRAINT PK_T_CLOB PRIMARY KEY (F_ID));
/
INSERT INTO SCOTT.T_CLOB(F_ID) VALUES(1);
/

Code: Select all

program Project1;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  System.SysUtils, DB, Ora;

var
  OraSession: TOraSession;
  OraQuery: TOraQuery;
  OraUpdateSQL: TOraUpdateSQL;

begin
  OraSession := TOraSession.Create(nil, 'scott/tiger@orcl');
  try
    OraQuery := TOraQuery.Create(nil);
    try
      OraQuery.Session := OraSession;
      OraQuery.SQL.Text := 'select * from t_clob';
      OraUpdateSQL := TOraUpdateSQL.Create(nil);
      try
        OraQuery.UpdateObject := OraUpdateSQL;
        OraUpdateSQL.SQL[ukModify].Text := 'update t_clob set f_clob = EMPTY_CLOB() where 1<>1 returning f_clob into :f_clob';
        OraQuery.Open;
        OraQuery.Edit;
        OraQuery.Fields[0].AsInteger := OraQuery.Fields[0].AsInteger + 1;
        try
          OraQuery.Post;
          WriteLn('Error')
        except
          on E: Exception do
            WriteLn('OK')
        end;
      finally
        OraUpdateSQL.free;
      end;
    finally
      OraQuery.Free;
    end;
  finally
    OraSession.Free;
    readln;
  end;
end.

[ths]

Hi Alex,
just try OraSession := TOraSession.Create(nil,'Direct=True;Server=MyServer;Port=1521;SID=MySid;User ID=MyUser;Password=MyPassowrd;Use Unicode=0');
This is the key point: Direct must be True.

[AlexP]

On the latest ODAC version 9.6.22 the error is not reproduced in both OCI and Direct modes. Check yhe sample on the latest ODAC version - and let us know the results.

[ths]

Sorry, Alex, but you probably did not read the end of the first post - I copy it here one more time:
P.S. We have found that this problem exists for the combination CachedUpdates= true and Direct=true in the ODAC version 9.5.14 that we use. The problem seems to be corrected in the last version 9.5.16 supported for Delphi5. However, for this last version we do not have compiled installation from DevArt. The last compiled Installation for Delphi5 is 9.5.14.

We still have this project under Delphi5 and you do not support it anymore.

[AlexP]

You are the first user to report such an issue. Therefore we can't tell what modification changed this behavior. You can compare the sources of your version and the current one to detect the modification.

P.S. As I wrote earlier, Delphi 5 support was discontinued in the SRC edition when Seattle was released.

[ths]

In 9.5.14 the code
FDataSet.FRowsAffected := TDBAccessUtils.GetRowsAffected(FUpdateQuery);
returns (wrongly) 1 although no row was updated.
In 9.5.16 it behaves correctly.

[AlexP]

The changes, that lead to correct behavior, were added to the OraNet module. Therefore we can't provide you a correct code.