Database Connectivity

lac42

HID_database_connectivity

The update processing in CA Live API Creator relies on database services for locking and transaction management. The retrieval processing in CA Live API Creator optimizes queries by leveraging the database engine's services, such as the index selection. The defined SQL joins determine how much CA Live API Creator can optimize the query.

You can connect to your database when you create your API or you can add a data source connection to your existing API. You can connect to databases that are in the cloud or on-premise. API Server requires access to your database, whether it be stored in the cloud or within your firewall.

You can manage your data source connections by adding, updating, and deleting them.

In this article:

Add a Connection to a Database

Each API includes a list of one or more registered databases and one or more SQL database locations. This list might include only the database connections that you established when you created your API.

You can connect your API to multiple databases or you can combine resources, build rules between databases, and access these resources by defining multiple databases. For example, you can create an empty database and then connect your API to other databases. You can also create a database using third-party tools and then connect to that database when you create your API using API Creator.

Use the following table to guide you to which database to add a connection based on the tasks the data sources support:

Task	Standard SQL databases*	CSV	SAP NetWeaver	Salesforce	Apache HBase	Apache Cassandra	MongoDB**
Provides optimistic locking	Yes	Yes	No	Yes	Yes	Yes	No
Provides pagination	Yes	Yes	Yes	Yes	Yes	Yes
Provides row/column security	Yes	No	No	Yes	Yes	Yes	No
Can use named filters and sorts	Yes	Yes		Yes	Yes	Yes***
Can define logic (create rules)	Yes	No	No	Yes	Yes	Yes
Use HTTP methods in REST requests	GET, POST, PUT, and DELETE	GET	GET	GET, POST, PUT, and DELETE	GET, POST, PUT, and DELETE	GET, POST, PUT, and DELETE	GET, POST, PUT, and DELETE

*Includes MySQL, Microsoft SQL Server, Oracle, IBM DB2, PostgreSQL, Teradata, and Derby.

**Uses the extensible data source framework. For more information about this framework, see Manage Data Source Providers.

***Cassandra includes known limitations when filtering rows. For more information about these limitations, see Apache Cassandra Data Source.

For more information:

About how to connect to your database when creating your API, see Creating APIs.

About how to define resources that combine data from different databases, see Define Table-Based Resource Types.

Follow these steps:

With your API open, in the Create section, click Data Sources
.

The Data Sources page appears. A list of existing data source connections displays on the Connection tab.

Above the list of data source connections, click Add
.

The Create data source window opens.

Create the data source by completing the following fields, and then clicking Add
:

JDBC data source

Create data source using

The category of data source. Select

JDBC data source

Values:

JDBC data source.
A JDBC data source for a JDBC driver that CA Live API Creator supports.

For more information about the JDBC drivers that CA Live API Creator supports, see Installation Requirements and Supported Platforms.

If you require a connection to a data source that CA Live API Creator does not support, create a data source provider.

For more information, see Manage Data Source Providers.

Managed data server.
A data source for a managed database.

JNDI named data source.
A data source through a Java container's JNDI definition.

Data source provider.
A data source for a generic data source provider.

Default:
JDBC data source

Data source type

Specifies the type of data source that you want to use.

Values:
MySQL, Oracle, SQLServer, AzureSQL, PostgreSQL, Derby, DB2 for z/OS, DB2 for LUW, Cassandra, Teradata, and Data source provider

Default:
MySQL

For more information about which data source to use, consult your database administrator or your system administrator.

Name

The name for your data source. Enter a name that coordinates with your API definition. You can modify the name.

CA Live API Creator uses this name as the file name for source control management (SCM) when you export the API.

Unique:
Yes

Prefix

The prefix for your data source connection. Data source prefixes help identify the database objects (for example, entities (tables or views) and procedures). Resources, rules, and JavaScript reference multiple data sources using the prefix. If you have more than one active database in your API, the prefix specifies the database that CA Live API Creator should use, for instance, when defining rules and resources.

References to entities (table or view)–for example, in rules, resource definitions, and role permissions–include the prefix.

The data source prefix is represented as text, so you can swap test/production databases.

Maximum length:
20 characters

Case Sensitive:
Yes

Best Practice:
Choose your data source prefix name carefully. Consider using a memorable word, for example

demo

customers

Managed data server

Create data source using

The category of data source. Select

Managed data server

. For more information about how to add a data source for a managed database from an existing API, see Managed Data Server Administration.

Values:

JDBC data source.
A JDBC data source for a JDBC driver that CA Live API Creator supports.

For more information about the JDBC drivers that CA Live API Creator supports, see Installation Requirements and Supported Platforms.

If you require a connection to a data source that CA Live API Creator does not support, create a data source provider.

For more information, see Manage Data Source Providers.

Managed data server.
A data source for a managed database.

JNDI named data source.
A data source through a Java container's JNDI definition.

Data source provider.
A data source for a generic data source.

Default:
JDBC data source

Managed data server

Defines the managed data server that you want to use to create your data source. If you do not have an active managed data server defined, create one.

Name

The name for your data source. Enter a name that coordinates with your API definition. You can modify the name.

CA Live API Creator uses this name as the file name for source control management (SCM) when you export the API.

Unique:
Yes

Prefix

References to entities (table or view)–for example, in rules, resource definitions, and role permissions–include the prefix.

The data source prefix is represented as text, so you can swap test/production databases.

Maximum length:
20 characters

Case Sensitive:
Yes

Best Practice:
Choose your data source prefix name carefully. Consider using a memorable word, for example

demo

customers

JNDI named data source

Create data source using

The category of data source. Select

JNDI named data source

Values:

JDBC data source.
A JDBC data source for a JDBC driver that CA Live API Creator supports.

For more information about the JDBC drivers that CA Live API Creator supports, see Installation Requirements and Supported Platforms.

If you require a connection to a data source that CA Live API Creator does not support, create a data source provider.

For more information, see Manage Data Source Providers.

Managed data server.
A data source for a managed database.

JNDI named data source.
A data source through a Java container's JNDI definition.

Data source provider.
A data source for a generic data source.

Default:
JDBC data source

Name

The name for your data source. Enter a name that coordinates with your API definition. You can modify the name.

CA Live API Creator uses this name as the file name for source control management (SCM) when you export the API.

Unique:
Yes

JNDI data source name

Specifies the name of your JNDI data source in your Java container, without the

jdbc/

prefix. For example,

MyJNDIDatasource

Prefix

References to entities (table or view)–for example, in rules, resource definitions, and role permissions–include the prefix.

The data source prefix is represented as text, so you can swap test/production databases.

Maximum length:
20 characters

Case Sensitive:
Yes

Best Practice:
Choose your data source prefix name carefully. Consider using a memorable word, for example

demo

customers

Data source provider

Create data source using

The category of data source. Select

Data source provider

Values:

JDBC data source.
A JDBC data source for a JDBC driver that CA Live API Creator supports.

For more information about the JDBC drivers that CA Live API Creator supports, see Installation Requirements and Supported Platforms.

If you require a connection to a data source that CA Live API Creator does not support, create a data source provider.

For more information, see Manage Data Source Providers.

Managed data server.
A data source for a managed database.

JNDI named data source.
A data source through a Java container's JNDI definition.

Data source provider.
A data source for a generic data source.

Default:
JDBC data source

Name

The name for your data source. Enter a name that coordinates with your API definition. You can modify the name.

CA Live API Creator uses this name as the file name for source control management (SCM) when you export the API.

Unique:
Yes

Data source provider

Specifies the data source provider for your data source. The list of options are the data source providers that the system administrator (sa) user added.

For more information about data source providers, see Manage Data Source Providers.

Prefix

References to entities (table or view)–for example, in rules, resource definitions, and role permissions–include the prefix.

The data source prefix is represented as text, so you can swap test/production databases.

Maximum length:
20 characters

Case Sensitive:
Yes

Best Practice:
Choose your data source prefix name carefully. Consider using a memorable word, for example

demo

customers

The data source is added. The Connection
tab for the data source appears.

Modify the following fields as required, and then save your changes:

JDBC data source

Title

The description of your data source.

Optional:
Yes

Type

The type of data source. This is the type that you specified when you created your data source.

Read-only:
Yes

URL

The database-dependent URL that points to your data source. When you create an API and you connect to an empty database, the database name is appended to this URL.

Examples:

MySQL database URL example:

jdbc:mysql://<server-name>[:port-number (default 3306)]/[database-name]

Derby database URL example:

jdbc:derby:C:\LAC\Jetty\projects\Market\MarketDB

DB2 for LUW database URL example:

jdbc:db2://<host>:<port>/<schema_name>

Database/Catalog name

The database or catalog name that points to your schema on the database server.

Case Sensitive:

Derby, DB2, Oracle, DB2 for zOS, DB2 for LUW:
Lowercase is equivalent to uppercase. For example, foo, Foo, and FOO are all equivalent and are converted to uppercase (FOO). If the value uses mixed case, enclose the name in double quotes ("), for example "Foo". You can enclose the value with the correct case and unusual characters using double-quotes (").

Example:
(For DB2 for LUW) LAC_DEMO_EBSDEV

PostgreSQL:
Uppercase is equivalent to lowercase. For example, foo, Foo and FOO are all equivalent and are converted to lowercase (foo). If the value uses mixed case, enclose the name in double quotes, for example "Foo". You can enclose the value with the correct case and unusual characters using double-quotes (").

SQL Server:
Case-insensitive but case-remembering. Enter the value exactly as it is spelled, using the correct case. You can enclose the value with the correct case and unusual characters using double-quotes (").

MySQL:
The name's behavior depends on the the underlying operating system and configuration settings. You can quote the value when required by MySQL, enclose the value with the correct case, and enclose unusual characters using back-ticks (`).

User name

Your assigned user name in the database. You use this user name in CA Live API Creator to connect to the database.

Case Sensitive:

Example:
DBADMIN

Password

The password for the database. This is the password for the user that accesses this data source.

Maximum connections

The maximum number of connections CA Live API Creator uses to create a pool of database connections. Each request accessing this data source retrieves a connection from the pool, uses the connection, and then returns the connection to the pool.

Default:
20

If you change this value, then you must apply the changes to connection pooling by restarting API Server.

Managed data server

Title

The description of your data source.

Optional:
Yes

Type

The type of data source. This is the type that you specified when you created your data source.

Read-only:
Yes

URL

The database-dependent URL that points to your data source. When you create an API and you connect to an empty database, the database name is appended to this URL.

Examples:

MySQL database URL example:

jdbc:mysql://<server-name>[:port-number (default 3306)]/[database-name]

Derby database URL example:

jdbc:derby:C:\LAC\Jetty\projects\Market\MarketDB

DB2 for LUW database URL example:

jdbc:db2://<host>:<port>/<schema_name>

Schema/Owner name

Defines the name that points to your schema on the database or SaaS server.

Case Sensitive:

Example:
DBADMIN

User name

Your assigned user name in the database. You use this user name in CA Live API Creator to connect to the database.

Case Sensitive:

Example:
DBADMIN

Password

The password for the database. This is the password for the user that accesses this data source.

Maximum connections

The maximum number of connections uses to create a pool of database connections. Each request accessing this data source retrieves a connection from the pool, uses the connection, and then returns the connection to the pool.

Default:
20

If you change this value, then you must apply the changes to connection pooling by restarting API Server.

JNDI named data source

Title

The description of your data source.

Optional:
Yes

Type

The type of data source. This is the type that you specified when you created your data source.

Read-only:
Yes

URL

The database-dependent URL that points to your data source. When you create an API and you connect to an empty database, the database name is appended to this URL.

Examples:

MySQL database URL example:

jdbc:mysql://<server-name>[:port-number (default 3306)]/[database-name]

Derby database URL example:

jdbc:derby:C:\LAC\Jetty\projects\Market\MarketDB

DB2 for LUW database URL example:

jdbc:db2://<host>:<port>/<schema_name>

User name

Your assigned user name in the database. You use this user name in CA Live API Creator to connect to the database.

Case Sensitive:

Example:
DBADMIN

Password

The password for the database. This is the password for the user that accesses this data source.

Maximum connections

Default:
20

If you change this value, then you must apply the changes to connection pooling by restarting API Server.

Data source provider

Title

Defines the description of your data source.

Required:
No

Type

Defines the type of data source. This is the type that you specified when you created your data source.

Read-only:
Yes

Data source provider

Defines the name of your data source provider.

Read-only:
Yes

Hostname

Defines the hostname of your database. For example:

localhost

Required
: Yes

Port

Defines the port number of your host. For example,

27017

Database Name

Defines the name for your database.
Required
: Yes

Username

Your assigned user name in the database. You use this user name in CA Live API Creator to connect to the database.

Case Sensitive:

Example:
DBADMIN

Password

Your password for the database to access this data source.

Activate your data source connection by selecting the Active
checkbox. Selecting this checkbox also makes this data source the database for your API. You can test the connections only for active data source connections. If CA Live API Creator is unable to connect to the database successfully, CA Live API Creator clears this checkbox and marks the database as inactive. By default, this checkbox is cleared.

(JDBC data sources and JNDI named data sources) Select the following options:

Schema is editable

Select this checkbox to allow users with the appropriate credentials to edit the schema for this database.

Default:
Cleared

Log errors

Select this checkbox if you want to record database-level errors for this data source in the log.

Default:
Cleared

Selecting this checkbox can impact performance.

Test the data source connection by clicking Test Connection
.

API Creator verifies the connection to this database.

CA Live API Creator must successfully connect to the database before you can use this data source as the database for your API.

The database connection is added.

: Recent versions of MySQL might require that you connect to MySQL using Secure Sockets Layer (SSL). The following error message indicates this requirement:

javax.net.ssl.SSLException: Unsupported record version Unknown-0.0

You can avoid this MySQL SSL exception.

For more information about how to avoid this exception, see Troubleshooting.

Next Steps

Complete the following procedures after you have added a connection to a database.

Define Schema Filters

When you connect to a database, you can have CA Live API Creator access only a small part of the database instead of reading the schema, including all tables, views, stored procedures, and database functions in the given schema. You can do this by defining schema filters.

For more information, see Define Schema Filters.

Associate Entities to Sequences

If you added a connection to a database and your entities use sequences, associate your entities to sequences.

For more information, see Associate Entities to Sequences.

Manage your Data Sources

Use the following topics to manage your data sources.

View your Schema

When you connect to a database, API Creator reads your database schema. API Creator references the database objects and determines the names (table names, view names, column names, foreign key names, and procedures) from the database or catalog. API Creator creates endpoints, or resources, and the JavaScript object model using the database schema.

The schema page shows your tables, views, and stored procedures.

CA Live API Creator escapes the periods (.) found in tables, stored procedures, and function names by appending a tilde (~).

Best practice:
Call endpoints using the

href

attribute that is in the

@metadata

section of a JSON response.

Follow these steps:

With your API open, in the Create section, click Schema
.

The Tables
tab displays by default.

Complete one of the following:

To view your tables, click the Tables
tab.

To view your views, click the Views
tab.

To view your stored procedures, database functions, and packages, click the Procedures
tab.

Debug Database Issues

You can debug database issues using the error log that displays on the Error log page.

Follow these steps:

In the Create section, click Data Sources
.

The Connection tab displays by default.

Click Error log
.

The error log displays.

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.

Follow these steps:

Export your API.

For more information about how to export APIs, see Import and Export APIs.

Change the data source prefix in a text editor.

Import your API.

Data Source Best Practices

Define explicit associations between child entities (tables or views) and the parent entity. Define foreign keys by way of validations, including a validation name. You can add virtual relationships across schemas by defining relationships between databases.

For more information:

About how to define foreign keys by way of validations, see Validation Rule Type.

About how to define relationships between databases, see Manage Relationships.

About a list of the best practices, see Best Practices.

Creating APIs

Microsoft SQL Server Data Source

Oracle Data Source

IBM DB2 Data Source

MySQL Data Source

PostgreSQL Data Source

Derby Data Source

Teradata Data Source