How do i connect OutSystems to my local SQL database?
The OutSystems platform makes available automatic integration capabilities with external SQL Server and ORACLE databases. With this integration method, you are able to expose your external database entities as visual objects that you can use to develop applications in Service Studio (the OutSystems Agile Platform IDE).
In the OutSystems Platform Tour you can see the video Reuse existing databases that gives you a very good idea of how this process is done.
The goal of this how-to is to give you a step by step description of how to integrate with in an external database in OutSystems and use this integration in your OutSystems application. The how-to instructions are designed to someone that is running an OutSystems Community Edition platform server on his local computer, but you can easily adapt to a situation where you have a standalone server on another machine.
This how-to will explain how to do an external database integration by assuming an example scenario where we want to create an OutSystems application that will give us the functionality to manage the list of Employees stored in an external SQL Server (or ORACLE) database with HR information.
1. Configuring the HR Database Connection in Service Center
The first step to be able to establish a connection to an external database is to define the database connection settings in Service Center (the OutSystems platform central management console). To do that, execute the following steps:
1.1 Login to your service center at http://localhost/ServiceCenter/ with administrator permissions;
1.2 Open the tab ADMINISTRATION / Database Connection and click the link New Database Connection;
1.3 In the Create Database Connection screen, define your HR Database connection parameters, test them pressing [Test Connection String] and save it pressing [Create];
2. Creating the HR Database Integration in Integration Studio
Once we have the connection to our server configured in Service Center, we can now create the integration component to our HR Database using the OutSystems Integration Studio. To do that, execute the following steps:
2.1 Open Integration Studio (at Start > OutSystems > Integration Studio 5.1) and create a new integration component. Let's name it HRConnector;
2.2 In Integration Studio, start the Import Entities from Database… wizard.
2.3 Run the wizard – choose the HR Database Connection and define the tables you want to use (in our example: Employees, Categories and EmployeeTerritories)
2.4 Hit the 1-click-publish button in Integration Studio to publish your extension in the server. When published, press the [Configure] button (it will open Service Center to configure the extension).
2.5 In the service center HRConnector extension page (in the Operation tab), associate the extension to the Database Connection that we want to use in runtime (in our example, the HR Database).
3. Using the HR Database entities in our OutSystems application
As the HRConnector extension is now available and configured to be used, we can use it to create OutSystems applications with the tables in the external HR Database (query data, change data, delete data). For example, we can easily create an HRBrowser eSpace that lists the content of the Employees table doing the following:
3.1 Create a new eSpace in Service Studio and reference the HRConnector entities using the [Add/Remove References] functionality.
3.2 In your eSpace, you can now use the imported (Employees, Categories and EmployeeTerritory) as any OutSystems entity in your Service Studio visual models! For example, to create a Employee listing screen, you simply have to drag the Employees entity to an OutSystems web flow.
3.3 Publish your HRBrowser eSpace and click [Open in Browser] to see the result.
3.4 The HRBrowser application now lists the content of the Employees table that exists in the external SQL Server or ORACLE database.