MySQL connectivity issues

[Coddiwomple]

Hello all!

I am presently working on a MySQL database. Trying to connect remotely to the server from my client computer using LabVIEW 18. I have tried many configurations along the way, however my UDL won't connect itself to the MySQL server and it's subsequent databases. I have made sure to install or update anything that may cause an architecture mismatch(ODBC drivers, Visual Studio, etc.), which I had hoped would be the answer, but woefully it is not. Any help to get this moving in an appropriate direction would be greatly appreciated.

[Michael_Munroe]

Go to Settings -> ODBC (64-bit) on the client computer and add a System DSN reference.  You can specify all the details of the connection and even test it.  Save this to dsn file and refer to it n LabVIEW.

[rolfk]

Except, watch out about 32-bit and 64-bit difference. If you use LabVIEW 32-bit you need to start C:\Windows\SysWOW64\odbcad32.exe to get the proper configuration for the 32-bit drivers.

Otherwise you configure a 64-bit ODBC DSN and that might refer to 64-bit ODBC drivers that the 32-bit ODBC manager that is called by the LabVIEW 32-bit process can not load and use.

[Coddiwomple]

First and foremost, let me thank both of you for your swift responses. It is greatly appreciated.

I have done this exact thing previously but as RolfK suggested being aware of the variance between 32 and 64 bit odbc sources. I followed your instructions to the letter except using the 32 bit odbc, because I am running 32 bit LV as well as 32 bit Data Connectivity Toolkit. I was able to create the system DSN with a successful connection, although I had to create a new user name on my server side, because for whatever reason the access was denied to my previous login, even though all the same permissions are granted. Still however, the issues persist. When creating the UDL to put into the path for the data connect open in my VI. I select my newly created system DSN from the data source list, enter my credentials and receive the error.... "Test Connection failed because of an error in initializing provider. Catastrophic failure"

My presently selected provider is Microsoft OLE DB for ODBC Drivers which I believed to be the appropriate selection as my System DSN is setup with MySQL ODBC 5.1 Driver

Further details of my present system should they offer any insight..

Server computer is DELL Inspiron running Windows 7. LabVIEW 18. MySQL 5.7 and MySQL Workbench 8.0

Client computer is DELL Optiflex 7010 running Windows 10. LabVIEW 18

Any further ideas or help would be greatly appreciated!

[rolfk]

Why do you think that the Microsoft OLE DB driver will work for a MySQL server?

MySQL is now maintained (owned?) by Oracle, and Microsoft never supported MySQL and now will even less likely. Rather the hell is freezing over first. In the past I always simply setup a connection in the ODBC Admin (right bitness) and then collected the settings from the registry entry and directly passed a connection string with those settings to the ODBC interface. I'm using my own ODBC driver usually, but that should also just work for the Database Toolkit.

[Coddiwomple]

Hahaha.

It appeared the most logical provider to select on the data link properties as my data source is an ODBC and  ALL of the options available on my provider list are Microsoft....Is there a way to create a new provider? Or some other option for me to choose? Am I just missing something(completely possible, haha)?

I have attached an image of my UDL Provider Tab for you to see

Thank you

[Defaphe]

I guess that downloading the ODBC driver for MySQL could help.

https://dev.mysql.com/downloads/connector/odbc/

[Coddiwomple]

I am already using the MySQL ODBC Driver 5.1 and use it to create my System DSN with a successful connection during data source creation. It is in the process of creating the UDL that I get the error messages and cannot connect.

[rolfk]

Are you using the Database Toolkit?

If so use the DB Tools Open Connec (String).vi instead of the one with a path input.

Then enter something along this line as text string input:

DRIVER=MySQL ODBC 8.0 ANSI Driver; SERVER=<your server address>; DATABASE=<database name>; PORT=3306

If you need other options to configure your server connection, try them out in ODBC Admin and store them to a DSN. Then check in the registry under "Computer\HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\ODBC\ODBC.INI\<DSN Name>" what settings are all there and add them to the above string.

Omit WOW6432Node in the registry path if you use 64-bit ODBC Admin and LabVIEW.

If you have a configured DSN Name you can also just pass that name to the DB Tools Open Connec (String).vi but if you work with a fixed driver and database setup I usually prefer to hardcode those settings in the LabVIEW code and avoid having people to edit ODBC DSN entries.

The "MySQL ODBC 8.0 ANSI Driver" may need to be adjusted as well, depending on the installed driver. What name to use you can also find in the registry. 

"Computer\HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\ODBC\ODBCINST.INI\ODBC Drivers" contains the list of drivers known to the ODBC Manager.

The UDN file basically is also just an INI style file with these settings in a list.

[wiebe@CARYA]

I haven't used MySQL, but for other databases ODBC is just an extra layer of complexity.

AFAIK, Everything you can configure in ODBC can be specified in the connection string. At least a proper connection string can connect to SQLServer, Access or Excel just fine without ODBC. I don't know about MySQL, it might depend on the library you're using.

See MySQL Connector/Net Connection Strings - ConnectionStrings.com

You need to be especially careful with ODBC and passwords, as the passwords are stored as plain text in the registry.