Creating an ODBC data source connection

In this example we will configure a connection to the MySQL database management system. For information on setting up connections to Oracle or Microsoft SQL Server see the Lua CRUD Jumpstart and Lua Scripting Jumpstart documents respectively.

Installing MySQL

Go to the MySQL download page and download the MySQL installer to your computer. It is better to download the “web”version which is smaller and will only download the necessary modules during the installation procedure.

  1. Once downloaded, open the installer and accept the License terms and press Next.

  2. On the next page of the installation dialogue choose the Custom install option and press Next.

    MySQL Installer - Setup Type
    Figure 1. MySQL Installer - Setup Type

  3. For this example we will install the following MySQL products:

    • MySQL Server

    • My SQL Workbench

    • Connector/ODBC

    Find these in the expandable trees in the Available Products part of the Select Products and Features section (MySQL Servers, Applications and MySQL Connectors) and add them to the installation pane using the arrows. Choose the 32 bit (x86) or 64 bit version depending on your system. Click Next to continue.

    MySQL Installer - Select Products and Features
    Figure 2. MySQL Installer - Select Products and Features

  4. Click Execute in the Installation window to begin download and installation of the selected components. Once the Status for all components is complete, click Next.

    MySQL Installer - Execute Installation
    Figure 3. MySQL Installer - Execute Installation

  5. The next section of the installation wizard indicates the products thet need to be configured. In this example we will only configure the MySQL Server. Click Next to go to the Server Configuration wizard.

    MySQL Installer - Type and Networking
    Figure 4. MySQL Installer - Type and Networking

  6. The Type and Networking section of the wizard can be left as default. Click Next to continue.

  7. In the Accounts and Roles section enter a password for the MySQL “root” account. This can be whatever you like but remember to make a note of it as you will need it to access the database later.

    MySQL Installer - Accounts and Roles
    Figure 5. MySQL Installer - Accounts and Roles

  8. The Windows Service and Plugins and Extensions sections can be left as default so click Next to the last page of the Server Configuration wizard and click Execute to apply the settings.

    Apply Server Configuration
    Figure 6. Apply Server Configuration

  9. Once all Configuration Steps have been applied, a green tick will be displayed by each of the steps. Click Finish to return to the Installation wizard, then Next to confirm the configuration and finally Finish again to complete installation and launch the MySQL Work-bench application.

    MySQL Installer - Installation Complete
    Figure 7. MySQL Installer - Installation Complete

Configuring MySQL and the ODBC connection

The MySQL Workbench should automatically launch after installation. If not, open the application to display the home screen.

MySQL Workbench
Figure 8. MySQL Workbench

  1. Click on the “Local instance MySQL” and enter the root account password that you set during the installation. In the MySQL server instance window click the Create new Schema button in the menu bar:

    MySQL Workbench – Create New Schema
    Figure 9. MySQL Workbench – Create New Schema

  2. Name the new schema test and click Apply to bring up the SQL script review. Click Apply again to create the schema and then Finish.

    Create Schema SQL command
    Figure 10. Create Schema SQL command

  3. The new schema should be visible in the SCHEMAS section of the Navigator panel.

    Schema in MySQL
    Figure 11. Schema in MySQL

Creating a System DSN

To create a System DSN (Data Source Name) to be used by the ODBC in Windows go to the Administrative Tools section of Control Panel under System and Security, and open ODBC Datasources (32 or 64 bit depending on your system).

  1. Click on the System DSN tab and click Add.

    Add System DSN
    Figure 12. Add System DSN

  2. In the Create New Data Source dialogue, select the MySQL ODBC 5.3 Unicode Driver and click Finish.

    Select MySQL Driver
    Figure 13. Select MySQL Driver

  3. In the MySQL Connector/ODBC Data Source Configuration dialogue, name the data source and fill in the details including the root password you configured during the setup. Select the Database that you added as the Schema in the last section.

  4. The new System DSN is now displayed in the ODBC Data Source Administrator dialogue. Click Ok to continue