Manage and share data from your Microsoft SQL Server databases.

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

What are Microsoft SQL Server and G8CC.

Microsoft SQL Server is a relational database management system (RDBMS). Its basic functionality is for storing and retrieving data.

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 Microsoft SQL Server database. G8CC provides a simple solution to seamless integrate and join-up information. This empower data mobility and secure, trusted data sharing.

In this documentation we will use the Sakila database. You can learn how to import Sakila data using SQLCMD here.

In this we focus on how to connect, extend and re-purpose data. This seamlessly integrates and builds on existing data in a Microsoft SQL Server database.

First you need to add a Microsoft SQL Server connector to G8CC.

To do this your need:

  • A Microsoft SQL Server database and G8CC node running.
  • Microsoft SQL Server ODBC connectors.
  • The following details for your database.
    • Username and Password of the Microsoft SQL database.
    • Host name. This can be an IP address, DNS name or "localhost" if your Microsoft SQL Server database is running locally.
    • Port your Microsoft SQL Server database is listening on. The default port for Microsoft SQL Server is 1433.
    • Database and collection name that we are integrating. A collection in Microsoft SQL Server is like a table.
  • Microsoft SQL Server uses SQL query language. The queries we use will be explained 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 MongoDB.

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 Microsoft SQL Server database.

Creating a G8CC view to use data in the Microsoft SQL Server 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 Microsoft SQL Server 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.

Microsoft SQL Server data integrated into G8CC data sharing layer.

Fig 2.3

You've now integrated a Microsoft SQL Server data source into your G8CC data sharing layer.

Easily integrate, join-up and extend information across your 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: