Integrating Existing Database – Rollbase External Tables

In this blog, we will look at how we can integrate existing database into Progress Rollbase Applications. Rollbase is a low-code platform that helps create all sorts of applications at an unprecedented speed and efficiency. In most cases, you 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 4 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_’).

External Table - schema

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

External Table - department

External Table - employee

 

 

 

 

 

 

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.

External Table - databases

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 Application create wizard and click on ‘Let me build it my way’.

External Table - application

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 ‘A new Object (with Tab) from an External Metadata’ option from the object create popup.

External Table - object create

  • Choose ‘External Tables’ option in the ‘Import Object Metadata’ page and click on ‘Next’.External Table - object create step 1
  • 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’.External Table - object create step 2
  • 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.External Table - object create step 3
  • In the ‘Adjust SQL’ page, you will be see options to configure your sql queries for the CRUD operations on the Department object.External Table - object create step 4

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.

External Table - object create step 5

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

External Table - department data External Table - employee data

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

External Table - employee create

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

External Table - employee create database verify

 

 

 

 

 

 

Conclusion

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

Comments are closed.