SQL to synchronize local/offline DB and remote DB?


The goal is to use off line data base with the built in L.O data base engine. When network become available, run sql code to transfer the local data to the remote postgresql server.

Is it possible to write SQL code to work with the 2 data bases ?

Thank you

Cross posted here → How to use off line/cache database and synchronise with online server

@intmail01 if you do this, please note in each posting that the question was also posted in XXXX. Eliminates needless duplication.

And curious as to why you did this almost one full day after an answer was provided here but you never responded to?

It is not duplication like copy/paste. Each person have his manner and flavor to explain because each person is different. I want even to post on other language because I am not very good in english. I go elsewhere because you said that some technics and technologies can not be explained here because the main goal here is L.O. For example what is JDBC and what are technologies and software to mix two database

Duplication is not just copy/paste. Both questions deal with LO and local database and a remote PostgreSQL DB. Neither talked of JDBC which is just a type of connection like your ‘PostgreSQL’ connection or ODBC. A look at the LO documentation explains many of these things.

It was also stated there is too much to consider. You could literally write books on the topic.

Even in different language you should mention cross post as I and others have answered questions in those forums also.


Don’t believe you have thought of how Base works.

You create a Base file. While creating this file you establish what database you are connecting to. If you connect to an embedded database, you are stuck with that connection for that Base File.

If you make the connection other than an embedded database, you can actually change that connection to a different database and even a different type such as MySQL to PostgreSQL. It is not recommended but can be done. Now even though you can do this, you are still only connected to one database.

Although it would need much consideration as to ALL the implications of doing what you ask, you may have one non-embedded database and connection locally for off-line processing and another to connect to the main server.

Now, again, you are typically not going to use SQL to transfer the data. Your solution (depending upon databases used) may be a third party utility to pump data from one table/database to another. But even this will be tricky as to what type of data/relations there may be.

There is too much to consider to discuss here and that further discussion is not even a topic for this forum as it does no concern LO.

As for SQL working with two databases, SQL depends mostly upon table names, field names and functions. If all are the same SQL should work.

As with any answer to any question, if this answers your question please tick the :heavy_check_mark: (upper left area of answer). It helps others to know there was an accepted answer.

And to answer you poorly stated question (clarified more in linked question), with Base and an embedded DB you cannot use SQL to deal with two different databases.

You can connect to a different database and access through SQL by using macros. But this is only going to add to the complexity of what you propose. So the macro needs to read one DB and update another. Have done that and it takes lots of time and thought and considerable macro knowledge to plan out.

Additionally, it you have read the answer you see it mentions there is a connection to a SINGLE database. Therefore you cannot:

INSERT ... TO ... database.table ...

(where ‘database.table’ is other than the current connection) as you state in the other post.