[fixed in 9.7.24] TOraQuery does not generate update code

[pcz]

Hello

I got a little problem with TOraQuery object and internal generation of update code

My SQL:

Code: Select all

with ww_min as
(
  select min(wiek) w_min
  from inw_wsp_wiek ww_g
)
select ww.id_sezonu, dwp.wartosc_param sezon, ww.wiek, ww.wspolczynnik,
      decode(ww.wiek, ww_m.w_min, 'T', 'N') czy_kasowac
from inw_wsp_wiek ww
     join
     it_dop_wartosc_param dwp
     on (dwp.id = ww.id_sezonu)
     cross join
     ww_min ww_m

TOraQuery just don't generate any update code by itself...
Both "UpdatingTable" and "KeyFields" properties have proper values

I change field values, call "Post" and nothing...

But when I
- set "SQLUpdate" manual
or
- change main SQL to "select * from inw_wsp_wiek"
everything is ok...

Any clues?...

Regards
P.C.

[AlexP]

Helo,

Please specify the versions of ODAC, IDE, and the scripts for creating database objects.

[pcz]

Delphi XE7 Pro + ODAC 9.6.2.2 Pro

By "internal generation" I mean the code that is auto-generated and sent to database after for example changing field value & executing "Post" method (what could be previeved by using "Debug")

[AlexP]

Please send table creating scripts (inw_wsp_wiek, it_dop_wartosc_param )

[pcz]

Sample code with same result:

Code: Select all

create table INW_WSP_WIEK_PC
(
  WIEK INTEGER NOT NULL
)
/
 
insert into inw_wsp_wiek_PC(wiek)
values(0)
/
insert into inw_wsp_wiek_PC(wiek)
values(-1)
/
insert into inw_wsp_wiek_PC(wiek)
values(-2)
/

Select:

Code: Select all

with ww_min as
(
  select min(wiek) w_min
  from inw_wsp_wiek_pc ww_g
)
select ww.wiek, ww_m.w_min
from inw_wsp_wiek_pc ww
     cross join
     ww_min ww_m

TOraQuery properties (KeyFields value does not matter)

Code: Select all

object OraQuery1: TOraQuery
  UpdatingTable = 'INW_WSP_WIEK_PC'
  KeyFields = 'WIEK'
  SQL.Strings = (
    'with ww_min as'
    '('
    '  select min(wiek) w_min'
    '  from inw_wsp_wiek_pc ww_g'
    ')'
    'select ww.wiek, ww_m.w_min'
    'from inw_wsp_wiek_pc ww'
    '     cross join'
    '     ww_min ww_m')
end

1) create new TDataModule
2) instert TOraSession + TOraQuery; set Debug = True, UpdatingTable, ...
3) Double-click, paste SQL
4) Data editor button
5) Updating row values generates no code
6) Attepmting to insert record generates

Code: Select all

INSERT INTO INW_WSP_WIEK_PC
  ()
VALUES
  ()

=> and causes ORA-00928

[AlexP]

Thank you for the sample, we have reproduced the described problem and will investigate the reason for such behavior.

[pcz]

It looks like problem still happens with ODAC 9.7.23...

[AlexP]

The new verson with this fix will be released tomorrow.

[pcz]

Oh... Thank You for information
I was just testing update

[AlexP]

The new version including these fixes is already available on our website.

[pcz]

[Updated to 9.7.24]

Update & Insert code generation now works fine!

Thanks for fix

[AlexP]

Glad to see that the issue was resolved. If you have any further questions, feel free to contact us.