Devart Forums

i use two pgQuery to from a master detail relationship
pgQuery1 connected to master table, SQL = 'SELECT * FROM master', cachedupdates = true, keyfields = id, keysequence = master_id_seq
pgQuery2 connected to detail table, SQL = 'SELECT * FROM detail', cachedUpdate = true, keyfields = id, keqsequense = detail_id_seq, mastersource = dtsMaster, masterfields = id, detailfields = master_id

then
1. append a record to pgQuery1 (master table), post
2. append a record to pgQyery2 (detail table), post
3. pgQuery1.applyupdate

after pgQuery1.applyupdate, all pgQuery2 record disappeared, why? and there're also no value (blank) in master_id field column

i've used other DAC before, i found that there should be a -1 in "id" field column, something wrong?


thanks & regards (urgent!)

jacky

background: delphi xe2, pgDAC latest trial version, postgresql v8.4

Hello,

For correct work of master-detail relation in cachedupdates mode, you should set the LocaMasterDetai property for both DataSets in true (this will allow save the inserted data locally), but, anyway, for inserting data in DB, you should call applyupdate of both DataSets explicitly

yes, already applyupdate for both dataset & even not work using pgConnection.applyupdate

for localMasterDetail property, already set to true but detail record still disappear.

btw, will i get the negative number for index ? (boz id still blank)

please help, thx!

Hello,

For obtaining sequence when inserting data, you should set the SequenceMode property in the smInsert value:

PgQuery2.SequenceMode := smInsert;

yes, its work.

but will it cause the sequence number running out quickly ?

Or actually I should use ClientDatset to handle these problem?

any advice ?

Hello,

Obtaining of the sequences values is possible in two modes:
smPost - the sequence value is generated directly when inserting data into a DB;
smInsert - when calling the Insert[Append] method of DataSet, we explicitly call sequence obtaining.
As you are working in the cachedUpdate mode and using Master-Detail, then the only possible variant is usage of the smInsert mode. Otherwise, when creating a new record in the Master table, it will be impossible to assign records from the Detail table to it, as the value of the key field (sequence) is not generated yet.

as i said previously, it may cause the sequence number running fast. and,

how can i handle this in offline mode ?

Hello,

The maximum sequence value in PostgreSQL is 2^63-1 (9223372036854775808), therefore, even when actively using a sequence, you will hardly manage to reach its maximum value. Moreover, after cancelling data insert, you can return the sequence value to the maximum field value in the following way

think if there're multiple users,
A get ID: 1
B get ID: 2

if B applyupdate first, the MAX(id) still 2, so this can't solve the problem.

and is that possible Master table using smInsert and the Details table us smPost ? any problem will cause ?

Hello,

In the CachedUpdates mode when working with sequences in the smInsert mode - such problems, some sequence values skip, cannot be avoided. For this, you should use triggers or procedures, that, when inserting data, will obtain the sequence value and return it to the application, where this value will take the place of the "virtual" value of the field in the Detail table.
You can use different modes (smPost or smInsert) in Master and Detail DataSet, however, in this case, there will occur "skips" in the Detail sequence.

may i have a conclusion, if i want to use CacheUpdates for Master&Detail application, and the connection are loss (or offline). the pgQuery will fail to create the relationship. correct ?

Hello,

If you have set the LocalMasterDetail property to True for both DataSets, than all data in both master and detail DataSets are stored locally till the ApplyUpdate call. Therefore you can create unlimited number of related records even in offline mode

which SequenceMode should i use then ?