Local SQLlite DB to hosted MYSQL DB

tommycahir

Hi Guys

I hope you can assist with this , It has been a few years since I done any development and I have been asked by a friend to assist with a project of his.

Basically there will be a Local SQLlite DB updated in real time during an event (race timing) by a locally installed application.
This DB will not be locked and the data can be retrieved from it using an SQL query during the event.

Now we want to pull this information from the SQLlite DB and push it to a MYSQL DB that we have available on a hosting package so that we can extract and format it in a nice presentable way for the event attendees to get the times as needed.

The issue is not with the SQL to extract the data (we have already got these queries nailed down) but more so the way that we trigger this on the machine to start and then to push it on to the Mysql server and how we ensure that we are updating the correct rows on the server DB with the information.

Or as my friend is curious is there a simple idiot proof way of taking the SQLlite DB contents at a specified period and dumping them into a mysql DB

Between us we have experience (all with standalone applications it must be noted) in PHP, VB, VBA, C++ and minimal experience in Java to work with on this issue so any help or pointers are more than welcome.

The reason we are looking for a simple solution as we will not always be there to run the timing so need to have a simple press this button type application for others to work with

Regards
Tommy

TonyStark

tommycahir wrote: »

Hi Guys

I hope you can assist with this , It has been a few years since I done any development and I have been asked by a friend to assist with a project of his.

Basically there will be a Local SQLlite DB updated in real time during an event (race timing) by a locally installed application.
This DB will not be locked and the data can be retrieved from it using an SQL query during the event.

Now we want to pull this information from the SQLlite DB and push it to a MYSQL DB that we have available on a hosting package so that we can extract and format it in a nice presentable way for the event attendees to get the times as needed.

The issue is not with the SQL to extract the data (we have already got these queries nailed down) but more so the way that we trigger this on the machine to start and then to push it on to the Mysql server and how we ensure that we are updating the correct rows on the server DB with the information.

Or as my friend is curious is there a simple idiot proof way of taking the SQLlite DB contents at a specified period and dumping them into a mysql DB

Between us we have experience (all with standalone applications it must be noted) in PHP, VB, VBA, C++ and minimal experience in Java to work with on this issue so any help or pointers are more than welcome.

The reason we are looking for a simple solution as we will not always be there to run the timing so need to have a simple press this button type application for others to work with

Regards
Tommy

Why not use the remote database in the first place?

Draupnir

tommycahir wrote: »

Hi Guys

I hope you can assist with this , It has been a few years since I done any development and I have been asked by a friend to assist with a project of his.

Basically there will be a Local SQLlite DB updated in real time during an event (race timing) by a locally installed application.
This DB will not be locked and the data can be retrieved from it using an SQL query during the event.

Now we want to pull this information from the SQLlite DB and push it to a MYSQL DB that we have available on a hosting package so that we can extract and format it in a nice presentable way for the event attendees to get the times as needed.

The issue is not with the SQL to extract the data (we have already got these queries nailed down) but more so the way that we trigger this on the machine to start and then to push it on to the Mysql server and how we ensure that we are updating the correct rows on the server DB with the information.

Or as my friend is curious is there a simple idiot proof way of taking the SQLlite DB contents at a specified period and dumping them into a mysql DB

Between us we have experience (all with standalone applications it must be noted) in PHP, VB, VBA, C++ and minimal experience in Java to work with on this issue so any help or pointers are more than welcome.

The reason we are looking for a simple solution as we will not always be there to run the timing so need to have a simple press this button type application for others to work with

Regards
Tommy

Sounds like you are building an Android app from the description?

I suppose your issue being that you don't want user interaction to kick off the export?

I would have said you can either have a button the user presses or a job run by some sequence of events that packages the data up in XML and sends it a web service which can pop it into your MySQL.

tommycahir

TonyStark wrote: »

Why not use the remote database in the first place?

The application that is running on the PC stores the data in a local SQLite DB and doesn't include the option to have a DB stored remotely.

Draupnir wrote: »

Sounds like you are building an Android app from the description?

I suppose your issue being that you don't want user interaction to kick off the export?

I would have said you can either have a button the user presses or a job run by some sequence of events that packages the data up in XML and sends it a web service which can pop it into your MySQL.

Not an android app but we want to make the race times available via webpage so that the drivers etc can access the times via mobile web browsers as needed.
we have no problem getting a user to select the correct DB generated by the local application and set some options before kicking off the process of updating the remote DB at the start but would prefer not have have them perform this task repeatedly throughout the event.

I suppose the question really is what is the best approach, what is the best language to work in for this type of job ( from research it looks like C++ ) and has anybody done this before so that they can share some of their experiences with us.

Regards
Tommy

Colonel Panic

How about having a REST style API on the web side of things? Then your app can package the data up as JSON or XML in web requests and submit those to the database?

This seems like a much saner approach than having clients connect directly to your database.

In terms of language, if one of you has PHP experience, then you could use that on the web end and whatever language the client is written in for the desktop side of things.

tommycahir

Colonel Panic wrote: »

How about having a REST style API on the web side of things? Then your app can package the data up as JSON or XML in web requests and submit those to the database?

This seems like a much saner approach than having clients connect directly to your database.

In terms of language, if one of you has PHP experience, then you could use that on the web end and whatever language the client is written in for the desktop side of things.

Thanks Colonel Panic I will have a look at a REST Style API and see if that is possible, given that we have never used it before it might be a good learning curve for us.