Hello All,

  I'm looking for an example of an application that will work on a Palm or
Blackberry that syncs to a SQL server.  Any ideas?



Thanks!!





  _____  

From: Eric Pankoke [mailto:epankoke@comc...]
Sent: Tuesday, February 12, 2008 1:36 AM
To: Conduit Developer Forum
Subject: RE: Performing fast sync with Access database



Okay, I think you and I are suggesting roughly the same solution from two
different vantage points, Jonathan.  I still think in the end mine will
require a lot less storage, however.  If both your solution and mine require
two writes, why not empty the temp tables at the end of the sync, write
changes to both the temp and main tables, and then use the temp tables to
determine what to sync to the Palm?  I know you're still thinking this
equates to a fancy "dirty bit" solution, but with your version of the
solution I still don't see how you'll know what changed without doing a
record by record compare.  In my solution you'll have a full database and
just a changes database, which requires a lot less storage.  I hope I'm
still not missing something here.



As for Access being a disappointment, you're right and wrong.  If people
would have used it for what it was, which was a nice desktop data store,
everything would have been great.  Instead, people thought that because it
was easy to use it would make a nice, cheap enterprise solution, and as it
turns out they were wrong.  I only say this because I've been through all of
that myself.  And ironically enough, a old co-worker and I actually wrote an
SQL Server implementation of a project that used the trigger solution to
create a bitmask change fieldset for syncing purposes (though the target for
that project was not a Palm database).  That was actually a pretty slick
solution for a data sync need, though we never got to implement it in a
production environment.



Eric Pankoke

Founder

Point Of Light Software

http://www.polsoftware.com/



-----Original Message-----
From: %%email.bounce%% [mailto:%%email.bounce%%] On Behalf Of Jonathan Carse
Sent: Tuesday, February 12, 2008 1:20 AM
To: Conduit Developer Forum
Subject: Re: Performing fast sync with Access database



Hi Mark,

First of all thanks for your detailed explanation of your solution!

That is an excellent idea.

As for my previous suggestion, I didn't mean to create a copy after each
sync.
You copy the database just once. Upon sync, you can use this middle-man
database copy as a way to determine which field in every record was modified
in the main database (as an alternative to the bit masks).
After completing the synchronization, the main database and the copy
database should hold exactly the same data.
That way you're not copying the database during each sync. You're just
making each change twice - to the middle database and to the "main"
database.

I am still thinking about this solution as way to avoid adding special code
to handle the bitmasks. There are quite a lot of update/add functions in my
project and that will take some time to accomplish.
However, I am still struggling on a way to pull out just the modified
records using this copy of the database, as I wrote in my first post.

Another thing I didn't mention is that it is not possible
<http://www.thescripts.com/forum/post725997-2.html>  to create triggers in
Access. Triggers are a way in SQL server to have the database perform
actions automatically upon an update, addition or deletion of a record. That
could have been extremely useful as well - you could just write the code for
the bit masks in these triggers - but, as I said, Access is a
disappointment.

If this "two DBs" solution won't see light soon, I will abandon it and start
implementing your bit-mask technique. Thanks.

This is becoming quite an interesting thread, and I'm sure it will be
helpful to more people in the future. Thanks for participating.

Jonathan

On Feb 12, 2008 6:41 PM, Mark Janveaux <mark@phys...>
wrote:



Hi Jonathan,



Creating a copy of the database each time it syncs is a little much!  That
will not scale well at all .



Off the top of my head, you're really interested in tracking a 'delta set'
and possibly the last

modification date of each member in the delta set so as to figure out how to
merge each member (db field).



The fastest (and in my opinion, an elegant) way of doing this would be to
maintain a bitmask for each table.

For example, an 8 byte array would be able to track the dirty status of up
to 256 columns per row.



You wouldn't have to necessarily modify your existing tables.  You would
just create one table which

would have a schema along the lines of:



tbl_delta_set

column: fld_tbl_name (unique, possibly PK)

column: fld_delta_set (8 byte array)



The update operations for each table would have to be able to update the
appropriate bit for the field

in the mask (delta set), but that is very very simple arithmetic.  The
ordinal number of the column would

correspond to the appropriate bit to set in the mask.  (i.e. Column n = bit
n)



Upon sync, you would start by looking at this version of the table on both
sides.  You would do a simple

AND operation to figure out if there were any changes made at all.
Furthermore, to figure out

exactly what fields you need to merge you would simple loop through the mask
and build your query

after each AND operation proved false.  You could use a 'handheld wins' /
'server wins' type rule to make

this very easy.  Alternatively you could have a separate table which holds
an update time stamp for every

single delta set member (the primary key would be the tablename + the bit
number in the mask) . this

table could be a two column table to make it real simple:



tbl_delta_set_stamp

column: fld_pk (string, table_name+bit) (eg value. tbl_sample0, tbl_sample1,
corresponding to first and second bits (fields) of tbl_sample)

column: fld_last_modified (timestamp)





If you went ahead and implemented the timestamp table you would have the
ability to merge, on a last modified basis every

single field of the database and would only pull the records you were
interested in pulling without having to keep multiple copies of the
database.



basically, what I'm describing is 'flattening' the audit data into a single
field which can be compared using bitmask operations . it would

be extremely efficient and very simple to implement. . I've written a lot of
sync engines and have done this in the past.



Let me know if this makes sense to you.



Thanks,



Mark



--
For information on using the ACCESS Developer Forums, or to unsubscribe,
please see http://www.access-company.com/developers/forums/



--
For information on using the ACCESS Developer Forums, or to unsubscribe,
please see http://www.access-company.com/developers/forums/

--
For information on using the ACCESS Developer Forums, or to unsubscribe, please see http://www.access-company.com/developers/forums/