Advertise here




Advertise here

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Database sync between iphone and server

idimaidima Posts: 7New Users
Hi everyone!

I would like to know, if it is possible to sync a database of an iphone app (sqlite) with any database on the server.
The goal is to develope an iphone-application that runs on several iphones as clients and syncs its database with the central database on the server.

Thank you very much!!!
Post edited by idima on

Replies

  • dicklacaradicklacara Posts: 123Registered Users
    edited November 2008
    idima wrote: »
    Hi everyone!

    I would like to know, if it is possible to sync a database of an iphone app (sqlite) with any database on the server.
    The goal is to develope an iphone-application that runs on several iphones as clients and syncs its database with the central database on the server.

    Thank you very much!!!

    Sure!

    1) You have to copy the iPhone SQLite DB to the iPhone's Documents folder so you can update it. (google for an example on how to do this)

    2) You need a way to access the DB server-- typically through a web app or a web service.

    3) You need to write the iPhone synch app to access the Server DB and update the local SQLite DB.

    You prolly want to add some sort of timestamp indication at both ends to avoid unnecessary synchs
  • idimaidima Posts: 7New Users
    edited November 2008
    Hi!

    Thank you very much for your answer!

    Is there any API or something like that, that i can simply use in Xcode and have not to develop the synchronization on my own? If not, are there any tutorials or best practice descriptions for development of the synchronization?

    Thank you!
  • dicklacaradicklacara Posts: 123Registered Users
    edited November 2008
    idima wrote: »
    Hi!

    Thank you very much for your answer!

    Is there any API or something like that, that i can simply use in Xcode and have not to develop the synchronization on my own? If not, are there any tutorials or best practice descriptions for development of the synchronization?

    Thank you!

    You can't really synch your app' s content in the way Apple synchs its iPhone apps content (Contacts, Calendar, etc). There is no program mechanism, as you do not have access to the iTunes synching app.

    Even if you did have an API, you can't write a (non-jail broken) iPhone app to use the iPhone USB connector.

    The only thing Apple does is allow you to replace your app (and its embedded data content) with a newer iteration thru iTunes synching or app store download.

    So, to maintain synch, you have to write an app that uses WiFi or Edge, and that leads us back to some sort of client-server interface. You can write a special server app, in say Perl, that communicates with your iPhone client app... but it is usually easier to interface a web app or a web service app running on the server that has access to the DB server.

    Most DB servers are capable of accepting direct calls, but IT would be crazy to permit this kind of access outside of the IT department.
  • idimaidima Posts: 7New Users
    edited November 2008
    Hey dicklacara, thank you very much!

    The synchronization must run over EDGE, 3G or WIFI not over USB. I think, it's the best solution to implement a web service for the server, that realizes the sync. Do you have some further information or tutorials for this?

    Thank you very much for your help!!!
  • dicklacaradicklacara Posts: 123Registered Users
    edited November 2008
    idima wrote: »
    Hey dicklacara, thank you very much!

    The synchronization must run over EDGE, 3G or WIFI not over USB. I think, it's the best solution to implement a web service for the server, that realizes the sync. Do you have some further information or tutorials for this?

    Thank you very much for your help!!!

    The web service is prolly the best solution.

    I haven't screwed around with web services or web programming since 2004, so my answer may be somewhat dated:

    The only experience I have writing/publishing WS (Web Services) is using CF (ColdFusion) on the server. It is easy to do but CF isn't as common as PHP and some other web programing languages.

    On the client side I used JavaScript.

    The data was exchanged using an XML packet. Alternately, you could exchange data using a WDDX packet. WDDX is simply XML coded with a convention to to allow the server to supply JavaScript routines (along with the data) to encode/decode the data on the client,

    Here's what the process looked like:

    The Client Side Application:
    1) receive input from the user (or client app event)
    2) generate SQL request
    3) XML encode the request if necessary
    4) pass the request to the web service

    The Server Side application:
    1) receive request
    2) decode (XML parse) the input if necessary
    3) analyze the request
    4) perform the DB query
    5) XML encode the query results
    6) return the results

    The Client Side application:
    1) receive results
    2) decode (XML parse) the results if necessary
    3) analyze the results if necessary
    4) generate DB query to update client DB if necessary
    5) perform the DB query to update client DB if necessary
    6) display results if necessary

    Given: that you have access to (or can write) a web service that functions as described above.

    Your iPhone app would likely include:
    1) an http interface to access the web service
    2) an XML parser
    3) an XML encoder
    4) an SQLite DB interface

    Since the iPhone SDK has only been around a few months, you prolly aren't going to find an example that does what you want. The SDK includes examples using XML and SQLite... the guys at pragprog.com are considering a chapter on consuming WS... and you can google for examples of other pieces.

    Whew!

    I, too, am interested in an iPhone app that does this. I need to add similar capability to a future version of an app that is currently being prepared for publication. I'd prefer not to spend the time to bring up a DB server, refresh my knowledge on WS publishing, learn how to do it in PHP...

    Rather, if someone knows of a publicly-accessible WS that interacts with an SQL DB (receiving DB queries, returning results) then I could concentrate on the client-side iPhone app.

    HTH

    Dick
  • smontgomeriesmontgomerie Posts: 5New Users
    edited April 2009
    dicklacara wrote: »
    The web service is prolly the best solution.

    I haven't screwed around with web services or web programming since 2004, so my answer may be somewhat dated:

    The only experience I have writing/publishing WS (Web Services) is using CF (ColdFusion) on the server. It is easy to do but CF isn't as common as PHP and some other web programing languages.

    On the client side I used JavaScript.

    The data was exchanged using an XML packet. Alternately, you could exchange data using a WDDX packet. WDDX is simply XML coded with a convention to to allow the server to supply JavaScript routines (along with the data) to encode/decode the data on the client,

    Here's what the process looked like:

    The Client Side Application:
    1) receive input from the user (or client app event)
    2) generate SQL request
    3) XML encode the request if necessary
    4) pass the request to the web service

    The Server Side application:
    1) receive request
    2) decode (XML parse) the input if necessary
    3) analyze the request
    4) perform the DB query
    5) XML encode the query results
    6) return the results

    The Client Side application:
    1) receive results
    2) decode (XML parse) the results if necessary
    3) analyze the results if necessary
    4) generate DB query to update client DB if necessary
    5) perform the DB query to update client DB if necessary
    6) display results if necessary

    Given: that you have access to (or can write) a web service that functions as described above.

    Your iPhone app would likely include:
    1) an http interface to access the web service
    2) an XML parser
    3) an XML encoder
    4) an SQLite DB interface

    Since the iPhone SDK has only been around a few months, you prolly aren't going to find an example that does what you want. The SDK includes examples using XML and SQLite... the guys at pragprog.com are considering a chapter on consuming WS... and you can google for examples of other pieces.

    Whew!

    I, too, am interested in an iPhone app that does this. I need to add similar capability to a future version of an app that is currently being prepared for publication. I'd prefer not to spend the time to bring up a DB server, refresh my knowledge on WS publishing, learn how to do it in PHP...

    Rather, if someone knows of a publicly-accessible WS that interacts with an SQL DB (receiving DB queries, returning results) then I could concentrate on the client-side iPhone app.

    HTH

    Dick

    Has anyone found anything that does this since this was first published?

    Thanks
  • jtarajtara Posts: 406Registered Users
    edited April 2009
    XML is almost certainly overkill. Not sure why everybody is so XML-crazy. XML is slow, difficult to parse, and bloaty.

    CSV is light and simple. With XML you are sending several times the number of bytes over the air. (Unless you compress, in which case the advantage of CSV may be minimal in terms of bytes transmitted, as XML's bloat is primarly in redundancy.)

    You can use a timestamp, or you could use revision numbers. You might prefer a timestamp, because it might be useful for internal auditing. But a revision number is lighter. The revision numbers would have to be GLOBAL within the database, just like a timestamp would be. (That is, not "revision 2 of record 10", but revision 2, period. Each record changed increments the global revision number.)

    You need a table in the database that keeps track of the highest revision number (or timestamp) that each user has. Or you could keep track of that in the client.

    Updates and new records are easy. Deletions are a bit more complicated, but not very much so - you can't ever really delete a record on the server. Have a "deleted" column in the master database. When you "delete" a record, you set the deleted column true, and the client needs to know when it gets an update, if the deleted column is true, to delete the record. (You CAN delete records on the client side, just not on the server side.)

    When the client makes a request to the web service for an update, the server will do a database query for all records with a revision number (or timestamp) greater than the largest revision number/timestamp which the client already has.

    There you go, there's your sync. Or, actually, replication.

    Now, did you really mean "sync", or just "replicate"? Because if you meant "sync" that is more complicated. That is, will clients be making changes to the database, and those need to be uploaded too?
  • smontgomeriesmontgomerie Posts: 5New Users
    edited April 2009
    jtara wrote: »
    XML is almost certainly overkill. Not sure why everybody is so XML-crazy. XML is slow, difficult to parse, and bloaty.

    CSV is light and simple. With XML you are sending several times the number of bytes over the air. (Unless you compress, in which case the advantage of CSV may be minimal in terms of bytes transmitted, as XML's bloat is primarly in redundancy.)

    You can use a timestamp, or you could use revision numbers. You might prefer a timestamp, because it might be useful for internal auditing. But a revision number is lighter. The revision numbers would have to be GLOBAL within the database, just like a timestamp would be. (That is, not "revision 2 of record 10", but revision 2, period. Each record changed increments the global revision number.)

    You need a table in the database that keeps track of the highest revision number (or timestamp) that each user has. Or you could keep track of that in the client.

    Updates and new records are easy. Deletions are a bit more complicated, but not very much so - you can't ever really delete a record on the server. Have a "deleted" column in the master database. When you "delete" a record, you set the deleted column true, and the client needs to know when it gets an update, if the deleted column is true, to delete the record. (You CAN delete records on the client side, just not on the server side.)

    When the client makes a request to the web service for an update, the server will do a database query for all records with a revision number (or timestamp) greater than the largest revision number/timestamp which the client already has.

    There you go, there's your sync. Or, actually, replication.

    Now, did you really mean "sync", or just "replicate"? Because if you meant "sync" that is more complicated. That is, will clients be making changes to the database, and those need to be uploaded too?

    jtara,
    I was more thinking of a framework for doing this kind of thing - it has to be a common task on the iPhone. But thanks for the suggestions on how to do this, as I suspect I'll be writing my own framework.

    I was more thinking of sync (as in, client makes changes on iPhone, they are reflected in a database, which can be changed via another medium, like a web interface, re-synced back to the iPhone - so 2-way sync). I imagine it would be similar to the structure that you propose, where each table in each database (client and remote) has a timestamp, and each record must be diff'd against each other record that has changed since the last sync time.

    In terms of protocols, I'm more partial to JSON. It's very lightweight, easy to parse, and much more object-friendly than CSV. And there are both great client-side libraries and server-side support for the protocol, but yeah, XML is way too heavy.
  • bugzappybugzappy Posts: 1New Users
    edited April 2009
    Maybe iAnywhere from Sybase will help. I have no idea how expensive it will be (or, open source?). The bad news is it's not even available for Beta yet. The good news is, if they are building infrastructure sw for this problem, then others are too..

    http://blogs.sybase.com/ithain/?p=577
  • smontgomeriesmontgomerie Posts: 5New Users
    edited April 2009
    bugzappy wrote: »
    Maybe iAnywhere from Sybase will help. I have no idea how expensive it will be (or, open source?). The bad news is it's not even available for Beta yet. The good news is, if they are building infrastructure sw for this problem, then others are too..

    Sybase Blogs - Ian Thain » Blog Archive » i(Phone) have my data here,now i want it there…Whats the problem?

    That's a great link bugzappy, thanks! Let's hope for open source...
  • tetontechtetontech Posts: 2New Users
    edited May 2010
    There is an open source, free, MIT licensed enterprise data sync feature in the QuickConnectFamily framework. (QuickConnectFamily)

    It handles all of the communication with your web service via JSON. It makes it fairly easy to do syncing. It is written in Javascript and is used generally by developers that are creating hybrid applications. It is :

    1 - database agnostic. Use the database of your choice on the backend.
    2 - database design agnostic. The tables and fields do not need to be the same in the device database and the enterprise database.
    3 - fully supportive of offline entry for later syncing.
    4 - service language agnostic. Build using any server software and any language. All you do is generate JSON for communication both directions.
    5 - device agnostic. Works on iPhone, Android, WebOS. Blackberry will be added to the list at the end of this week.

    There is a pure Objective-C implementation coming very soon. I'm debugging it now.
  • tetontechtetontech Posts: 2New Users
    edited May 2010
    bugzappy wrote: »
    Maybe iAnywhere from Sybase will help. I have no idea how expensive it will be (or, open source?). The bad news is it's not even available for Beta yet. The good news is, if they are building infrastructure sw for this problem, then others are too..

    i(Phone) have my data here,now i want it there…Whats the problem? | Sybase Blogs : Ian Thain

    Sybase has had a problem getting approved because of background services that they require to run. I don't think they have a working solution yet.
  • SadGeekSadGeek Posts: 12Registered Users
    edited August 2010
    tetontech wrote: »
    There is an open source, free, MIT licensed enterprise data sync feature in the QuickConnectFamily framework. (QuickConnectFamily)

    It handles all of the communication with your web service via JSON. It makes it fairly easy to do syncing. It is written in Javascript and is used generally by developers that are creating hybrid applications. It is :

    1 - database agnostic. Use the database of your choice on the backend.
    2 - database design agnostic. The tables and fields do not need to be the same in the device database and the enterprise database.
    3 - fully supportive of offline entry for later syncing.
    4 - service language agnostic. Build using any server software and any language. All you do is generate JSON for communication both directions.
    5 - device agnostic. Works on iPhone, Android, WebOS. Blackberry will be added to the list at the end of this week.

    There is a pure Objective-C implementation coming very soon. I'm debugging it now.

    Hi Tetontech, did you ever manage to get all this working in Objective C? I've got the same sort of problem.

    Any help would be appreciated.

    Steve
  • all7daysall7days Posts: 1New Users
    edited September 2010
    Has anyone found anything that does this since this was first published?

    Thanks

    Hi,
    I have a solution that will allow you to sync data between your iPhone application and enterprise server. Our solution is very robust and currently handle 16 million records for financial and retail businesses.

    Disclaimer:
    Unfortunately this solution is not FREE, its a hosted service currently used for B2B only. I am trying to see if consumers can use it.


    Its very easy to integrate. Install our client in your app and configure our server information and you are set to configure on server and sync data from client to server anytime.
    Our tool benefits apps to work offline and we can sync data during occasional connections.

    Please contact me @ rajunpatil@yahoo.com to discuss your requirements. I will setup a demo for you in person.



    thanks
    Raj
    rajunpatil@yahoo.com
  • ideapodideapod Posts: 1New Users
    edited November 2010
    idima wrote: »
    Hi everyone!

    I would like to know, if it is possible to sync a database of an iphone app (sqlite) with any database on the server.
    The goal is to develope an iphone-application that runs on several iphones as clients and syncs its database with the central database on the server.

    Thank you very much!!!

    This would be a use case for sync services. (see Loading… )

    The only problem is - I'm not sure that I can put syncservices.framework on to the iphone. Anybody got any ideas? I've come from the microsoft sync framework Microsoft Sync Framework Developer Center which solves the problem and I'm looking for the equivalent in mac/iphone land.

    In that framework there is a way to have a proxy web service that implements the protocol for sync calls. It then translates the xml updates that say what has changed. It should work with Sync Services for apple. But, I don't see anything....
  • aneelupaneelup Posts: 2New Users
    edited August 2011
    I would like to know, if it is possible to sync a database of an iphone app (sqlite) with any database on the server.
    The goal is to develope an iphone-application that runs on several iphones as clients and syncs its database with desired web server
  • aneelupaneelup Posts: 2New Users
    edited August 2011
    aneelup wrote: »
    I would like to know, if it is possible to sync a database of an iphone app (sqlite) with any database on the server.
    The goal is to develope an iphone-application that runs on several iphones as clients and syncs its database with desired web server
    web services
  • ALG4ALG4 Posts: 15Registered Users
    edited September 2011
    jtara,
    I was more thinking of a framework for doing this kind of thing - it has to be a common task on the iPhone. But thanks for the suggestions on how to do this, as I suspect I'll be writing my own framework.

    I'd like to suggest our database SDK called RDM Embedded. We currently have support for our own proprietary interface which is network model based for faster performance (using set relationships), and also an SQL interface. Version 10.1 is also supported for iOS, and we have a free download available (the Mac package includes the iOS simulator libraries.

    What is unique is that we support fully ACID compliant database replication and mirroring, even to an enterprise level database (MS SQL Server, etc).

    Currently we have C and C++ APIs, but we are working on a native Objective-C interface for version 10.2, which should be out soon!

    Hope this helps anyone looking for a solution, you can try it out for free from raima.com, just choose Mac OS X for the platform.
    <a href="http://teknadesigns.com/" target="_blank">[SIGPIC][/SIGPIC]</a>
  • stesx51stesx51 Posts: 1New Users
    edited February 2012
    ...if someone is still interested let me know.
  • ScratoneScratone Posts: 1New Users
    edited February 2012
    stesx51 wrote: »
    ...if someone is still interested let me know.

    I'will update my SQlite Database on the Iphone App with data from an Database on the Webserver.

    Thanks
  • avorsavors Posts: 1New Users
    edited May 2012
    stesx51 wrote: »
    ...if someone is still interested let me know.

    I'm interested. I'm using SQLite in Adobe Flex mobile app. Could you share your solution to syncing the local SQlite with server side DataBase?

    Cheers
  • alexandalexand Posts: 1New Users
    edited May 2012
Sign In or Register to comment.