Connect to MySQL with libmysql

Introduction

This article explains how to connect to MySQL databases for Windows, Linux and Mac.

It covers the following topics:

  1. How to connect to MySQL (with code samples).
  2. How to setup the libmysql shared library:
    • Install libmysql
    • Configure Iguana to use libmysql

If you are having trouble getting Windows to recognize MySQL you should look at the last section Making the MySQL Database Option Visible in Windows.

Note: If you are using a 64 bit Windows you should usually use a 64 bit version of MySQL. If you are using a 32 bit Windows you will need to use an older 32 bit version of MySQL (5.7.24 or earlier).

Connect to MySQL [top]

We strongly recommend using connection objects to connect to databases, and this is what we demonstrate in the code samples. The code samples will work with all operating systems.

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 the libmysql shared library:
    • 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, 
            name='mydatabase@server_name',
            user='your_login', 
            password='secret',
            use_unicode = true,
            live = true
         }
      
         conn:execute{sql='SELECT * FROM <your table>', live=true}
      end
    • The location (name) of the host can be specified as any of the following:
      • The machine name: mydatabase@server_name
      • The server name: mydatabase@server_name
      • The IP address: mydatabase@10.10.10.10
    • If the MySQL server is using a port other the standard port of 3306, you can specify it as part of the name parameter:
      • mydatabase@server_name:1111
  3. Adapt the code to your requirements.

Setup libmysql for Windows [top]

The easiest way to install the drivers on Windows is to use an MSI installer rather than a ZIP archive, as the install process is simpler.

Note: The screenshots in this section are from Windows 10 — and may differ somewhat if you are using a different version of Windows.

  1. Download MySQL:
    • Go to the MySQL download site
    • Download the MSI install file for the latest version of MySQL for Windows

      Note: Iguana may not always support the very latest version of MySQL, so you can simply download an earlier version if necessary. This is particularly true if you are not able to use the the latest version of Iguana.

      If you need to know which versions of MySQL are supported by the version of Iguana you are using please contact us at support@interfaceware.com.

  2. Install the MySQL drivers:
    • Double click the MSI file to open it.
    • Click Yes to allow the installer to make changes on your machine.
    • Choose Client Only to install the client tools which includes the drivers:
      windows mysql install client
    • Click Next.
    • Ignore the two failed Check Requirements:
      ignore failed check items

      • Click Next to continue.
      • Click Yes to continue in the warning dialog box.
    • Follow the remaining steps to finish installing the MySQL client tools:
      • We strongly recommend accepting the default install values.
      • Note: Do not configure MySQL Router — just click Finish.
  3. In Iguana, click the Settings tab.
  4. Click IGUANA Setup > Database to view the available database APIs.
  5. Click the Edit button for MySQL Compliant:
    edit button MySQL
  6. Load the MySQL shared library:
    • Try these library paths:
      • C:\Program Files\MySQL\MySQL Workbench 8.0 CE\libmysql.dll
      • C:\Program Files\MySQL\MySQL Router 8.0 CE\libmysql.dll
      • Otherwise search for libmysql.dll in the MySQL install directory.
    • Paste the path into the Preferred Version text box:
      preferred version
    • If you installed MySQL in a different location (than the default) then you can browse to find the libmysqlclient.dll driver file.

Setup libmysql for Mac [top]

The easiest way to install the drivers on a Mac is to install the full MySQL database server. We recommend using a DMG archive rather than a TAR archive, as the install process is simpler.

Note: The screenshots in this section are from macOS Mojave — and may differ somewhat if you are using a different version of macOS.

Tip: When you install the MySQL on Windows the installer includes a “drivers only” option. There is no “drivers only” option for the MacOS installer — so you need to install the full database engine.

  1. Download MySQL:
    • Go to the MySQL download site
    • Download the DMG install file for the latest version of MySQL for the MacOS

      Note: Iguana may not always support the very latest version of MySQL, so you can simply download an earlier version if necessary. This is particularly true if you are not able to use the the latest version of Iguana.

      If you need to know which versions of MySQL are supported by the version of Iguana you are using please contact us at support@interfaceware.com.

  2. Install MySQL:
    • Double click the DMG file to open it.
    • Double click the package to run it:
      MySQL install package
    • Follow the steps to install MySQL:

      We strongly recommend accepting the default install values.

  3. In Iguana, click the Settings tab.
  4. Click IGUANA Setup > Database to view the available database APIs.
  5. Click the Edit button for MySQL Compliant:
    edit button MySQL
  6. Load the MySQL shared library:

    If you are using an older version of MySQL it may use libmysqlclient_r.dylib instead of libmysqlclient.dylib.

    • Use the default library path: /usr/local/mysql/lib/libmysqlclient.dylib
    • Paste the default path into the Preferred Version text box:
      MySQL preferred version
    • If you installed MySQL in a different location (than the default) you will need to browse to find the libmysqlclient.dylib driver file.

Making the MySQL Database Option Visible in Windows [top]

You must have MySQL installed and the system PATH must be set correctly to ensure that the MySQL database option is visible in the API drop-down menu.

Note: The screenshots in this section are from Windows 2000 — the screens will be different for other versions of Windows.

To make the MySQL database option visible:

  1. Search for “Environment” and choose Edit the system environment variables:
    edit the envirnment variables
  2. The System Properties window will open on the Advanced page:
    system properties advanced
  3. Click the Environment Variables button near the bottom of the page:
    environment variables button
  4. Highlight the System Path variable and click Edit:
    highlight system path
  5. The Edit environment variable window appears.
  6. Add the location of your libmysql.dll file on the next line:
    add libmysql path
  7. Click OK to save your changes.
  8. Restart your computer for the changes to take effect.
  9. The MySQL database option should now be visible in the API drop-down menu.

More Information

Leave A Comment?