Android Question Access SQLite DB stored on web

[MoraviaVenus]

Hi,

I face a challenge now, how to access SQLite database (*.db file), which is stored on web hosting server (actually it is just a webpage, no real server).

My situation is as follows:
On my webpage there is stored SQLite database global_database.db (among other files related to webpage). In every device, that has installed my application, there is similar SQLite database, stored locally - local_database.db. During run of application, I want to download data from global_database.db and store them to local_database.db, or vice versa.

When I read/write data only from/to local database, everything works fine using following command:

Dim LocalDB As SQL
Dim csr As Cursor

LocalDB.Initialize(File.DirRootExternal, "FileFolder/local_database.db", False)

csr = LocalDB.ExecQuery("SELECT FirstColumn FROM DataTable ORDER BY FirstColumn ASC")

When I want to select data from global_database.db stored on webhosting server, I connect to server using FTP at first (this works OK, because I am copying some files from server through application). Then I try to initialize global database object, and then I wreck. I use following code, but apparently it is wrong.

Dim ServerDB As SQL

ServerDB.Initialize("ftp.webpage.com", "/public_html/DB/global_database.db", False)

I got error message: android.database.sqlite.SQLiteException: unable to open database file.

Could you please suggest me how to get data from global_database.db?

Thank you.

 

[Erel]

You will need to download the SQLite file and then work with it locally (assuming that you don't want to run a server like RDC together with the web server).

 

[MoraviaVenus]

Hi Erel,

exactly, I want to avoid the run of RDC server in parallel with web server (running virtual private server is too expensive for my free-of-charge app ).

That means, there is no other way to get data from remotely stored database file? Just download DB file to device...?

Currently, the global_database.db has around 2,5 MB and its tendency is growing. I prefer not to copy it to device, it can be bothering for users. Moreover, when user finishes the work with locally copied global_database.db (INSERTs to DB were done), user has to copy it back from device to the server, which can cause chaos when several users would like to do that concurrently (they will overwrite changes of each other).

Thank you.

 

[nwhitfield]

If users are going to be updating the database, then the only practical way do to this probably is by running a proper web server with a database on it. It doesn't necessarily have to be very high powered, and you can find a lot of cheap hosting plans that include PHP and MySQL (or, indeed, you can use SQLite via PHP).

It needn't be too complicated (though for security, it shouldn't be too simple, either). It's very easy to get a PHP script to turn all your data into JSON, which B4A can then work with simply, and you can create a script that accepts new entries via HTTP POST, and use httputils2 to process the info, updating the central database.

For a quick example, the 1&1 basic Linux Basic hosting plan includes MySQL, and is £4.99 per month plus vat, with a first year offer of £0.99 per month, and 100GB traffic per month.

 

[Erel]

See this tutorial for a simple PHP script: http://www.b4x.com/android/forum/threads/8339/#content