Uniquery ,dbgrid postgresql 9.1

[Suhaimin]

Hello,
I use TUniquery to display data in TDBgrid, why i can not input data directly via dbgrid ? is it something i forgot to setting?
uniquery.close;
uniquery.sql.clear;
uniquery.sql.add('select * from inventory ');
uniquery.open;


thanks in advance..

[FCS]

Hello,

You probably should set the DbGrid into edit mode.

Reagards
Michal

[Suhaimin]

hello,
i have set to edit mode. after i post (move to next row) that record missing from dbgrid.
i have two uniquery. both uniquery is master detail relation.
master query :
uniquery1.cacheupdates := true;
uniquery1.sql.add('select * from sales');
uniquery1.options.localmasterdetail := true;


detail uniquery :
uniqery2.cacheupdates := true;
uniquery2.sql.add('select * from salesdetail where salesid = :salesid');
uniquery2.options.localmasterdetail := true;
uniquery2.datasource := datasource uniquery1;
uniquery2.masterfield := salesid;

i tried to input data detail via dbgrid.. but after post (move to next record),that record missing from dbgrid. is it something wrong in my code? thanks in advance..

[DemetrionQ]

Hello.


It is more convenient to create master-detail relationship using the TUniQuery.MasterFields and TUniQuery.DetailFields properties. For example:

master query :

Code: Select all

  UniQuery1.CachedUpdates := True;
  UniQuery1.SQL.Clear;
  UniQuery1.SQL.Add('select * from sales');

detail uniquery :

Code: Select all

  UniQuery2.CachedUpdates := True;
  UniQuery2.SQL.Clear;
  UniQuery2.SQL.Add('select * from  salesdetail');
  UniQuery2.Options.LocalMasterDetail := True;
  UniQuery2.MasterSource := datasource_uniquery1;
  UniQuery2.MasterFields := 'salesid';
  UniQuery2.DetailFields := 'salesid';

This method works always, independently on the LocalMasterDetail option value.
Note: there is no need to set TUniQuery.Options.LocalMasterDetail to True for Master Query.

The detailed information about LocalMasterDetail, MasterFields, DetailFields properties can be read in the UniDAC documentation. You can also see a sample how to create Master-Detail relationship in the UniDACDemo\MasterDetail demo.

[Suhaimin]

Hello,

UniQuery2.CachedUpdates := True;
UniQuery2.SQL.Clear;
UniQuery2.SQL.Add('select * from salesdetail');
UniQuery2.Options.LocalMasterDetail := True;
UniQuery2.MasterSource := datasource_uniquery1;
UniQuery2.MasterFields := 'salesid';
UniQuery2.DetailFields := 'salesid';


I dont understand in this statement "Select * from salesdetail " ,is it all data loaded from server in technically? or is it equal "select * fromsalesdetail where salesid = :salesid" ?

thanks a lot..

[DemetrionQ]

Hello.

If you set the TUniQuery.Options.LocalMasterDetail property to True for the detail query, then the detail query will execute the 'select * from salesdetail' query one time, loading all data from the salesdetail table, after that it will filter the displayed dataset basing on the values of the TUniQuery.MasterFields, TUniQuery.DetailFields properties and the current record in the master query. There will be no additional calls to the database.
Note: if the TUniQuery.Options.LocalMasterDetail property is set to True for the detail query when executing the following query:

Code: Select all

select * from  salesdetail where salesid = :salesid

the detail query won't fill in the :salesid parameter automatically basing on the master query. Therefore, in this situation, the detail query must contain the query:

Code: Select all

select * from  salesdetail

and you should use the TUniQuery.MasterFields and TUniQuery.DetailFields properties.

If you set the TUniQuery.Options.LocalMasterDetail property to False for the detail query, then on every move to a records in the master query the detail query will run the SQL query, the text of which consists of the text specified in TUniQuery.SQL.Text and an additional condition based on the values of the TUniQuery.MasterFields and TUniQuery.DetailFields properties. For example, code:

Code: Select all

  UniQuery2.SQL.Clear;
  UniQuery2.SQL.Add('select * from  salesdetail');
  UniQuery2.Options.LocalMasterDetail := False;
  UniQuery2.Mastersource := datasource_uniquery1;
  UniQuery2.MasterFields := 'salesid';
  UniQuery2.DetailFields := 'salesid';
  UniQuery2.Open;

will sent the following query to the database:

Code: Select all

  select * from  salesdetail where salesid = :salesid

and the :salesid parameter value will be automatically taken from the master query.

And the next code:

Code: Select all

  UniQuery2.SQL.Clear;
  UniQuery2.SQL.Add('select * from  salesdetail where salesid = :salesid');
  UniQuery2.Options.LocalMasterDetail := False;
  UniQuery2.Mastersource := datasource_uniquery1;
  UniQuery2.MasterFields := 'salesid';
  UniQuery2.DetailFields := 'salesid';
  UniQuery2.Open;

will sent the following query to the database:

Code: Select all

  select * from  salesdetail where (salesid = :salesid) AND salesid = :salesid 

I.e., if the TUniQuery.MasterFields and TUniQuery.DetailFields properties are set, then there is no need to manually add the 'where salesid = :salesid' condition to the SQL query text.

[Suhaimin]

hello Master,


so if i have 500.000 records in detail it is not effective to do that in detail query.
actually I just want to insert data via TDBGrid. Do you have any idea to insert data via DBGrid without loading all data detail from server to client? because everytime insert at master record ,i fill salesid to -1 so query master always blank.

master query :
query1.close;
query1.sql.clear;
query1.sql.add('select * from sales where salesid = :varsalesid);
query1.parambyname(varsalesid').value := -1;
query1.open;

so what should I write statement SQL in detail query without loading all data from server to client just entry data via DBgrid ?
thanks a lot

[DemetrionQ]

Hello.

1) Detail query always allows inserting records, independently on the number of records it returns.
2) In order for detail query to load from a DB only records that meet the condition, generated from master-detail relationship, set the TUniQuery.Options.LocalMasterDetail property to False for detail query.
3) If master query doesn't return any records, then detail query won't return any records as well (even if TUniQuery.SQL.Text = 'select * from salesdetail'), since the condition for detail query records, generated from master-detail relationship, will look like the following: salesid = NULL - this is always FALSE. In this situation, you can insert a new record to detail query via TDBGrid. And the record will really be inserted to a DB table. But since the condition for detail query records is always FALSE, then just after adding a new record to detail query the record will disappear from detail query.

[Suhaimin]

Hello,

if I set query detail TUniQuery.Options.LocalMasterDetail to false, after entry data via dbgrid then move to next row in Dbgrid ( after Post),It is mean that record direct post to DB (server) ? actually I want to insert data via dbgrid but still in Detail Query Localmasterdetail = true, and I dont want detail query Loading all data from server to client, is it still possible to do this criteria?

Master query design time :
Query1.cacheupdates := true;
Query1.sql.add('select * from sales where salesid = :parameter');

Detail query design time :
Query2.cacheupdates := true;
Query2.options.localmasterdetail := true;
Query2.sql.tex := ' select * from salesdetail';
query2.detailfield := 'salesid;
query2.masterfield := 'salesid';
query2.mastersource := source query1;



Onform show :
query1.close;
query1.parambyname('parameter').value := -1;
query1.open; // return null

in event Onnewrecord of Query1 :
Query1salesid.value := 1;
Query1.post;
query2.open // detail query Is it Loading all data from server to client ???


Please advice ....thanks a lot

Note :
main purpose is entry data via dbgrid in query detail still used localmasterdetail = true and dont want loading all data detail..

[Suhaimin]

Hello,

if detail query i set localmasterdetail= false, then is it possible if query2.post (detail) data still in local (client) without direct save to DB (server)?? thanks

[Suhaimin]

Hello Master,
any idea for my problem ? thanks a lot

[Suhaimin]

Hello,

is it possible to used cache memory while uniquery.cacheupdates = false?i mean while uniquery.post data still in cache memory even if uniquery.cacheupdates = false. after uniconnection.commit data will be saved to DB(server). thanks a lot

[DemetrionQ]

Hello.

If the TUniQuery.Options.LocalMasterDetail option is set to True, the the detail query loads ALL records from the table, independently on the status of the master query.

If the TUniQuery.CachedUpdates property us set to True, when calling the Post method (or when moving to another record after data editing in TDBGrid), data modifications will be stored to the local cache. The modifications will be sent to the DB only after calling the TUniQuery.ApplyUpdates method.
If the TUniQuery.CachedUpdates property is set to False - data modifications will be sent to the DB immediately.
The detailed information about the CachedUpdates property and the ApplyUpdates method can be found in the UniDAC documentation.

[Suhaimin]

Hello,

example DB :

Sales DB have salesid =1 to salesid = 500.000 // 500000 records
salesdetail DB have salesid = 1 to 500.000 // 500000 records


master query :
query1.close;
query1.sql.clear;
query1.sql.add('select * from sales where salesid = :parameter');
query1.parambyname('parameter').value := -1;
query1.open; // return null
query1.insert;
query1.fieldbyname('salesid').value := 500001;

query2.close;
query2.cacheupdates := true;
query2.sql.clear;
query2.sql.add('select * from salesdetail);
query2.masterdetail := salesid;
query2.mastersource := mastersource master query;
query2.localmasterdetail=true;
query2.open ;


while query2.open execute, is it loading all data (500000 record to cache memory ? )
sorry i still dont understand how it work properly.. thanks a lot

[Suhaimin]

Hello,


Is there property of TUniquery that contains loading all data or not ? thanks a lot