Remote acces to MySQL on web server with VFP9

[Eliott]

Hi there,
I mainly use MySQL on the web server for data storage and I read them by using PHP scripts. Until now it worked fine but now I'm looking for a solution where is possible to connect with database on web server from VFP and to read few records or write them there. In order to ensure some security environment I used putty and this program did it OK: on the screen is black terminal window with visible and working prompt; then I disabled ZoneAlarm and even antivirus-software to ensure non-stoppable connection. I installed MySQL ODBC connectivity 3.51.12.00 and my forms in VFP9 on Win7 locally works great and fast, but when I change connection parameter in order to connect with remote MySQL it fails! After start of forms it looks like something will happen but mouse pointer is in state Busy and nothing more. Form is frozen, I can't click anything until I wrote exit in terminal window made by putty, and then I got error message like ODBC doesn't exist (Can't find driver)!
Is there some option to check is connection established, beside classic variable:

query1="SELECT * FROM mytable1"
*parameters:
lcStringConn="Driver={MySQL ODBC 3.51 Driver};Server=&lcServer;Port=3306;Option=16384;Stmt=;Database=&lcDatabase;Uid=&lcUser;Pwd=&lcPassword"
* don't show a window login
*SQLSETPROP(0,"DispLogin",1)
lnHandle=SQLSTRINGCONNECT(lcStringConn)
*if all is fine
IF lnHandle > 0
	thisform.label7.Caption='-connected-'
	thisform.label7.ForeColor=RGB(0,255,0)
	rez=SQLEXEC(lnHandle, query1, "ccursor")
ELSE
	thisform.label7.Caption='-disconnected-'
	thisform.label7.ForeColor=RGB(255,0,0)
   =AERROR(laError)
   ? laerror
   MESSAGEBOX("Error "+CHR(13)+;
              "note:"+laError[2])
ENDIF

It seems like program control never reach IF... part because button command doesn't change its caption. Any help, please? Thank you.

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

 

[Olaf Doschke]

lcStringConn="Driver={MySQL ODBC 3.51 Driver};Server=&lcServer;Port=3306;Option=16384;Stmt=;Database=&lcDatabase;Uid=&lcUser;Pwd=&lcPassword"
lnHandle = SQLSTRINGCONNECT(lcStringConn)

>I got error message like ODBC doesn't exist

Well, if this already errors, you don't have the MySQL ODBC 3.51 Driver installed. Locally.
To make a remote connection via MySQL ODBC you need that ODBC driver locally.

Bye, Olaf.

 

[Olaf Doschke]

Besides, not only Windows and ZoneAlarm (Firewall) need to allow remote connections, but of course MySQL itself.

http://www.cyberciti.biz/tips/how-do-i-enable-remote-access-to-mysql-database-server.html

Especially take a look at step 5 and step 7.

Bye, Olaf.

 

[Eliott]

Hi Olaf,
I can't understand a part described like ODBC isn't installed? Maybe isn't installed properly, but how to explain that part of same application read MySQL tables locally but don't works with MySQL remotely? I tried today more than couple of hours and always I got 1526 error, even when I removed ODBC 3.51.12 and installed 3.51.30 (now it's appears ugly connect dialog to MySQL!)I tried with demo MySQL DB management software and it connected with remote w/o problem. Like something is broken inside VFP, connection string is always same thing, same contents, no complicated formula... strange.
Thanks for your input.


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

 

[Mike Lewis]

Like something is broken inside VFP

Well, it should be easy to verify or eliminate that possibility. Just use another ODBC client (Excel? Access?) with the same connection string. That will at least tell you if it's a VFP problem.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

Option=16384 could be the reason. There are lots of options changing behavior during queries mostly, but some of them might also be important during connection.

One thing is still importatn: If you first connect via SSH or Telnet, and then connect to MySQL from that prompt, you're not connecting remotely to MySQL, you're making a remote connection to the server and local connection to MySQL, then. You really need to open port 3306 not only there but also on your client to be able to get through.

Bye, Olaf.

 

[Olaf Doschke]

Oh, and...

If you get errror 1526 you are already over the hurdle of addressing the ODBC driver.
You look into laError[2], there is a bit of further info in the other array elements 3,4 and 5:

3
Character. The text of the ODBC error message.

4
Character. The current ODBC SQL state.

5
Numeric. The error number from the ODBC data source.

See

http://msdn.microsoft.com/en-us/library/t99d4hhy(v=VS.71).aspx

Especially the text of the ODBC error message will tell you what MySQL has to say to your connection attempt, while array element 2 is only telling you VFPs "point of view".

Bye, Olaf.

 

[Eliott]

Hi Olaf, hi Mike,
In meantime I tried to uninstall ODBC driver and to replace it with better. I used Add/Remove option, then I checked presence of MySQL in Registry, then I shutdown PC, wait few minute and then I installed again 3.51.12, a version with VFP worked months ago remotely without problems. I went into c:\windows\system32 and execute registration of ODBC driver with

myodbc3i -a -d -t"MySQL ODBC 3.51 Driver;DRIVER=myodbc3.dll;SETUP=myodbc3S.dll"

as now VFP started to inform me with error Driver could not find driver {MySQL ODBC 3.51} in system information, then followed with error code 1526. Simply I don't get it, what could be wrong, and why it's so complicated?
Guys, thank you for your inputs, I'll try in meantime your suggestions.

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

 

[Eliott]

Update: when I start [highlight #FCAF3E]MySQL 5 server locally[/highlight], on PC running under Win7 I simply connect with same connection strings then [highlight #FCAF3E]I'm able to read & write data into local MySQL[/highlight] database/tables. So, I concluded that ODBC is OK and working, just remotely from some reason it doesn't work. Is there some way to check that maybe my ISP closed a port or protocol I used earlier and when I was able to connect to remote MySQL? Thank you.

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

 

[Olaf Doschke]

I never did such a thing with any ODBC driver version, manually registering any DLL. You get a Windows setup from mysql you can just use 1:1 and have no problems or work to do with registering a DLL.

Bye, Olaf.

 

[JRB-Bldr]

This is a totally wild guess....

I went into c:\windows\system32 and execute registration of ODBC driver with...
and then later on you say
PC running under Win7

Could it be that you are encountering the 32-bit/64-bit issue where your 32-bit MySQL OBDC driver must be installed into the Windows\SysWow32 directory?
See:

http://www.interworks.com/blogs/dsm...-32bit-odbc-connection-64bit-operating-system

Good Luck,
JRB-Bldr

 

[Eliott]

Hi Olaf,
yes I never did registering any software components before, as you said, but I had a wish to try everything in order to find out problem. I'm now (again) on 3.51.12 and it works locally great and started to work also remotely via Putty but not by command line way but via Putty GUI, what's very uncomfortable because almost anyone could enter some wrong data and crash the connection. I tried many time this way and each time worked as charm. Now, in the air is problem how to take out involved parameters into Putty configurations window and to make command line version suitable for call from VFP app...
Thank you for your valuable inputs, suggestion and ideas.

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

 

[Olaf Doschke]

Well, as said before, this solution is not really using the database remotely.

Putty is a secure shell and you connect to the remote PC this way. This is done via ports open, eg port 80, so you have no firewall problem. Once you are remotely connected to the pc in putty you use the MySQL Server locally from there.

If you want to use MySQL local, you don't need putty, but you need to configure MySQL as stated in the link I gave you earlier:

http://www.cyberciti.biz/tips/how-do-i-enable-remote-access-to-mysql-database-server.html

In short this includes: Configuring MySQL to allow remote connections, configuring the server's firewall to allow to connect to the mysql port 3306, configuring the local client firewall to allow connections through port 3306.

If you go through putty you don't use the database remotely, but the whole server.

Bye, Olaf.