Saturday, June 8, 2013

Insert data into multiple tables using WSO2 Data Services Server - Introduction

The WSO2 Data Services Server (DSS) is an enterprise-grade, lean, 100% open source, cloud-enabled, multi-tenant data service hosting and management platform. From the next series of posts, I'm going to explain several ways that we can use the WSO2 DSS to insert data into multiple tables.

Before starting to go into detail, let us setup a simple environment to demonstrate our scenario. I'm going to use WSO2 Data services Server 3.0.1, which is the latest release at the time of this post. If you haven't downloaded it yet, you can download the product distribution through here. I will be using MySQL as the database during these posts.

In this introduction post, I'll be guiding you step by step through the process of creating a sample database to creating a sample data service that inserts data into two tables.

First, lets create the database.

Creating the sample database


1. Create a sample database.

I have named this database as 'order_sample'.


2. Create the tables. 

I am creating 2 tables in this database. "orders" and "order_items". Im using the following SQL statement to create the database tables.



Creating the sample data service


1. Copy the MySQL connector jar

Before starting the server, we need to copy the MySQL connector jar to $DSS_HOME/repository/components/lib directory.

2. Start the server and login

The DSS server can be started using the wso2server.sh/wso2server.bat file located in $DSS_HOME/bin folder. For more information on configuration of the Data Services Server, please refer to the documentation

3. Create the sample data service.

To create a data service we have to navigate to the following location, from the management console.
Home > Manage > Services > Add > Data Service > Create

Note: Please refer the documentation for more detailed description on how to create a data service

I have given the name "SampleInsert" as my data service name.


Next we need to give the data source information. I have given the connection details to the database we created earlier. 

Then Im going to construct my queries that needs to be executed. I have two queries, one to insert data in to the order_items table and another one to insert data to orders table.

Following is a snapshot of the "insert_order_items" query which I use to insert data to the order_item table. We need to pass 2 parameters for this query which are the order id and the item id.

The following image shows the "insert_orders" query which insert data to the order table. Even though this table has 3 columns we only pass values to "creation_time" and "created_by" columns. The "order_id" is an auto generated value from the database. Since we need this auto generated value for the  "insert_order_items" query, we enable "Return Generated Keys" in this query. As a result of this query execution, the auto generated id will be returned to the client.


Next I'm going to define the operations that needs to be exposed from the data service. I expose 2 operations, "addOrder" and "addOrderItem".


Now we have finished creating our data service. I'm going to test this using the "TryIt" functionality of WSO2 DSS.

Following is the request and the response of the "addOrder" operation for the DSS.



Then we can use the above generated id to invoke the "addOrderItem" operation.

We can check the database to see whether our tables got updated correctly.


Conclusion

This post explains the basic method of inserting data into multiple tables using WSO2 DSS. Note that this method is not executed in a single database transaction. From my next post, I'll explain on how to insert data into multiple tables in a transactional manner.

Wednesday, June 5, 2013

Return JSON response from WSO2 Data Services Server




WSO2 Data Services Server is  an enterprise-grade, lean, 100% open source, cloud-enabled, multi-tenant data service hosting and management platform. This post explains how we can configure the WSO2 DSS to return the response in JSON format.

During this post I will be using WSO2 Data Services Server 3.0.1, which is the latest release at the time of this post. If you haven't got it already, we can go to the Data Services Server homepage and use the download link there or you can use this link to download it directly.

I will not be explaining any of the basics about Data Services Server since all of the information can be found using the documentation.

I will be using the ResourcesSample that is shipped with the DSS distribution to demonstrate this functionality. This sample explains how data can be exposed as a REST resource using WSO2 Data Services Server. What I'm going to explain is how we can get the response of these services as a JSON String.

1. Change the axis2.xml and axis2_client.xml


We need to add the following parameter to the axis2.xml and axis2_client.xml in the DSS server.  These files are found in CARBON_HOME/repository/conf/axis2 directory.

<parameter name="httpContentNegotiation">true</parameter>

First, lets change the axis2.xml. I have added this parameter to the parameters section in the axis2.xml. Note that this parameter is not there by default and we have to add that.


Next, we need to set the value of this parameter in axis2_client.xml to 'true'. Note that this parameter is there in the axis2_client.xml and we only need to change the value.




2. Deploy the samples


Use the instructions given in the samples guide to deploy the samples.

1. Stop the currently running Data Services Server instance if any. This is recommended in order to clean the current sample database and redeploy the services.
2. Remove the current sample database and existing sample data services.
# cd CARBON_HOME/samples
# ant clean
3. Deploy all samples.
# ant
4. Start the Data Services Server
# cd CARBON_HOME/bin
# wso2server.bat|sh

3. Run the sample 


Im going to use CURL to send a request to the DSS server. I will be using the same command that is described in the DSS documentation.

curl -X GET http://localhost:9763/services/samples/ResourcesSample.HTTPEndpoint/product/S10_1678

As as result, I get the following response from the DSS server.


Now let us try to get the same response in JSON format. In order to do that, I'm going change my request and add the following header to the request

"Accept:application/json"

Now my new request looks like the following.

curl -X GET -H "Accept:application/json" http://localhost:9763/services/samples/ResourcesSample.HTTPEndpoint/product/S10_1678 

Now you will observe that the same response is sent as a JSON string.



We can follow the same approach get a JSON response from the other invocations that is described in the DSS sample.

Tuesday, January 1, 2013

Getting started with WSO2 AppFactory


In my previous post, I mentioned about the WSO2 AppFactory application development platform. From this post I'm planning to give you some guidelines on how to run the pre-configured VM image of WSO2 AppFactory version 1.0.0 milestone 8 release.

The WSO2 AppFactory version 1.0.0 milestone 8 can be downloaded using this link. This zip file contains several files.
  • README.txt  
Readme file that describes how to run the VM image. This includes the necessary instructions on how to configure the IP settings in your local machine.
  • Samples
This is the samples directory. The milestone 8 release contains 5 samples. 
  • AFHelloWorld  
  • APIMIntegration  
  • DBSample  
  • FavouriteColourApp  
  • JenkinsTestReportingSample 
  • Wso2Appfactory
This folder contains the VMware image. This image is a pre-configured VMware image that includes all the necessary servers. It is configured in a way that it will start all the servers at VM image boot-up. 

Prerequisites

  1. VMware player should be installed in the host machine. 
  2. Host machine should be connected to the internet(for email verification purposes)
  3. At least 4GB ram is required(8GB ram is recommended)

Startup

  • Start the VMware Player.
  • Open the image. (open the Wso2Appfactory.vmx file)
  • Start the virtual machine.
  • If prompted, selected the option "I moved it".
  • After startup, login to the server. (username - appfactory, password - appfactory)
  • Get the IP address of the VM server. (ifconfig in linux)
  • Add the following entries to the host file in your local machine( /etc/hosts in linux). Use the above IP address for these entries(replace 10.100.3.36 with the correct IP address).
10.100.3.36 appfactorypreview.wso2.com
10.100.3.36 appserver.prod.appfactorypreview.wso2.com
10.100.3.36 appserver.dev.appfactorypreview.wso2.com
10.100.3.36 appserver.test.appfactorypreview.wso2.com
10.100.3.36 controller.appfactorypreview.wso2.com
10.100.3.36 jenkins.appfactorypreview.wso2.com
10.100.3.36 redmine.appfactorypreview.wso2.com
10.100.3.36 apimanager.appfactorypreview.wso2.com

  • Finally to access different servers, use the following URLs
Access Appfactory https://appfactorypreview.wso2.com/appmgt/
Access Dev cloud Application server https://appserver.dev.appfactorypreview.wso2.com/
Access Test cloud Application server https://appserver.test.appfactorypreview.wso2.com/
Access Production cloud Application server https://appserver.prod.appfactorypreview.wso2.com/