Empty generated inserts when using UpdatingTable

[a-s-z]

Hello,

after upgrading to 9.5 we are getting ORA-00928 when inserting records into some queries.

The problem seems to be caused by missing TableInfo references in FieldDefs.

I will send a test project to [email protected]

Here is the DDL to prepare the schema

Code: Select all

DACProductName DACVersion: ODAC 9.5.16
------------------------------------------------------------
Preparing Schema ...
OCIVersion: 11203
begin
  begin
    execute immediate 'drop table odac_updtable_error cascade constraints';
  exception when others then null; 
  end;   
  begin
    execute immediate 'create table odac_updtable_error
(
  DOKU_VERWEIS_ID NUMBER(18, 0) CONSTRAINT XNNodac_updtable_error1 NOT NULL,
  DOKU_ID         NUMBER(18, 0),
  TAB_NAME        VARCHAR2(30 BYTE),
  TAB_ID          NUMBER(18, 0),
--  ERST_ID         NUMBER(18, 0),
--  ERST_DATUM      DATE,
--  BEARB_ID        NUMBER(18, 0),
--  BEARB_DATUM     DATE,
  CONSTRAINT XPKodac_updtable_error PRIMARY KEY (DOKU_VERWEIS_ID) USING INDEX
)';
  --exception when others then null; 
  end;   
end;
============================================================

Here is the failing query:

Code: Select all

SELECT
  T.*
FROM
  ( SELECT
      T.*,
      :P_TAB_KEY PAM$ZUORD_KEY_TAB,
      :P_TAB_DETAIL PAM$ZUORD_DETAIL_TAB,
      :P_FORM_ID PAM$ZUORD_FORM_ID,      
      ''||DOKU_ID KEY_LOOKUP,
      ''||TAB_ID TAB_LOOKUP      
       --
    FROM
      odac_updtable_error T  
       --
    WHERE 
      :P_KEY_ID = DECODE(0, 1, DOKU_ID, TAB_ID)
       AND TAB_NAME LIKE NVL(:P_TAB_DETAIL, '%') /* M_MULTITAB */ 
  ) T
WHERE 1=1
 --
 --
 --
OCIVersion: 11203
------------------------------------------------------------
     OracleVersion: 11.2.0.3.0
OraCall.OCIUnicode: True
    Options.Direct: False
Options.UseUnicode: True
Time to open: 1422ms
RecordCount: 0
Time to fetch: 0ms
Exception [EOraError]: ORA-00928: Schlüsselwort SELECT fehlt

Best regards,
Andre

[AlexP]

Hello,

Thank you for the sample. We have reproduced the described case and will investigate the reason for such behavior. We will inform you as soon as we have any results.

[AlexP]

In your case, you are setting the UpdatingTable value manually, therefore an attempt to generate a query occurs. However, we don't retrieve fields for subqueries. You should write UPDATE queries manually in your case.

[a-s-z]

Hi Alex,

why did it work with previous versions then?

When analyzing the problem, I have noticed that Odac will get the fields from the server in the first call (because usage of '*' in the query). In subsequent calls, it does not fetch the fields and so the update statement is empty.
It has to do with UpdTableIsArtificial, which is set in first call, but not in subsequent calls of GetExtFieldsInfo.

Is it possible to fix the issue without writing manual updates? Our components are highly configurable and are not really useable with manual update statements.

Best regards,
Andre

[AlexP]

Please specify the old version of ODAC, on which INSERT/UPDATE queries were generated for subqueries.

[a-s-z]

Hi,

We get no error when using DACVersion: ODAC 9.1.4

Best regards,
Andre

[AlexP]

In the previous versions, UPDATE queries were generated incorrectly (when UpdateAllFields = True), since all the fields were included (even those who were not in the table) - and an error occurred on attempt to execute the query. We will consider the possibility to generate queries for such a case in one of the next versions.

[a-s-z]

Hi Alex,

is it possible to restore the old behavior with UpdateAllFields = False?

I think the current version does only fail, when using * in the inner query, because the fields are not read from the server. Is it possible to "force" this behavior for such queries?

Best regards,
Andre

[AlexP]

Currently, to solve the problem, you can remove the call of the Prepare method and set the UpdateAllFields property to False. In this case, correct queries will be generated.

[a-s-z]

Hi Alex,

unfortunatly we cannot remove the call to Prepare in our project

I hooked the method THookCRRecordSet.InitUpdatingTableIdx.
In this method I replaced the line

Code: Select all

FUpdTableIsArtificial := False;

with

Code: Select all

  if not (FUpdTableIsArtificial and (FTablesInfo.Count = 1) and (NormalizedName = FTablesInfo[0].TableName)) then
    FUpdTableIsArtificial := False;

This is working even when calling Prepare.

Best regards,
Andre

[AlexP]

Glad to see that you have found the solution. We will investigate the possibility to return the old behavior.