search

sqlite-sync / SQLite-sync.com

AMPLI-SYNC is a framework for synchronizing data between a Sqlite database and an MS SQL/MySQL/Oracle/PostgreSQL database. With this framework your application can work completely offline (Airplane Mode), then perform an automated Bidirectional Synchronization when an internet connection becomes available.
https://ampliapps.com/sqlite-sync/
BSD 3-Clause "New" or "Revised" License
328 stars 82 forks source link

Bad query when initializing a suscriber #28

Closed fazendeiro closed 4 years ago

fazendeiro commented 4 years ago

Hello,

First thank you for that tool that is so useful.

I tried it on versions 3.2.7 and 3.2.15.

I got an issue initializing a suscriber: the returned query contains an error.

In the mergeupdate trigger creation, there is : "CREATE TRIGGER IF NOT EXISTS \"trMergeUpdate_\" AFTER UPDATE OF"

The "OF" create an error when the query is executed on the local database.
I fixed it removing the word after get the response from the server.

sqlite-sync commented 4 years ago

Can you check the content of the table 'MergeTablesToSync' in your master database and see what is inside it? Look for records with the empty table name, or tables you don't have in your database (for example if you deleted a table from master database). After 'trMergeUpdate_' should always be table name (example trMergeUpdate_Table1).

fazendeiro commented 4 years ago

Hello,

Sorry for not having answered. Indeed, I added an empty table name to the synchronization.

I meet this concern again, when the names of the tables are well informed. After several tests I realized that it happened on the tables of joins with only two fields which form a primary key.

I note that the creation of update trigger on a table is performed only on non-key fields. There are none on these tables, which must be the source of the error.

Log line triggering the error:

"00139 PE_ENTRETIEN_ETAT_MergeUpdate": "CREATE TRIGGER IF NOT EXISTS \" trMergeUpdate_PE_ENTRETIEN_ETAT \ "AFTER UPDATE OF ON \" PE_ENTRETIEN_ETAT \ "BEGIN \ tUPDATE \" PE_ENTRETIEN_ETAT = "old Row"; END; ",

The table creation script : "CREATE TABLE [dbo].[PE_ENTRETIEN_ETAT]( [CLE_ENTRETIEN] varchar NOT NULL, [CLE_ETAT] varchar NOT NULL, [RowId] [uniqueidentifier] NULL DEFAULT (newid()), [RowVer] [int] NOT NULL DEFAULT ((1)) ) ON [PRIMARY]"

I am using version 3.2.15 with Microsoft SQL Server.

Do you have a solution?