Input mappings allow you to add parameters to a query so that you can set the parameter value when executing the query. For example, when you define a query as
SELECT *, FROM TEST_TABLE, WHERE ID=1, an input mapping is a parameter that sets the value of ID.
Generating input mappings
If you have defined an SQL query, you can generate input mappings corresponding to the input fields specified in the query by clicking Generate Input Mappings. As shown in the example below, an input mapping is created for the emp_no field, which will allow you to invoke this query by specifying a value for this field as an input.
Adding input mappings manually
If you are manually adding input mappings, click Add New Input Mappings to open the following page:
The following options are available when you create an input mapping:
Note that ARRAY parameter type cannot be used with the QUERY_STRING data type (SQL type).
The data type of the corresponding SQL parameter can be selected from this menu. Note that the QUERY_STRING data type cannot be used if the parameter type is set to ARRAY. Find more from here about data types.
Default values help you automatically assign a value to a parameter when a user has not entered a specific parameter value in a request. Default values are added when defining queries. Therefore, this value gets automatically added to the query if it is ignored by the user.
You can refer to Internal Property Values using Default Values. You can use special system variables that are defined as default values. At the moment, it only provides a variable for retrieving the username of the current user authenticated in a secured data service. You can access this variable as follows:
For a demonstration of the usage of default values, see Default Values Sample.
These are used in stored procedures which takes out parameters and in/out parameters. IN is the usual parameter we give to provide some value inside. OUT only returns a value from a stored procedure. INOUT does both.
Validators are added to individual input mappings in a query. Input validation allows data services to validate the input parameters in a request and stop the execution of the request if the input doesn’t meet required criteria. The ESB profile of WSO2 EI provides a set of built-in validators for some of the most common use cases. It also provides an extension mechanism to write custom validators.
Long Range validator
Validates if an integer value is in the specified range. The validator requires a minimum and a maximum value to set the range. For example,
Double Range validator
Validates if a floating point is in the specified range. The validator requires a minimum and a maximum value to set the range. For example,
Validates the string length of a given parameter against a specified length. For example,
Validates the string value of the parameter against a given regular expression. For example,
Used to add your own validation logic by implementing the interface
org.wso2.carbon.dataservices.core.validation.Validator. The definition of the interface is as follows:
If the validation fails, the validate method in the interface by default throws an exception of type
ValidationException . The parameters of the method are as follows:
- context : Is of type
ValidationContext, which contains information about the full set of parameters passed into the request. When the validation logic depends on other parameters, the validation context can be used to check the names/values of the rest of the parameters.
- name : A string value that represents the name of the parameter to be validated.
- value : Is of type
ParamType, which represents the value of the parameter to be validated. It is either
If you need to provide properties when initializing the custom validator, it is necessary to implement the
org.wso2.carbon.dataservices.core.validation.ValidatorExt interface. This extends the Validator interface as shown below.
The init method initializes the set of properties provided for the custom validator via the management console or the configuration file of the data service. See the examples given below.
- Properties provided via the management console:
- Properties provided via the data service configuration file:
After creating a custom validator class, package it in a JAR file and store it in the server's classpath location for external libraries (which is the
Returning generated keys
The Return Generated Keys option appears under Input Mappings on the Queries page.
It inserts data to a table that has auto increment key columns. The auto incremented key value of the record is mapped to the result output mappings of the data service. For example, the sample query below is used to insert values to a table by the name
wes_teams, which has an auto increment column:
INSERT INTO wes_teams(TEAM) VALUES(?)
Once the user selects
Return Generated Keys option, an auto increment key is added as an output mapping as follows:
Returning updated row count
With the current data services functionality, we don't have a way to indicate that the update operation did not affect any rows. But, we can return the updated row count as a response to the client in queries like update/insert to indicate how may rows are affected by the query execution.