Manage and share data from your MySQL databases.

What are MySQL and G8CC.

MySQL is a popular open source database. It's used in the Public Sector and Businesses of all sizes.

GARNET8 Collaboration Clouds (G8CC) provides a powerful data sharing layer. It allows you to seamlessly integrate and extend data value. That lowers delivery and ongoing costs.

This demonstrates how to integrate and extend a MySQL database. G8CC provides a simple solution to modernise legacy databases.

The screencast above shows how easy it is to integrate MySQL as a data source for G8CC. Below is a simple to follow tutorial. This tutorial is a step by step guide to integrate MySQL as a data source.

In this "how to" we will use the employees example data for MySQL. You can download the data here or view the schema structure here.

We will use the employees table. This has the following column names:

• emp_no (Primary Key)
• birth_date
• first_name
• last_name
• gender
• hire_date

In this example we will use columns emp_no, first_name and last_name.

First you need to add a MySQL connector to G8CC.

To do this your need:

• A MySQL database and G8CC node running.
• The following details for your connector.
  • Username and Password of the MySQL database.
  • Host name. This can be an IP address, DNS name or "localhost" if your MySQL database is running locally.
  • Port your MySQL database is listening on. The default port for MySQL is 3306.
  • Database name that you're using.
• Two SQL queries. The first is a SELECT query to list information. The second is a SELECT query to read a specific row. These get explained in greater detail below.

Once you have these you can login to your G8CC administration interface. In the menu on the left your see a database icon. This is highlighted below with a green border (Fig 1.0). Click this to open your integrated data sources.

Fig 1.0

If you don't have any integrated data sources your have a box with a plus sign in it (Fig 1.1). You click this to add a new external data source connector. To start your configuration click the plus sign then Databases and MySQL.

Fig 1.1

Step one.

In the first dialog box you add the connectors title and description (Fig 1.2). The title gets displayed in a number of places. These include the integrated data source list and App Wizard integrator. The title should be descriptive.

Fig 1.2

Step two.

In this step configure the connection details (Fig 1.3). This defines what to connect to and how to authenticate.

Fig 1.3

Step three.

You now defined which G8CC containers can use this integrator (Fig 1.4). This provides flexibility and allows you to govern data.

Fig 1.4

Step four.

This is our first SQL query. This query populates a list of options to choose. This query must include a column name "id". The "id" should be a primary key. Our select query will use the employees table. The table has a primary key "emp_no". We use "AS" to define this as "id" in our column names.

The following SELECT query selects emp_no, first_name and last_name columns. You can use one or more WHERE conditions. We use a LIKE operator. The question mark (?) is used to define a variable in our SQL statements.

Step five.

G8CC will build a read query based on what is in step four. You can add additional columns and WHERE operators. The column names get used by the App wizard. If these aren't descriptive use "AS" then a new name.

We will use the same query G8CC defined. This query is below. You now click "next".

Save connector.

These are the minimum steps needed to create a connector. We can now "Save without update query" (Fig 1.5).

Fig 1.5

You will see your new connector in the "Integrated data source" list (Fig 1.6).

Fig 1.6

Creating a G8CC view to use data in the MySQL database.

Creating a G8CC view to use data in the MySQL database.

To do this we open G8CC's App Wizard. The first step is to create a title and description for our app (Fig 1.7). We can then click next.

Fig 1.7

In our second step we create a view. For this we will use three input type (Fig 1.8). The first two inputs will use input (text). The third input (email) and fourth input (Telephone).

Fig 1.8

To create a view you drag the input type and drop it in the highlighted green box. An input configuration screen will appear (Fig 1.9). Each input will use the following configuration:

Fig 1.9

1. Drag n' Drop Input (text) then type title "First Name". In the index section click text, then click "Save".
2. Drag n' Drop Input (text) then type title "Last Name", then click "Save".
3. Drag n' Drop Input (Email) then type title "Email", then click "Save".
4. Lastly Drag n' Drop Input (Telephone) then type title "Number", then click "Save".

You will have created a view for your data (Fig 2.0).

Fig 2.0

You've created a view. Now click "Next".

In our final screen we can define a title and integrate our MySQL data source. A title is similar to "Subject" in email.

In the box at the top click "#", then select "First Name", press space, then hash (#) again. Now select "Last Name" (Fig 2.1). The values in our First and Last Name inputs will replace the titles after the "@".

Fig 2.1

Next we configure our data source integrator. Click "Integrate external data source". A dialog open, click "Add". In the next dialog click "Employees". We can now match our column names to the titles of our inputs (Fig 2.2).

The table below shows the relationships.

SQL Query Column	View Title
first_name	First Name:
last_name	Last Name:

Fig 2.2

When this is complete click "Save".

We can now save the G8CC App. Scroll the page down and click the "Save" button at the bottom.

You've now created an G8CC view to manage your integrated data.

You can now integrate and share data. To do this click the () sign in the menu on the left of the screen. Now click "Employees". This opens the view we've just created. In the first input type 3 letters (Fig 2.3). This opens a pop-up with options below the input. You can click on one to select. This populates the first and last name inputs.

Fig 2.3

You've now integrated a MySQL data source into your G8CC data sharing layer.