The query in a data service specifies the type of task that should be performed on the data in a particular data store. For example, consider the task of retrieving data from a data store, or posting, updating, and deleting data. Data consumers send requests to the data service by invoking the relevant operation (or REST resource) in the data service. Consequently, the query connected to the operation/resource is executed to perform the task.
REST resources and Operations are used depending on whether the particular task should be invoked RESTfully, or by using SOAP. Read more about REST resource and operations in data services.
For most data stores, a query typically represents an SQL statement. However, some data stores such as Excel, and CSV, require queries to be specified differently.
A query in a data service consists of the following elements:
If the data store supports SQL, you need to specify the SQL statement to execute the required task. In other data stores (such as Google Spreadsheets), you need to specify the query details.
If you are writing an SQL query that requires an input value, you need to specify the parameters that can be used to provide the input. For example, if the SQL statement is to get the price of a particular product, it is necessary to provide the identifier of the product in the data store. In the Input Mapping section, you can create a parameter and map it to the column name of the product identifier in the database.
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.
|Generate Input Mapping|
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.
This is the data type of the input mapping, which determines how the input mapping parameter will be given in the target query.
Note that ARRAY parameter type cannot be used with the QUERY_STRING data type (SQL 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.
|IN/OUT Type||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.|
|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
Once the user selects
|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.
Just as Input mapping allows you to add parameters to a query, output mapping determines how the output of a query should be presented. Use this section to specify how the result of the query should be presented. You can choose XML, JSON, or RDF as the format of the result, along with the parameters that should be used to represent the data.
|Output Type||The output type determines the format in which the query output will be presented. You can select either XML, RDF or JSON.|
The following parameters are configurable for XML/RDF output types.
|Generate Output Mapping|
Note that this option is only available for
If you have defined an SQL query, you can generate output mappings corresponding to the fields specified in the query by clicking Generate Response. In the example shown below, there is an SQL query that needs to output values for the
|Use column numbers|
If this option is selected the mapping will be done by the column number basis instead of the column name. The following screenshot provides an example for using column numbers
|Escape non-printable characters||Tick this option if the data in your database consists of characters that are not serializable to XML. Few examples are & < > " '. When you invoke services that access such data and produce responses, the sever throws errors. Ticking this option ensures that non-printable characters will be ignored when producing the responses.|
|Row Namespace||See Defining Namespaces.|
|Query Result Export|
When you click Add New Output Mapping in the Result(Output Mapping) section of the Queries page as explained above, the Edit Output Mapping page will open. You can specify the type of fields that will present the output of your query by giving the data source type, output field name, data source column name etc.
In the Edit Output Mapping page, you can define query result export options. Query Request Export feature must be used in conjunction with request box. It allows individual queries executed in a request r to communicate with each other. The concept is 'exporting' a specific result element so that the next calling query will get that result element as a query parameter. So, if you've two queries, namely, 'query1' and 'query2' that's executed sequentially in a request box, and if 'query1' has a specific result element and that element is exported with the name 'foo', then 'query2' also gets a query param named 'foo'. So when this request box session is executed, the query1's exported value will be passed into query2 as an input parameter.
This feature is very useful in situations where the result of an earlier-executed query is required for the execution of a subsequent query (e.g. a newly created primay key).
The following figure shows how a result element can be declared to be exported with a given name when defining a query in a data service.
There are two export types that can be used.
For a demonstration on the usage of export options, refer to request box sample .
This section can be used to trigger notifications from the query.
Advanced query parameters
Advanced query properties help define additional features when querying the database. This option is available when adding queries to datasources such as RDBMS.
Query property details are described below.
Sets a timeout for the underlying JDBC query.
Forward - rows in a result set will be processed in a forward direction; first-to-last.
The number of rows that should be fetched from the database when more rows are needed. If the fetch size is zero, the JDBC driver ignores the value and is free to make its own best guess as to what the fetch size should be. Note that the fetch size is set to a lower value in the ESB profile of WSO2 EI by default. However, if you expect a very large number of rows to be fetched, you should increase the fetch size accordingly (e.g. 1000) to improve performance.
Max Field Size
Maximum data size for the field.
Maximum number of rows to be returned. Zero means all rows.
Force Stored Procedure
Forces the current SQl statement as a stored procedure.
Force JDBC Batch Requests
Forces to use native JDBC batch request.