Skip to content

Data Access Service Reference Guide

SQL Service

The SQL service is a command service for writing and manipulating data in the data source. It comprises

  • an input
  • a sqls component
  • an input bindings component that map the input fields to the sql parameters
  • an optional output
  • an optional query, and
  • an optional output bindings component that map the output fields to the query columns, if the query is specified.

In SQL service, the query is optional. If no query is specified, the service return nothing. If the query is specified, the output and output bindings must also be specified.

The SQL service support a single execute operation.

File Structure

The file structure of the SQL service is as follows:

mySqlService/
    service.json
    input.json
    output.json
    sqls.sql
    query.sql
    input-bindings.json
    output-bindings.json
    tests/
        testMySqlService.json

This file structure is generated when the service is created. There is a file for each component of the SQL service. The user is expected to complete the input, sqls,and input bindings components. The output, query, and output bindings components are optional, depending on the requirement of the SQL service.

Example

The following is an exemplary SQL service for cloning a product line, with a query to return the new product line.

Input

{
    "newProductLine": "Electric Cars",
    "sourceProductLine": "Classic Cars"
}

The input is a simple or nested object providing SQL and query parameters. However, no array structure except array of values is allowed in the input object.

Output

{
    "productLine": "Electric Cars",
    "description": "The new generation cars",
    "products": {
        "productCode": "N_S10_4757",
        "productName": "Porsche 356-A Roadster"
    }
}

The output of the SQL service is an object or array of object from the query, if it is specified. In this example, the output is the new product line with the list of the products for the product line.

Sqls

insert into classicmodels.productlines (
    productLine, textDescription
)
select :newProductLine, textDescription
  from classicmodels.productlines
 where productLine = :sourceProductLine
;

insert into classicmodels.products (
    productLine, productCode, productName
)
select :newProductLine, concat('E-', productCode), productName
  from classicmodels.products
 where productLine = :sourceProductLine

The sqls component is a list of DML statements separated with a ";" at the end of the last line of the SQL statement. The SQL parameters may be a value or a value list. In this example, the sqls component includes two INSERT ... SELECT statements to copy the source product line and the products associated with the source product line. It is an efficient way to perform this task.

Query

select pl.productLine, pl.textDescription, p.productCode, p.productName
  from classicmodels.productlines pl, classicmodels.products p
 where p.productLine = pl.productLine
   and pl.productLine = :newProductLine

The query component of the SQL service is a single SELECT statement. The query parameters may be a value or a value list. In this example, the query returns the new product line along with its products.

Input Bindings

[
    {
        "parameter": "sourceProductLine",
        "field": ".sourceProductLine"
    },
    {
        "parameter": "newProductLine",
        "field": ".newProductLine"
    }
]

The input bindings of the SQL service map the input fields to the sql and query parameters.

Output Bindings

[
[
    {
        "field": ".productLine",
        "column": "productLine"
    },
    {
        "field": ".description",
        "column": "textDescription"
    },
    {
        "field": ".products..productCode",
        "column": "productCode"
    },
    {
        "field": ".products..productName",
        "column": "productName"
    }
]

The output bindings map the output fields to the query columns.

The user is expected to generate the input and output bindings from the input, output, sqls and query with Service Builder, and then review and edit them if needed. The Service Builder is expected to generate the input and output bindings accurately in normal case, but the user is ultimately responsible for the accuracy of the input and output bindings.

DDL and Substitution Variables

SQL service is mainly for data manipulation, but it does support DDL statement in the sqls component and substitution variables in the DDL statements. For example, we may have

create table  test$testNo

in the sqls component, where $testNo is a substitution variable that maps to an input data field of the SQL service by name.

By default, the variableLength property in the service.json file is set to zero and no substitution variable is allowed in the SQL statements. To enable substitution variables, the variableLength property needs to be set to a value greater than zero, based on the needs of the DDL statements. This property sets a limit on the number of characters that the substitution variables may assume, to minimize the risk of SQL injection. The maximum value enforced by the service engine for the variableLength property is 30.

Batch Input

SQL service supports batch input. That means that, assuming that we have a SQL service to add test score for a student as:

Input

{
    "studentId": 1,
    "score": 89
}

Sqls

insert into test_score (student_id, score)
values (:studentId, :score)

we may call this service with an array of input objects, as

[
    {
        "studentId": 1,
        "score": 89
    },
    {
        "studentId": 2,
        "score": 99
    },
    ...
]

The SQL service will execute multiple times, once for each input object in the array, as one transaction. For SQL service with query, the query will be executed only once at the end of the service. This is something we need to consider when making service request with batch input.