Database Connectivity
Database Connectivity
lac42
HID_database_connectivity
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, clickData 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, clickAdd.The Create data source window opens.
- Create the data source by completing the following fields, and then clickingAdd:JDBC data sourceCreate data source usingThe category of data source. SelectJDBC data source.Values:
- JDBC data source.A JDBC data source for a JDBC driver thatCA Live API Creatorsupports.For more information about the JDBC drivers thatCA Live API Creatorsupports, see Installation Requirements and Supported Platforms.If you require a connection to a data source thatCA Live API Creatordoes 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 sourceData source typeSpecifies 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 providerDefault:MySQLFor more information about which data source to use, consult your database administrator or your system administrator.NameThe name for your data source. Enter a name that coordinates with your API definition. You can modify the name.CA Live API Creatoruses this name as the file name for source control management (SCM) when you export the API.Unique:YesPrefixThe 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 thatCA Live API Creatorshould 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 charactersCase Sensitive:YesBest Practice:Choose your data source prefix name carefully. Consider using a memorable word, for example
ordemo
.customersManaged data serverCreate data source usingThe category of data source. SelectManaged 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 thatCA Live API Creatorsupports.For more information about the JDBC drivers thatCA Live API Creatorsupports, see Installation Requirements and Supported Platforms.If you require a connection to a data source thatCA Live API Creatordoes 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 sourceManaged data serverDefines 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.NameThe name for your data source. Enter a name that coordinates with your API definition. You can modify the name.CA Live API Creatoruses this name as the file name for source control management (SCM) when you export the API.Unique:YesPrefixThe 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 thatCA Live API Creatorshould 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 charactersCase Sensitive:YesBest Practice:Choose your data source prefix name carefully. Consider using a memorable word, for example
ordemo
.customersJNDI named data sourceCreate data source usingThe category of data source. SelectJNDI named data source.Values:- JDBC data source.A JDBC data source for a JDBC driver thatCA Live API Creatorsupports.For more information about the JDBC drivers thatCA Live API Creatorsupports, see Installation Requirements and Supported Platforms.If you require a connection to a data source thatCA Live API Creatordoes 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 sourceNameThe name for your data source. Enter a name that coordinates with your API definition. You can modify the name.CA Live API Creatoruses this name as the file name for source control management (SCM) when you export the API.Unique:YesJNDI data source nameSpecifies the name of your JNDI data source in your Java container, without thejdbc/prefix. For example,MyJNDIDatasource.PrefixThe 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 thatCA Live API Creatorshould 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 charactersCase Sensitive:YesBest Practice:Choose your data source prefix name carefully. Consider using a memorable word, for example
ordemo
.customersData source providerCreate data source usingThe category of data source. SelectData source provider.Values:- JDBC data source.A JDBC data source for a JDBC driver thatCA Live API Creatorsupports.For more information about the JDBC drivers thatCA Live API Creatorsupports, see Installation Requirements and Supported Platforms.If you require a connection to a data source thatCA Live API Creatordoes 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 sourceNameThe name for your data source. Enter a name that coordinates with your API definition. You can modify the name.CA Live API Creatoruses this name as the file name for source control management (SCM) when you export the API.Unique:YesData source providerSpecifies 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.PrefixThe 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 thatCA Live API Creatorshould 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 charactersCase Sensitive:YesBest Practice:Choose your data source prefix name carefully. Consider using a memorable word, for example
ordemo
.customersThe data source is added. TheConnectiontab for the data source appears. - Modify the following fields as required, and then save your changes:JDBC data sourceTitleThe description of your data source.Optional:YesTypeThe type of data source. This is the type that you specified when you created your data source.Read-only:YesURLThe 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\MarketDBDB2 for LUW database URL example:jdbc:db2://<host>:<port>/<schema_name>Database/Catalog nameThe 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 nameYour assigned user name in the database. You use this user name inCA Live API Creatorto 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 (`).
PasswordThe password for the database. This is the password for the user that accesses this data source.Maximum connectionsThe maximum number of connectionsCA Live API Creatoruses 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:20If you change this value, then you must apply the changes to connection pooling by restarting API Server.Managed data serverTitleThe description of your data source.Optional:YesTypeThe type of data source. This is the type that you specified when you created your data source.Read-only:YesURLThe 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\MarketDBDB2 for LUW database URL example:jdbc:db2://<host>:<port>/<schema_name>Schema/Owner nameDefines 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 nameYour assigned user name in the database. You use this user name inCA Live API Creatorto 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 (`).
PasswordThe password for the database. This is the password for the user that accesses this data source.Maximum connectionsThe 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:20If you change this value, then you must apply the changes to connection pooling by restarting API Server.JNDI named data sourceTitleThe description of your data source.Optional:YesTypeThe type of data source. This is the type that you specified when you created your data source.Read-only:YesURLThe 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\MarketDBDB2 for LUW database URL example:jdbc:db2://<host>:<port>/<schema_name>User nameYour assigned user name in the database. You use this user name inCA Live API Creatorto 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 (`).
PasswordThe password for the database. This is the password for the user that accesses this data source.Maximum connectionsThe 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:20If you change this value, then you must apply the changes to connection pooling by restarting API Server.Data source providerTitleDefines the description of your data source.Required:NoTypeDefines the type of data source. This is the type that you specified when you created your data source.Read-only:YesData source providerDefines the name of your data source provider.Read-only:YesHostnameDefines the hostname of your database. For example:localhost.Required: YesPortDefines the port number of your host. For example,27017.Database NameDefines the name for your database.Required: YesUsernameYour assigned user name in the database. You use this user name inCA Live API Creatorto 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 (`).
PasswordYour password for the database to access this data source. - Activate your data source connection by selecting theActivecheckbox. Selecting this checkbox also makes this data source the database for your API. You can test the connections only for active data source connections. IfCA Live API Creatoris unable to connect to the database successfully,CA Live API Creatorclears 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 editableSelect this checkbox to allow users with the appropriate credentials to edit the schema for this database.Default:ClearedLog errorsSelect this checkbox if you want to record database-level errors for this data source in the log.Default:ClearedSelecting this checkbox can impact performance.
- Test the data source connection by clickingTest Connection.API Creator verifies the connection to this database.CA Live API Creatormust 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, clickSchema.TheTablestab displays by default.
- Complete one of the following:
- To view your tables, click theTablestab.
- To view your views, click theViewstab.
- To view your stored procedures, database functions, and packages, click theProcedurestab.
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, clickData Sources.The Connection tab displays by default.
- ClickError 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.