Newbie: How to set up a db on a a server and add records from Desktop

General
1

Long title, but says it all.
Is there a guide to creating a db on my web host, and save records to it from a desktop app?

1 Like
2

This isn’t really hard. First you have to add a database in the interface your hosting company provides. Then you have a database name, a user name and a password. Usually, there is something like phpMyAdmin to manage the database online.

Then you connect to the database like you would for every other database:

OrdersDBOnline = New MySQLCommunityServer
OrdersDBOnline.Host = “d167xxx”
OrdersDBOnline.Port = 3306
OrdersDBOnline.DatabaseName = “4877xxx”
OrdersDBOnline.UserName = “4877xxx”
OrdersDBOnline.Password = “xxx”
Try
OrdersDBOnline.Connect
Return True
Catch error As DatabaseException
// Connection error
MessageBox("It wasn’t possible to connect to mySQL: " + error.Message)
end try

3

I read that is not recommended to have a database server to be available to receive connections from everywhere and have a server/service that helps protecting the database.

4

It appears that the IP address of the connecting app must be known in advance.
That seems to rule out the possibility of ‘any instance of my app’ connecting from ‘anywhere they are using it’, and inserting a record from there.
Is that the case?

5

So I need to know how to write one of these too?

6

I guess it depends on your needs and what you want to do/offer. You may want to use online services that offer this for you.

I have read several users in this forum recommending Supabase but I haven’t used it, so I don’t know how easy/hard is that to use.

7

In general you tell the database what the security is. The code above is from a private project where I can write and the users read. For a more public facing database I have a php script do the writing.

3 Likes
8

@Geoff_Perlman @Paul_Lefebvre
this would be a good thing to add to the eddie electronic example
connect to a sqlite db like now, or add the possibility to connect to a postgres or mysql server
with tutorial on how to setup these servers basically

just my 2 cts.

9

Locally ok.

Over internet, never.

Or in 5 minutes, the first smart and bad 15yo who decides to play with your app will read all your data and delete your DB.

Over internet you should implement some safe auth protocol and data exchange using JSON for example. No DB port open. Just a https API needing registration of its users without hardcoded authorizations and DB internals (user, pw, port, etc).

1 Like
10

This thread by a fellow newbie might have some relevant info. Security discussions farther down.

11

@Julia_Truchsess I LOLed

1 Like
12

Once you go outside the local area network, you’re now talking about a web application. You can set up mechanisms for a desktop app to talk to the web app (I just use a php script myself) but you really have graduated out of the desktop and into the web. There are significant advantages to doing it as a web app. Most significantly is that only your web app has access to the database. It is sheilded by your app. Granting desktop access to your web hosted database is a bad idea, unless you really lock down the API to certain well defined actions.

1 Like
13

In my desktop driven apps, the desktop can create / record an event in the online database (employee swipes their ID card). There is no access in the API for read, update, or delete. Any other access becomes a web app, run in the browser.

14

Googling “php crud wrapper api” will likely show you how to do the php ‘wrapper / front end’ part on the server which will keep out the meddlers!