Integrating an Existing Database into Rollbase External Tables

In this tutorial, you'll learn how to easily integrate data from your existing database tables into your Rollbase application.

In this blog, we will look at how we can integrate an existing database into Progress Rollbase Applications. Rollbase is a low-code platform that helps create all sorts of applications at unprecedented speed and efficiency. In most cases, you probably intend to modernize your existing applications to an all new user interface and experience. This can be easily achieved with Rollbase external database support. This feature is only available on private cloud.

Purpose and Prerequisite

We will build an Application in Rollbase which will have

• Employee and Department as objects
• 1:M relationship between Department and Employee

As a prerequisite, we need to have a database schema with two tables 'Employee' & 'Department' as:

• Employee table with employee ID, name & department ID columns
• Department table with department ID & name column
• Both these tables have some data

Setting up Rollbase on the Existing Database Schema

Rollbase installation comes with SQL files for four databases:

1. Oracle (create_ora.sql)
2. MySQL (create_mysql.sql)
3. MSSQL (create_ms.sql)
4. OpenEdge (create_oe.sql)

For the purpose of this blog, we will use the MySQL version and MySQL workbench. We will now run 'create_mysql.sql' script to install Rollbase tables on the existing schema.

Your database schema (rb_dbo) will now have existing tables (department, employee) and Rollbase tables (tables that start with 'rb_').



Let's take a quick look at the data from the 'employee' and 'department' tables.

Configuring Database in Rollbase

We will configure databases.xml in Rollbase to point to this schema with External flag set. You can also set this using the Manage databases section in System Console app.

Creating Application in Rollbase

Let's start building our application in Rollbase with objects accessing data from the existing tables.

As a first step, we will create an Application 'External Table Demo' in Rollbase by launching the Application create wizard and click on 'Let me build it my way.'

Creating External Objects in Rollbase

1. We will now create the Department object that points to the 'department' table in the schema.

• In order to this, we should choose the 'A new Object (with Tab) from an External Metadata' option from the object create popup

• Choose the 'External Tables' option in the 'Import Object Metadata' page and click on 'Next'

• Choose the department table from the dropdown and a name column. Now, provide a name for the Rollbase object that will point to this database table and click on 'Create Object.'
  
  
  
  
  
• Choose a primary key for the 'department' table viz. 'id' and click on 'Create Fields.' This will now create fields in the Department object and associate them with the respective database columns. In this page, you can set Rollbase field details such as their label, field type, uniqueness constraint etc.
  
  
  

• In the 'Adjust SQL' page, you will be see options to configure your SQL queries for the CRUD operations on the Department object
  
  

2. Follow the same steps to create the Employee object that points to the 'employee' table in the schema. During this configuration, make sure you select the 'department_id' column as a foreign key in 'Create Fields' page. This will automatically create a 1:M relationship in Rollbase between Employee and Department object.

3. The 'Department' and 'Employee' Tabs in the Application now show the data from their respective database tables




4. Let's now create an Employee for the Finance Department. This will also create an entry in the 'employee' table.


Let's now query the 'employee' table and verify the new Employee entry.

Conclusion

To conclude, we learned how easy it is to develop applications in Rollbase that can reuse your existing data. There is comprehensive documentation on this subject here.

Curious to learn more about Rollbase? Find out more here, or dive right in with a free 30 day trial at the link below (if you want a private cloud, be sure to choose that option).

Try Rollbase Now