Thanks to the extensibility of the Corticon Advanced Data Connector, you can dynamically connect to multiple databases even in unusual situations. Read on to learn how.
Today business decisions require data that is stored across multiple databases. With the Progress Corticon Advanced Data Connector (ADC), Decision Services can dynamically retrieve data from separate databases. In this post we will investigate a special case where the data for a business decision is stored in two databases with an implied relation (implied join) spanning the two separate data sources, retrieve separate sets of data and integrate them into a Ruleflow.
This is a great example of how to customize a Corticon Service Callout to implement features beyond those of an ADC multiple database configuration. This cross database join can be accomplished by adding some custom code.
Note: This example assumes a working knowledge of Java, Corticon APIs, Corticon Studio, ADC, relational databases and Java Database Connectivity (JDBC).
For this scenario, the business decision will assess a proposal for an investment account to purchase specific assets. The Decision Service will receive as input the investment account and several possible assets for purchase.
It will access internal and external databases for additional information about the account and the assets. Then, it will determine which of the assets the account should purchase. The data required for the decision is retrieved from two databases: an internal database holds private secure account information, and an external database holds public asset information.
The vocabulary consists of Proposal, Asset, AssetType and Account entities. The Account entity attributes match the columns of the Account table in the internal database. One Account can be associated with many AssetTypes which will hold the asset types the account is allowed to purchase. Asset and AssetType attributes match the columns of the Asset and AssetType tables in the external database. One AssetType can be associated with many Asset entities.
Figure 1: The Vocabulary tree in Corticon Studio
Figure 2: Set up the ADC Datasource for the internal database
Do the same for the external database. Add another ADC Datasource to the vocabulary and enter a different name, such as “externalDB.” Enter the database connection information. Import the metadata from the database or enter the mappings manually if needed.
Figure 3: Set up another ADC Datasource for the external database
Learn more about setting up ADCs in the Corticon Server: Data Integration Guide.
The service callout (SCO) needs to do 10 things:
IDatasource lDatasource = aDataMgr.getDatasourceManager().getDatasource(
String lstrDatabaseId = lDatasource.getDatasourceDriverId();
String lstrDatabaseUrl = lDatasource.getDatasourceConnectionURL();
String lstrUsername = lDatasource.getDatasourceUserName();
String lstrPassword = lDatasource.getDatasourcePassword();
Connection lConnection = aDataMgr.getDatabaseDriverManager()
, lstrDatabaseId, lstrDatabaseUrl, lstrUsername, lstrPassword);
Set<ICcDataObject> proposalSet = aDataMgr.getEntitiesByName(
ICcDataObject proposal = (ICcDataObject)proposalSet.iterator().next();
// create Account entity
ICcDataObject account = aDataMgr.createEntity(
ArrayList<Long> accountAssetTypeIds =
Statement lStatement = lConnection.createStatement();
String lstrQuery =
"select id,name,cash from Account where id = "
ResultSet lResultSet = lStatement.executeQuery(lstrQuery);
// set Account attributes with query results
Long accountId = lResultSet.getLong(
lStatement = lConnection.createStatement();
lResultSet = lStatement.executeQuery(
"select assetTypeId from AccountAssetType where accountId = "
// keys collected in accountAssetTypeIds
Set<ICcDataObject> tradeAssets = aDataMgr.getEntitiesByName(
// map asset Id to Asset entity for lookup ease
Map<Long, ICcDataObject> assetIdMap =
(ICcDataObject asset : tradeAssets)
String lstrPassword = lDatasource.getDatasourcePassword();
Connection lConnection = aDataMgr.getDatabaseDriverManager().getConnection(
Map<ICcDataObject, Long> assetToAssetTypeId =
"select id,name,price,assetTypeId from Asset where id in "
lstrQuery = lstrQuery.replace(
Long assetId = lResultSet.getLong(
// lookup asset by id
ICcDataObject asset = assetIdMap.get(assetId);
// set attributes
Long assetTypeId = lResultSet.getLong(
"select id,type from AssetType where id in "
lResultSet = lStatement.executeQuery(lstrQuery);
// create AssetType entity
ICcDataObject assetType = aDataMgr.createEntity(
To test the data retrieval, create a Corticon Ruleflow and add a Service Call-out (SCO) node. Point this SCO to the method created above. To learn how to set up SCOs, see Corticon Server: Data Integration Guide: Use the ADC Connection in a service callout.
Create a Ruletest with the new Ruleflow as the test subject. And supply input data as shown below.
The internal database has an Account with id = 1. Accordingly, the input test data includes a Proposal with the attribute accountId set to 1.
The external database has six Assets defined with ids = 1,2,3,4,5 and 6. Accordingly, the input test data includes six Asset entities associated with the Proposal. Their id attributes correspond to those in the database.
Run the test. If the data connection is successful, the output will include the data enrichment of the Asset entities and the new Account entity with allowed asset types, as shown in the output panel below.
Figure 4: Input and output for a successful test of the ADC service callouts.
At this point, the necessary data is available to determine which assets the account should purchase, and you can model the decision logic in Rulesheets. The associations between Account and AssetType entities defined in the vocabulary integrate the input data with the data retrieved from two separate sources. The service callout method supports that association as well (i.e., Account.allowedAssetTypes.asset).
Keep in mind, in this example, the data source mappings are exact names. In reality, the vocabulary name usually does not match the database name, so you need to map database properties to Vocabulary properties. The ICcDataManager provides access to vocabulary metadata for lookup of entity, attribute and role names. You can also access the data source metadata for column names and return types. To learn more about mapping data, see Corticon Data Integration Guide: Create and map the ADC schema and queries.
You now have the tools to access two databases in one Decision Service for additional flexibility and power in your applications. To learn more, request a demo of Corticon 5.7, and don’t forget to check out all the updates in the latest release.
Eric Moore is a Software Architect at Progress.
Subscribe to get all the news, info and tutorials you need to build better business apps and sites
Copyright © 2019 Progress Software Corporation and/or its subsidiaries or affiliates.
All Rights Reserved.
Progress, Telerik, and certain product names used herein are trademarks or registered trademarks of Progress Software Corporation and/or one of its subsidiaries or affiliates in the U.S. and/or other countries. See Trademarks for appropriate markings.