Skip to content

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:

  • SELECT
  • INSERT
  • UPDATE
  • DELETE
  • CALL of 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
{
  "dataApiDefinitionName": "getAll",
  "dataApiDescription": "Return all customer records",
  "sqlStatement": "SELECT * FROM DEMO_SCHEMA.CUSTOMERS",
  "uri": "/api/customers/all",
  "connectionName": "DemoConnection",
  "supportedHttpVerbs": [
    "GET"
  ],
  "readOnly": false
}

The following call makes the Hub run the SQL statement below it:

GET /api/customers/all
SELECT *
FROM DEMO_SCHEMA.CUSTOMERS

Example 2: SQL with variables

An SQL statement can contain named variables in the following form:

:<variableName>

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:

{
  "status": "ACTIVE",
  "customer_name": "John Smith"
}

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

  • limit is the maximum number of rows returned. The default is 100. A missing, non-numeric, zero, negative or greater than 1000 value is replaced with 100.
  • offset is the number of rows to skip. The default is 0. A missing, non-numeric or negative value is replaced with 0.

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:

{
  "status": "OK",
  "data": [],
  "meta": { "limit": 0, "offset": 0, "total": 0, "affectedRows": 3 }
}

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:

CALL DEMO_SCHEMA.GET_CUSTOMERS_BY_STATUS(:<status>)

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:

$count($) > 0
?
{
    "found":"1",
    "data":$[0]
}
:
{
    "found":"0",
    "data":{}
}

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:

jdbc:as400://<endpoint>;secure=<true|false>[;database name=<IASP>][;libraries=<library list>]

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