DMLQuery - Why / Can it be disabled?

[Robertio]

Having run our current aplication through Oracle Monitor it seems that on certain queries a seperate query is fired afterwards (a DMLQuery relating to the same dataset)

Having not noticed this before I am wondering why it should be running, and is it possible to stop it from being executed?

Thanks

 

[Marco Kalter]

It depends on what it is doing. For example, if certain TOracleDataSet.OracleDictionary options have been enabled, it may be querying the table defintion or constraints.

------------------
Marco Kalter
Allround Automations

 

[Robertio]

Marco,

We have tried changing all of the properties relating to a TOracleDataset ie:

CommitOnPost = False
EnforceConstraints = False
OracleDictionary (all options) = False
ReadOnly = True
Refresh (all options) = False

Yet still the DMLQuery is being run. This is causing a significant overhead on the application, so we really would like to have a way of disabling / switching off this feature (for our read only queries).

Thanks for any suggestions

 

[Marco Kalter]

What is the SQL that it is executing?

------------------
Marco Kalter
Allround Automations

 

[Robertio]

An example: (although it happens with many different queries, not just this one)

select *
from :ivTable
where rownum 0

where
ivTable is substitute type
ivMaxRows is an integer
ivWordIndex is a word indexed field
ivText is a string

 

[Robertio]

The 2 DML Queries that get fired:

1).
begin
begin
select user, table_name into :table_owner, :table_name
from sys.user_tables where table_name = :name;
exception when no_data_found then
begin
select user, view_name into :table_owner, :table_name
from sys.user_views where view_name = :name;
exception when no_data_found then
begin
select table_owner, table_name into :table_owner, :table_name
from sys.user_synonyms where synonym_name = :name;
exception when no_data_found then
begin
select table_owner, table_name into :table_owner, :table_name
from sys.all_synonyms where owner = 'PUBLIC' and synonym_name = :name;
exception when no_data_found then
:table_owner := null; :table_name := null;
end;
end;
end;
end;
end;

2).
select constraint_name, column_name from sys.all_cons_columns
where owner =

wner and table_name = :table_name
and constraint_name not like 'SYS_C%'
order by constraint_name, position

 

[Marco Kalter]

Are you using a TDataSetProvider?

------------------
Marco Kalter
Allround Automations

 

[Robertio]

No, we are using a TOracleDataset.

 

[Marco Kalter]

Okay, can you send me a demo application that behaves like this?

------------------
Marco Kalter
Allround Automations

 

[Robertio]

Marco, YHM (sent to the support e-mail address)

BTW, after 17:30 I won't be around until Monday.

Cheers

 

[Marco Kalter]

I noticed from your demo project that you are using Delphi 6. As it turns out, the TDataSet.CreateFields function in Delphi 6 calls PSGetKeyFields, which leads to these dictionary queries. This will only happen once per table per session. In Delphi 5 this did not happen (see Delphi's DB.PAS).

There are 2 work arounds for this:

1. Create persistent fields. This way the TDataSet.CreateFields function will not be called.

2. Set TOracleDataSet.UniqueFields. You could even supply a dummy field name, if it will not actually be used.

------------------
Marco Kalter
Allround Automations

 

[Robertio]

Thanks Marco, filling the uniquefields property sorts it out for us.

Just a couple of hundred datasets to go through now