[V6] DML does not work with TSmartQ and SELECT FROM SELECT

[cis-wurzen]

I found another difference between ODAC 5 and 6. With ODAC 6 and TSmartQuery for exam. Append and Post does not work if the SQL statement contains a "SELECT * FROM (SELECT" statement. The first hurdle was that the PK field was readonly in contrast to ODAC 5, but that has something to do with the default value of SetFieldsReadOnly and so I set that to "False".

The test case for this is the following:

SQL for the test table

Code: Select all

CREATE TABLE SMARTTESTTABLE (
  PK  INTEGER
);

ALTER TABLE SMARTTESTTABLE ADD CONSTRAINT PK_SMARTTESTTABLE PRIMARY KEY (PK);

Delphi console application

Code: Select all

program SmartInsertTest;

{$APPTYPE CONSOLE}

{

CREATE TABLE SMARTTESTTABLE (
  PK  INTEGER
);

ALTER TABLE SMARTTESTTABLE ADD CONSTRAINT PK_SMARTTESTTABLE PRIMARY KEY (PK);

}

{.$DEFINE WITHOUT_SELECT_FROM_SELECT}

uses
  Ora, OraSmart;

const
  cServer = 'YourServer';
  cUserName = 'YourUser';
  cPassword = 'YourPassword';

var
  S: TOraSession;
  Q: TSmartQuery;
begin
  S := TOraSession.Create(nil);
  try
    S.Server := cServer;
    S.Username := cUserName;
    S.Password := cPassword;
    S.ConnectPrompt := False;
    Q := TSmartQuery.Create(nil);
    try
      Q.Session := S;
      {$IFDEF WITHOUT_SELECT_FROM_SELECT}
      Q.SQL.Add('SELECT * FROM SMARTTESTTABLE');
      {$ELSE ~WITHOUT_SELECT_FROM_SELECT}
      Q.SQL.Add('SELECT * FROM (SELECT * FROM SMARTTESTTABLE)');
      Q.Options.SetFieldsReadOnly := False;
      {$ENDIF ~WITHOUT_SELECT_FROM_SELECT}
      Q.KeyFields := 'PK';
      Q.UpdatingTable := 'SMARTTESTTABLE';
      Q.Open;
      Q.Append;
      Q.FieldByName('PK').AsInteger := 1;
      Q.Post;
      Q.Append;
      Q.FieldByName('PK').AsInteger := 1;
      Q.Post;
    finally
      Q.Free;
    end;
  finally
    S.Free;
  end;
end.

Steps:
- create the table
- create a new console application in Delphi and paste the code
- adjust the constants cServer, cUserName and cPassword
- compile and run the example

expected: ORA-00001 on second post and one record in the DB
actual: no exceptions, but no record in the DB

- remove the first dot in "{.$DEFINE WITHOUT_SELECT_FROM_SELECT}"
- compile and run the example

expected: ORA-00001 on second post and one record in the DB
actual: ORA-00001 on second post and one record in the DB

[Plash]

ODAC 6 parses the SQL statements more accurately. It detects that UpdatingTable is not present in your statement (subqueries are not considered). In ODAC 5 you can assign any value to the UpdatingTable property. But in ODAC 6 you can assing only a value that is present in the FROM list. ODAC cannot generate update statements for an incorrect updating table. You need to set update statements manually for ODAC be able to post changes to the database.

[cis-wurzen]

That is a huge step backward and prevents me from using ODAC 6 to support Oracle 11g and to finish the migration to Delphi 2009.
The app is using a lot of such statements and due to performance issues it is not possible to rewrite every statement.

It is very inconvenient to write insert or update statements, which care about the changed fields to limit the traffic, on my own. For me this is a feature of TSmartQuery and without the support for such statements I don't think that TSmartQuery is actually smart.

Please consider to support such statements again!

[Plash]

We'll consider the possibility of adding this feature agian in one of the next ODAC builds.

[cis-wurzen]

I've just downloaded 6.90.0.51 and it seems that this feature hasn't been added yet (it's not in the history and my test case still fails). Right?

This issue still blocks me from using Delphi 2010 and Oracle 11g. I need a solution asap to be able to run automated and manual tests to make sure the app does what it should do. (So far a lot of tests fail due the behavior change)

What has to be changed in the source to re-enable the old behavior?

[AntonH]

Same problem here.

Cannot switch to Delphi 2010 and ODAC 6.90 because a lot of queries from ODAC 5.80 and older Delphi-Versions stoped working.

Also very insidious (as said in the first post of cis-wurzen) the case, that inserting of records is possible without any error shown. If you afterwards call Q.Refresh these errorless inserted records are vanished. This could definitely not be called expected behavior

[Plash]

We have changed the ODAC behaviour.
Now when the FROM clause of your query contains subquery only and UpdatingTable is not empty, ODAC considers that all the fields belong to UpdatingTable. So you can edit them.

The fix will be included in the next build of ODAC. If you have ODAC with Sources, you can contact us by e-mail support*devart*com, and we will send you the fix. Please specify your license number in the e-mail.

We will also make ODAC raise an error if the StrictUpdate option is True and no query is executed on inserting record.

[cis-wurzen]

Thanks for the fix. I gave it a try and inserting itself does now work as expected. The queries contain sometimes additional fields and they are now a problem after insert and so on. All three TSmartQuery.RefreshOptions are enabled and TSmartQuery tries to refresh the fields from the other table too.

I'll compare the behavior with ODAC 5.80 and try to create a test case.

[Plash]

We have enhanced the ODAC behaviour to enable the detection of UpdatingTable fields in case of SELECT FROM SELECT. I have sent the fix to your e-mail.

[cis-wurzen]

Thanks. In some major cases the refresh does work now. I am sorry, but unfortunately there are still cases where it doesn't work yet.

Where it does work the refresh statement does look like this

Code: Select all

SELECT  AS "_0",  AS "_1", ... FROM .
WHERE
   = :

Where it doesn't work the refresh statement does look like this

Code: Select all

SELECT * FROM (SELECT * FROM .)
WHERE
  . = :

or

Code: Select all

SELECT * FROM (SELECT * FROM .)
WHERE
  .. = :

(it depends on the UpdatingTable if it is .. or just .)

I found out that it depends on the FullRefresh option. You should be able to repeat it if you add the following lines to the above test case

Code: Select all

      Q.RefreshOptions := [roAfterInsert, roAfterUpdate, roBeforeEdit];
      Q.Options.FullRefresh := True;//try it also with False

before

Code: Select all

      Q.Open;

, add DBAccess to the uses clause and adjust the PK values to avoid a PK error.
With Full Refresh ODAC 5 creates that refresh statement

Code: Select all

SELECT * FROM (SELECT * FROM SMARTTESTTABLE)
WHERE   PK = :OLD_1

and ODAC 6 creates that refresh statement

Code: Select all

SELECT * FROM (SELECT * FROM SMARTTESTTABLE)
WHERE
  SMARTTESTTABLE.PK = :PK

From the outside the only thing you have to do is to remove the qualification of the field - make it

Code: Select all

PK = :PK

instead of

Code: Select all

SMARTTESTTABLE.PK = :PK
or
OWNER.SMARTTESTTABLE.PK = :PK

[Plash]

We have fixed this problem. The fix will be included in the next build of ODAC.