Help Center> ROMA Connect> Developer Guide> Developer Guide for Service Integration> Developing Custom Data Backends> SQL Syntax
Updated on 2023-05-17 GMT+08:00
View PDF

SQL Syntax

SQL Syntax Differences Between Data Backends and Databases

  • 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.

    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:

    select `delete` from table01
  • If Precompiling is selected during data backend configuration, input parameters are used for fuzzy match query, and the match field contains %, use the CONCAT function for concatenation.

    In the following example, name is a string.

    select * from table01 where name like concat('%',${name})

If precompiling has been enabled and an SQL statement references backend request parameters of multiple data types, the input parameters will be converted to String by default. Therefore, when the SQL statement is executed, the corresponding function needs to be called to convert 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 UPDATE and INSERT)

  • 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.
    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