Versions Compared

Old Version 2

changes.mady.by.user Former user

Saved on

compared with

New Version Current

changes.mady.by.user Former user

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Updated the table of content zone macro.

...

Localtabgroup
Localtab
activetrue
titleQuery mode disabled
Panel
borderColor#613A91
bgColor#ffffff
borderWidth2

If Query Mode is disabled for the spreadsheet, you cannot write SQL statements to query the spreadsheet. Instead, you need to specify the query details by filling in the data extraction parameters. This also means that, when query mode is disabled, you cannot use the data service to insert, update, or modify data in the spreadsheet. This data service can only be used to get data that is already stored in the spreadsheet.

Follow the steps given below:

  1. Click Add New Query and add the following details:

    Query IDGetProducts
    Datasource TypeEXCEL
    Workbook NameSheet1
    You want the query to get the data from Sheet1 in the excel spreadsheet.
    Start reading from2
    You enter 2 because the 1st row of the spreadsheet is the header. The data is available from row 2 onwards.
    Rows to read5
    Five rows of data will be fetched by the query.
    Headers availabletrue
    Header row1
    You enter 1 because the 1st row of the spreadsheet includes the header.
  2. Define Output Mapping:
    Now, let's specify how the data fetched from the datasource should be displayed in the output. The Excel datasource we are using contains three columns: ID, Model, and Classification. We will create an output mapping for each of these columns.    

    1. Start by giving the following information:

      Output typeSelect XML.
      You can select XML, JSON, or RDF. This specifies the format in which the query results should be presented. 
      Grouped by elementEnter Products.
      This is the XML element that groups the query result.
      Row NameEnter Product.
      This is the XML element that should group each individual result.

    2. Click Add New Output Mapping to start creating the output mapping. Listed below are the output mappings that should be created.
      Fill in the details and click Add to add each output mapping.

      Mapping TypeElement NameDatasource TypeDatasource Column NameParameter TypeSchema Type
      ElementIDColumnIDSCALARstring
      ElementModelColumnModelSCALARstring
      ElementClassificationColumnClassificationSCALARstring
  3. Once you have created all of the above mappings, click Main Configuration to return to the Query screen.
  4. Save the query.
Localtab
titleQuery mode enabled
Panel
borderColor#613A91
bgColor#ffffff
borderWidth2

If Query Mode is enabled  for the datasource, follow the steps given below.

Table of Content Zone
maxLevel5
minLevel5
locationtop
Creating a query to GET data

  1. Click Add New Query and add the following details.

    Query IDGetProductbyID
    DatasourceEXCEL
    SQL
    Code Block
    select ID, Model, Classification from Sheet1 where ID=:ID
  2. Click Generate Input Mapping, and the default input mappings will be generated for all the fields that you have specified in your SQL statement.
    You need to create input mappings for the ID, Model and Classification columns specified in the above SQL statement. These input mapping parameters will be used for inserting data into the relevant columns. 
  3. Click Generate Response to create the output mapping.
    This defines how the employee details retrieved from the datasource will be presented in the result. Note that, by default, the output type is XML. 
  4. Save the query.
Creating a query to POST data

You can query data in the spreadsheet using SQL statements. At the moment, the query mode supports only basic SELECT, INSERT, UPDATE, and DELETE queries. Nested queries will be supported in an upcoming release. The  org.wso2.carbon.dataservices.sql.driver.TDriver  class is used internally as the SQL Driver. It is a JDBC driver implementation used with tabular data models such as Google spreadsheets, Excel sheets, etc.   

Follow the steps given below.

  1. Click Add New Query and add the following details.

    Query IDAddProducts
    DatasourceEXCEL
    SQL
    Code Block
    INSERT INTO sheet1 (ID, Model, Classification) VALUES(:ID, :Model, :Classification)
  2. Click Generate Input Mapping, and the default input mappings will be generated for all the fields that you have specified in your SQL statement.
    You need to create input mappings for the ID, Model, and Classification columns specified in the above SQL statement. These input mapping parameters will be used for inserting data into the relevant columns.

  3. Save the query.

...