• Introduction
• Connect to MySQL
• Setup ODBC
• Troubleshooting
• More Information

Introduction

This article explains how to connect to MySQL with ODBC when using Windows 10.

It covers the following topics:

1. How to connect to MySQL (with code samples).
2. How to setup ODBC:
   • Configure ODBC using the ODBC Data Source Manager:

     We recommend using system DSNs (available to all users).

Connect to MySQL [top]

We strongly recommend using connection objects to connect to databases, and this is what we demonstrate in the code samples.

Follow these steps to connect:

1. Create a new channel to try out the code:
   • Create a channel like this:
     • Name: DB MySQL Connection (or similar)
     • Source: From Translator
     • Destination: To Channel
   • Alternatively use an existing channel:

     Your channel will need a From/To Translator or a Filter component.

2. Connect to a database using ODBC:
   • Paste this code into the Translator:

     If your script uses connection object methods conn:query{} or conn:execute{}to connect to a remote MySQL database, the location of the remote host is specified as part of the name parameter in the db.connect{ } command. If you use conn:merge{} to connect the remote host is specified as part of the name parameter in the conn:merge{} command itself.

     function main()
           local conn = db.connect{   
           api=db.MY_SQL_ODBC, 
           name='your_odbc_server_name',
           user='your_login',
           password='secret',
           use_unicode = true,
           live = true
        }
     
        conn:execute{sql='SELECT * FROM <your table>', live=true}
     end

   • The name is the name of an ODBC source that you create using the ODBC Administrator.
3. Adapt the code to your requirements.

Setup ODBC [top]

To set up a new ODBC data source for MySQL Server:

1. Install the latest MySQL ODBC drivers:
   1. Download and install the Visual Studio C++ 2015 redistributable:

      At the time this was written you specifically need the 2015 redistributable, and using the 2017 redistributable did not work. Future versions of the MySQL ODBC drivers may be compatible with the 2017 redistributable — check the MySQL ODBC documentation.

      • If you have installed a later version of the redistributable (like 2017) you will need to uninstall it first:

        The 2015 installer will not install if it finds that a later redistributable version (like 2017) is already installed. You may wish try the 2015 redistributable install without uninstalling first in case this behaviour has changed.

      • Run the 2015 redistributable installer and accept the defaults.
      • If you need the later version of the redistributable (like 2017) you can install it again:

        You can now safely re-install the later (like 2017) redistributable if you have other programs that depend on it.

   2. Download the latest MySQL installer from the MySQL download site:

      There was no separate ODBC installer for MySQL (at the time of writing). You will need to download the full MySQL installer, and choose the custom option when you run the installer.

   3. Run the installer and accept the defaults, until you get to the Choosing a Setup Type page.
   4. Choose the Custom option:

      You can choose any another option if you wish, as they all include the ODBC drivers. For example you might choose the Client Only option to install all the client tools (and all drivers).

      

   5. Click Next.
   6. Expand the MySQL Connectors and select 64 bit ODBC, then press the green arrow:
      
   7. Click Next, then Execute, then Next, then Finish.
2. Open the 64 bit ODBC Administrator:

   Windows 10 (64 bit version) supports 32 and 64 bit ODBC sources — always use the 64 bit ODBC Administrator.

   • Search for ODBC and choose ODBC Data Sources (64 bit):
     
   • Right Click and choose Run as Administrator:
     
3. Open the System DSN tab and click Add:
   
4. Choose the latest MySQL ODBC driver and click Finish:
   
5. Enter the ODBC credentials:
   • Use any Data Source Name and Description you prefer.
   • Enter the Network Name (or IP) of the MySQL Server in the TCP/IP Server field:
     Note: If you are unsure of this ask your DBA (database administrator).
   • Enter the User name and Password:
     Note: If you are unsure of these ask your DBA (database administrator).
   • Optional: Choose your default Database:
     
6. Test the data source connection:
   • Click Test Data Source…:
     
   • And if it works you are good to go:
     
   • Click OK to create the ODBC data source:
     
7. If the connection does not work speak to your DBA (database administrator) about how to login to the database (user authentication).

Troubleshooting [top]

• Problems with connection settings:

  Speak to your DBA (database administrator) for general connection issues like: User name and password, Database Server network name etc.

• Iguana may not always support the very latest version of the ODBC drivers:

  Please contact us at support@interfaceware.com if you need to know the latest supported version of the ODBC drivers.

• The ODBC install fails:

  This is probably because you need to install the the Visual Studio C++ 2015 redistributable. You will need uninstall any later versions (like 2017) of the redistributable before you can install the 2015 version (you can re-install the later version immediately installing 2015), see the step (1.1) of Setup ODBC above. NOTE: Using the 2017 redistributable does not work (you specifically need 2015 as well). Future versions of the MySQL ODBC drivers may be compatible with the 2017 redistributable — you will need to check the MySQL ODBC documentation.

• Incorrect port number:

  MySQL defaults to port 3306, but a different port can be used. Speak to your database administrator (DBA) or network administrator.

• Using integrated security:

  If you wish to use Windows integrated security speak to your DBA (database administrator).

If you need more help please contact us at support@interfaceware.com.

More Information [top]

• Using Database Connection Objects
• Forum thread on Windows ODBC
• Connect to MySQL with libmysql
• Connect to MySQL from Linux or Mac with ODBC