How to synchronize auto increment keys between different databases in Firedac?

Question

How to synchronize auto increment keys between different databases in Firedac?

Navigation

#1 by (1 votes)

1

I'm looking at how auto-increment fields work in Firedac, using Delphi xE7, and I've been able to put together an example documentation that syncs two master-details tables with the correct key. The doubt arose when I wanted to mirror my Firebird server in SQLite, because I'm going to distribute an application that needs not only to work offline, but also to persist the data when closing the application.

Then in my remote application I will add records in two tables ( Pedidos , Itens ) in the master-detail schema, which need to have a foreign key in common. When connecting to the server at the end of the day I want to send requests and new items, knowing that there may be other users who have also generated SQLite keys that are the same as mine. Using Firedac, how to update them with unreleased keys, without losing the master-detail link? Remembering that the server is Firebird and locally is SQLite ...

sqlite delphi firebird firedac

asked by anonymous 28.12.2016 / 03:06

1 answer

To display the day of the week of any date in the last 1,000 years Change Content within a modal

score 1 · Answer 1

Let's break it down:

First: Firedac is data access so autoincrement is something from the database and not from firedac. In Firebird 3 the auto increment option is available without the use of the sequence / trigger combination.

fieldid bigint generated by default as identity primary key

To enter you have the ID value firebird offers the resource of returning

INSERT INTO tab1 (...) values (...) RETURNING idpk;

Second: In the system that maintains sales automation, the sales order table (SQLLite) of each vendor has its own ID, when sending the request to the main database the combination VendorID / OrderID is used to unequivocally identify the request. In other words, these fields are request attributes on the Firebird side of the force. One solution for synchronizing primary key in different applications that operate asynchronously, regardless of the reason for working this way, is the use of UUID, Universally Unique IDentifier (UUID) The UUID is a 128-bit hexadecimal type, Firebird uses a 16-byte char type for this purpose. Firebird has the functions GEN_UUID () since version 2.1, UUID_TO_CHAR () and CHAR_TO_UUID () since version 2.5, required to use the UUID in your application on the Firebird side.

When to SQLite: See the references below Generating UUID
DDL Example