Integrate Multiple Databases

Integrate Multiple Databases
You can integrate multiple databases as part of your API. You can define relationships, resources, and rules between databases.
For rules defined between databases, each database commits separately. One database may succeed while the other fails.
In this article:
Define Relationships Between Databases
Relationships are useful when creating resources that require 'joins' between entities (tables and views). Your database defines foreign keys. Foreign keys identify the relationship of a row in an entity with a row in another entity or in the same entity. API Creator requires references to foreign key-based relationships to express logic (for example, sum/count and parent references), retrieval joins, and JavaScript programming with the JavaScript object model. Data Explorer requires references to foreign key-based relationships to support for master/detail, lookups, and automatic joins.
If your database schema does not define foreign keys, you can add a relationship between databases, or a virtual foreign key. Virtual foreign keys do not affect the database schema. You can add relationships between parent and child entities. Related entities can be in the same database or different databases.
For more information:
When you add a virtual foreign key, API Creator reloads the schema. The time it takes API Creator to read the metadata depends on the size of the database and the latency to the database server.
  1. With your API open, in the Create section, click 
    Data Sources
    The Connection tab displays by default.
  2. Click the 
    The Relationships tab appears. The following image shows this tab:
    Screen Shot 2017-03-16 at 1.16.20 PM.png  
  3. In the relationships list, click 
  4. In the 
    Column Relationships
     section, define one or more pair of columns, one from each entity (table or view). These become the join columns and are used to join the two entities.
The relationship is defined.
View Relationships Between Databases in Data Explorer
The following image shows customers and orders from the (same) Demo database: CA Technologies The following image shows Customers and Orders from the Sample databases:
Define Resources Between Databases
You can define resources that combine data from different databases. The resource's join condition defines how this resource is joined with the containing resource.
  1. With your API open, in the Create section, click 
    The Resource tab displays by default.
  2. Enter syntax in the 
     field and then save your changes. For example:
    "customer_name" = [name]
    Defining the relationship between the databases defaults the join and the key appears as the value in the 
     field. You can change this value.
    The following image shows this field on the tab:
The resources between databases are defined.
Refer to Columns in a Table of the Containing Resource
Use the following notation in the 
customer_number = [custnum] and region_ident = [regident]
In this example, 
 are columns of the current resource's table, and 
 are columns of the table for the containing resource.
MongoDB Resource Syntax Example
If you are defining a MongoDB resource, use Mongo syntax in the 
 field. For example:
{"$and": [{"CompID": "<CompanyID>"}, {"CustID": "<CustomerID>"}]}
CA Live API Creator
 efficiently optimizes resources that combine data from different databases. For example, 
CA Live API Creator
 retrieves customers and then retrieves the 
Rename Data Source Prefixes
Renaming a data source prefix breaks rules and resource definitions.
For more information about data source prefix names, see Database Connectivity.
  1. Export your API.
  2. Change the data source prefix in a text editor.
  3. Import your API.
APIs for Database Access
You can use the same APIs to interact with data, whether your API interacts with data in a single database or in multiple databases.
For more information about how to use the same APIs to interact with data in multiple databases, see The logicContext Object.