We have a larger ERP system that is local connecting to an on-site MSSQL database. It a mission critical application that runs our entire operation. It currently supports over 25 users. We added some functionality in a new update that collects data that I’d like to share with people outside of our organization that are remote. The data is a small subset of data in the DB that mainly focuses on CRM type data and the data would be read-only. Obviously, I don’t want anyone connecting directly to our on-site DB via the internet.
It is not critical that the data be “live” but a snapshot that is updated several times a day would be adequate. I thought of creating a web-app using Xojo cloud to provide a UI that users can log into to review the data.
I have not used Xojo for web development yet, but I consider it a viable option. I have questions and need advice:
What would be an appropriate way to get the data from our local DB uploaded to xojo cloud to a web DB? I could create a small console app on the server that would upload a file to the cloud, but I need advice on the best practice of doing this or any other ideas regarding implementation.
I think Xojo Cloud only supports PostgreSQL or MySQL (and SQLite if you add it to your project?), so MS SQL Server replication doesn’t help in this case. Besides, you’d need some sort of VPN/Secure tunnel to connect the two.
Hi Joseph, what you are asking about is not just a Xojo Web problem, it is in fact a problem for any web application built in any programming language, including mobile apps.
The issue lies in that any code that runs on a web browser can be “seen” by anyone. Typically, the best way to solve your case is to create an API that runs on the server and it talks directly to your database. Things that run on the server cannot be seen by anyone. You then create web pages that talk to this API after authentication. Many programming languages and frameworks allows you to build the API and the web pages in the same application. They define what can be seen by users and what cannot be seen, like the API endpoints. I hope it makes sense. If I was you, I would look at creating a Desktop App with Xojo, which creates an .exe that can be distribute to your users, you can be better protected this way.
Get a root VPS, they are probably cheaper in your country as here in .ch (o;
The costs per year would be probably what you pay for Xojo Cloud in a month (o;
Create API JSON endpoints that collect the data from your server and distribute to the end users…those can not only be on a laptop/PC but also on a mobile platform…here is where Xojo really shines
I last year a simple iOS app that gets JSON data from the online shop of my girlfriend. So she can see all the orders and visits when she is abroad on her mobile phone…
It even then sends push notifications about a new order to iOS apps who have the app automatically.
I think Xojo Web app looking at a database on the same server is the typical use, just like they are doing with the Eddies Electronics app. This would seem secure and a perfect use case for Xojo web, per their advertisement.
My issue or question resolves around an efficient way to get a subset of my LOCAL data up to the cloud DB that the web app will use. It may be as simple as FTPing an SQLite or other DB to location on the server where the web app is looking at the DB.
I don’t want the Xojo web app to be looking at my local data live so no connection to that is necessary.
Create a Xojo Web app that accesses a PostgresSQL database with both running on Xojo cloud. Update the database as many times per day as you need to keep the data fresh. To do this, use a console app that runs locally, connects to the local MSSQL database and also connects to the remote PostgreSQL database. Loop through all of the databases and tables on the MSSQL server and sync them with the remote PostgreSQL database. Run some tests first to see if the sync is fast enough.
If the sync performance is too slow, then you have to start getting creative. For example, you could create a dump of all of the data in the MSSQL server and SFTP it to your Xojo cloud server. Then have the Xojo Web app update the PostgreSQL server running on Xojo cloud. Since both will be essentially running on the same cloud, the reads/writes to the database should be much faster. If during the sync your Web UI gets sluggish, run it at night or create a separate Web App that just handles the syncing (assuming Xojo cloud allows multiple Web apps to connect to the same PostgreSQL server).
