VFP 8 ODBC connection to MySql 2

[saahg]

Hi All

I am using the sqlstringconnect function successfully to access a 5.6.17 MySql Server - locally. I am also using the MySql 5.3.11 ODBC Driver. The user has a host definition of "%" so connection from any IP is suppose to be possible. The sqlstringconnect content is "Driver={MySql ODBC 5.3 Unicode Driver};Server=192.168.1.141;Port=3306;Database=xxxx;User=Billy;Password=xxxxx;Option=3;" where 192.168.1.141 is the IP address of the system where MySql Server is installed. The connection works fine until I try to connect using this same sqlstringconnect content from a remote system on the LAN. To simplify the connection I am executing the same .exe in both the local and the remote cases. The remote system is executing the .exe by way of a client:server environment - mapped drive starting in the same folder! The ODBC setup is a System DSN with the same setup as the sqlstringconnect content (arguments). The test function under the System DSN configure option works because it is on the MySql Server system. If I install the identical ODBC driver on the remote system with the identical setup and test the System DSN configuration - it fails. And Billy is a user with the same password on both the "local" and the "remote" system. I am somewhat convinced that this is a "credentials" issue but I am not sure what layer is blocking the connection. Any help would be greatly appreciated.

 

[atlopes]

Make sure you're setting a 32-bit ODBC DSN. If you're using the 64-bit one (that is, the one the system will go for, normally), a mismatch architecture error will be triggered. The version you must use is located at %windir%\sysWOW64\odbcad32.exe.

If you're already doing this, then after a failed connection, issue AERROR() and inspect the result to be sure of what's keeping the connection to be made.

 

[Neil Toulouse]

Also check if your MySQL server is allowing your IP address in. I had a very similar issue attempting to connect to MySQL on my web server. Not sure it is relevant to you, but I had to add the IP of the machine trying to connect via the "cPanel -> Remote MySQL" menu option and then it all worked.

I like work. It fascinates me. I can sit and look at it for hours...

 

[Neil Toulouse]

Apologies - just re-read the post - you have already done that. Might be worth double checking though.

I like work. It fascinates me. I can sit and look at it for hours...

 

[Olaf Doschke]

IP blocking or explicit allowing from the server side could also have no effect, if the connection is blocked in general by your hoster. Not every hoster will allow to make remote connections and your overrides don't do nothing to that, if there is a general blockade of remote connections.

I'd look into FAQs of your hoster.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[saahg]

Thanks guys for all of the posts. I will try to answer in sequence.
To atlopes:
There are two Data Source Administrators on the "Server". A 32 bit and a 64 bit. I configured the 32 bit System DSN. The DSN in the System DSN is the 5.3.11 32 bit Driver.
To Neil Toulouse:
I think you have answered your question. Just for clarity, however, and for my edification the fact that the connection works on the Server using the Server IP address and not localhost I think implies that the Server is allowing the IP address. If I go to DOS and CD to c:\wamp\bin\mysql\mysql5.6.17\bin and issue the following command mysql -h 192.168.1.141 -u Billy -p I am prompted for the password and upon entering the correct password connected to the mysql server. I don't think this is a "network issue". Somewhere in the "stack" the credentials are failing.
To Olaf Doschke:
By "the hoster" I am assuming you are referring to the mysql server system. This is a FoxPro LAN (client:server) environment totally excluded from the Internet world.
Since the mysql server is accepting the ip address and the Billy's credentials locally I have to assume that "the hoster" is not blocking them. In addition, since I am running the application from the mysql server system the only loose link I can see is the inability to find the DSN definition on the mysql server system?
This is what AERROR says - classic 1526.
1526
Connectivity error: [Microsoft] [ODBC Driver Manager] Data source name not found and no default driver specified
[ODBC Driver Manager] Data source name not found and no default driver specified.
IM002
0
1
.NULL.
As I am re-investigating this issue it is becoming a little clearer that the real problem is the client:server connection is not seeing the ODBC setup. If this is the problem then how do I make it "seeable"? In the name of eliminating ambiguity I did NOT install a DSN/ODBC setup on the remote system. Still searching.

​

 

[atlopes]

[tt]saahg[/tt], the system DSN must be set in the same PC where the executable is running (no matter where it is stored). It's the same silent error you'll get if you run

m.ODBC = SQLCONNECT("SomeReallyWeirdDSN")

(assuming, of course, that you don't have a DSN named SomeReallyWeirdDSN in your system).

 

[Olaf Doschke]

There are two Data Source Administrators on the "Server".

Well, the DSN has to be defined at the client making the connection, not on the server. "Data source name not found" is pretty clear: The client side trying to use a DSN name you defined server side is not finding it. when you make a connection with DSN what's searched is local System DSNs.

Forget what I said about hosters, I assumed you were not talking about a LAN situation.

Network admins often say they llike to have DSNs, as tehy can control and reconfigure them. That's one side of it, but you have to have them on every single client. So that also involves some way of ditributing sch system DSNs to all users.

As a developer I find it much easier to use a DSNless connection string, that can be stored in an INI or a DBF or anything that could also be somewhere central in one place where you only need to maintain it once for every user.

See

https://www.connectionstrings.com/mysql/

I assume you do the following in your tests: You connect to the FoxPro LAN server via remote desktop (or any other way) make your DSN test your software connects three. Fine. In principle, and if allowing remote IPs works as you intend, this would also work on clients, but they also need that DSN, a System DSN is nothing that is shared from any PC, client or server.

DSN - there is a little possibility for getting that wring, in this case is Data Source Name.

Bye, Olaf.



Olaf Doschke Software Engineering

https://www.doschke.name

 

[saahg]

Many Thanks to atlopes, Olaf, and Neil.
I setup the DSN on the remote/client system and after setting the firewall inbound rule to accept port 3306 I was able to connect to MySql on the Server via the DSN/ODBC connector. This was my first trek into the ODBC world so my confusion regarding where the DSN had to reside must reside in "amateur". I should have picked up on it because I have a number of vb.Net packages (that I wrote) that FoxPro interfaces with, all resident on a Server, that use the Office suite and the Office software must reside on the client. This project is actually the start of the migration from FoxPro to vb.Net so my thanks to you guys in spades. You guys did the windex move! For the sake of knowledge and communication the LAN application is strictly client:server (drive mapping) - no RDP. Again, thanks guys!!
Respectfully,
saagh