Devart Forums

ODAC 6.20.0.11
I use next sceem: OraSession>OraQuery>DataSetProvider>ClientDataSet>DataSource>DBGrid.

QraQuery.sql = 'SELECT n.cod, n.ver, x.name1, x.name2,'W' TYPE
FROM wi.MATERIAALIT n, wi.nam x, where n.cod=:ItemCode
and n.ver = :itemver and x.cod(+) = n.cod '

QraQuery.Insert='INSERT INTO wi.MATERIAALIT
(cod, ver, cod1, ver1, qtty)
VALUES
(:cod, :ver, :cod1, :ver1, :qtty)'


after

ClientDataSet.Append;
FieldByName('cod').AsString := 'WTR00001';
FieldByName('ver').AsString := '3';
FieldByName('cod1').AsString := 'EER1';
FieldByName('ver1').AsString := '1';
FieldByName('qtty').AsInteger := 1;
FieldByName('TYPE').AsString := 'M';

ClientDataSet.ApplyUpdates(-1);

Error ORA-00904
from DBMonitor i can see:

insert into "WI"."MATERIAALIT "
("cod", "ver", "cod1", "ver1", "qtty", "TYPE")
values
(:1, :2, :3, :4, :5, :6 )

why QraQuery add last field??
How can I disable automatical query change?

SQLInsert value is used only when you insert a record directly in TOraQuery. When you use TDataSetProvider, SQL queries for INSERT, UPDATE and DELETE are generated by the provider, and SQLInsert, SQLUpdate, SQLDelete properties of TOraQuery are not used.

If you want to insert a record using your own INSERT statement, you should assign the statement to SQL property of another TOraQuery or TOraSQL and execute it. Then call Refresh method of TClientDataSet to get new record.

Why BDE works perfect?

Please specify how do you work with BDE. BDE TQuery component does not have SQLInsert property.

use TQuery and UpdateSQL

In ODAC you can use TOraUpdateSQL component. SQL from this component will be used when you apply updates in the client dataset.

In the next ODAC build we'll also add support for SQLInsert, SQLUpdate and SQLDelete properties of TOraQuery when updating through TDataSetProvider.