Connect to other databases with ODBC

Introduction

This article explains the general procedure for connecting to other databases using ODBC. It includes instructions for Windows, Linux and Mac.

Note: This process should work with all ODBC database drivers — though we do not guarantee it will work for unsupported databases.

If you need help with setting up ODBC please contact us at support@interfaceware.com.

It covers the following topics:

  1. How to connect to a database (with code samples).
  2. How to setup ODBC:
    • Install an ODBC manager:

      We use unixODBC for Linux and iODBC for Mac.

    • Configure ODBC:

      We discuss the how to configure ODBC for Windows, Linux and Mac.

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 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. We use a little trick as there is no “db.OTHER_ODBC” option we can use any ODBC type connection like db.ACCESS or db.DB2 etc.

      function main()
            local conn = db.connect{   
            api=db.ACCESS,           -- "ODBC Cheat" - this (or another ODBC connection type) will work with most ODBC connections
            name='your_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 same as the server entry in the odbc.ini file.
  3. Adapt the code to your requirements.

Setup ODBC [top]

Note: Iguana may not always support the very latest version of the ODBC drivers the database, 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.

CentOS:

Note: The procedure and screenshots in this section are from CentOS 7 — and may differ somewhat if you are using a different version of CentOS , or another Red Hat based Linux.

  1. Install the unixODBC package:
    1. Terminal Command: Get the latest package info:

      yum check-update

    2. Terminal Command: install unixodbc:

      sudo yum install unixODBC.x86_64

  2. Install the ODBC drivers for the database:
    1. Terminal Command: Install ODBC package:

      sudo yum install <odbc package for database>

    2. Alternatively you might need to download and unpack a ZIP file or TAR archive.
  3. Check file names and create symbolic links to the versioned file names if needed:

    You may need to create a symbolic link to a versioned driver file, or to a driver file you install in non-standard location. Links (or driver files) go in /lib64 in CentOS.

    sudo ln -s /<path>/<source driver> /usr/lib64/<unversioned driver name>
  4. Create the odbc.ini file entries:

    Warning: The ODBC parameters for different database can vary you may need to experiment and/or check the documentation for the database you are connecting to.

    In particular some some databases use Server to identify the server name, whereas other databases use Servername.

    1. Format your entries something like this:
      • Bare minimum:
        Note: Change [My <other DB> connection name] to the name you prefer for your ODBC connection.

        [My <other DB> connection name]
        Driver = /usr/lib64/<odbc driver file name>
        Server = your_server_name
        Port   = <port for DB>
      • With user details etc:
        Note: The <<comments>> in double angle brackets should not be included.

        [My <other DB> connection name]
        Driver   = /usr/lib64/<odbc driver file name>
        Server   = your_server_name <<network name or IP address for the server>>
        Port     = <port for DB> <<if omitted the default port will be used>>
        Database = dbname <<if omitted the default database will be used>>
        Username = dbusername <<if omitted then integrated security is used - needs kerberos>>
        Password = dbpassword
  5. Check the location of ODBC ini files on your system:
    1. Terminal Command: Check the locations:
      odbcinst -j
    2. Usually the ODBC ini files are in the default locations:
      • System ODBC ini file: /etc/odbc.ini
      • User ODBC ini file: is in the user home directory (/home/parallels/.odbc.ini)
        odbc ini locations
  6. Append your entries to the ODBC ini file:

    We generally recommend creating System DSNs (available to all users) rather than User DSNs (only available to the current user).

    1. Create a System DSN:
      1. Append your ODBC entry to the System ini file you identified in step (5)
      2. Open the file with a text editor and paste your entry at the end.
      3. Alternatively: Use the unixODBC odbcinst utility to append your entry:
        1. Save your entry to a text file (unixODBC calls this as a “template file”)
        2. Terminal Command: Append the entry from your template file:
          odbcinst -i -d -h -f template_file
    2. Create a User DSN:
      1. Append your ODBC entry to the User ini file you identified step (5)
      2. Open the file with a text editor and paste your entry at the end.
      3. Alternatively: Use the unixODBC odbcinst utility to append your entry:
        1. Save your entry to a text file (unixODBC calls this as a “template file”)
        2. Terminal Command: Append the entry from your template file:
          odbcinst -i -d -l -f template_file
    3. An ODBC ini file will look something like this after a new entry is added:
      new odbc.ini unsupported DB entry
  7. If the connection does not work speak to your DBA (database administrator) to troubleshoot the settings.

Ubuntu:

Note: The procedure and screenshots in this section are from Ubuntu 18.04 — and may differ somewhat if you are using a different version of Ubuntu, or another Debian based Linux.

  1. Install the unixODBC package:
    1. Terminal Command: Get the latest package info:
      sudo apt update
    2. Terminal Command: Install unixodbc:
      sudo apt install unixodbc
  2. Install the ODBC drivers for the database:

    Check the database documentation for the correct procedure to install ODBC drivers.

    1. Terminal Command: Install ODBC package:
      sudo apt install <odbc package for database>
    2. Alternatively you might need to download and unpack a ZIP file or TAR archive.
  3. Check file names and create symbolic links to the versioned file names if needed:

    You may need to create a symbolic link to a versioned driver file, or to a driver file you install in non-standard location. Links (or driver files) go in /usrlocal/lib in Ubuntu

    sudo ln -s /<path>/<source driver> /usr/local/lib/<unversioned driver name>
  4. Create the odbc.ini file entries:

    Warning: The ODBC parameters for different database can vary you may need to experiment and/or check the documentation for the database you are connecting to.

    In particular some some databases use Server to identify the server name, whereas other databases use Servername.

    1. Format your entries something like this:
      • Bare minimum:
        Note: Change [My <other DB> connection name] to the name you prefer for your ODBC connection.

        [My <other DB>  connection name]
        Driver = /usr/local/lib/<odbc driver file name>
        Server = your_server_name
        Port   = <port for DB>
      • UNICODE with user details etc:
        Note: The <<comments>> in double angle brackets should not be included.

        [My <other DB> connection name]
        Driver   = /usr/local/lib/<odbc driver file name>
        Server   = your_server_name <<network name or IP address for the server>>
        Port     = <port for DB> <<if omitted the default port will be used>>
        Database = dbname <<if omitted the default database will be used>>
        Username = dbusername <<if omitted then integrated security is used - needs kerberos>>
        Password = dbpassword
  5. Check the location of ODBC ini files on your system:
    1. Terminal Command: Check the locations:
      odbcinst -j
    2. Usually the ODBC ini files are in the default locations:
      • System ODBC ini file: /etc/odbc.ini
      • User ODBC ini file: is in the user home directory (/home/parallels/.odbc.ini)
        odbc ini locations
  6. Append your entries to the ODBC ini file:

    We generally recommend creating System DSNs (available to all users) rather than User DSNs (only available to the current user).

    1. Create a System DSN:
      1. Append your ODBC entry to the System ini file you identified step (4)
      2. Open the file with a text editor and paste your entry at the end.
      3. Alternatively: Use the unixODBC odbcinst utility to append your entry:
        1. Save your entry to a text file (unixODBC calls this as a “template file”)
        2. Terminal Command: Append the entry from your template file:
          odbcinst -i -d -h -f template_file
    2. Create a User DSN:
      1. Append your ODBC entry to the User ini file you identified step (4)
      2. Open the file with a text editor and paste your entry at the end.
      3. Alternatively: Use the unixODBC odbcinst utility to append your entry:
        1. Save your entry to a text file (unixODBC calls this as a “template file”)
        2. Terminal Command: Append the entry from your template file:
          odbcinst -i -d -l -f template_file
    3. An ODBC ini file will look something like this after a new entry is added:
      new odbc.ini unsupported DB entry
  7. If the connection does not work speak to your DBA (database administrator) to troubleshoot the settings.

Mac:

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

  1. Install the MySQL ODBC drivers:
    1. Download the ODBC drivers for your database.
    2. Install the downloaded ODBC drivers for your database.
  2. Setup the unixODBC driver manager:

    We need to use unixODBC because the default Apple driver manager does not work with some databases, see Setup unixODBC on the Mac for more information.

  3. Create the odbc.ini file entries:

    Be aware that the ODBC parameters in odbc.ini entries can vary for different databases. In particular MySQL and Microsoft SQL Server use Server to identify the server name, whereas some other databases use Servername (for example Oracle and PostgreSQL). It is important to use the correct parameters for your database or the ODBC connection will not work correctly.

    1. Format your entries like this:
      • Bare minimum :
        Note: Change [My UNICODE MySQL connection name] to the name you prefer for your ODBC connection.

        [My <other DB> connection name]
        Driver = /usr/local/<odbc driver file name>
        Server = your_server_name 
        Port   = <port for DB>
      • With user details etc:
        Note: The <comments> in angle brackets should not be included.

        [My <other DB> connection name] 
        Driver = /usr/local/<odbc driver file name>
        Server = your_server_name <network name or IP address for the server> 
        Port     = <port for DB> <<if omitted the default port will be used>>
        Database = dbname <if omitted the default database will be used> 
        Username = dbusername <if omitted then integrated security is used - needs kerberos> 
        Password = dbpassword
  4. Check the location of ODBC ini files on your system:
    1. Terminal Command: Check the locations:
      odbcinst -j
    2. In this case the ODBC ini files are in the default locations:
      • System ODBC ini file: /usr/local/etc/odbc.ini
      • User ODBC ini file: is in the user home directory (/Users/julianmuir/.odbc.ini):
        odbcinst -j
  5. Append your entries to the ODBC ini file:

    We generally recommend creating System DSNs (available to all users) rather than User DSNs (only available to the current user).

    1. Create a System DSN:
      1. Append your ODBC entry to the System ini file you identified in step (5)
      2. Open the file with a text editor and paste your entry at the end.
      3. Alternatively: Use the unixODBC odbcinst utility to append your entry:
        1. Save your entry to a text file (unixODBC calls this as a “template file”)
        2. Terminal Command: Append the entry from your template file:

          odbcinst -i -d -l -f template_file

    2. Create a User DSN:
      1. Append your ODBC entry to the User ini file you identified step (5)
      2. Open the file with a text editor and paste your entry at the end.
      3. Alternatively: Use the unixODBC odbcinst utility to append your entry:
        1. Save your entry to a text file (unixODBC calls this as a “template file”)
        2. Terminal Command: Append the entry from your template file:

          odbcinst -i -d -h -f template_file

    3. An ODBC ini file will look something like this after a new entry is added:
      new ODBC entry
  6. If you have problems configuring the ODBC driver or connection speak to your DBA (database administrator).

    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.

    • 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:

      Kerberos is needed on Linux, this SQL Server page Using Integrated Authentication has some information

    • Versioned Packages:

      When a package installs a shared library, it can append a version number, so double check the actual library names and if needed symlink them to the library names shown in the previous section (libtdsodbc.so & libtdsS.so), for example:

      sudo ln -s /usr/lib/x86_64-linux-gnu/libodbc.so.1 /usr/lib/x86_64-linux-gnu/libodbc.so

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

    More Information [top]

Tagged:

Leave A Comment?