Manage and share data from your MySQL databases.

Integrate, join-up and extend data across your MySQL databases. Build on value, lowering ongoing costs.

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.

MySQL employees columns names.

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.

G8CC integrated data source menu item.

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.

G8CC click button to add new data integrator.

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.

Create a title and description for your data integrator.

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.

Define the connection details for your data integrator.

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.

G8CC containers that can use the data integrator.

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).

Create a G8CC data integrator.

Fig 1.5

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

New G8CC data integrator.

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.

G8CC App Wizard new title and description.

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).

G8CC App Wizard input types.

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:

Configure a new input for G8CC.

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).

Group of inputs that create a view in G8CC.

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 "@".

Create G8CC information title.

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:

Map read query column names to G8CC input titles.

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.

MySQL data integrated into G8CC data sharing layer.

Fig 2.3

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

Easily integrate, join-up and extend information in your MySQL databases.

You can contact us here today if you don't understand or need help with you integration needs.

With G8CC you can:

Deploy anywhere.

Deploy our Application Platform on AWS, Microsoft Azure, Google Cloud Platform (GCP), Kubernetes, private cloud.

Access Management.

G8CC's flexibility allows you to manage your data building trust. That empowers you to add value to your data across traditional borders.

Save Money.

Remove duplicated effort and wastage. Harness and build on the value of digital and data, reducing delivery and ongoing costs.

Get started by: