Developing Data API Statements

SQL Syntax

The SQL syntax differences between a data API and each database are as follows:

To transfer parameters carried in a backend request to an SQL statement, use ${parameter name} to mark the parameters. Parameters of the String type must be enclosed in single quotation marks, whereas parameters of the int type do not need to be enclosed.
In the following example, name is a parameter of the String type and id is a parameter of the int type.
1

select * from table01 where name='${name}' and id=${id}
Parameters can be transferred in the headers, parameters, or body of backend requests.
If the character string in an SQL statement contains keywords, you must escape the character string.
For example, if a field name is delete, the SQL statement must be written in the following format:
1

select `delete` from table01

If an SQL statement references backend request parameters of multiple data types, ROMA Connect converts the input parameters to the String type by default. Therefore, when the SQL statement is executed, ROMA Connect needs to invole the corresponding function to convert the data type of the non-String parameters.

For example, if both the name (String type) and id (int type) parameters are transferred to an SQL statement, the id parameter will be converted to the String type. Therefore, you need to use a conversion function to convert the id parameter back to the int type in the SQL statement. The following uses the cast() function as an example. The conversion function varies depending on the database type in use.

       
            select * from table01 where name='${name}' and id=cast('${id}' as int)

SQL query examples (similar to the update and insert commands):

Full query
1

select * from table01;
Query with parameters specified
Transfer parameters (Headers, Parameters, or Body) carried in backend requests to SQL statements to provide flexible conditional query or data processing capabilities for the SQL statements.
- For APIs using the GET or DELETE method, obtain parameters from the request URL.
- For APIs using the POST or PUT method, obtain parameters from the request body. Note: The body is in application/x-www-form-urlencoded format.
1

select * from table01 where 1=1 and col01 = ${param01};

Query with optional parameters

       
            select * from table01 where 1=1 [and col01 = ${param01}] [and col02 = ${param02}]

IN query

       
            select * from table01 where 1=1 and col01 in ('${param01}','${param02}');

UNION query

By default, duplicate data will be deleted. To return all data, use the keywords union all.

        
             select * from table01 
union [all | distinct]
select * from table02;

Nested query

       
            select * from table01 where 1=1 and col01 in (select col02 from table02 where col03 is not null);

Native commands compatible with NoSQL (such as MongoDB and Redis):

Command formats supported by the Redis data source:
GET, HGET, HGETALL, LRANGE, SMEMBERS, ZRANGE, ZREVRANGE, SET, LPUSH, SADD, ZADD, HMSET, DEL
Command formats supported by the MongoDB data source:
find

NoSQL examples:

Insert a key of the String type. The value is obtained from the request parameter.
```
set hello ${parm01}
```
Query the key of the String type.
```
get hello
```

Stored Procedure Calling

Currently, a data API cannot create stored procedures, but can execute stored procedures of MySQL, Oracle, and PostgreSQL data sources. The following uses the Oracle database as an example.

Data source description

Assume that the database contains a table. The table structure is as follows:

      
           create table sp_test(id number,name varchar2(50),sal number);

Insert data into the table.

**Table 1** sp_test table data set
ID	NAME	SAL
1	ZHANG	5000
2	LI	6000
3	ZHAO	7000
4	WANG	8000

The Oracle database contains a stored procedure for querying the value of sal based on name.

      
           create or replace procedure APICTEST.sb_test(nname in varchar, nsal out number) as
begin
	select sal into nsal from sp_test where name = nname;
end;

Statements in a data API
When a data API invokes a stored procedure, parameters can be transferred through Headers, Parameters, or Body of a backend request. The syntax of a parameter name is as follows: {Parameter name}.{Data type}.{Transmission type}.
- The data type can be String or int.
- Transmission type indicates whether the parameter is an input parameter or output parameter. in indicates an input parameter, and out indicates an output parameter.
The following is an example statement used for invoking the stored procedure in the data API:
1

call sb_test(${nname.String.in},${nsal.int.out})
In the example script, nname is an input parameter of the String type and the parameter name is nname.String.in. The value is the parameter to be queried. nsal is an output parameter of the numeric type and the parameter name is nsal.int.out. Due to the format restriction, the value of the output parameter must be set. You can set it to any value that meets the data type requirements, which does not affect the output result.
- The data API uses String and int to distinguish character strings and values when invoking a stored procedure. Single quotation marks are not required. This is different from the SQL requirement.
- The parameter names defined in Headers, Body, or Parameters of a backend request must be different. Otherwise, they will be overwritten.
- The following is an example of transferring parameters in Body:
  Body of the backend request:
```
{
  "nname.String.in": "zhang",
  "nsal": 0
}
```
  Response result:
```
{
  "test": [
    5000
  ]
}
```
- The following is an example of transferring parameters in Parameters:
  Parameters of the backend request:
```
https://example.com?nname.String.in=zhang&nsal=0
```
  Response result:
```
{
  "test": [
    5000
  ]
}
```

Data Source Orchestration

A data API can contain multiple data sources. Therefore, an API request can involve multiple data sources. For example, the query result of the first data source can be used as the parameter of the second data source.

MySQL is used as an example. Assume that the data API contains data source 1 and data source 2, user01 is the data table of data source 1, and user02 is the data table of data source 2. The structures of the two tables are as follows:

**Table 2** Table structures
Data Source	Table Name	Parameter
Data source 1	user01	id (int) name (varchar)
Data source 2	user02	user_id (int) user_name (varchar) user_age (int) user_sex (varchar)

The data source SQL statement is designed as follows:

For data source 1, query the ID of the data record whose name is zhang in table user01.

    
         select id from user01 where name='zhang';

For data source 2, find the corresponding data record in table user02 based on the ID found in table user01, and change the value of user_name in the record to zhang.

    
         update user02 set user_name='zhang' where user_id=${result1[0].id};

Usage of Optional Parameters

In a data API, the square brackets ([]) are used to mark optional parameters. An example SQL statement is as follows:

    
         select * from table01 where id=${id} [or sex='${sex}']

The statement enclosed in square brackets ([]) indicates that the parameter takes effect only when the backend request carries the ${sex} parameter. If ${sex} is not carried, the statement enclosed in [] is ignored during execution.

If the backend request carries the id=88 parameter but does not carry the optional parameter sex, run the following SQL statement:
1

select * from table01 where id=88;
If the backend request carries both id=88 and sex=female, run the following SQL statement:
1

select * from table01 where id=88 or sex='female';