VFP9 connection to remote MySQL at hosting company 1

[Eliott]

Hi there,
I bought at hosting company some web places where I have installed MySQl database and pile of PHP pages that works fine. In my house I have running VFP9 app that operate with free tables, also working fine. When I wish to upload some fresh data from client PC to MySQL I use PHP pages for upload file and after explode I insert data into MySQL. In the case that I need data from MySQl tables to client PC then I also use PHP code that download file into reserved folder on client PC and then VFP read that file, parse data and insert into VFP table. I wished to direct access to MySQL DB/tables as I have al necessary data but classic approach with server IP, username, password, socket doesn't work. I installed MySQL on client PC and then VFP regularly read data from them, but from remote I got always error. I tried to establish VPN based on known IP of hosting server, my username and password but was unable! I checked cpanel at my hosting place but saw nothing what I don't already knew. Is there some logical and step by step solution how to setup VPN or connect VFP and MySQL at hosting place, I'm using MySQL ODBC driver localy and works fine. Any help, please!?

There is no good nor evil, just decisions and consequences.

 

[alan92rttt]

You can connect via ODBC to a remote mySql server. To do it your host has to change their configuration.

Most hosts do not allow remote connections to their sql servers. I have only worked with one would allow that kind of connection.



Alan

 

[Mike Lewis]

Eliot,

My experience is similar to Alan's. I've tried to do what you are doing, and came up against the security policies of the web hosting company. They simply won't allow ODBC access from outside the site.

One solution would be to write PHP code (on the web server) to do the updating; then, in VFP, instantiate a web browser control programmatically, and have that control execute the PHP. I've never tried to do that, and don't know how easy it would be.

Another possibility would be to have VFP generate a script that runs against MySQL. For example, if you want to update MySQL with data from VFP, the script would be a series of INSERT or UPDATE statements. You could then upload the script to the PHPAmin page, which will let you run it against the database.

It's all a bit of a hassle - and one of the reasons I don't like using MySQL on a hosted site.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[Eliott]

Thank pals for your inputs. I wondered by days why it not worked even I have all necessary access data..!? Now, from my side of view it's almost wasted space in VFP to have option to connect remote server and not to be in situation to do it for real! In this situation "remote server" is when a server is in another room not in other city or state. Don't know what to do if hosting co won't to allow such way of access. How to set up some kind VPN in order to get same result? Is it all up to me or again up to hosting co?
Thanks.

There is no good nor evil, just decisions and consequences.

 

[GriffMG]

Would that be the same for SQL Server Mike?


Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[Mike Lewis]

Would that be the same for SQL Server Mike?

Martin, it depends on the hosting company and their security policies. When I was having the hassles with MySQL, I determined that things would be easier with SQL Server (but in that particular case, the licensing costs of SQL Server were unacceptable to the client). But that might not apply to other hosting companies.

On another project, I've been able to access a web-hosted instance of SQL Server from VFP, by using a web service (written in C#). But I only had to deal with the VFP side, so can't give any details of how to set it up.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[GriffMG]

That's pretty much what I expected Mike...

I think this is probably a simple security issue - I presume (I don't know) that the odbc connection probably isn't on the same port as ftp & http, and most ISPs/Hosts would restrict access to just ftp and http (maybe some more, but probably not the odbc ports).



Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[Mike Lewis]

I think this is probably a simple security issue - I presume (I don't know) that the odbc connection probably isn't on the same port as ftp & http, and most ISPs/Hosts would restrict access to just ftp and http (maybe some more, but probably not the odbc ports).

That would be my guess as well.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[Eliott]

I asked from hosting company for establishment of VPN and I got today answer:

Unfortunately we couldn't allow you VPN service, but if you could make application that use SSH tunneling then it would be granted.
I don't understand now what opinions this option offer in my case. How I shall to create my VFP9 app in order to use this hosting option? I have read some articles about SSH tunneling but I stuck in try to find out solution.
Can somebody of you help me or directed to somewhere for explanation? Thank you.

There is no good nor evil, just decisions and consequences.

 

[HostingXchange]

You're right, it is a simple issue. MySQL will accept ODBC connections on port 3306 if configured properly. I've written VFP apps that depend on remote updatable views to a MySQL table with great success. It's not that hard to get around the security issue if there's enough interest. You simply pool your funds and get a VPS (private server). A good one will cost around $35 per month without CPanel. 49.00 a month with. (Other control panels are available for free.) If interested, just let me know, I can have you up and running within an hour.

Regards,

Karl

http://hostingxchange.com

- Fast, Easy, Reliable and Affordable Hosting

 

[Eliott]

@HostingXchange: Karl, my friend, thank you for your input. I checked your offer on mentioned web site, it's cost twice more than I paid for my present host and with better offer (domain, sub-domain, parked domain, MySQL...)...
I think I will try to find out some third solution or I'll keep exchange data between VFP & remote MySQL by download of files.

There is no good nor evil, just decisions and consequences.

 

[HostingXchange]

No doubt there are less expensive solutions out there Eliott. I too could offer such packages and make them look very attractive, but then I would have to do as other hosts do and limit the number of connections to the server (ever had a MySQL timeout or site just freeze up on you?), limit bandwidth (read, sluggish page load times) and not provide value added services like allowing ODBC connections or assistance with making them work for your needs. It truely is a get what you pay for environment my friend. I can provided you with a $17.50 per month private server if that's what you want, but I want you to know there will be problems using a server like that no matter who you get it from. It's all a matter of economics and truth in advertising. If you'd still like to try one out at that price, just let me know. I'd be willing to invent something for you if it meets a need.

http://hostingxchange.com

- Fast, Easy, Reliable and Affordable Hosting

 

[RandyRiegel]

If you are using a host that has Cpanel you can set "Remote Host Access" to "%" and will allow connection from ANY ip address instead of just local host. You can also hard code specific IP addresses that you allow to connect. I use MySQL Workbench installed on my machine to create tables, etc. all the time to a MySQL DB located on my VPS.

The above works on Linux based hosting using Cpanel, I have no experience on Windows based hosts so couldn't tell you if there is something similar on them.

 

[HostingXchange]

Setting your Remote Host Access to % means anyone in the world can attempt connections to your MySQL server Randy. A much better idea is to limit connections to a specific host name or IP address. If you have a dynamic IP address you want to connect from, just go to dyndns.org and set up a free host name. You can then use your dyndns hostname instead of an IP address for access. This would also allow you better security overall as you could use csf or apf to limit connections to ftp and ssh as well. Much safer than relying on the MySQL security system alone.

http://hostingxchange.com

- Fast, Easy, Reliable and Affordable Hosting

 

[Olaf Doschke]

There are two main technical barriers. One is to what hosts MySQL itself does allow remote access, the other one is, if the MySQL Port is opened by the hosting company. You might be able to set up other or all IP adresses, but if the MySQL Port is closed, it's not reachable from remote. You'll have to talk about that with the hoster anyway, or look for a hosting package specifically dedicated to remote database access. Alternatively hosting of a root server/virtual root server, for which you can open ports. With virtual servers it still depends what ports are open by the hoster in general. Dedicated Database hosting is perhaps the best option.

Bye, Olaf.

 

[JRB-Bldr]

HostingXchange -
"If interested, just let me know, I can have you up and running within an hour."
Providing your input/advice/suggestions is most welcome.
Promotion of your services is not.

You might want to look right below the Your Reply - Submit Post button:
Promoting, selling, recruiting and student posting
are not allowed in the forums.

JRB-Bldr

 

[HostingXchange]

Hosts that allow remote odbc connections are becoming hard to find, I thought I might be doing the community a service. Sorry about that.

http://hostingxchange.com

- Fast, Easy, Reliable and Affordable Hosting

 

[Eliott]

Wow, plenty information. Thank you. I'll contact again my hosting company but as they stated that only SSH is possible I'm afraid that they closed MySQL port for remote access even in cPanel is RemoteHost option. Hosting is under Linux. Just my client computer is under WinXP from where I wanted to connect to host computer with MySQL.
Thanks again.

There is no good nor evil, just decisions and consequences.

 

[HostingXchange]

You might find this tutorial useful. It uses cygwin to set up an ssh tunnel to connect via odbc to a mysql database. This would only require that ssh be available on the remote host and that you had the ability to set the correct permissions for the mysql user. (IOW Port 3306 doesn't need to be open on the remote host) I haven't tried it, but it should give you the information you need to get started:

http://www8.pair.com/dmurdoch/mysql.html

http://hostingxchange.com

- Fast, Easy, Reliable and Affordable Hosting