Sample Queries

This section gives examples of writing queries using the features discussed so far.

Sample 1: Using input mappings in a query

This sample demonstrates how to write a simple query with input mappings.

Query ID : customerAddressSQL

SQL Statement : select * from Customers where contactLastName = ? and contactFirstName = ?

The following query needs two parameters for execution. The Input Mapping section is used to specify these input parameters.

For information on adding validations to your input mappings, see Input Validators.

Sample 2: Adding output mappings to a query

Following sample shows how to query the Cassandra data source created in Cassandra and add output mappings to it.

Query ID : getUsers

Data Source : CassandraDatasource

CQL : select 'key', 'username', 'password' from USER

You can define how the output of this query looks by adding output mappings as follows:

Sample 3: Using a result row namespace

This example shows how to use a results row namespace in output mappings. See Defining Namespaces for information on namespaces.  

Sample 4: Querying a RDF data source

Following sample shows a query written for the RDF data source created in Exposing Relational Data in RDF Format, which is based on the added RDF file. This sample uses the following SPARQL query to extract movies information from the data source.

PREFIX cd: <http://www.popular.movies/cd#> 

SELECT ?title ?director ?year ?genre ?actor  
WHERE {    
      ?movie cd:title ?title.  
      ?movie cd:director ?director.  
      ?movie cd:year ?year.  
      ?movie cd:genre ?genre.  
      ?movie cd:actor ?actor. 
}

The input mapping section is used to specify parameters to the query. The above query extracts movie information according to the genre. Therefore, we add genre as an input parameter. Next, the output mappings are used  to map the response to an output XML.  

Sample 5: Querying a Web data source

Following sample shows a query written for the Web data source created in Web Resource. When you add a query to a Web data source, you must enter a Scraper Variable. This scraper variable must be the same as the output name in the Web data source configuration, which returns the output from the configuration. In this example, the var-def name in the configuration is weatherInfo (< var-def name = 'weatherInfo' >). Therefore, the Web resource output variable name is also specified as weatherInfo as shown below:

It defines output mappings as follows to specify how the output looks like.  

Sample 6: Querying a google spreadsheet 

Given below are sample queries that can be written for a google spreadsheet datasource. Note that SQL queries of this type can only be used for a google spreadsheet if the Query mode is enabled for the datasource in the data service.

Sample 1:

SELECT customerNumber, customerName, phone, state, country
FROM customers

Sample 2:

INSERT INTO customers (customerNumber, customerName, contactLastName)
VALUES(?,?,?)

Sample 3:

UPDATE customers
SET contactFirstName=?, contactLastName=?
WHERE customerNumber=?

Sample 4:

DELETE FROM customers
WHERE customerNumber=?

You can also create new sheets in the Excel or drop existing sheets.

Sample 5:

CREATE SHEET ProductCategories (ProductCode, Category)

Sample 6:

DROP SHEET ProductCategories

Sample 7: Calling a MySQL function

Assume you have the following MySQL function, which takes a string parameter and returns the same as output. Create a database before executing the query.

create function myFunction(p_inparam varchar(20)) 
  returns varchar(20)
  begin
     declare output_text varchar(20);
     set output_text = p_inparam;
     return output_text;
  end

To call this function from the data service, add a query to the data service definition file (.dbs) pointing to an RDBMS datasource that connects to the MySQL database. For example,

<sql>select myFunction('WSAS') as ABC</sql>

For example, see the following data service configuration:

<data name="sqlfunctionService">
   <config id="mynew">
      <property name="driverClassName">com.mysql.jdbc.Driver</property>
      <property name="url">jdbc:mysql://localhost:3306/sample&lt;/property>
      <property name="username">root</property>
      <property name="password">root</property>
   </config>
   <query id="NewfunctionQuery" useConfig="mynew">
      <sql>select myFunction('WSAS') as ABC</sql>
      <result element="wsas" rowName="wsas">
         <element column="output_text" name="n_param" xsdType="string"/>
      </result>
      <param name="imparam" sqlType="STRING"/>
   </query>
   <operation name="functionop">
      <call-query href="NewfunctionQuery">
         <with-param name="imparam" query-param="imparam"/>
      </call-query>
   </operation>
</data> 

You can also call this function in the Query Details page of the management console as follows:

Sample 8: Calling an Oracle function

Assume you have the following Oracle stored function, which returns the total number of entries in a table:

CREATE OR REPLACE FUNCTION myfunction(ename IN VARCHAR, eid IN NUMBER) RETURN INTEGER 
AS myCount INTEGER;
BEGIN
    INSERT INTO TEAMS values(eid, ename);
    SELECT COUNT(*) into myCount from TEAMS;
    RETURN myCount;
END;
/

Create a table before executing the query as follows:

CREATE TABLE TEAMS(id INTEGER, team VARCHAR(30));

To call this function from the data service, add a query to the data service definition file (.dbs). For example,

"{call ?:=myfunction(?,?)}"

First input parameter carries the return value of the function. Other two parameters are inputs to the function. You must define an Input parameter with OUT type to get the result of function (i.e., the first parameter in the query above). Then, define a Output parameter to get this value as a result set from the data service. The following code segment does this:

<result element="TotalTeams" rowName="">
    <element name="totalTeams" column="totalTeams" xsdType="xs:integer" />
</result>
<param name="totalTeams" sqlType="INTEGER" type="OUT" ordinal="1" />

For example, see the following data service configuration:

<data name="testOracleFunction">
   <config id="or">
      <property name="org.wso2.ws.dataservice.driver">oracle.jdbc.driver.OracleDriver</property>
      <property name="org.wso2.ws.dataservice.protocol">jdbc:oracle:thin:user/pwd@localhost:1521/XE</property>
      <property name="org.wso2.ws.dataservice.user">user</property>
      <property name="org.wso2.ws.dataservice.password">pwd</property>
   </config>
   <query id="q1" useConfig="or">
      <sql>{call ?:=myfunction(?,?)}</sql>
      <result element="TotalTeams" rowName="">
         <element name="totalTeams" column="totalTeams" xsdType="xs:integer" />
      </result>
      <param name="totalTeams" sqlType="INTEGER" type="OUT" ordinal="1" />
      <param name="ename" sqlType="STRING" ordinal="2" />
      <param name="eid" sqlType="INTEGER" ordinal="3" />
   </query>
   <operation name="op1">
      <call-query href="q1">
         <with-param name="ename" query-param="ename" />
         <with-param name="eid" query-param="eid" />
      </call-query>
   </operation>
</data>

You can call this function in the Query Details page of the management console as before.

Sample 9: Defining a dynamic SQL query

Dynamic SQL query support allows you to change SQL queries (e.g., defining additional conditions in the SQL) in the runtime without changing the data service configuration. For this to work, you must specify required SQL query statements (e.g., with WHERE clause) as a QUERY_STRING data type. These statements will be directed to the final SQL query in the runtime.  

The QUERY_STRING data type is available as an SQL type when creating Input Mappings for queries. For example,

<data name="DynamicQuerySample" serviceNamespace="http://ws.wso2.org/dataservice/samples/rdbms_sample">
   <config id="default">
      <property name="driverClassName">org.h2.Driver</property>
      <property name="url">jdbc:h2:file:./samples/database/DATA_SERV_SAMP</property>
      <property name="username">wso2ds</property>
      <property name="password">wso2ds</property>
      <property name="minIdle">1</property>
      <property name="maxActive">10</property>
      <property name="autoCommit">false</property>
   </config>
   <query id="employeesSQL" useConfig="default">
      <sql>select * from Employees :filterQuery</sql>
      <result element="employees" rowName="employee">
         <element column="lastName" name="last-name" xsdType="string"/>
         <element column="firstName" name="first-name" xsdType="string"/>
         <element column="email" name="email" xsdType="string"/>
         <element column="salary" name="salary" xsdType="double"/>
      </result>
      <param name="filterQuery" sqlType="QUERY_STRING"/>
   </query>
   <query id="customerInCountrySQL" useConfig="default">
      <sql>select * from Customers where country = :country :filter</sql>
      <result element="customer-addresses" rowName="customer-address">
         <element column="customerNumber" name="customer-number" xsdType="integer"/>
         <element column="contactLastName" name="contact-last-name" xsdType="string"/>
         <element column="contactFirstName" name="contact-first-name" xsdType="string"/>
         <element column="addressLine1" name="address-line1" xsdType="string"/>
         <element column="addressLine2" name="address-line2" xsdType="string"/>
         <element column="city" name="city" xsdType="string"/>
         <element column="state" name="state" xsdType="string"/>
         <element column="postalCode" name="postal-code" xsdType="string"/>
         <element column="country" name="country" xsdType="string"/>
      </result>
      <param name="country" sqlType="STRING"/>
      <param name="filter" sqlType="QUERY_STRING"/>
   </query>
   <query id="insertUpdateQuery" useConfig="default">
      <sql>:query</sql>
      <param name="query" sqlType="QUERY_STRING"/>
   </query>
   <operation name="getEmployees">
      <call-query href="employeesSQL">
         <with-param name="filterQuery" query-param="filterQuery"/>
      </call-query>
   </operation>
   <operation name="getCustomersInCountry">
      <call-query href="customerInCountrySQL">
         <with-param name="country" query-param="country"/>
         <with-param name="filter" query-param="filter"/>
      </call-query>
   </operation>
   <operation name="insertUpdateOp">
      <call-query href="insertUpdateQuery">
         <with-param name="query" query-param="query"/>
      </call-query>
   </operation>
</data>