Writing Queries

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.
 

• SCALAR: In the target query, the parameter will be used as one value.
• ARRAY: In the target query, the parameter will contain one or many values for a mapped parameter.
  

In the context of RDBMS and SQL datasources, an ARRAY parameter mapped to an SQL query will be expanded to multiple comma separated parameters at runtime. For example, this can be used in SQL statement conditions such as SELECT ... WHERE ... IN(?).

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:

#{USERNAME}: Dynamically replaces the input mapping with the current user's username when a data service request is processed.

#{NULL}: Sets the current input mapping value to null.
         It's the same as providing "xsi:nil" in the incoming message's input parameter element.

#{TENANT_ID}: Represents the current tenant ID.
              This is useful in a Stratos deployment where multiple tenants live in the same server.

#{USER_ROLES}: This value contains the list of user roles that the current calling user has.
               If the parameter mapped is of type ARRAY, it will have the full list of user roles.
               If it's a SCALAR, it will only contain the first user role of the user.

For a demonstration of the usage of default values, see Default Values Sample.  

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:

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.

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 customernumber and customername fields in the customers table.

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

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. 

• SCALAR : The single element value is exported. If there are multiple instances of this value, the last one will be exported.
• ARRAY : An array of values will be exported. Each occurrence of the value is added to an array and exported. 
  

For a demonstration on the usage of export options, refer to request box sample .