Database Connectivity

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:
 
Follow these steps:
 
  1. 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.
  2. Above the list of data source connections, click 
    Add
    .
    The Create data source window opens.
  3. 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
    or
    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
     
    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
    or
    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
     
    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
    or
    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
     
    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
    or
    customers
    .
    The data source is added. The 
    Connection
     tab for the data source appears.
  4. 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:
     
      •  
        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:
         DBADMIN
      •  
        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 (`).
     
    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:
     
      •  
        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:
         DBADMIN
      •  
        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:
     
      •  
        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:
         DBADMIN
      •  
        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 (`).
     
    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:
     
      •  
        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:
         DBADMIN
      •  
        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 (`).
     
    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.
     
    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:
     
      •  
        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:
         DBADMIN
      •  
        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 (`).
     
    Password
     
    Your password for the database to access this data source.
  5. 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.
  6. (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.
  7. 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:
 
  1. With your API open, in the Create section, click 
    Schema
    .
    The 
    Tables
     tab displays by default.
  2. 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:
 
  1. In the Create section, click 
    Data Sources
    .
    The Connection tab displays by default.
  2. 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:
 
  1. Export your API.
    For more information about how to export APIs, see Import and Export APIs.
  2. Change the data source prefix in a text editor.
  3. 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: