Using Xcelsius and Webservices to insert comments into a database
January 26, 2012 by: David LaiA common request that I have seen with my clients, is the ability to enter comments into a dashboard and then view those comments when they re-open the dashboard in the future.
Unfortunately Xcelsius does not have a native mechanism to send data back to the database, as a dashboard’s main purpose to read data and not write back to the database. However, I do see the value in being able to persist comments so that we can go back later and be able to look at documentation on why certain things happened, etc.
In this article I will give step by step instructions on how to use web services and Xcelsius to accomplish the task of inserting comments. However you will have to write your own web service code 🙂
Preparation
In order to facilitate the ability to create comments from Xcelsius we must have a mechanism to do so and a place to persist the comments.
You will need to store the comments in a database.
Thus you will need the following:
- Database
- Database table that will house the comments and additional parameters such as user, dashboard, etc
To allow Xcelsius to send comments to the database, we will need to create a Web Service and then call that Web Service from Xcelsius while sending in the appropriate parameters (comments, user, dashboard, etc).
To facilitate the ability to use Web Services, you must have a .NET or Java application server available.
Pseudo steps to create the Web Service
- Start creating your web service using the programming language of choice (VB .NET or Java)
- Create a method that will house your “insert comment” code
- Method will need to have input parameters that contains your comment text and any other parameters such as user, dashboard, etc
Steps in the method
- Log into database
- Begin Transaction
- Create insert statement that will insert the input parameters (comment text, etc)
- Make sure that there is some sort of rollback mechanism to handle errors
- End transaction
- Close database connection
When you are finished coding, you will then need to deploy the Web Service onto your .NET or Java application server.
Useful Resources to help you code your Web Service
1. Introduction on what a Web Service is
http://en.wikipedia.org/wiki/Web_service
http://www.w3schools.com/webservices/ws_intro.asp
2. Example of creating a simple VB . NET Web Service for a .NET application server
http://www.w3schools.com/webservices/ws_example.asp
3. Example of creating a simple java based Web Service that runs on a Java server
http://www.roseindia.net/webservices/buildingsimplewebservice.shtml
4. Java code to connect to a DB, create a table, insert into table, and retrieve from a table (you are only interested in inserting) Start on step 22 if you are interested in the code
Xcelsius Steps
In the Xcelsius example, we will walk through the steps on creating an input/output comment box; and creating a web service connection that inserts a user’s comment into the database.
1. Drag an Input Text Area and Label component onto the canvas. Users will enter their comments into the input text area and the label component contain existing comments.
2. Bind the Input Text Area to the cell highlighted in yellow. The web service connection will use this as one of the inputs.
3. Now let’s setup our Web Service Connection. On the data manager, add a Web Service Connection and enter the WSDL url into the WSDL URL: input box.
4. Bind the input values to the cells of your spreadsheet. (In our example we bind to 3 input values. Note that the output value is optional so we will skip that part. In addition we will skip creating a Web Service Connection that retreives prior comments since it uses the same concept of what we are doing here)
5. Insert a Connection Refresh Button component onto the canvas. Make sure the Web Service Connection is checked.
thanks very much for this blog, its very useful David..
I love your blog.. very nice colors & theme. Did you design this website yourself or did you hire someone to do it for you?
Plz respond as I’m looking to create my own blog and would like to know where u got this from. kudos
Thanks I used a wordpress theme that I found off the internet.
Then modified the css to my own liking.
Good luck on creating your blog
Nice article, could you share an example of the webservice /methods ?