Duplicate Primary ID - Unique Index Violation

[AlastairP]

I have been having troubles with duplicate primary ID's
I am using VFP9.
The main application mostly uses cursor adapter classes to access the tables, where records are added and edited.
I have some other remote users on tablets that access the same tables to add records when they need remotely using a cut down "Tablet version" of the application.
The unique ID is an Auto Increment Integer field (Indexed)

The tablets connect to the main server database using a VPN

Typically:

Use MAINTABLE shared
select MAINTABLE
cursorsetprop("Buffering",5,"MAINTABLE")
Append Blank
Replace FIELD with VALUE
=tableupdate(.f.,.t., "MAINTABLE")
use in MAINTABLE

I think the problem arises because the connection back to the main database is perhaps not great sometimes and the users on the server add records at the same time.
I usually open the MAINTABLE at the beginning of the code run and may have to loop through a local table on the tablet doing the various updates.
What can I do to validate the unique ID prior to TABLEUPDATE?
How do I refresh the MAINTABLE on the tablet just before I append a new record?
Or should I approach things a little differently.
I do have a couple of tables that are used for just the Unique ID's for some of the tables, so it would be not to hard to modify the table to have a "ID used" field or similar and get the server to add records maybe a few thousand when needed so the tablets just retrieve the ID, not add the new records to the ID tables.

Any help would be appreciated

 

[AlastairP]

Hi MK,

Thanks for the input. That loop looks like what I need to validate the new record.

Alastair

 

[AlastairP]

I think I may have deleted your post by accident, sorry, here it is again:

Original Post: Posted 27 Feb 19 08:20
Hi,
1) Why do you set "Buffering" to 5 since you only add ONE record? You may want to set it to 3
2) You might insert a loop like below

CODE -->
Use MAINTABLE in 0 shared
Select MAINTABLE
cursorsetprop("Buffering",3,"MAINTABLE")
Append Blank in MAINTABLE
Replace FIELD with VALUE

do While !tableupdate(.f.,.t., "MAINTABLE")
= TableRevert(.T.,"MAINTABLE")
Wait window "*** updating ***" Timeout .5
Append Blank in MAINTABLE
Replace FIELD with VALUE
EndDo

use in MAINTABLE

3) If the ID field is correctly indexed (PRIMARY) it should throw an error when there is a duplicate
4) Why would you like to refresh the MAINTABLE - I understand that it generates the ID and should be up to date. Or do you have more than ONE MAINTABLE?

hth
MK

 

[Olaf Doschke]

Since you say you're using autoinc integer in a DBF and cursoradapter. This is not easy to do, as the cursor the cursoradapter creates for you to modify is not inheriting the autoinc feature and you can't generate IDs in it. So you either create empty records in the DBF and then query that and only update records and never create new records in the cursoradapter cursor, or you pick another key type, like uniqueidentifier.

You can make use of the AfterInsert Event of the Cursoradapter, but it'Äs much easier to work with no sequence type IDs so they can also be generated distributed, i.e. at every client without a roundtrip to the server, also in case of DBF autoinc, where the filesystem and fileserver is taking that role.

With the tablets you have the additional problem of going through WLAN, which is a pain with DBFs, it's even hard with a SQL server backend when notebooks autoconnnect and switch access points and get a new SSID with the same connection handle, SQL SErver might reject requests for such known handles from SSIDs not associated with them,

So one way to lower the problem is to lock them to one specific access point only.

But I guess your problem is already with sequence numbers, what your code does is just the first step (I assume) before you then fetch that data with a cursoradapter. You don't gain a separation anyway, the cursoradapter also will be working on the DBF file, only a real database server separates data access. It's still necessary to have a stable connection not switching routes all the time.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[AlastairP]

Hi Olaf,
Thanks for that.

Alastair