Data API Configuration Guide
A Data API is an HTTP endpoint in InfoConnect Hub that runs an SQL statement on IBM i. You define the SQL in the Data API configuration, so you do not need a separate application or service. Clients call the Hub endpoint, and the Hub runs the SQL through the selected IBM i connection.
The SQL can be any single statement that IBM Db2 for i supports, for example:
SELECTINSERTUPDATEDELETECALLof a stored procedure
Each Data API runs exactly one SQL statement. The Hub does not interpret or generate SQL. It checks the statement against the rules in SQL statement rules and runs it as written. SQL variables pass values from the HTTP request into the statement.
Creating a Data API
A Data API consists of the following parts:
- API name
- SQL statement
- HTTP endpoint (URI)
- Supported HTTP methods
- IBM i connection
- Optional SQL variables
- Optional JSONata request and response transformations
After you create the API, clients call the configured endpoint on the Hub. The Hub runs the SQL on IBM i and returns the result. You can create a Data API in the web UI (see Data API in the Web UI Guide) or with the Admin API.
Example 1: running an SQL query
This example creates an endpoint that returns all rows of a table.
Sample JSON for the getAll Data API
The following call makes the Hub run the SQL statement below it:
Example 2: SQL with variables
An SQL statement can contain named variables in the following form:
The Hub resolves each variable value from the incoming HTTP request, according to the Source Field Name mapping of the variable. Program Calls, Data APIs and RPA Calls share this mapping mechanism. It reads values from the request body, HTTP headers, URI path parameters or query parameters. For the supported formats and examples, see Configuring Functional API Parameters.
In this example, both variables are mapped from the JSON request body.
Sample JSON for the insertCustomer Data API
{
"dataApiDefinitionName": "insertCustomer",
"dataApiDescription": "Insert customer",
"sqlStatement": "INSERT INTO DEMO_SCHEMA.CUSTOMERS (STATUS, CUSTOMER_NAME) VALUES (:<status>, :<customer_name>)",
"uri": "/api/customers",
"connectionName": "DemoConnection",
"supportedHttpVerbs": [
"GET",
"POST"
],
"readOnly": false,
"dataApiVariables": {
"variables": [
{
"variableName": "status",
"sourceFieldName": "$['status']",
"dataType": "STRING",
"length": 10,
"usage": "IN"
},
{
"variableName": "customer_name",
"sourceFieldName": "$['customer_name']",
"dataType": "STRING",
"length": 100,
"usage": "IN"
}
]
}
}
Example request body:
When a client calls the endpoint, the Hub substitutes the variable values and runs the SQL statement on IBM i.
SQL statement rules
The Hub validates the SQL statement when you create or update a Data API. It rejects the request with a validation error if the statement breaks one of these rules:
| Rule | Validation message |
|---|---|
| The statement must not be empty | Query for a missing or blank value, SQL query is empty for a statement such as ; |
| The statement must start with a word (letters only) | SQL query must start with a SQL keyword |
| The statement must contain more than just the keyword | SQL query must contain more than just the statement keyword |
SQL comments (-- and /* */) are not allowed |
SQL comments are not allowed |
Only one statement is allowed; a single trailing ; is accepted |
Multiple SQL statements are not allowed |
| Parentheses must be balanced | Unbalanced parentheses in SQL query |
| String literals must be closed | Unclosed string literal in SQL query |
| Quoted identifiers must be closed | Unclosed quoted identifier in SQL query |
Execution and response
How the Hub runs the statement depends on its type:
| Statement | Behavior |
|---|---|
SELECT or WITH |
The Hub reads the result in pages. The limit and offset query parameters control the page. |
CALL ... |
The Hub calls the stored procedure and returns its result. |
Any other statement (INSERT, UPDATE, DELETE, ...) |
The Hub runs the statement and returns the number of affected rows. |
Paging
limitis the maximum number of rows returned. The default is100. A missing, non-numeric, zero, negative or greater than1000value is replaced with100.offsetis the number of rows to skip. The default is0. A missing, non-numeric or negative value is replaced with0.
Response
The result of a SELECT is returned in the data field, together with the paging details in meta:
{
"status": "OK",
"data": [
{ "CUSTOMER_ID": 1, "STATUS": "ACTIVE" }
],
"meta": { "limit": 100, "offset": 0, "total": 1, "affectedRows": 1 }
}
For statements that change data, data is empty and meta.affectedRows contains the number of affected rows:
If the SQL statement fails, the Data API gets the FAILED status and the error message is stored with the API definition. The next successful execution sets the status back to ACTIVE. If reading a page of a SELECT result fails, the response has "status": "ERROR" and the error text in data.message, and the status of the API does not change. For the API states, see Managing Hub APIs.
Request validation
Before the Hub runs the SQL, it validates the values of the IN and INOUT variables:
- A value must be present. A default value configured on the variable counts as a provided value.
- The type of the value must match the variable
dataType. - The length of the value must not exceed the configured
length.
The Hub rejects an invalid request with HTTP 400 and does not run the SQL.
Validation is disabled by default. To switch it on for a Data API, set "schemaValidationEnabled": true in the Data API definition. For the validation rules and error messages, see Configuring Functional API Parameters.
Calling stored procedures
A Data API can call an existing stored procedure on IBM i. You create and maintain the procedure on IBM i. The Hub does not contain or run the business logic; it calls the procedure and returns its output.
For example, assume that the following procedure exists on IBM i:
CREATE OR REPLACE PROCEDURE DEMO_SCHEMA.GET_CUSTOMERS_BY_STATUS
(
IN P_STATUS VARCHAR(10)
)
DYNAMIC RESULT SETS 1
LANGUAGE SQL
READS SQL DATA
BEGIN
DECLARE C1 CURSOR WITH RETURN FOR
SELECT *
FROM DEMO_SCHEMA.CUSTOMERS
WHERE STATUS = P_STATUS;
OPEN C1;
END;
The SQL statement of the Data API is then:
When a client calls the endpoint, the Hub calls the stored procedure on IBM i and returns the result set that the procedure produces.
Response transformations
Like Program Call APIs, Data APIs support optional JSONata response transformations. A transformation reshapes the SQL result before the Hub returns it to the client. The paging details are still returned in meta next to the transformed data.
For example, the following transformation turns a result set into a custom response object:
JDBC connection string
Data APIs connect to IBM i over JDBC (Java Database Connectivity) with the IBM i connection selected in the Data API definition. By default, the Hub builds the JDBC URL from the connection settings:
To use your own JDBC URL, set the optional jdbcUrlOverride field in the connection definition (maximum 2000 characters). The value must start with jdbc:as400:. Otherwise, the Hub rejects the request with the message JDBC URL override must start with jdbc:as400:. Only Data APIs use the override.
The connection details returned by the Hub contain the read-only effectiveJdbcUrl field with the URL that is actually used, either the override or the built URL. In the web UI, you set the override in the Native Connection form, see the UI User Guide.
Connection pool
The Hub keeps a pool of JDBC connections for Data APIs. It uses a separate pool for each unique combination of connection settings: endpoint, user, secure flag, IASP, library list, JDBC URL override, password and truststore. A pool that has not been used for the configured time is closed automatically.
You can tune the pool in the Hub application.properties. The properties are optional; the Hub uses the defaults when they are not present.
| Property | Default | Description |
|---|---|---|
dataapi.hikari.maximum-pool-size |
5 |
Maximum number of connections in a pool |
dataapi.hikari.minimum-idle |
0 |
Minimum number of idle connections |
dataapi.hikari.connection-timeout |
30000 |
Time in milliseconds to wait for a connection from the pool |
dataapi.hikari.idle-timeout |
300000 |
Time in milliseconds after which an idle connection is closed |
dataapi.hikari.max-lifetime |
1800000 |
Maximum lifetime of a connection in milliseconds |
dataapi.hikari.pool-ttl-hours |
24 |
Hours of inactivity after which a whole pool is closed |