Database Connectivity

lac311
Your APIs include a list of one or more registered databases and one or more SQL database locations. This list might include only the database connections that 
CA Live API Creator
created when you created your API. You can add database entries, such as test databases, integration databases, and a production database and define cross-database relationships. 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).
You can use multiple databases in the same API or combine resources, build rules between databases, and access them by defining multiple databases.
This article includes information about how to connect to your database and how to view your schema.
In this article:
3
Database Connections
You can connect to multiple databases, so these options are not restrictive. For example, you can click 
New empty Database
 and then connect your API to multiple databases. 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. Use this procedure to connect to your on-premise databases.
Use the following scenarios to connect to your database when creating new APIs:
  • Example Northwind database 
    (Provided in some
    CA Live API Creator
    configurations). This option illustrates how to create an API using a pre-defined database. The Northwind database operates in conjunction with the Tour, which provides a brief introduction to
    CA Live API Creator
    's functions.
    For more information about the Northwind database, see Northwind API Sample.
  • Target database
    . Use this option if you want to create an API for an existing database.
  • JNDI data source.
     Select this option if your connection information is not stored in the admin database or if you want greater flexibility in leveraging database management system (DBMS)-specific options, such as SSL access and mirroring.
     Firewalls require accommodations.
  • New empty database. 
    This option creates an API for a new managed database using API Creator and extended in Data Explorer.
    Prerequisite:
    Your managed data server must be defined and active.
    For more information about creating a new database using API Creator, see Managed Data Background.
  • No database
    . This option creates an API without an initial database connection. API without databases leverage base API Server for message listening and response generation. For an example, you can download the JSON sample file.
    For more information about this JSON sample file, see Create your API.
  • A new database using your current tools.
    In this scenario, you create your database using your current tools then connect to that database when you create your API.
For more information about connecting to a database when creating new APIs, see Create your API.
Add a Connection to a Database
  1. Go to the Integrate, Data Sources, Connection tab.
  2. Complete the following fields and save your changes:
    Database name
    The name for your database connection.
    Database prefix
    The prefix for your database connection. If you have more than one active database in your API, the prefix specifies which database to use, for instance when defining rules and resource definitions.
     Choose your database prefix name carefully, since renaming the prefix breaks rules and resource definitions.
    For more information specifying database prefixes and multiple databases, including how to connect your API to multiple databases, see Integrate Multiple Databases.
    Best Practice:
     Specify a database prefix using a memorable word like "demo" or "customers".
    Maximum length:
     20 characters
    Case Sensitive:
     Yes
    Database type
    Specifies what type of database you want to use. For more information about which database to use, consult your database administrator or system administrator.
    Values:
     MySQL, Oracle, SQLServer, AzureSQL, NuoDB (beta), PostgreSQL, Pervasive PSQL (beta), Salesforce, Derby, DB2 for z/OS, DB2 for LUW, Apache HBase, SAP Netweaver, CSV Files
    Database URL
    The URL that points to your database. This URL is heavily database-dependent. For more information, see the relevant documentation.
    Examples:
    • MySQL database URL example:
      jdbc:mysql://<server-name>[:port-number (default 3306)]/[database-name]
    • Derby database URL example:
      jdbc:derby:C:\espresso\Jetty\projects\Market\MarketDB
    Schema/Owner name
    The database-dependent 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 (").
    • 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
    The user name under which you access your database. Your database administrator assigns you a user name.
    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 (").
    • 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 user specified in the 
    User name
     field.
    Active
    Select this checkbox to use this database as the current database for your API and to test the connection to the database. If API Creator is unable to successfully connect to the database, this checkbox is cleared and the database is marked inactive.
    Log errors
    Select this checkbox if you want to record database-level errors for this data source in the Error log.
     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.
    Role name strategy
    The role name strategy for this data source.
    Values:
    • Standard:
       Generates consistent role names that are stable as foreign keys are added and removed from the database.
    • Deprecated:
       Provided for backward compatibility with existing APIs and is not recommended for new APIs.
    Do not change the role name strategy for existing APIs. Changing the role name strategy impacts existing rules and JavaScript calls that use these role names.
    Default:
    Standard
  3. Click
    Test Connection
    . The connection to the database is tested. API Creator verifies that API Creator can use this database properly using the information you provide in the fields on this page. API Creator runs a trivial query to test this connection. If the query is successful, the test is considered successful.
     
    CA Live API Creator
    must successfully connect to the database before you can mark the database as activate.
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.
Manage your Data Sources
Use the following topics to manage your data sources.
Debug Database Issues
Use the error log for debugging database issues. You can view the error log on the Integrate, Data Sources, Error log tab.
View your Schema
When you create your API and connect to your database, API Creator reads your database schema. API Creator references the database objects and determines the names (table names, column names, and foreign key names) from the database catalog. API Creator uses the database schema to create resources and the JavaScript object model.
 Table references are prefixed with the data source name.
For more information about the JavaScript object model, see Customize your API.
You can view your tables and views on the Schema page.
Follow these steps:
  1. With your API open, in the Integrate 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.
Database Administration
API Creator includes the Demo, Sample, and Your Database test database. This test 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 must reload it. You can configure your data source to access your database administration web tool using the fields on the Integrate, Data Sources, Details tab.
For more information about using managed databases, see Database Creation.
Reload Schemas
When you first create an active data source connection (the 
Active
 checkbox is selected on the Integrate, Data Sources, Connection tab), API Creator reads the database metadata and caches it in the API Server admin database. This metadata is shared between nodes. As the schema evolves and you change the schema, you must reload it. When you reload the schema, API Creator throws away the API Server cache, rereads the database metadata, and caches it in the API Server admin database.
On the Integrate, Schema, Tables tab, click 
Reload Schema
.
The time it takes API Creator to rescan the database metadata and update the cache depends on the size of the database and the latency to the database server.
Reload the Browser from the Cache
You can refresh the local copy of schema from 
CA Live API Creator
. API Creator reloads the browser from the cache (possibly update by other users) by polling the 
@tables
 system REST endpoint for your API.
Go to the Integrate, Schema, Tables tab and click the
Synch
icon.
For more information about the 
@tables
 system REST endpoint, see System REST Endpoints.
Export Schemas
You can export any schema from the current API data source listed.
  1. Go to the Integrate, Schema, Tables tab and click 
    Export Schema
    .
  2. Select the data source to export and then click 
    Export
    .
Your schema is exported.
Import Schemas
Prerequisite:
 The schema must be marked as editable (the 
Schema is editable
 checkbox on the Integrate, Data Sources, Connection tab is selected).
  1. Go to the Integrate, Schema, Tables tab and click 
    Import Schema
    .
  2. Select the data source to import and then click 
    Import
    .
Your schema is imported.
Verify your API after Schema Changes
The JavaScript object model always matches your current schema (subject to schema caching). Changes to your schema can make rule-based objects (for example, resources) invalid, due to dangling references.
  1. On the Integrate, Schema, Tables tab, reload the schema by clicking 
    Reload Schema
    .
  2. On the Create, API Properties, Details tab, verify your API by clicking 
    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. On the Analyze, Issues tab, review the issues.
    For more information about generating a list of API issues and reviewing the issues, see Manage API Issues.
Display Sequences
Databases that support sequences display the column and sequence name. The sequences are displayed on the Integrate, Data Sources, Sequences tab. The following image image shows sample sequences on this tab:
CA Technologies
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 these attributes in resources and rules. Just as with conventional systems, you must make design decisions about whether to persist derived data. If you decide to persist attributes, synchronize data with logic.
 For more information:
Synchronize Data with Logic
Logic processing interacts with values currently stored in the database.
Data Source Best Practices
We recommend explicit foreign key definitions. Define foreign keys by way of validations, including a validation name. You can define virtual foreign keys across multiple databases by defining relationships between databases.
For more information: