While developing Apps in ODC, developers will for sure use external database persisted data.

In order to achieve this goal, developers need to create connections to the given external databases inside ODC Portal and then select business data in the form of entities, based on tables in those external databases.

These entities, will then be used as App data.

If this is true for the development Platform environment, for Runtime, Admins need to make sure that, for a given App:

• external databases are configured for each stage;
• the connection information is the same for each stage, namely that the database model is the same in all stages, so the entities can also be the same.

Data in an App can be originated from different entities and even different external databases.

Given that most of the customer's systems of records (databases) are located on-premises, under private networks, ODC provides private gateways, in order to access private data in a secure way.

Supported databases

The currently databases and external systems supported in ODC are the following:

• MS SQL Server
  • MS SQL Server 2014
  • MS SQL Server 2016
  • MS SQL Server 2017
  • MS SQL Server 2019
  • MS SQL Server 2022
• Azure SQL
  • Azure SQL V12
• Oracle
  • Oracle 19c
• SAP
  • SAP S4
  • SAP HANA
• Salesforce
• PostgreSQL(*)
  • PostgreSQL 12
  • PostgreSQL 13
  • PostgreSQL 14
  • PostgreSQL 15
  • PostgreSQL 16

(*) - PostgreSQL is supported as self managed or as AWS Aurora or Azure provisioned

Create a connection to an external system in the ODC Portal

To create a connection to an external system in the ODC Portal, you should refer to the navigation menu, on the left side of the screen, and:

1. Under the Integrate tab, select Connections;
   
2. Press the "Create connection" button;
   
3. Select the database provider from the available provider and click "Confirm";
   
4. In the next screen you need to enter the database our external system parameters. At this step, you can define the parameters for each of the stages (Development, Test, Production, etc), or apply the same settings to all stages (we don't recommend this approach, though).
   When done, you can test the connection, by pressing on the "Test connection" button.
   If it succeeds, click the "Save" button, in the top right of the screen. If it fails, please review the parameter inserted and check with your DBA what can be missing - you won't be able to create a connection if the test validation fails.
   
5. In this same page, you can also set how NULL values should be handled. You can decide to keep NULL values or overwrite them with your default values, as shown below:
   
   This means that if you select:

• Overwrite database NULL values:
  • When writing data in the database, ODC will store the values set at this screen, instead of NULL values;
  • when reading from the database, ODC will replace NULL values with the ones set at this screen;
• Keep database NULL values: both for writing or reading, ODC will assume NULL values as is.

If connections are to be set for MSSQL or Oracle dabases, it's possible to use advanced parameters to add additional parameter for the database connection. If there's more than one parameter, each one can be separated with a semi-colon (;).
Being different, MSSQL and Oracle, the requirements are different, as well, for instance, to select a given database schema:

• For MSSQL and Azure SQL, you should enter: currentSchema = <schema-name>;
• For Oracle, you should enter: current_schema = <schema-name>.

For PostgreSQL, and following the example above, it's also possible to use the Schema parameter in the connection settings.

Editing an existing connection

Editing an exiting connection is restricted to change its name and description. Any other settings aren't available for editing.

This means that if the user credentials have changed in an existing connection, you need to create a newer one and go through the same steps as described in the previous section.

Select Entities

As described in another article, the whole purpose of creating connections is to get all data relevant for an App.

After the connection is established, one can extract the needed data as Entities, which will then be used by applications.

After selecting the Entities and the attributes for each Entity, you can press Save.

From this moment on, the selected Entities and their attributes will be available as public elements for all Apps. Since some times the names in the database can follow some naming rule, it's possible to edit both the Entities and attributes names in ODC Portal.

Data Type mapping

Given that each database vendor has its own data types and in order to not be dependent of each vendor, ODC also has its own data types and when reading or writing data into a given database, it does data type mapping.

The data type mapping is done as described in the following table:

Other considerations, when connecting to an external database

When connecting to an external database, please consider the following:

• Data Preview and runtime queries with Unicode characters are not supported;
• Advanced SQL Nodes don't support external entities;
• Building of aggregator or mashup data from different sources (external and local) is not supported;
• .NET doesn't support Julian calendar for Oracle or Salesforce and the minimum suport timestamp value is -62135596800000;
  • To avoid breaking .NET send the maximum value between the original timestamp and the minimum supported to convert dates like 0001-01-01 to 0001-01-03.
• Given that Views don't have primary keys, when importing Views in ODC Studio, the only actions being generated are Create<EntityName> and DeleteAll<EntityName>

For other considerations, which may be specific to your database vendor, please check ODC's documentation here.