Re : How-to integrate with an external SQL Server or ORACLE database in OutSystems

Re : How-to integrate with an external SQL Server or ORACLE database in OutSystems


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) databas ewith HR information.

The main steps in the integration process are:

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

Related Resources

Kind Regards,


Daniel Lourenço
Great article!

What about updating, do we reimport? (ie you import your database the first time, later on your external database changes, new tables are added, existing tables updated etc).

Hi Robert,

Pretty simple, you just go to IntegrationStudio, right click over the imported entity definition and hit "Refresh Entity". The structure will be updated.
You can import new entities to the extension at anytime
"Hi Robert,

Pretty simple, you just go to IntegrationStudio, right click over the imported entity definition and hit "Refresh Entity". The structure will be updated.
You can import new entities to the extension at anytime

thats impressive, thats good engineering!
There is an issue what is still bothers me.

If you have a logicalname different than the database-connection you cannot refresh an entity.
Is this by design or a bug?
Hi Joost,

That problem with the refresh was fixed long time ago (around, It should work fine as long the extension is published on the server and the logical database for the extension is already associated with a DB Connection

If that is not the case, you should submit the problem.

João Rosado
Just for the record for anyone that followed the instructions posted need to add the "OSRuntime" user to the database that you want the agile platform to connect to, otherwise it wouldn't be able to connect with the runtime user. You can add the OSRuntime user via SQL Server Management Studio.

Does it work if your database is seperate from your outsystems server database? (It should work right? , have not tested but if you've tested this, let me know :)) )

Hi Robert, yes it works.

The idea of this feature is exactly to support the scenario where you need to integrate with an external system by accessing to the database server directly, which means that in most situations the database will not be in the same server.

Kind Regards,

Daniel Lourenço

Great, thanks.
I am currently running Community addition, and I am gradually ramping up the testing of various components.
Right now, everything is on my laptop, but what if I just want to move the SQL database for the whole application to a remote server?
Ultimately, I will need to do this, but also, I want to test using Crystal Reports.  These are hosted on a remote Crystal Reports server, and they need to be able to read data from a SQL database.  Since my laptop can't be that resource, I want to move the SQL to an external server, leaving the community edition locally for now.  How do I simply change the location of all the tables in the platform and my espace, and linked resources.
Thanks...Great job.. Really helpfull
Hello Daniel and all contributors to this awesome forum,

Thanks for the info it was a great help getting to work with an Oracle Database. However I have a small issue regarding the AutoIncrement when in ServiceStudio I try to add a new Entity perhaps in the EntidadesTable.

This is my SQL code creating the table:


I  I used the SYS_GUID() to create my auto increment. I noticed when I import the entities from the Integration Studio that the box Auto Number is not thicked. Could you please be so kind and help me out getting the CreateOrUpdate Action in ServiceStudio to auto increments ID's?

Thanks in advance and keep up the good work.

hello again,

I am trying to connect to my external oracle database which I was succesfull, however I have a problem when I want to publish my extension. Compilation is fine. I found out that when I only have tables with one level of foreign keys the publishing is ok but when I have 2 levels i get this error:

There was an error validating the extension: System.ArgumentException: Unknown datatype bt9Mc9Eu_NXEyWuGGEXDT1GA
  at OutSystems.HubEdition.ServerCommon.Extensions.ExtensionValidator.#0ib(String type, String recordType, String extensionName, XElement extensionXml, TypeMapper mapper)
  at OutSystems.HubEdition.ServerCommon.Extensions.ExtensionValidator.CheckExtensionConsistency(String temporaryXIFPath, String temporaryResourcesDirPath, String compoundTypesXml)

The SQL code can be like this:

attrib1 VARCHAR2(256) NOT NULL,
attrib1 VARCHAR2(256) NOT NULL,
attrib1 VARCHAR2(256) NOT NULL,

If I use only table a and b no errors when I get the a,b and c the errors pops up.

FYI I am using the last version of the FULL DOWNLOAD OF OUTSYSTEMS.

Please if you could help me I would appreciate as it is a very important matter.

Thanks in advance.

any input on this matter?

Thank you.

I believe it has to do with the fact that your primary key is also a foreign key in 2 of those tables, but I let someone of the support to give their expertise on the matter...

Best regards,
Hello Pedro,

Thank you for your feedback.

Do you believe that what you suggest is an expected behaviour or rather a technical problem regarding Integration Studio?


It is possibible to get connection string defined in service server to used it in service studio?

Best Regards,
Luís Teixeira

I'm trying to Refresh Entities on an existing database conection as mentioned in comment #1 and 2. That goes OK, I can see the added attributes in my entity list in integration studio. When i try to PUBBLISH the updated extention i'm getting the error:
"The property 'ssJW_test_2_int' does not exist or is not public in the 'OutSystems.NssExtension.ENJW_TradingPartnerAgreementEntityRecord' struct." 

Tht error is on the newly added attribute. Creating a new extention i working just fine. Am I missing a step?


Couple of questions.
Our PK is a Number(9), which means the wizard does not recognize it as a integer.
we change it to integer, set the ID to that attribute.

- Do we also have to check "autonumber" to actually get the ID back when inserting a record?
- using a "insert into" advanced query, does that convert 0 to NULL when setting that correct in the extension?