Master Detail with CachedUpdate

lauchuen · Post by **lauchuen** » Thu 12 Apr 2012 20:30

master table (
 id serial primary key,
 name varchar(50)
);

detail table (
 id serial primary key,
 master_id integer,
 name varchar(50)
);

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

AlexP · Post by **AlexP** » Fri 13 Apr 2012 10:26

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

lauchuen · Post by **lauchuen** » Fri 13 Apr 2012 13:17

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!

AlexP · Post by **AlexP** » Fri 13 Apr 2012 14:55

Hello,

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

PgQuery2.SequenceMode := smInsert;

lauchuen · Post by **lauchuen** » Fri 13 Apr 2012 15:08

yes, its work.

but will it cause the sequence number running out quickly ?

lauchuen · Post by **lauchuen** » Fri 13 Apr 2012 20:41

Or actually I should use ClientDatset to handle these problem?

lauchuen · Post by **lauchuen** » Mon 16 Apr 2012 09:42

any advice ?

AlexP · Post by **AlexP** » Wed 18 Apr 2012 08:27

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.

lauchuen · Post by **lauchuen** » Thu 19 Apr 2012 09:29

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

how can i handle this in offline mode ?

AlexP · Post by **AlexP** » Fri 20 Apr 2012 15:13

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

Code: Select all

select setval('my_seq', (select COALESCE(max(id),1) from my_table));

lauchuen · Post by **lauchuen** » Sat 21 Apr 2012 02:26

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 ?

AlexP · Post by **AlexP** » Mon 23 Apr 2012 10:24

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.

lauchuen · Post by **lauchuen** » Mon 23 Apr 2012 16:14

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 ?

AlexP · Post by **AlexP** » Tue 24 Apr 2012 08:56

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

lauchuen · Post by **lauchuen** » Tue 24 Apr 2012 13:55

which SequenceMode should i use then ?