Integrate Multiple Databases

Integrate Multiple Databases
lac42
You can integrate multiple databases as part of your API. After you have configured your API for multiple databases, you can define resources and build rules between databases.
In this article:
Configure your API for Multiple Databases
You can configure your API for multiple databases by connecting your API to multiple databases and then defining relationships between databases.
Connect your API to Multiple Databases
Though you probably
created
your API starting with a single database, your APIs are not limited to a single database connection. You can connect your API to multiple databases or you can combine resources, build rules between databases, and then access these resources.
For more information about how to connect your API to multiple databases, Database Connectivity.
Define Relationships Between Databases
CA Live API Creator
 uses physical and virtual relationships for resource definition, including subresource joins, for multi-table rules, for Data Explorer, and code completion. When you connect your API to a database,  
CA Live API Creator
 generates physical relationships for each foreign key it discovers. For situations where your database does not define foreign keys, and for relationships between tables in different databases and database types (for example, Oracle and SQL Server), you can engage these same services by adding virtual relationships across schemas.
For more information:
Use Multiple Database Connections
After you have configured your API for multiple databases, you can use multiple database connections just as would tables in a single database.
Define Resources Between Databases
For more information about how to join data from multiple databases by defining resources between databases, see Define Table-Based Resource Types.
Use Data Explorer
You can use Data Explorer, which navigates the relationships between databases.
For more information about Data Explorer, see Data Explorer.
Build Rules Between Databases
After you have defined relationships between databases, you can create rules that span tables, such as sum, count, parent copy rules.
Each database commits separately. One database might succeed while the other fails.
For more information about the rules that you can create, see Rule Types.
Interact with Data
When you create an API and connect to your database, 
CA Live API Creator
 creates (not code generation) the JavaScript object model, made available through the 
row
 variable that is passed into your logic. You can use the same APIs to interact with data, whether your API interacts with data in a single database or in multiple databases.
If your API is connected to more than one active database, the prefix specifies the database that 
CA Live API Creator
 should use, for instance, when defining resources, rules, and JavaScript. They can reference multiple data sources using the data source prefix. 
Use the following syntax:
<data source prefix>:<table name>
Example:
main:Customer
For more information: