Docs‎ > ‎Integrate Menu‎ > ‎

Data Sources

Each API project has a list of one or more registered databases. This list may include only the database entry that was created when you created your API project. You can create multiple database entries, such as test databases, integration databases, and a production database. 

An API can have one or more SQL database locations. Your database can be in the cloud or on-premise. For on-premise databases, see the following "Connect to your Database" section.

API Creator update-processing relies on database services for locking and transaction management. Retrieval-processing leverages the database engine's services for query optimization (index selection, join strategy).

For key database concepts and terms used in Live API Creator, see Database Concepts.

The following image shows the Integrate, Data Sources, Connection tab:

Connect to your Database

The API Server needs access to your database, whether it be stored in the cloud or within your firewall. Live API Creator includes the Java DB/Derby driver. All other databases require that you install the appropriate JDBC drivers for your system.

You can connect to:

  • An existing database.
  • A new database.
For more information:

Connect to an Existing Database

Log on to Live API Creator and create an API project and connect to an existing database.

Note: Firewalls require accommodations.

You can also connect to your database by way of a Named Java Naming and Directory Interface (JNDI) data source.

Possible motivations:

  • Your connection information is not stored in the API Server's Admin DB.
  • JNDI definitions can provide greater flexibility in leveraging some dbms-specific options, such as SSL access and mirroring.
For more information:
  • About logging on to Live API Creator and creating an API project, see Log on to Live API Creator.
  • About database connection security, see Security.
  • About resolving database connectivity issues, see the Database Connections section in Troubleshooting or contact CA Support.

Connect to a New Database

You can create a new database using your current tools and then connect to Live API Creator. If the database driver is not available or you cannot load it, complete the following:

  1. Provide the JDBC driver.
    For more information about installing a JDBC driver, see Install JDBC Driver.
  2. Restart the API Server.
  3. Attempt to connect again.

Use Multiple Databases in the Same API

You can use multiple databases in the same API. Resources, rules, and JavaScript can reference multiple data sources by specifying a database prefix. 

For more information about specifying database prefixes, see Multiple Databases.

Define Multiple Databases

You can combine resources, build rules between databases, and access them by defining multiple databases.

For more information about multiple databases, see Multiple Databases.

Schema as Data Model

API Creator reads your database schema during the API project creation and connection process. It references the database objects and determines the names (table names, column names, and foreign key names) from the database catalog. The following image shows the Integrate, Schema, Tables tab:

Foreign Key-based Relationships

API Creator requires references to foreign key relationships to express logic (sum/count, parent references etc), retrieval joins, and Live Browser support for Master/Detail, Lookups, and Automatic Joins. For more information about foreign keys, see Database Concepts.

The underlying concept is a one-to-many relationship between two tables in a relational database. Relationships are discovered by Foreign Keys defined in your database. The Sample Database has examples of multiple different kinds of relationships.

Live API Creator uses the following terminology:
  • Parent - Within a Relationship, the table containing the Primary Key on the "one" side. For example, Purchaseorder is a parent to Lineitem.
  • Child - Within a Relationship, the table containing the Foreign Key on the "many" side. For example, Purchaseorder is a child to Customer.  PUT/POST JSON provides mechanisms to associate a child with its parents.
  • Parent Role - the name by which the Child refers to the Parent.
  • Child Role - the name by which the Parent refers to the Children.

Determine Role Names

Relational database catalogs do not specify role names. You must determine them. In most cases, these defaults mean you do not need to worry about this; however, multiple relationships between tables and databases without Foreign Key names require special consideration.

Parent roles default to the name of the Foreign Key. If this is not present, the Parent Table Name is used, as shown in the following code snippet:
CONSTRAINT product FOREIGN KEY (product_name) REFERENCES products (name) ON UPDATE CASCADE,

Child Role Names default to the name of the child table concatenated with "List". So, for example,
Customers could reference (perhaps in a sum rule) OrdersList.

Naming Alert

Role Names must not conflict with attribute names.
The defaults above do not work when there are multiple relationships between the same two tables, such as in the Sample (Department has worksFor and onLoan Employees). You can specify your Role Names using API Creator or you can encode your role names into your foreign key validation names. For example:


 CONSTRAINT reportsTo__reportingEmployees FOREIGN KEY (department_name) REFERENCES departments (name) ON DELETE SET NULL ON UPDATE CASCADE,

  CONSTRAINT onLoanTo__onLoanEmployees FOREIGN KEY (on_loan_department_name) REFERENCES departments (name) ON DELETE SET NULL ON UPDATE CASCADE

Virtual Foreign Keys

API definitions, rules, and Data Explorer use the virtual foreign keys.

Note: Related tables can be in different databases.

The following image shows the Data Sources, Relationships tab:

Database Administration

The start-up process creates the Demo, Sample, and Your Database test database. The Your Database is a blank placeholder.

Configure Your Data Source to Access your Database Administration Web Tool

You manage your schema using your existing tools and procedures. As the schema changes, you can reload it. You can configure your data source to access your database administration web tool using the fields on the Data Sources, Details tab.

Reload the Schema

When you first create an active connection (the Active checkbox is selected on the Data Sources, Connection tab), API Creator reads the database meta-data. Depending on the size of the database and the latency to the database server, this can take significant time. After API Creator has read the meta-data, it caches it in the API Server admin database and shared between nodes. As the schema evolves and you change the schema, reload the schema and flush that cache to tell API Creator to scan the database meta-data.

Verify your API Project

The Object Model built using Schema Discovery is not a code generation process. It always matches your current schema (subject to Schema caching). Changes to your schema can make Rule Base objects (e.g., Resources) invalid, due to dangling references.

Complete the following:
  1. Reload your schema.
  2. Verify your API project. From the Create menu, click API Properties, Verify. The resources that refer to objects that no longer exist (for example, because they were deleted or renamed), are identified and logged as issues.
  3. Review the issues. If errors are detected, the Problems navigation are highlighted. You can click it to see/address the errors. The following image shows the Analyze, Issues page:

Define Virtual Foreign Keys

Foreign keys are useful for defining multi-table resources, object model accessors, and rules. Many schemas do not define these. You can define foreign keys using API Creator. The related tables can be in the same database or different databases.

For more information about defining relationships between databases, see Multiple Databases.

Create Relationships between Parent and Child Tables

You can create relationships between parent and child tables using the Relationship Editor (under the database tab). This is useful when creating Resources that require 'joins' between tables. API Creator uses the existing relationships to create navigation, tab panels, parent pick choices using these definitions.

 API Creator reloads the schema, so give it a moment.

Display Sequences

Databases that support sequences display the column and sequence name. The following image image shows the Integrate, Data Sources, Sequences tab:

Extend your Data Model to Include Non-persistent Attributes

You can extend your data model to include attributes not in the schema. You can use such attributes in resources and rules. Just as with conventional systems, you will need to make design decisions about whether to persist derived data. If you decide to persist attributes, the following Synchronize Data with Logic section.

For more information about non-persistent attributes (NPAs) and NPA best practices, see Non-Persistent Attributes.

Synchronize Data with Logic

To maintain high performance, the API Server presumes that existing persistent attribute values match your logic. For example, in Demo, we assume that the Customer balance is correct, so we can adjust it on purchaseorder changes and avoid expensive aggregate (select sum) queries.

If you alter the logic, bring your current into conformance. For example, the following are the (MySql) queries used for Demo:

update lineitem item set item.product_price = (select price from product p where p.product_number = item.product_number);

update lineitem item set item.amount = item.qty_ordered * item.product_price;

update purchaseorder po set po.amount_total = 
(select sum(item.amount) from lineitem item where (item.order_number = po.order_number));

update customer cust set cust.balance = 
(select sum(po.amount_total) from purchaseorder po where po.customer_name = and po.paid = false);

For the following rules:
If you are using SQL/Server, a similar script would be:

update [dbo].[Customers] set Balance = 
  (select sum(orders.AmountTotal) from [dbo].[Orders] orders
          where (orders.CustomerID = [dbo].[Customers].CustomerID));

Data Sources Best Practices

We recommend explicit foreign key definitions. Define foreign Keys by way of validations, including a validation name.

For more information about defining foreign keys by way of validations, see Validation.