Page 1 of 1
Empty generated inserts when using UpdatingTable
Posted: Wed 03 Jun 2015 07:02
by 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
Re: Empty generated inserts when using UpdatingTable
Posted: Wed 03 Jun 2015 09:14
by 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.
Re: Empty generated inserts when using UpdatingTable
Posted: Mon 15 Jun 2015 11:47
by 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.
Re: Empty generated inserts when using UpdatingTable
Posted: Mon 15 Jun 2015 12:32
by 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
Re: Empty generated inserts when using UpdatingTable
Posted: Tue 16 Jun 2015 08:19
by AlexP
Please specify the old version of ODAC, on which INSERT/UPDATE queries were generated for subqueries.
Re: Empty generated inserts when using UpdatingTable
Posted: Tue 16 Jun 2015 09:33
by a-s-z
Hi,
We get no error when using DACVersion: ODAC 9.1.4
Best regards,
Andre
Re: Empty generated inserts when using UpdatingTable
Posted: Wed 17 Jun 2015 08:55
by 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.
Re: Empty generated inserts when using UpdatingTable
Posted: Wed 17 Jun 2015 14:43
by 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
Re: Empty generated inserts when using UpdatingTable
Posted: Thu 18 Jun 2015 08:23
by 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.
Re: Empty generated inserts when using UpdatingTable
Posted: Thu 18 Jun 2015 09:12
by 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
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
Re: Empty generated inserts when using UpdatingTable
Posted: Fri 19 Jun 2015 05:18
by AlexP
Glad to see that you have found the solution. We will investigate the possibility to return the old behavior.