CA Live API Creator 4.1

5.4 5.3 5.2 5.1 5.0 4.1 4.0 3.2 3.1.1 3.1
English

Database Connectivity

Database Connectivity
calac41
HID_database_connectivity
You can set up connectivity to your database when you create your API or you can add a data source connection to your existing API. Your database can be in the cloud or on-premise. API Server requires access to your database, whether it be stored in the cloud or within your firewall.
For more information about how to set up connectivity to your database when creating your API, see Create your API.
You can connect to multiple databases in the same API or combine resources, build rules between databases, and access them by defining multiple databases. For example, you create an empty database and then connect your API to other databases. You can also connect to a new database using your current tools and then connect to that database when you create your API using API Creator.
API Creator update processing relies on database services for locking and transaction management. API Creator retrieval processing optimizes queries by leveraging the database engine's services, such as the index selection. The defined SQL joins determine how much API Creator can optimize the query.
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.
Follow these steps:
 
  1. 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
    :
    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.
    • Managed data server.
       A data source connected to the selected managed data server.
    • JNDI data source.
       A JDBC data source through a container's JNDI definition.
    Data source type
     
    Specifies the type of data source that you want to use.
    Values:
     MySQL, Oracle, PostgreSQL, Salesforce, Derby, Apache HBase, SQLServer, AzureSQL, DB2 for z/OS, DB2 for LUW, SAP Netweaver, CSV Files, Cassandra, and Teradata
    Default:
     MySQL
    For more information about which data source to use, consult your database administrator or system administrator.
    Data source name
     
    The name for your data source connection. Enter a name that coordinates with your API definition.
    CA Live API Creator
     uses this name as the file name for source code control management when you export the API.
    Unique:
     Yes
    Data source prefix
     
    The prefix for your data source connection. Database prefixes help identify the database objects (for example, tables, views, and procedures). Resources, rules, and JavaScript reference multiple data sources using the database 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 database prefix.
    The database prefix is represented as text, so you can swap test/production databases.
    Maximum length:
     20 characters
    Case Sensitive:
     Yes
    Best Practice:
     Choose your database prefix name carefully. Consider using a memorable word, for example "demo" or "customers".
    Your JDBC data source is added.
  4. Complete the following field, and then save your changes:
    Name
     
    The name for your data source. This is the 
    Data source name
     that you specified when you created the data source. You can modify the data source name.
    Unique:
     Yes
    Prefix
     
    The prefix for your data source connection. This is the 
    Data source prefix
     that you specified when you created the data source. You can change the data source prefix.
    Maximum length:
     20 characters
    Case Sensitive:
     Yes
    Changing the data source prefix breaks rules and resources.
    For more information about how to rename your database prefix, see Integrate Multiple Databases.
    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.
    When you are creating your data source, this field is the 
    Data source type
     field.
    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.
    Active
     
    Select this checkbox to activate this data source connection, to use this data source as the current database for your API, and to test the connection to the database. 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.
    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.
    Schema is editable
     
    Select this checkbox to allow users with the appropriate credentials to edit the schema for this database.
    Default:
     Cleared
  5. Click 
    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 database as the current 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.
Debug Database Issues
You can debug database issues using the error log.
Follow these steps:
 
  1. In the Create section, click 
    Data Sources
    .
    The Connection tab displays by default.
  2. Click 
    Error log
    .
    The Error log page displays.
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.
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.
    CA Live API Creator
     escapes the periods (.) found in table, stored procedure, and function names by appending a tilde (~).
    Best practice:
     Call endpoints by using the 
    href
     attribute that is in the 
    @metadata
     section of a JSON response.
Traverse Rules
  1. With your API open, in the Create section, click 
    Schema
    .
    The Tables tab displays by default.
  2. Double-click the rule in the Rules list.
    The rule opens.
Data Source Best Practices
We recommend explicit associations between a child entity (table or view) and its parent entity. Define foreign keys by way of validations, including a validation name. You can define virtual relationships across schemas by defining relationships between databases.
For more information: