Difficult to send an example bacause of the mssql db.
I was debuging and testing a lot.
Ill try to explain the issues I found:
Sever side:
If the sts is zero, it happend when you add a new record or you add sts field to an existing table with existing records, the sync allways wants to Insert, the Insert founds a duplicate error and theres no update so sts is never saved then the record is synced every sync.
In the template code, when insert, there are a check for Err to update but it seams not to work.
Workarround: I set all the sts to 1 and now its updating and creating the new sts.
Clien side:
Resumed:
1- everithingafter is not calculated in SQL drivers if you dont have GUID and TS as the first fields in the dct
1.1- everithingafter need to be calculated including the filter
2- All records always send to update if using a filter, pView{prop:filter} does not append the filter but replace it
3- Insert and Delete a records between syncs keeps the sts=0 and dts<>0 then makes the server to try to insert it and you got the same problem indicated in the Server side issue.
Detailed:
1- everithingafter problems: The Debug is saying that the table to sync has no records and this is not true (its sending the correct 2 records to the server), so it always sets everithingafter to -1 and then having or not sts, it allways try to update the same records.
see pic 1
This is the code in NetSync.clw
if Self.ViewTable{PROP:SQLDriver}
stsname = who(fRecord,pStsKey{prop:field,1})
Self.ViewTable{prop:sql} = 'Select Max(' & clip(stsname) & '), Count(*) from ' & clip(self.TableLabel)
message(Self.ViewTable{prop:sql}&' '&fServerTimeStamp&' '&fRecords&' '&errorcode()&' '&error()&' '&fileerror()&' '&fileerrorcode())
Next(self.ViewTable)
fServerTimeStamp &= What(fRecord,1)
fRecords &= What(fRecord,2)
if fRecords = 0 or errorcode()
everythingafter = -1
self.trace('No records in table ' & clip(self.TableLabel) & ' so setting EverythingAfter to -1')
else
everythingafter = fServerTimeStamp
end
Self.ViewTable{prop:sql} = ''
As you can see Ive added a message to see the porp:sql, see pic2, and its:
select max(sts),count(*) from operadores
If I run it in the sql studio I get two values without error, but you are getting an sql error "22003 Numeric Error Out of range"
My table is "Operadores" and
its first field (where you are trying to set max(sts)) is LONG in clarion and INT in Sql while sts is REAL in clarion and a float in SQL
its second field ( count(*) ) is cstring(41) in clarion and varchar(50) in sql
its third field is cstring(11)
So just to test I change the prop:sql to set the two sql values to my varchar field like:
Self.ViewTable{prop:sql} = 'Select 0,Max(' & clip(stsname) & '), Count(*) from ' & clip(self.TableLabel)
Next(self.ViewTable)
fServerTimeStamp &= What(fRecord,2) !2nd field
fRecords &= What(fRecord,3) !3rd field
And now without error EverithingAfter is set OK
May be you need to change the way you do the prop:sql, may be using an internal dummy table?
Or the user are forced to have determinated type of fields in the 1rst and 2nd position of the file.
May be in your tests GUID is allways the 1rst and ts the second and never had this error.
But this may be difficult to do in an old app.
Ok, then I went go to my dct and move my 1srt ID field down letting GUID as the first and ts as the second.
Now with that dct change, the Everithingafter is set OK
2- Always send all records problem:
Looking at the NetSync.clw Ive added lines to see the filter and there the problem, see pic 2
message('2 '&pView{prop:filter})
pView{prop:filter} = self.SetFilter(pView{prop:filter})
message('3 '&pView{prop:filter})
self.SetFilter(pView{prop:filter})
is changing the
OPE:TimeStamp <> OPE:ServerTimeStamp
for
ope:idsucursal =1
which is the client filter, instead of adding it like:
OPE:TimeStamp <> OPE:ServerTimeStamp AND ope:idsucursal =1
Then using ope:idsucursal =1 ALL the ope:idsucursal =1 records are sent to the server anytime it synced.
To test it I set the filter manually in the .clw as OPE:TimeStamp <> OPE:ServerTimeStamp AND ope:idsucursal =1
and the problem go away...
But testing a new issue comes out...
3- Add a new record and Cancel the Add:
Insert and Delete a record between syncs makes the sts=0 and dts<>0 then makes the server to insert it and you got the same problem indicated in the Server side issue.
It seams that the server does not set sts when dts<>0
Hope this helps.