Dynamic query

allows the creation of SQL queries on the fly

Enabling flexibility in constructing complex queries based on changing criteria, user input, or business logic. This can involve adding or removing conditions, altering select columns, or changing table names.

The advantages of dynamic queries include flexibility, as they allow applications to adapt to changing requirements, user preferences, or evolving data conditions. However, they may also introduce risks like SQL injection if not handled securely. Therefore, precautions are essential to ensure the safety and reliability of dynamically generated queries in applications.

Dynamic queries assist in resolving functionalities, such as creating a REST service for a UI table. Such a service needs to provide the possibility of using a variable number of options for ordering.

select * from "orders" 
    where "createdAt" >= '01-01-2020' and "createdAt" < '01-01-2024'
    order by "name" desc, "createdAt" asc, "type" desc
        limit 20 offset 10

SQL

SQL should be defined and configured at runtime. The best scenario is to use pdk.core.String flow local variable.

Placeholders for variables should be enclosed by curly brackets.

ordersQuery :: String = 'select * from "orders"' //FLOW local variable

supplierId - input parameter

1. Add WHERE clause
dateFrom :: String = '01-01-2020' //FLOW local variable
dateTo :: String = '01-01-2024' //FLOW local variable

ordersQuery = ordersQuery +
    ' where "supplierId" = {supplierId} 
        and "createdAt" >= ' + dateFrom + 
      ' and "createdAt" < ' + dateTo

2. Add ORDER BY 
nameOrderType :: String = 'desc' //FLOW local variable
createdAtOrderType :: String = 'asc' //FLOW local variable
typeOrderType :: String = 'desc' //FLOW local variable

ordersQuery = ordersQuery +
    ' order by "name" ' + nameOrderType +
    ' order by "createdAt" ' + createdAtOrderType +
    ' order by "type" ' + typeOrderType
    
3. Add LIMIT and OFFSET
limitValue :: Integer = 20
offsetValue :: Integer = 10 

ordersQuery = ordersQuery + 
    ' limit ' + limitValue + ' offset ' + offsetValue

Input parameters

All input parameters are optional but need to be defined. System maps provided values into placeholders defined in the sql query string.

The parameter name is indicated by the text enclosed within curly brackets { }. The name should consist only of letters, digits, and underscore characters, , and must start with a letter.

ordersQuery :: String = 'select * from "orders"' //FLOW local variable

1. Add WHERE clause
ordersQuery = ordersQuery +
    ' where "createdAt" >= {dataFrom} and "createdAt" < {dataTo}'
    
dataFrom, dataTo - variables that will be defined during flow execution

Valid parameters names are:

{myParam1_} - valid
{1myParam} - not valid
{_myParam} - not valid
{myParam@$#^%&*!-+=} - not valid

The parameter type should be defined. Default type is: pdk.core.String

Possible types are:

PreviousPrepared queryNextREST Connector

Last updated