Skip to Content (Press Enter)
2019-10-09 06-46-56
Daniel Lourenço
Staff
1591Views
19Comments
Re : How-to integrate with an external SQL Server or ORACLE database in OutSystems
Discussion
OS Certified
Integration
Database
How-to

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.

Contents

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
  2. Creating the HR Database Integration in Integration Studio
  3. Using the HR Database entities in our OutSystems application

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 https://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
OutSystems
16
0
05 May 2011
Copy Link
2018-03-08 20-43-12
Robert Chanphakeo
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).

0
0
07 May 2011
Copy Link
2016-11-21 23-23-05
Gonçalo Borrêga
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
cheers
0
0
07 May 2011
Copy Link
2018-03-08 20-43-12
Robert Chanphakeo
"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
cheers"

thats impressive, thats good engineering!
2
0
08 May 2011
Copy Link
2016-04-21 20-09-55
J.
 
MVP
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?
0
0
27 Jul 2011
Copy Link
2012-03-16 12-21-09
João Rosado
Staff
Hi Joost,

That problem with the refresh was fixed long time ago (around 5.1.0.14), 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.

Regards,
João Rosado
0
0
27 Jul 2011
Copy Link
2018-03-08 20-43-12
Robert Chanphakeo
Just for the record for anyone that followed the instructions posted above...you 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.

thanks.
1
0
01 Aug 2011
Copy Link
2018-03-08 20-43-12
Robert Chanphakeo
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 :)) )



0
0
10 Oct 2011
Copy Link
2019-10-09 06-46-56
Daniel Lourenço
Staff
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
OutSystems
0
0
10 Oct 2011
Copy Link
2018-03-08 20-43-12
Robert Chanphakeo
Daniel

Great, thanks.
0
0
18 Oct 2011
Copy Link
2012-01-11 22-29-57
Joey Yaffe
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.
0
0
27 Feb 2012
Copy Link
UserImage.jpg
Graciela Lafontaine
Thanks...Great job.. Really helpfull
0
0
27 Feb 2012
Copy Link
UserImage.jpg
Pedro Sousa
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:

CREATE TABLE ENTIDADE (
 ID VARCHAR2(128) DEFAULT SYS_GUID(),
 NOME VARCHAR2(256) NOT NULL,
 NIF INT,
 URL VARCHAR2(256),
 CONTACTO_ID VARCHAR2(128),
 PRIMARY KEY (ID),
 CONSTRAINT entidade_contacto_id_fkey FOREIGN KEY ( CONTACTO_ID ) REFERENCES O_CONTACTO( ID )

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.

Regards
0
0
11 Apr 2012
Copy Link
UserImage.jpg
Pedro Sousa
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:

CREATE TABLE a (
ID INT NOT NULL,
attrib1 VARCHAR2(256) NOT NULL,
PRIMARY KEY (ID)
);
 
CREATE TABLE b (
ID INT NOT NULL,
attrib1 VARCHAR2(256) NOT NULL,
PRIMARY KEY (ID),
CONSTRAINT b_id_fkey FOREIGN KEY ( ID ) REFERENCES a( ID )
);
 
 
CREATE TABLE c (
ID INT NOT NULL,
attrib1 VARCHAR2(256) NOT NULL,
PRIMARY KEY (ID),
CONSTRAINT c_id_fkey FOREIGN KEY ( ID ) REFERENCES b( ID )
);

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.
1
0
13 Apr 2012
Copy Link
UserImage.jpg
Pedro Sousa
hello,

any input on this matter?

Thank you.

Regards
0
0
16 Apr 2012
Copy Link
2015-04-10 17-57-05
Pedro Coelho
 
MVP
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,
PC
0
0
16 Apr 2012
Copy Link
UserImage.jpg
Pedro Sousa
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?

Regards,
PS
0
0
16 Apr 2012
Copy Link
2015-12-27 13-21-28
Luís Teixeira
Hello,

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

Best Regards,
Luís Teixeira
0
0
07 Sep 2012
Copy Link
UserImage.jpg
hioolen

Hi,
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?

0
0
19 Oct 2012
Copy Link
2016-04-21 20-09-55
J.
 
MVP
Hi,

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?

0
0
19 Jun 2015
Copy Link
Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.