Android Question SQLite Timestamp problem

[makis_best]

Hi.

I have one MSsql database and I transfer through JRDC2 some records to my B4A application who use sqlite database.
Some records of MSsql have timestamp fields that I need to sqlite.
On MSsql server one field called TS is declared as TIMESTAMP.
The format it has look like 0000000000111966.

Now on my sqlite database the same field is declared as TEXT.
But there one the record transfer the format of TS
look like totally different. Look like BLOB.

Why that happening?

 

[Erel]

What is the output of Manager.PrintTable(Result) ?

 

[makis_best]

Hi @Erel.
The export of PrintTable is like [B@55bca29

 

[Erel]

Please post the full output of calling Manager.PrintTable.

 

[makis_best]

Please post the full output of calling Manager.PrintTable.

OK @Erel

Tag: null, Columns: 12, Rows: 289
AA Code Description AlternativeDescription Inactive Address City PostalCode TelephoneNumber1 TelephoneNumber2 PersonOfCommunication TS
888 1101 Α ΑΘΗΝΩΝ null 0 Αναξαγόρα 6-8 ΑΘΗΝΑ 101 10 210- 52.72.775 210- 52.72.757 Αποστολοπούλου Αδαμ. [B@42c3020
888 1104 Δ ΑΘΗΝΩΝ (Δ,Η) null 0 Kωλέτη 14A ΑΘΗΝΑ 100 10 210- 38.35.638 null Βλάχου Μαγιούλα [B@163a6dd9
888 1105 Ε ΑΘΗΝΩΝ (Ε,Θ) null 0 Αριστογείτονος 19 ΑΘΗΝΑ 176 71 210- 95.78.710 210- 95.66.817 Αθανασάκη Κων/να [B@132f139e
888 1106 ΑΘΗΝΩΝ ΣΤ (ΣΤ, Ζ)(ΑΘΗΝΩΝ ΣΤ,Ζ,ΙΣΤ) null 0 Aγ. Eλεούσης 10 ΑΘΗΝΑ 105 54 210- 32.50.630 210- 32.15.422 Πλιώτας Γεώργιος [B@d39ae7f
888 1110 Ι ΑΘΗΝΩΝ null 0 Γʼ Σεπτεμβρίου 33 ΑΘΗΝΑ 104 32 210- 52.32.500 210- 52.37.447 Σαριδάκη Αλεξάνδρα [B@29f42e4c
888 1111 ΙΑ ΑΘΗΝΩΝ null 0 Aλκιβιάδου 1 ΑΘΗΝΑ 104 39 210- 88.33.677 null Φλώρος Νικόλαος [B@1ddb0e95
888 1112 ΙΒ΄ ΑΘΗΝΩΝ null 0 Γρ. Aυξεντίου 30-32 ΑΘΗΝΑ 157 71 210- 77.02.653 null Κασιάρα Αριάδνη [B@10f4cbaa
888 1113 ΑΘΗΝΩΝ ΙΓ (ΑΘΗΝΩΝ ΙΓ,ΙΘ,ΚΓ) null 0 Πατησίων 125 ΑΘΗΝΑ 112 51 210- 82.10.218 null Κηπαρίδου Μαρά [B@3dc4b39b
888 1114 ΙΔ ΑΘΗΝΩΝ null 0 Kουρτίδου 184 ΑΘΗΝΑ 111 43 210- 25.27.736 210- 25.25.394 Μπαγατέλα Ειρήνη [B@5be338
888 1115 ΑΘΗΝΩΝ Α (Α,Β,Γ)(ΑΘΗΝΩΝ ΙΕ,Α,Β,Γ,ΚΒ) null 0 Αναξαγόρα 6-8 ΑΘΗΝΑ 101 10 210- 52.72.775 210- 52.72.757 Αποστολοπούλου Αδαμ. [B@1baaff11
888 1116 ΑΘΗΝΩΝ ΣΤ (ΣΤ,Ζ)(ΑΘΗΝΩΝ ΙΣΤ,ΣΤ,Ζ) null 0 Aγ. Eλεούσης 10 ΑΘΗΝΑ 105 54 210- 32.50.630 210- 32.15.422 Πλιώτας Γεώργιος [B@39abf876
888 1117 ΙΖ ΑΘΗΝΩΝ null 0 Δαμάρεως 175 ΑΘΗΝΑ 116 32 210- 75.75.504 null Νεραντζάκη Σοφία [B@1530ee77
888 1118 ΜΕΓΑΛΩΝ ΕΠΙΧΕΙΡΗΣΕΩΝ null 0 Kηφισίας 32 MΑΡΟΥΣΙ 151 25 _ null _ [B@1587fae4
888 1124 ΑΘΗΝΩΝ ΙΖ (ΑΘΗΝΩΝ ΙΗ,ΙΖ) null 0 Δαμάρεως 175 ΑΘΗΝΑ 116 32 210- 75.75.504 null Νεραντζάκη Σοφία [B@9af7b4d
888 1125 ΚΑΤΟΙΚΩΝ ΕΞΩΤΕΡΙΚΟΥ null 0 Μετσόβου 4 ΑΘΗΝΑ 106 82 210- 82.04.626 210- 82.04.625 Τζωρτζόπουλος Ευστ. [B@1d72602
888 1126 ΑΘΗΝΩΝ ΙΓ (ΑΘΗΝΩΝ ΙΘ,ΙΓ,ΚΓ) null 0 Πατησίων 125 ΑΘΗΝΑ 112 51 210- 82.10.218 null Κηπαρίδου Μαρά [B@3dbbfb13
888 1129 ΑΓΙΟΥ ΔΗΜΗΤΡΙΟΥ null 0 Aργοστολίου 39 AΓ. ΔΗΜΗΤΡΙΟΣ 173 42 210- 98.34.958 null Ματσιας Σπ. [B@133be150
888 1130 ΚΑΛΛΙΘΕΑΣ null 0 Eλ. Bενιζέλου 195 ΚΑΛΛΙΘΕΑ 176 73 210- 95.62.108 null Κοντού Μαρκέλλα [B@3057f49
888 1131 ΝΕΑΣ ΙΩΝΙΑΣ null 0 Eλ. Bενιζέλου 1 Ν.ΙΩΝΙΑ 142 31 210- 27.94.525 null Τσιτζόγλου Ιουλία [B@3195a04e
888 1132 ΝΕΑΣ ΣΜΥΡΝΗΣ null 0 Λ. Συγγρού 251 Ν.ΣΜΥΡΝΗ 171 22 210- 94.08.809 null Μπίρμπα Βασιλική [B@3de1356f
888 1133 ΠΑΛΑΙΟΥ ΦΑΛΗΡΟΥ null 0 Aλκυόνης 19 ΠΑΛ. ΦΑΛΗΡΟ 175 61 210- 98.22.549 null Χατζηαργυροπούλου Β. [B@3a05c27c
888 1134 ΧΑΛΑΝΔΡΙΟΥ null 0 Aισχύλου 27 ΧΑΛΑΝΔΡΙ 152 34 210- 68.49.435 210- 68.50.140 Γελαδάρη Δήμητρα [B@1d9dc705
888 1135 ΑΜΑΡΟΥΣΙΟΥ null 0 Πλαταιών 57 & Aγ. Kων/νου MΑΡΟΥΣΙ 151 24 210- 61.22.335 210- 80.56.010 Μενδρινός Κλήμης [B@21af735a
888 1136 ΑΓΙΩΝ ΑΝΑΡΓΥΡΩΝ null 0 Πριγκ.Ολγας 3 & Λ.Δημοκρ. ΑΓ. ΑΝΑΡΓΥΡΟΙ 135 10 210- 26.48.637 210- 26.48.644 Κοντου Γεωργία [B@1e75b98b
888 1137 ΑΙΓΑΛΕΩ null 0 Κηφισού 44 & Αλατσάτων 93 AΙΓΑΛΕΩ 122 41 210- 56.16.866 210- 56.16.867 Τελώνη Αθηνά [B@232a8a68
888 1138 Α΄ ΠΕΡΙΣΤΕΡΙΟΥ null 0 ΤζωνΚένεντυ 146&Αιγαίου 31 ΠΕΡΙΣΤΕΡΙ 121 36 210- 57.76.500 210- 57.11.397 Σερπάνος Θεόδ. [B@23b8ce81
888 1139 ΓΛΥΦΑΔΑΣ null 0 Γούναρη 227 ΓΛΥΦΑΔΑ 166 74 210- 91.18.853 210- 91.18.855 Μακρή Σπυριδούλα [B@3d616b26
888 1140 Κ ΑΘΗΝΩΝ null 0 Λ. Γαλατσίου & Παλαμά 2-4 ΑΘΗΝΑ 111 10 210- 21.11.108 210- 21.11.185 Μπαρδή Αννα [B@16846367
888 1141 ΨΥΧΙΚΟΥ (ΚΑ ΑΘΗΝΩΝ,ΨΥΧΙΚΟΥ) null 0 Κηφισίας 160 ΨΥΧΙΚΟ 154 10 210- 67.52.259 null Βλασσοπούλου Αικατ. [B@3551e514
888 1142 ΑΘΗΝΩΝ Α (Α,Β,Γ)(ΑΘΗΝΩΝ ΚΒ,Α,Β,Γ,ΙΕ) null 0 Αναξαγόρα 6-8 ΑΘΗΝΑ 101 10 210- 52.72.775 210- 52.72.757 Αποστολοπούλου Αδαμ. [B@2922d1bd
888 1143 ΑΘΗΝΩΝ ΙΓ (ΑΘΗΝΩΝ ΚΓ,ΙΓ,ΙΘ) null 0 Πατησίων 125 ΑΘΗΝΑ 112 51 210- 82.10.218 null Κηπαρίδου Μαρά [B@14e913b2
888 1144 ΑΓΙΟΥ ΔΗΜΗΤΡΙΟΥ (ΔΑΦΝΗΣ,ΑΓΙΟΥ ΔΗΜΗΤΡΙΟΥ) null 0 Aργοστολίου 39 AΓ. ΔΗΜΗΤΡΙΟΣ 173 42 210- 98.34.958 null Ματσιας Σπ. [B@18e9cf03
888 1145 Ν.ΗΡΑΚΛΕΙΟΥ null 0 Λ.Ηρακλείου 420 Ν.ΗΡΑΚΛΕΙΟ 141 22 210- 28.27.303 null Κάππα Ιωάννα [B@37f23e80
888 1151 ΧΟΛΑΡΓΟΥ null 0 Δ. Γούναρη 38A ΑΓ. ΠΑΡΑΣΚΕΥΗ 153 10 210- 63.95.678 null Μπασετας Γεώργιος [B@382fccb9
888 1152 ΒΥΡΩΝΟΣ null 0 Aδριανουπόλεως 45 ΚΑΙΣΑΡΙΑΝΗ 161 21 210- 76.42.500 210- 76.58.240 Τσεκούρα Σταματία [B@cd0b8fe
888 1153 ΚΗΦΙΣΙΑΣ null 0 Αχαρνών 43 ΚΗΦΙΣΙΑ 145 61 210- 80.84.363 210- 80.11.374 Μπαστούνης Κων/νος [B@1fb0585f
888 1154 ΑΓΙΩΝ ΑΝΑΡΓΥΡΩΝ (ΙΛΙΟΥ,ΑΓΙΩΝ ΑΝΑΡΓΥΡΩΝ,ΑΝΩ ΛΙΟΣΙΩΝ) null 0 Πριγκ.Ολγας 3 & Λ.Δημοκρ. ΑΓ. ΑΝΑΡΓΥΡΟΙ 135 10 210- 26.48.637 210- 26.48.644 Κοντου Γεωργία [B@317cc2ac
888 1155 Ν.ΦΙΛΑΔΕΛΦΕΙΑΣ null 0 Λ. Δεκελείας 231 Ν.ΦΙΛΑΔΕΛΦΕΙΑ 143 42 210- 27.19.804 210- 27.71.862 Μουστακάτου Αικατ. [B@12777b75

That's it.

 

[asales]

Maybe because this:
timestamp is the synonym for the rowversion data type and is subject to the behavior of data type synonyms. In DDL statements, use rowversion instead of timestamp wherever possible.

The Transact-SQL timestamp data type is different from the timestamp data type defined in the ISO standard.

Documentation:

rowversion (Transact-SQL) - SQL Server

rowversion (Transact-SQL)

docs.microsoft.com

 

[Albert Kallal]

Maybe because this:
timestamp is the synonym for the rowversion data type and is subject to the behavior of data type synonyms. In DDL statements, use rowversion instead of timestamp wherever possible.

The Transact-SQL timestamp data type is different from the timestamp data type defined in the ISO standard.

Documentation:

rowversion (Transact-SQL) - SQL Server

rowversion (Transact-SQL)

docs.microsoft.com

+1 - liked!

To the OP:

I would quite much just ignore the columns. And the term "timestamp" is one of those epic bad data columns. To be fair, the term row version is starting to be used, but in the SQL table designers - it still uses and has the name timestamp. It should be noted that this column has ZERO to do with time, and YOUR code should NEVER attempt to set nor modify this column. Turns out that .net and especially MS-Access will use this column to detect if the row has been changed. thus under all cases you the developer will NEVER modify or mess with this column, nor should your code care about this value. A datetime column is however a different matter.

I am close to coding a sqlite to sql server sync routine. I not written the code, and at the napkin stage. But, right now, I may well copy the columns to android/sqlite, but for update code, I will "ignore" such columns, or I going to leave them out of the sqlite schema.

However, since the code I plan to write will be "generic" then on the sqlite side, I'll just give the data type a data type name that can be ignored. So me also will have to solve this issue soon. So far plans are to ignore the column and not bother. You can/could create a few sql server views that don't include this column if in fact your going to write code that is generic and will operate on the fields/column collection (but much caution since your B4A code cannot ask sql server which column in a view is the PK - but you can (and I often do) with B4A "ask" sql server to give me the PK column of the given table.

.As noted, even if you use a view and don't update/touch the rowversion column (aka: timestamp column), it is NOT to be touched or updated by you, or even any t-sql code. It is automatic updated and maintained by sql server to allow software to detect if the row has become dirty or changed by another process. It is of no concern or value to you.

Best bet? Don't pull or even add/have these columns in the sqlite database. The values will change if you update the sql server table.

Having written the above?? - thinking out loud? I going to ignore the columns, and not even include them in the sql lite database. Given that your general or even specific code will NEVER touch or update such columns? Well, then might as well ignore them 100%

Regards,
Albert D. Kallal
Edmonton, Alberta, Canada

 

[Erel]

The field returned as a BLOB. You can try to find out the format using something like:

Dim b() As Byte = row(DBResult.Columns.Get("TS"))
Dim bc As ByteConverter
Log(bc.HexFromBytes(b))

 

[makis_best]

+1 - liked!

To the OP:

I would quite much just ignore the columns. And the term "timestamp" is one of those epic bad data columns. To be fair, the term row version is starting to be used, but in the SQL table designers - it still uses and has the name timestamp. It should be noted that this column has ZERO to do with time, and YOUR code should NEVER attempt to set nor modify this column. Turns out that .net and especially MS-Access will use this column to detect if the row has been changed. thus under all cases you the developer will NEVER modify or mess with this column, nor should your code care about this value. A datetime column is however a different matter.

I am close to coding a sqlite to sql server sync routine. I not written the code, and at the napkin stage. But, right now, I may well copy the columns to android/sqlite, but for update code, I will "ignore" such columns, or I going to leave them out of the sqlite schema.

However, since the code I plan to write will be "generic" then on the sqlite side, I'll just give the data type a data type name that can be ignored. So me also will have to solve this issue soon. So far plans are to ignore the column and not bother. You can/could create a few sql server views that don't include this column if in fact your going to write code that is generic and will operate on the fields/column collection (but much caution since your B4A code cannot ask sql server which column in a view is the PK - but you can (and I often do) with B4A "ask" sql server to give me the PK column of the given table.

.As noted, even if you use a view and don't update/touch the rowversion column (aka: timestamp column), it is NOT to be touched or updated by you, or even any t-sql code. It is automatic updated and maintained by sql server to allow software to detect if the row has become dirty or changed by another process. It is of no concern or value to you.

Best bet? Don't pull or even add/have these columns in the sqlite database. The values will change if you update the sql server table.

Having written the above?? - thinking out loud? I going to ignore the columns, and not even include them in the sql lite database. Given that your general or even specific code will NEVER touch or update such columns? Well, then might as well ignore them 100%

Regards,
Albert D. Kallal
Edmonton, Alberta, Canada

The point is as you say @Albert Kallal , I try to make a sync routine but I need to know when a record change.
The rownumber is the only field I have to know if something change.
So... I think there are no other way except to check all that fields.
As you say... you create one sync routine also....
It will be interesting if we can exchange ideas.

 

[Albert Kallal]

The point is as you say @Albert Kallal , I try to make a sync routine but I need to know when a record change.

The rownumber is the only field I have to know if something change.

Golly? You have a VERY good point, or shall I say idea!!!

One could use that column to detect if sql server has changed the data. So, I am "somewhat" backpedaling here!

However, we still would need a change detection column on the client (android) side. So, one could for example shove a actual date/time for updates to a record on the android/sqlite side.

However, lets assume we do/did/think that using the change detection column? Well, this would force a whole table compare. That's off my table design (pun intended).

right now, on my napkin design?

We have two distinct issues:

First up, change detection. A whole table (and row by row) compare is too slow. Not going down this road.

So, we adopt a standard of last update on each table (yes, that means a extra column for all tables). This column will be updated automatic on sql server side, and on android side it will be part of any update routine, or a setting in the sqlite table (not 100% sure which option - but a time/date update column is possible in sqlite - just not sure if it automatic).

So, now we ONLY need the last sync time. We ONLY take records updated AFTER this sync time.

Second issue:

PK generation. Call me a surrogate key crazy - but that's my design's. That means all my tables have a auto number PK (and I use "ID" for that PK).

So, how to handle LOCAL additions of data, and not have PK collisions?

I could/would consider a GUID PK - but I have too much existing designs and code written around the assuming that all tables have a autonumer PK.

So, there are several VERY interesting approaches to this problem MS Access + SharePoint uses a VERY cool trick. When off-line, then PK autonumbers start a -1, then -2, then -3 etc. In fact code to check WHEN off line simply has to check for negative PK's!!! (MS Access does this automatic at engine level). When you sync, then the negative keys are sent up to server, and the actual new/real PK's are returned during a sync process.

(and yes, this means I WILL use the relational PK/FK update abilities of sqlite on the android side. )


I experimented with sqlite and starting out with negative PK's (for adding records). Unfortunately, they STILL increment forward. If ANYONE is aware to tricking or setting sqlite to start at -1, then -2 etc. Please jump in - I'll give you a big group hug!!!!


So, you can start at say -100,000, and it increments forward (-99,000 etc.). This approach ALSO has the added benefit of telling that a PK exchange during sync has to occur! (so you kill two birds with one stone!!!).

However if you edit EXISTING data, then the next autonumber is re-set on sqlite/android side. So no go!!!!

Our sync code then can easy deal with:

Added records - exchange with sql server - pull back sql server assigned PK values.

Then you sync updated records (client side).

Then you sync server updated records.

Because my experimenting with sqlite showed "limited" control over the PK increments? Well, then right now the solution is to start all tables PK auto increment values on the sqlite side at 100,000. This will work, and during a sync, we get the PK values returned from sql server - and they are thus below 100,000. Now these days, tables with MORE then 100,000 rows is not all that rare. But hey, baby steps.

And editing data and PK's below 100,000 does not re-set the autonumber increment.

So "V1" of my sync design will assume:

autonumber PK sql server side.

sqlite (android side) - autonumber PK - all PK's start at 100,000 and auto increment forward.

all tables have a last updated column - this is a date + time column. This will allow us to sync ONLY changed records, and thus I want this to work if we have 5,000 rows, or 40,000 rows in a table.

So far, the above design means the least amount of changes to existing tables and software. If someone has a better sync design, I am all ears.

The negative PK autonumber trick is a really nice idea. but that pesky sqlite re-set during edits has kaboshed this design.

So we could consider and use the sql row version column to detect changes to data sql server side, but it would not be time based, and thus a full table scan and compare from both ends would be required. Full table scans are off the table for my design - not considering such a huge bandwidth requirement design unless all other approaches were to fail.

Have to run, but I never really considered the idea of using row version - a VERY neat-o idea on your part. For smaller tables, this is a good idea. But, I am as noted shying away from designs that require touching of all rows for a sync process. I going with last updated as a date/time value.

The other possible? And one that a few commercial android vendors offer? They adopted the .net sync framework, and built a android compatible sync client.

This I have looked at. And it would be a way to build a android to sql server sync system, and also a android to ms-access sync client - two birds with one stone. I don't know how the underlying architecture for the .net sync frame is setup. From what I can tell the .net framework uses an additional database to track the sync process.

but, for now? I going with a simple last sync time, and the assume that each record updated will have a time "later" then the sync time.
And yes, a exchange of the correct new PK value(s) that sql server assigns will be pulled down to the android side.
R

Albert

 

[makis_best]

From my part simple use two different sql commands and exclude everything that match.
Then I simple transfer the records of the one database to the other.
Before I try to do the same with records sizes but it can't work with sqlite so I skip.
The idea to exclude everything match i know it is little bit slow but for the 8000 records I have to manage
time to finish every table is very good as much quick I can say.

 

[lip]

I have a mechanism that seems to work. I may be missing the point as it looks like you're trying to do something a bit more generic at a lower level, but in case it helps:

I sync SQLite on thousands of Tablets and Raspberry Pi's with each other, via a single SQL Server instance via HTTP. I have a LastEdit column in each SQLite table. For upload the trick is to run the EditedRows query and send the results (in a JSON), along with the DateTime.now value at the point the query was run. The server does the update and acknowledges Success by returning the DateTime.Now value that it received. The Tablet store this in a Parameters table as the "LastSynced" value, which is used to compare with LastEdit next time you run Sync. This avoids any issues with Tablet and Server time being out of sync. There are a few other elements to catch if things have gone wrong, like including a RowCount and if that doesn't match then doing a PK Comparison and Delete/Fetch any Extra/Missing records as appropriate. For downloads, the Tablet stores the Servers clock time in the same way. The table structures are defined with a Map of ColumName -> Type, plus a List if PKColumns. This helps to parameterise the Subs with just a TableName.

 

[makis_best]

I have a mechanism that seems to work. I may be missing the point as it looks like you're trying to do something a bit more generic at a lower level, but in case it helps:

I sync SQLite on thousands of Tablets and Raspberry Pi's with each other, via a single SQL Server instance via HTTP. I have a LastEdit column in each SQLite table. For upload the trick is to run the EditedRows query and send the results (in a JSON), along with the DateTime.now value at the point the query was run. The server does the update and acknowledges Success by returning the DateTime.Now value that it received. The Tablet store this in a Parameters table as the "LastSynced" value, which is used to compare with LastEdit next time you run Sync. This avoids any issues with Tablet and Server time being out of sync. There are a few other elements to catch if things have gone wrong, like including a RowCount and if that doesn't match then doing a PK Comparison and Delete/Fetch any Extra/Missing records as appropriate. For downloads, the Tablet stores the Servers clock time in the same way. The table structures are defined with a Map of ColumName -> Type, plus a List if PKColumns. This helps to parameterise the Subs with just a TableName.

And about speed? All that continuous checking doesn't make the code slow?

 

[Albert Kallal]

I have a LastEdit column in each SQLite table.

Very nice. Yes, my suggested design is all tables will have a datetime (LastEdit) type of column added.

So, client - > sql server - all new updates.
sql -> client - all new updates.

Deletes - not yet designed.

So I am going both ways.
(and its not 100% clear if your sync does?).

So, I am curious as to your handling of the PK values?

I am looking for a design in which I don't add/have additional columns (except for LastEdit - that I am open to and is quite much a given).

I COULD consider adding a location (or device) column, and combine the PK autonumber with the location. This would eliminate the key collusions, and thus remove and eliminate any need to "pull/get" new PK ID's from the server.

But existing software (server side assumes and uses PK values).

So, any android side (new rows only) sent to SQL server would require the PK to be sent back (the new row gets a SQL server assigned PK - I'm going to get that back to the android side).

The reason for the above is EXISTING software is using the tables I need/want to sync. And they assume a autonumber PK.

So, I can't have the PK values Android side collide with server side PK values WHILE off line.

And while I will have the LastEdit column, I can also detect by the fact that the PK id is > 100,000 (or 1 million) that this is not only a record to sync, but a NEW row - that part will thus use the PK exchange with SQL server.

Sync existing data - via LastEdit. (both ways, quite easy). And this should also pull down new records.

Sync new rows - (and exchange/get/grab the new PK that SQL server assigned).

Hence, I plan to set the auto increments on the Android side to start say at 1+ million.

If you have any suggestions on dealing with (or how you did) with the auto number PK issues? Much open to suggests.

Right now? My design is considering pulling the PK keys back from SQL server to the android client during a sync.

I have considered creating a PK based on autonumber ID + deviceID (this can work well, but ONLY if you have control of the already existing server side software that currently is using the SQL server tables). If I did not have this requirement (existing software), then the sync would be far less work.

The problem is that with SQL server, and also with sqlite? If you add numbers greater then the existing autonumber, then it jumps to the next highest

So, I am looking for "generic", but the nuts and bolts will run using a LastEdited column - very similar to what you have.

I am just cooking up a solution for the auto number problem.

Thank you for sharing - +1 +Liked for your post!!!

Regards,
Albert D. Kallal
Edmonton, Alberta, Canada

 

[makis_best]

I use two columns for PK, one Pk internal that the application use and one second column I call like PK external that is the PK who sync with the server side.
The Pk internal sync also with the server so I can understand both side witch record belong to witch.
If one of the two fields are empty I can understand need to update from the one side or the other.

About delete same logic I just fill them with -1 for server -2 for client but used for hiding records.
So if in my server field I update the record with -1 it means that is deleted... I don't actually delete the record... it is just hidden
Same thing for client side. If I fill the field of a record with -3 then the record truly deleted.

 

[Albert Kallal]

See, you ask and fish for some ideas and that idea and suggestion pops up!

Golly! - I really like this idea!

I can just add a PK/FK to all tables for the android side. Quite sure this idea can and will work rather well.
And on a initial sync well, I just use/take the sql ones if blank.

I am going to do some more napkin research here but as initial "candy" for my brain?

A rather great idea.

I have looked at quite a few solutions, and not seen this idea suggested. Even the archeticture of replication (say SQL server) was also on my list of looking.

And best so far is the simplicity of how MS Access to SharePoint syncs occur. this "simple" feature however requires the ability to one to "tell" at the db engine level to create negative PK values when off line. This I would adopt if sqlite could be made to cooperate.

However, I can rather easy live with a few extra columns in each table.
So, in a typical table (say a child table). I will have to add:
Android side PK
Android side FK
LastUpdated (this was a given anyway).

So, only two columns for a child table, and one for tables without a FK.

So far I like this idea and suggestion! I do think that this will effect the "least" amount existing software.

And it will as you note actually simplify the android side code. Note that I do plan to use + allow cascade deletes on the android side. If this was just 1-2 tables, then I really could just cook up something based 100% on the LastUdated column. And if this was one-way from Android to SQL server - again, not at all hard.

Anyway, I'll now go do some cooking, but this indeed is one of those why did I not think of that idea!!!

This is a acceptable amount of columns, and better not having to mess with PK ranges and what not very much seals the deal here.

I thank you so oh kindly for this idea.

I don't want to short change this idea, but it is a rather simple idea, and those tend to be the best!

So far, you have made my day!!!

I had a great week working with great people - and some ideas and solutions we cooked up to some UI issues and problems during the week made me realize how much I enjoy the software industry. It been a great ideas week!

And now Friday, and this plate of B4A food gets served. There is a smile on this end of the keyboard!

Again, thank you, and thanks to the B4A community - a marvelous bunch you all are!


Regards,
Albert D. Kallal
Edmonton, Alberta, Canada

 

[makis_best]

I am happy I could help with my solution.
No matter if I am in Greece or where ever.
I always think that ideas must be always open to anyone worldwide.
Simple things make people always happy and more progressive.

Glad I help.

A big Bravo to all B4A community.