Using Xcelsius and Webservices to insert comments into a database

January 26, 2012 by: David Lai

A 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 🙂

Web Service Comments life cycle

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

  1. Start creating your web service using the programming language of choice (VB .NET or Java)
  2. Create a method that will house your “insert comment” code
  3. 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

  1. Log into database
  2. Begin Transaction
  3. Create insert statement that will insert the input parameters (comment text, etc)
  4. Make sure that there is some sort of rollback mechanism to handle errors
  5. End transaction
  6. 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

http://www.java2s.com/Code/Java/Database-SQL-JDBC/AccessMySQLDatabaseopenconnectioncreatetableinsertandretrieve.htm

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.

Bind input text

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)

Data Manager

5.  Insert a Connection Refresh Button component onto the canvas.  Make sure the Web Service Connection is checked.

Connection Refresh Button

 

Comments

4 Responses to “Using Xcelsius and Webservices to insert comments into a database”
  1. Santosh says:

    thanks very much for this blog, its very useful David..

  2. Emanuel says:

    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

    • David Lai says:

      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

  3. Gert Jan says:

    Nice article, could you share an example of the webservice /methods ?

Leave a Reply


4 × five =