Sending or Updating data to hosted database on internet

[Jay9988]

Hi Expert
I have developed foxpro application for recording sales/ purchase transactions using mysql database.
The company has also a website with mysql database hosted on the internet.

I want to add a feature to my foxpro application, to send data from desktop database to the database hosted on the internet.

Any practical suggestion for this matter or link of sources which I can learn ?
Because it's very new for me... I have search about cURL, REST API -> is it relevant?

Thank you all

 

[Mike Gagnon]

Probably there is something similar with MySql

https://smallbusiness.chron.com/synchronize-two-microsoft-sql-databases-52142.html

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[Chris Miller]

You need an ODBC driver and use SQL to work with the MySQL database, no matter if it's local (on localhost) or in the internet.

Simnple comparison of how to work on data locally with PHP vs remotely from a desktop application:

<?php
$conn = new mysqli($servername, $username, $password, $dbname);
$sql = 'SELECT * FROM `atable`';
$result = $conn->query($sql);
... fetch result records
$conn->close();
?>

hConnection = SQLStringConnect(connectionstring)
cSQL = 'SELECT * FROM `atable`'
SQLExec(hConnection,cSQL,'resultname')
...work with the resultname cursor
SQLDisconnect(hConnection)

There are many variations of this, but you see it's pretty much the same. If you come from programming a webste hosted in PHP you see how similar this works with SQL passthrough (that's the roof name for all SQxyz() functions in VFP). You can instead use remote views, which require a VFP DBC defining them and the USE remoteview instead of USE some.dbf, you can use cursoradapters, too.

The main ingredient missing is how a connectionstring whould be composed. It will need the information which ODBC driver to use, and in this case of connecting to a MySQL database the paameters for that database. You find most any ttype of connection string at

https://www.connectionstrings.com/

Don't look for VFP there, you'll find it, but what you need is a MyQL connection string, of course. Another way to get there is defining a DSN with the ODBC Data Source Administrator of Windows and then use SQLCONNECT(DSN).

Important: As VFP is not Unicode compliant, you must use an ANSI ODBC driver, no matter if the MySQL database characterset/collation is Unicode/utf8/utf8mb or latin-1 or similar ANSI character sets. One topic therefore will be to convert strings you get from MySQL to Ansi within VFP. There exist some helpful features like Sys(987) to automatically do that. What works depends on details.

Chriss

 

[Chris Miller]

The other front is "sending or updating data".

Mike Gagnon addessed the overarching aspect of getting the same data local and into the cloud by synchronization.

Well, the usual usage of a database in the internet is as the only database, i.e. it's just another form of sharing a database, not just in a LAN as with DBF files in a file share, not jut in a WAN wextending a LAN, but in the cloud/internet, so it can be accessed from anywhere with an internet connection.

The aspect of keeping the central MySQL database up to date at all times can be covered by the same code as in the sample of my previous post, as the cSQL is not limited to only read data with SELECT, you also can and use UPDATE and INSERT statements. All SQL must be MySQL dialect, but that's the way to work with a database server, that's not even depending on remote vs local - on site or "on premiose", as it's also called.

I already mentioned an alternative can be using remote views, epecially updatable views cannot only fetch data but also forward changes you make in their resultsets back into the MySQL database. Same is possible with cursoradapters, which just combine all aspects of SQLEXEC and remote views, also making them the most complex tool of the toolbelt from SQL Passthrough, remote views and cursoradapters. On the level of knnowing nothing it's perhaps easiest to begin with SQL Passthrough functions, as they are so similarly working as you (maybe) know from PHP, so use the pattern I posted above.

Stepping up to remote views you gain the simplicity of not needing to write update or insert statements, whena view query is defined with the infromation about keys, mainly the submission of changes and new records is just made with a TABLEUPDATE() call, which is very nice. On the other hand, defining views is defining a specific query, that's not just a parameter as cSQL is for SQLEXEC. You also mainly loose the ability to run a script of multiple queries with views. Cursoradatpers finally gain back that flexibility of SQLEXEC and combine it with the ability to specify all the necessary information that enables the submission of all modifications to a resultset with a simple TABLEUPDATE. So in the end Cursoradapters is merging all capabilities of SQL Passthrough and updatable views into one construct.

One more aspect that makes usage within a desktop appication differernt from a PHP script: In PHP you usually open and close connections for every reuqest browsers make, mainly because a PHP script has to end for the re4sponse to be sent back and then the next request starts a new PHP session, in short the code runs stateless.

In a desktop application the application can keep the state, you therefore can keep a connection open and only clode it when the EXE quits. There's an advantage of using remote data from a desktop application vs a web application with the old school PHP script style. There are some modern ways of single page web applications that technically only work with one request and keep that alive, making further requests and response interactions with the webserver via web sockets. It's leading off topic, but just to be fair, a similar experience likje a desktop application is possible with a web based architecture, too.

So it's not that big of an advanatage, but you see in your programming you can split off the connection/disconnection and your code can concentrate on the SQLEXEC() part.



Chriss

 

[Jay9988]

Hi Mike, Chriss
Thank you for your explanation.

Sorry for my lack of knowledge about accessing remote database on internet. I am very new on this.

Could we use Sqlstringconnect to access the remote database? and use SQLEXEC to insert, update, or delete? As far as i know, mostly the database server/ web server will reject direct sql statements, am I right?

Could you give me a very basic example of VFP Coding/Method to read from remote database, and to insert data.

Many thanks

 

[Chris Miller]

You obviously misunderstood.

Could we use Sqlstringconnect to access the remote database?

Yes, that's what I said above. You just have to have the right connection string and of course ODBC driver - on the client connecting to the server.

As far as i know, mostly the database server/ web server will reject direct sql statements, am I right?

No, why should that be forbidden? When you are connecting to a datbase server you are authenticating by user/password and then will have granted priviledges for that user. There are even further security options when you need to make the connection with SSL and a certificate, but in most cases you'll perhaps have the rooot MySQL user and a password and connections could be limited to a whitelist of IP addresses only.

Once you are connected, permissions and restrictions can allow or hinder different queries, i.e. you could be denied to drop tables, i.e. delete them. But it wouldn't make sense when you can connect, that you couldn't do anything via your connection.

The basic exampe is already what I gave you. If you don't know SQL yet, a single example of an insert or update statement won't give you everything you need to know anyway, so I spare that. Get a MySQL ODBC driver - from Oracle - install and then first make a connection to execute a simple Select query and that's a good enough first task to complete.

Chriss

 

[Steve Meyerson]

I want to add a feature to my foxpro application, to send data from desktop database to the database hosted on the internet.

Chris has given you a basic example. Another (short) example which might help you get started is a simple connection string which works in one of my programs. It's a simplified version of a method named DbConnect() called from the main form. The return value is .T. if connection is successful.

Of course substitute the Sql properties with your own values. For example in this case the SqlDriver is {MySQL ODBC 5.3 ANSI Driver}

WITH THISFORM
 RETURN SQLSTRINGCONNECT(;
   + 'Driver='    + .SqlDriver   ;
   + ';Server='   + .SqlServer   ;
   + ';Database=' + .SqlDatabase ;
   + ';UID='      + .SqlUser     ;
   + ';Password=' + .SqlPassword,.T.)
ENDWITH

Hope this helps some.

Steve

 

[Chris Miller]

Jay, to add what's perhaps not obvbious to you: you cannot expect complete sample code including a concrete connection string.

First of all you need the detail information which MySQL version is used, then get the appropriate MySQL ODBC driver, which does very much depend on that and your Windows version. The Driver= part of the connection string wil then depend on what the drivers exact name is and you'll find that in the ODBC Data Source Administrator, after you install the ODBC driver.

So your starting point is asking the MYSQL version infdormation from the company.

Meanwhile you could of course also download MySQL itself and install it locally, get warm with SQLStringConnect/SQLExec/SQLDisconnect and then only need to adapt the connection string, later.

Chriss

 

[Chris Miller]

I have developed foxpro application for recording sales/ purchase transactions using mysql database.

Sorry, I thought you used something else than VFP as frontend. So what's actually missing, if you already used a mysql database? You also had to make a connection, either one in a remote views DBC, one in a cursoradapter base class or one defined as a connectionstring used by SQLStringConnect or a DSN used by SQLConnect.

You'd have used localhost or the IP address 127.0.0.1, that's the only thing that changes now from the client perspective. You're not administrating their MySQL database, do you? If so, you'd also need to allow incoming connections, obviously.

Chriss

 

[Steve Meyerson]

The return value is .T. if connection is successful.

Wrong. The return value is a numeric file handle, not a logic variable. A positive number is a successful connection.

Steve

 

[Jay9988]

Hi Chris,
Sorry for late response
Yes, I have use mysql and VFP for develop desktop in LAN Environment. And I am trying to connect to remote database (which I am not administrating this database)

I am looking for the other ways to connect to remote database from VFP besides use sqlstringconnect, maybe such as API?

many thanks

 

[Chris Miller]

You don't need another way to connect. It doesn't matter whether a MySQL database is in a LAN or on the internet, what changes is the server part of the connection string. I don't know why that still is unclear to you.

What you, of course, need is information about what to put in as the server. It could be an IP address colon port number, it could be the domain name, that depends on the web hoster your customer uses. As you don't administer their MySQL database, they will have to both configure it to allow such connections and also give you that information. If they also don't administer their MySQL database, they would delegate this to their hoster and/or find this information in a FAQ or their dashboard or by contacting customer service. If the existing documentation is not mentioning secure connections, it would be another reason to contact web hosting support of whether it's possible to use a certificate and look into the ODBC options about secure connections, currently the ODBC connection string option for SSLMODE would be about that, but also requires the MySQL server configuration to support that or even require it.

There is no HTTP protocol based REST API to get to a MySQL database, unless you'd program that on the server. With PHP, for example. But why would you do that, if you could really simply reuse what you already have and just adjust your connection string?

Chriss

 

[Steve Meyerson]

Jay, to add what's perhaps not obvbious to you: you cannot expect complete sample code including a concrete connection string.

Yes, some do expect that and shouldn't. Personally, code examples have been how I have learn the best. My first language (Microsoft Basic) was loaded with them, at least til I ran into dBASE II (am I showing my age?)

Steve

 

[Chris Miller]

Personally, code examples have been how I have learn the best

Well, the code example really is just a few lines of code I already posted.

What you can't expect is knowing which server ip port etc. to provide in the connection string. That will differ on what webhoster is used. Some allow using the domain you host at them, some host a MySQL server for all their customers and provide access through a specific IP or dmain for that, which differs from the hosted domain. Some only provide mysql databases for local usage, i.e. only scripts like PHP scripts are allowed to connect locally. In such a case you would actually need to change the hoster or use other hosting options. You could colocate a server or get a managed or even unmanaged server or virtual server whwere you could install whatever MySQL server version you need and configure it to allow remote connections, only allow secure connections etc. As already also said there are possibilities to make secure encrypted connections.

But all that said, there is no http based REST API to MySQL- not from MySQL or Oracle, the main connector for anything is ODBC. And that's good, it's more direct than hooking up all communication between clients and server over the http protocol and a http server like Apache or IIS, you directly connect to the MySQL database serice running on a server without indirection and that makes ODBC the best way to connect to any databse, no matter if LAN or internet.

I really wonder why you don't understand how good news that is, as it means you already have everything you need when you already developed a MySQL based application with a MySQL database in the LAN. Especially when you used VFP as frontend it means you already have used ODBC. The only thing that may change on top of a connection string change of the Server parameter/option and some additional options about secure connections is, that the internet hosted MySQL database is another version and would need another ODBC driver version. Still, all your code changes can be limited to making the connection via a changed connection string.

All you can get from here is all I already gave, all you need is information from your customer and their webhoster.

Last not least:

code examples have been how I have learn the best

Did you understand what jay said?

I have use mysql and VFP for develop desktop in LAN Environment

Well, all that code is working if you just change the Server parameter to the database server and make any other necessary adjustments. So Steven, Jay has more than just a bit of sample code, he has a whole application already working. So what is still not understood about that? What to put into the server parameter of the connection string. Well, nobody here can answer that, the customer of Jay has to or their hoster.

There's one more obvious change to do: POut up the whole data into the hosted database, because at first that will be empty. You can use MySQLdump or you can migrate data with the MySQL Workbench or with the tool Mike Gagnon pointed out. Is it still not clear after I said so multiple times?

Chriss

 

[Steve Meyerson]

Did you understand what jay said?

Obviously you imply with that snarky comment that I don't understand what he said nor do I understand anything about servers. I have nowhere near your knowledge level on that subject.

BTW, I wasn't trying to answer his question in detail as you did. I was only trying to offer a small example which I thought might help Jay and maybe someone else. Of course I knew my situation doesn't nearly cover the universe. It works for me.

Didn't expect the criticism. I'll be more careful in the future.

Steve

 

[Chris Miller]

Steve,

the only thing I didn't understand about YOUR comment of my quote is that you criticisme ME of not giving an example while you did. I did give an example, and when you would have read all the discussion you already know Jay has all he needs, he doesn't even need an example anymore, as he has a whole applcation that's full of examples and all he and perhaps also you need to get is that the connecton string needs to change to address an internet database server instead of a LAN databse server. It really is that simple. I get the impression that's still not understood. Of course that makes one mad, if one has told it several times without any reaction to it at all.

Is it that you gave a better example of how to compose a connection string, what you're after Steve, I agree with that, but I pointed out connectionstrings.com, which has all variations of connection strings for any database in the world, on top of article like "Connection strings explained".

Chriss

 

[Steve Meyerson]

that you criticisme ME of not giving an example while you did.

Not at all, Chris! Sorry if you took it that way. Of course I know there is more than one way to connect to a server. Mine was a small example. Didn't read Jay's post close enough. Anyway, I hope it might have helped somebody somewhere.

P.S. My example had an error which I thought I corrected but apparently not. I stated the return value for the connection string would be .T. if successful. Of course the return value would be a positive number (handle) if successful, not .T.

Steve

 

[Chris Miller]

Jay,

now before you go through all the discussion, here are the main points about your problem. To summarize what you told about your situation, let me quote you:

1. The company has also a website with mysql database hosted on the internet.
2. I want to add a feature to my foxpro application, to send data from desktop database to the database hosted on the internet.
3. Any practical suggestion for this matter or link of sources which I can learn ?
4. I have search about cURL, REST API -> is it relevant?
5. I have use mysql and VFP for develop desktop in LAN Environment.
6. Could we use Sqlstringconnect to access the remote database? and use SQLEXEC to insert, update, or delete?
7. As far as i know, mostly the database server/ web server will reject direct sql statements, am I right?

Even though most of this has already been answered, let me give you a summary answer in order of most relevance:

5. As you have an application using a MySQL backend in VFP, all you need to do is change the connection string to connect to the MySQL database in the internet you mention in point 1 and 2.
6. yes
4. no
7. no, today it's more common hosted databases allow remote connections.
2. That's done by changing the Server= part of the connection string, connecton object or DSN your application mentioned in point 5 uses. An ODBC driver cannot only connect to a local/LAN MySQL databse, but also to a database hosted in the internet.
1. Fine, but that's very low information. You should know more details about it. When the contact you have is not a technical person, you need to make contact with their administrator, web administrator, web designer, who ever knows enough about their hosting to give you the essential information you need.
3. Because of 5 you actually only were missing the information I gave in the answer to point 2, a change of the connection strings Server parameter. There might be more changes needed, if the LAN database you programmed for differs in its version from their hosted database, you may also need another driver and change the Driver= part of the connection string, too.

If you want to learn much more, there are even some books for MySQL with VFP from Hentzenwerke, you can find that with google:

https://www.hentzenwerke.com/catalog/mysqlvfp.htm

But again, if you already have an application using a MySQL database, there's nothing new to learn but the simple fact, that the Server=xyz cannnot only be a xyz server name of a LAN but also a domain name or (static) IP adress or something else.

The challenges you might face is that the personal contact you have at your customer has no technical knowledge about their hosting, so that requires a bit fighting through to get in touch with the right person. It's not the only challenge, because a database server they have available as a feature on their site is fine, but will surely not have the data of your application from the LAN, so that needs to move there, which again requires the technical informations necessary to address that database. After you have that, the topic of database synchronization Mike Gagnon posted in the first answer could be helpful, though it is about MS SQL Server and not MySQL.

You'll still be waiting for a sample code you can copy&paste and have a first runnable example of a MySQL connection with a database hosted somewhere not on LAN. Well, you find sites like SQLFiddle allowing you to experiment with online databses, but they won't hand out credentials and server IP addresses or domains which run these databases, because any such concrete connection information is an invitation for hackers. If you really want to experiment and confirm that all you already have continues to work once a MySQL server is in the internet, you find tons of hosters you could try out for free for a limited period, most of the time, or that only cast $1 a month or even host a mysql database for free at some sites, which pop up and vanish again, but could be used just for experimenting. Something like

https://www.freemysqlhosting.net,

they host databases on subdomains like sqlN.freemysqlhosting.net with a user and password you get or specify in an account you create amd then you could start seeing that all you did in your application also works with a datbase in the internet.

I already addressed the case that some hoster doesn't allow remote connections. It's a sign of a bad oster incapable to have a security hardened MySQL server, one sure sign of that is their local only databases allow you to connect with the root user, the super user/admin of any MySQL database, any decent hoster will allow you to have a user that already has limited priviledges to start with, so you can't even grant too much rights accidentally. If you need all options and they want all rights, then they also should have a technically knowledgable person and at least host their own virtual server, if not colocate server hardware at a datacenter of a hoster.

Security concerns? You can make secure connections requiring certificates on both server and client side, so in short ensuring that only the appplication they want to acesss their databse is possible, it's a feature of ODBC/MySQL and it's the way to connect I'd recommend to use.

Chriss

 

[Chris Miller]

Jay, now, should we try to make it easier to build up a connection string?

I'll give you an example of a connection string with masking sensitive informations with ...:

DRIVER={MySQL ODBC 8.0 ANSI Driver};SERVER=...;PORT=...;DATABASE=...;UID=...;PWD=...;

So, does that help? I doubt it does, but with anything more concrete than that, I'd breach sensitive information.
What you can take from this is that
a) It's totally doable to get a connection and run queries with the MySQL ODBC 8.0 ANSI Driver.
b) the names for the ODBC parameters in MySQL connection strings don't differ much from any other database/driver, it's SERVER for the server, and that can be a domain name, subdomain name, ip adress any of which can be on the LAN or [highlight #FCE94F]on the internet[/highlight], so there you have your aspect of [highlight #FCE94F]MySQL databases on the internet[/highlight]. PORT could be the standard MySQL port 3306, it could be changed from that by MySQL server configuration, DATABASE is the name of the database within that server, it's one of the optional parameters, but if you don't specify that, your first command to execute would be switching to a database you want to use or you'd need to fully qualify table names with `databaseame`.`tablename`. The UID is the user, PWD is the password, and there are further ways to authenticate than by user/password, security of connections is a topic you shoud look up and learn about in the MySQL documentation and/or consult an expert in that, not in VFP. Here's a starting point about that:

https://dev.mysql.com/doc/refman/8.0/en/using-encrypted-connections.html

So, can you see how I can't give you sample code you can just copy&paste and run? I won't ever publicly post sensitive information and even if I could set up a MySQL database with no data in it in an "unimportant" domain I'd publish how to get into the MySQL databse engine on some server, might it only be a virtual server. A hacker would perhaps be able to use this with informations he has about vulnerabilities to break out into the next level of the host of the MySQL server, and even if that's just a virtual machine it runs on a real server and has valueable resources a hacker could make use of, connections to other servers to attack from there, etc. So, nobody ever does that.

Some would even argue that giving the driver name is a tid bit of information, but it just shows you can use the 8.0 driver and connect to MySQL server, provided the MySQL server version is sufficiently modern, too. It's not impossible as some may claim.

And how do I want to end here: You can do that.

And once more: You may only need to change the SERVER= portion of your connection and already can use your application with MySQL on the internet. You may need to add the PORT=... parameter to specify the port, you may need to use a different driver and user and password, etc. But nobody here can tell you these detail information. Your customer, someone of their technical employees or some other developer/web developer they hired for their site could do that.

You could even do one simple thing and make the connection string configurable data or use a DSN name you agree on, so they can configure that DSN on clients and you won't need any details about that, when they don't trust you to give sensitive informations. All you need to understand then is that you can use an ODBC connection to a database on the internet and nothing in code needs to change to using a REST API or anything like that.

Chriss

 

[Jay9988]

Hi all,
Thank you all for your suggestion.