Problem during Send: can't write new records on the remote db

[igghera]

Hi, I'm using the Ionic client provided in this repo.

As a database I'm usng SQL SERVER 2016.

The connection to the server works fine, and I'm able to add a table, initialise the client's database and hit the Sync endpoint to get data from the remote db.

Then I create some record on the client, and when I hit the Send endpoint, it looks like the data is not being sent.
I mean, I do send the XML payload and everything, and get a 204 response, it just doesn't recognise my local changes and thus the remote db doesn't receive the new data. I mean, in the XML payload, there's no trace of my local changes.

In the attempt to fix the issue, I added an auto-increment key to my table in the remote db.
At this point, when I hit the Sync endpoint, I get Error while syncing databases!.
I debugged the issue, and it's actually a column index out of range from SqLite.

I when to the bottom of it, this is what I found: basically, if my table has 4 columns, the Sync endpoint sends me 4 + 1 columns, where the extra one is RowId.

I'm not sure, at this point, whether it's a client or server issue. I'm just stuck and I don't know what to do.

Maybe someone has a clue?

Thanks in advance

[EDIT]
I've tried with the basic HTML5 and the C# client examples.
In both cases I can create new records locally but they are not sent to the server during the Send.
If I manually change the value of the MergeUpdate field to 1, it sends the new rows but they are not saved in the remote DB. I'm using the code provided by the examples, but 3 examples that behave the same makes me think I'm doing something wrong...

[tdziemidowicz]

What version of sync service do you use?

[tdziemidowicz]

If it is possible, send me link to your sync service.

[igghera]

Hi @tdziemidowicz and thanks for your reply.
The version of sync service is the latest one, downloaded from https://docs.sqlite-sync.com.
Here is the link to my service http://testenv.quidinfo.it:8090/SqliteSync_319/API3

[sqlite-sync]

First, try newer version. Current version is 3.2.3. (you can download it from our page). There were known issue in 3.1.x version with parameters generation when master db was MSSQL. Let me know if problem still persists with updated version.

[igghera]

I have updated to version 3.2.3.
There seems to be some problem with the clients, both HTML5 and Ionic.
The HTML5 one is Syncing correctly, getting records from remote database and storing them in local WebSQL, but the first column in the table is rowid (lowercase). So, when it tries to do the Send operation, it doesn't send anything new.

For the Ionic client, I get a 500 error during the Send and I'm still investigating why, because both catalina.out and sync.log don't show any error.

This is what I get from InitializeSubscriber

00000 SQLiteSync.com version: "3.2.3"
00001 MergeDelete drop: "DROP TABLE IF EXISTS MergeDelete;"
00002 MergeDelete create: "CREATE TABLE "MergeDelete" ( "TableId" INTEGER, "RowId" TEXT );"
00003 MergeIdentity drop: "DROP TABLE IF EXISTS MergeIdentity"
00004 MergeIdentity: "CREATE TABLE "MergeIdentity" ([Id] INTEGER ,[TableId] INTEGER NOT NULL ,[SubscriberId] INTEGER NOT NULL ,[Rev] INTEGER NOT NULL  DEFAULT (1) ,[IdentityStart] INTEGER NOT NULL ,[IdentityEnd] INTEGER NOT NULL ,[IdentityCurrent] INTEGER NOT NULL ,[RowId] TEXT NOT NULL ,"MergeUpdate"  INTEGER NOT NULL DEFAULT (0) );"
00005 MergeIdentity_Index: "CREATE INDEX "MergeIdentity_PK_Index" ON "MergeIdentity" (TableId ASC, SubscriberId ASC, IdentityCurrent ASC, Rev ASC)"
00006 TEST_ANDREA drop: "DROP TABLE IF EXISTS TEST_ANDREA"
00007 TEST_ANDREA: "CREATE TABLE "TEST_ANDREA" ([Col1] INTEGER NOT NULL ,[Col2] TEXT ,[Col3] DATETIME ,"MergeUpdate"  INTEGER NOT NULL DEFAULT (0) );"
00008 TEST_ANDREA_MergeUpdate: " CREATE TRIGGER IF NOT EXISTS "trMergeUpdate_TEST_ANDREA"     AFTER UPDATE OF [Col1],[Col2],[Col3]    ON TEST_ANDREA  BEGIN  	UPDATE TEST_ANDREA SET MergeUpdate = 1 WHERE RowId = old.RowId and RowId<>'';  END; "
00009 TEST_ANDREA_MergeDelete: " CREATE TRIGGER IF NOT EXISTS "trMergeDelete_TEST_ANDREA"     AFTER DELETE     ON TEST_ANDREA   BEGIN 	  	INSERT INTO MergeDelete values (3,  old.RowId);  END; "
00010 TEST_ANDREA_MergeUpdate_Index: "CREATE INDEX "TEST_ANDREA_MergeUpdateIndex" on TEST_ANDREA (MergeUpdate ASC);"

There's no trace of RowId. But then, in the HTML5 client, it looks for RowId is null:

'select * from ' + item['tbl_name'] + ' where RowId is null

I'm probably missing something, but it really looks like the query to create the local tables, that I receive from InitializeSubscriber, are wrong as they don't create the RowId column

[tdziemidowicz]

Looking at your script, it's definitely something wrong with table configuration. Do those steps:

1. Remove Table from synchronization https://docs.sqlite-sync.com/#removetable
2. Add table to synchronization again https://docs.sqlite-sync.com/#addtable
3. Check if two columns were created on your table in master database (RowVer and RowId)

[XplaSir]

hi @igghera did you get assistance, facing the same challenge. Been stuck for 5 days now

[igghera]

@XplaSir we ended up using Xamarin because we found a library that was working fine with it. This repo is definitely a good project, but we couldn’t get it to work