Sunday, December 25, 2011

Call an external program from MySQL trigger

MySQL is the choice of many when it comes to database. Its free and quite robust.
During one of our prototype implementations we had a requirement of calling some external processes when there is a change in a MySQL table. MySQL triggers are provided for the same purpose. They get executed when the table is changed in certain ways that is specified by the programmer. Now it is very easy (rather trivial) to do some thing in other MySQL database tables when trigger gets fired. But, our requirement was to call a C program.

Fortunately MySQL provides a way to implement your own functions, its called User Defined Functions (UDF). The "how to" is here. Now that we know, how to define your own functions and call them from MySQL events, we need to write our logic in a C program by following the interface provided by MySQL and we are done.

Wait a minute. That is already done by somebody. They have made a library of UDFs. One of them, LIB_MYSQLUDF_SYS does exactly what we want. It allows you to call an external program from MySQL procedure/trigger. Once you download and untar the files, you'll get an install.sh. Just need to run it but, make sure you have gcc and make before that. The install script copies the library shared object file to /usr/lib. In some 64 bit systems it may be /usr/lib64. Also you need to check MySQL plugin_dir property which points to the directory where the .so file should be placed.


Once the UDF is installed successfully, we can use 2 functions to call an external program -

1. sys_exec : Returns the exit code of the external program.
2. sys_eval : Returns the output from the external program.

Here is a code snippet which show an example:

DELIMITER @@

CREATE TRIGGER Test_Trigger 
AFTER INSERT ON MyTable 
FOR EACH ROW 
BEGIN
 DECLARE cmd CHAR(255);
 DECLARE result int(10);
 SET cmd=CONCAT('sudo /home/sarbac/hello_world ','Sarbajit');
 SET result = sys_exec(cmd);
END;
@@
DELIMITER ;


I have a hello_world program (/home/sarbac/hello_world) which accepts 1 command line argument and writes to a file.
Don't forget to return int from the main function if you are using sys_exec().

Deepti Sharma has thoroughly investigated this approach. I thank her for that.

Friday, November 11, 2011

Read a log file in tail mode from python

When you want to develop a log reader application, you'll need to keep reading the log. There is a Linux utility doing exactly the same. Its the tail command. tail blocks on the file till new entries are appended to it.

I needed the similar functionality in python and there was a small library providing it already. Its called filetail.py. The program performs tail functionality and also handles log rotation.

But in my case, the log file used to change (file name) depending on the date. filetail could not handle it as the file name has changed already. To handle this I have made following changes to the filetail code -

In nextline() method file size should be checked to be less or equal to the cursor position:


                # maybe the log got rotated out from under us?
                if stat(self.path)[ST_SIZE] <= self.pos: # modified by sarbajit
                    # file got truncated and/or re-created
                    self._reset()

In _reset() method I have added the logic to re-create the log file name from the current date value and replace it with the original file name:

    def _reset(self):
        """Internal method; reopen the internal file handle (probably
        because the log file got rotated/truncated)."""
        self.f.close()

 # modified by sarbajit
 localtime1 = localtime(time())
 year = str(localtime1[0])
 if localtime1[1] < 9:
  month = "0"+str(localtime1[1])
 else:
  month = str(localtime[1])
 if localtime1[2] > 9:
  date = str(localtime1[2])
 else:
  date = "0"+str(localtime1[2])
 logfile_name = "applog."+year+"-"+month+"-"+date+".log"
 self.path = "/var/log/" + logfile_name
 # end of modification

        self.f = open(self.path, "r")
        self.pos = self.f.tell()
        self.last_read = time()

I apologize for not following standards of coding here. It served the purpose for me.

Sunday, October 16, 2011

jQuery UI dialog without any close option

We use jQuery UI for designing web pages in out project. Some times back we had a scenario where the user log in to a dashboard was implemented using jQuery UI dialog (modal) component. Requirement was that the dashboard page should not be reached without passing the authentication process. But the dialog component of jQuery UI library provides a default X icon at the top left corner of the element which provides an option to close the dialog.

In our case, we wanted to reload the same page on click of the X icon. So that the user can not escape the login dialog by using the X icon. We achieved this by writing the page reload code inside dialog's close handler.  Everything worked fine until we had to handle the successful log in. Once user login succeeds we need to call dialog widget's close handler which reloads the page in turn. Because of this, even after a successful login, user gets the login dialog again.

The only way to stop this was, to remove the page reload code from the widget's close handler. Then it would require to remove the X icon from the dialog widget so that the close handler can not be invoked by clicking the X icon. But jQuery UI gives no option to build a dialog without close icon.

Thanks to Deepti Sharma for finding out a easy solution to the problem. Here is how it looks after the modification -


Below is the HTML code for creation of the dialog box.
<div id="login_div" title="Login" style="display:none;">
  <form id="login_form" name="login_form" action="LoginServlet?action=login" method="post">
   <table>
   <tbody>
    <tr>
     <td>Username: </td>
     <td><input type="text" name="username" id="username" size="20"></td>
    </tr>
    <tr>
     <td >Password:</td>
     <td><input type="password" name="password" id="password" size="20"></td>
    </tr>
   </tbody>
   </table>
  </form>
</div>


The JavaScript part of code is doing the actual business. First we create the login dialog using jQuery UI API, then we hide the X icon by setting its display property. Here is the code
$( "#login_div" ).dialog({
  autoOpen: false,
  height: 'auto',
  width: 400,
  modal: true,
  buttons: {
  "Submit": function() {
                  // form submission code goes here
  },
  "close": function() {
                  // close handler
  }
});

var dialogDivId = "login_div";

$('div[aria-labelledby="ui-dialog-title-'+ dialogDivId +'"] .ui-dialog-titlebar-close').css("display","none");



The important part is the aria-labelledby attribute. It will contain the original div id of the dialog. So, we can find out the correct X icon to hide (there are multiple dialog boxes in the page). Though the code looks dirty, it works.
Once the close icon is removed, user can only submit the login page but can't close/cancel the login.

Saturday, September 17, 2011

Setup VNC server in Ubuntu 10.04

I needed a system outside my company firewall to access access some internet based applications. I wanted to use a web browse from that. But all I had was, a cloud node running Ubuntu 10.04 with command line access to it. I installed VNC server in that to run firefox web browser. Steps are below -

  1. sudo apt-get install xinit
  2. sudo apt-get install x11vnc
  3. sudo xinit &
  4. x11vnc -safer -localhost -nopw -xkb -once -display :0 &
  5. Create a ssh connection from Putty. I have user ssh port forwarding feature to make the connection secure. After configuring normal host name, user id or ssh key etc. go to Connection > SSH > Tunnels section. Input 5902 for Source and localhost:5900 for Destination. Click Add.
  6. Now open this session from Putty.
  7. Download Tightvnc java client and unzip it to some directory.
  8. Run the jar file that was extracted in last step.
  9. In the new window, input 127.0.0.1 for Server and 5902 for port. It should launch another window showing Ubuntu system.
Now you can install any GUI application in Ubuntu and use it through VNC.

Tuesday, August 30, 2011

Run Cherrypy web server in Android

I was in much need of a way to run Python CGI scripts in Android. Basically I needed a web server capable of running CGI scripts in Android platform but, I found none. Finally I modified my CGI script to suit the Cherrypy web server and it could run on Android.

I'll try to describe the steps here which I had to figure out myself. The information I found by googling was not completely correct as Android platform has changed from the time of the publication. So, I'll mention the version no. for each software I've used to make sure it is not misunderstood later.


  1. First thing we need is to download the SL4A (r4) software in the Android (2.3) emulator. It can be done from your Android browser by going to the SL4A site. Now install the software in emulator.
  2. Then install Python for Android from the same SL4A site. It'll download an apk of version r4.
  3. Launch SL4A application and check that HelloWorld python script is running. It will make sure that your installation is fine.
  4. Now is the time to install Cherrypy library module. It can be found at http://www.cherrypy.org/wiki/CherryPyDownload. I have taken 3.2.0 version. Download the egg for python 2.6.
  5. Now we need to transfer the egg file to Android emulator. Use adb push command to transfer the egg file to /mnt/sdcard/Download.
  6. Launch Python for Android from emulator and click on Import modules. It will list the newly uploaded egg file. Select it and install.
  7. Now we can write a Cherrypy script to be run as CGI. Below is a HelloWorld example taken from Cherrypy tutorial (modified a bit) -

    # Import CherryPy global namespace
    import cherrypy
    
    class HelloWorld:
        """ Sample request handler class. """
    
        def index(self, **params):
            # CherryPy will call this method for the root URI ("/") and send
            # its return value to the client.
            for key in params:
            	print key, '=', params[key]
            return "Hello world!"
    
        # Expose the index method through the web. CherryPy will never
        # publish methods that don't have the exposed attribute set to True.
        index.exposed = True
    
    # CherryPy always starts with app.root when trying to map request URIs
    # to objects, so we need to mount a request handler root. A request
    # to '/' will be mapped to HelloWorld().index().
    cherrypy.config.update({'server.socket_host': '127.0.0.1'})
    cherrypy.config.update({'server.socket_port': 8080})
    cherrypy.quickstart(HelloWorld(), '/')
    
  8. The script needs to be transferred to /mnt/sdcard/sl4a/scripts directory in emulator. Then you can launch SL4A and tap the script to run. Debug outputs are visible if you run in SL4A console.
  9. From the Android browser, check the URL http://localhost:8080/. It will say "Hello world".
This the web server set we can place python scripts to access Android phone data and other stuff which can be exposed to the outside clients.


EDIT:
Below are the screen shots from the emulator -

1. Apps list after installing SL4A and Python for Android



2. Tap on Python for Android icon to launch below screen.



3. Tap on Import Modules after copying the CherryPy egg in /mnt/sdcard/Download.


Now tap on CherryPy to install it.

Saturday, July 23, 2011

Flexigrid table becomes read only when no rows returned from server - solution

Flexigrid is a popular jQuery plugin which builds multipurpose data grid in web pages. It is a light weight plugin with rich controls. I have used Flexigrid heavily in my project. While using the plugin I faced one problem. It disables the grid when there are no rows in it. This looks like a proper behavior until you need the buttons in the table (Add) to be enabled for the first row to be added.

To clarify the requirement I had, there is a grid with Add and Delete buttons in it. The first row in the grid will be added through the add button. So, when the table loads for the first time, there is no row and this makes the grid read only. Now, we can not click the add button. It becomes a recursive problem.

The Flexigrid code that does this -

//add block
g.block.className = 'gBlock';
var gh = $(g.bDiv).height();
var gtop = g.bDiv.offsetTop;
$(g.block).css(
{
   width: g.bDiv.style.width,
   height: gh,
   background: 'white',
   position: 'relative',
   marginBottom: (gh * -1),
   zIndex: 1,
   top: gtop,
   left: '0px'
});
$(g.block).fadeTo(0,p.blockOpacity);

I have devised a dirty fix for this problem -

// unblock flexigrid
$("div .gBlock").css("zIndex", 0);
 

Use this code in your document.ready() function.
I tried asking the problem in the Flexigrid mailing list also, but could not get any other solution. So, currently this is the only working solution available. Try it out.

Saturday, June 25, 2011

Resolve Hibernate connection timeout issue with MySQL server

This problem has been faced by many of the developers but the fix is not very common. I had to do some research to find out the problem and solve it.
If you are using a MySQL server, the default connection timeout is 8 hours (tested on MySQL 5.2). This causes a problem in production environment as the database could not be connected overnight. Below is the log trace of the problem -

org.hibernate.util.JDBCExceptionReporter: The last packet successfully received from the server was 56697 seconds ago. The last packet sent successfully to the server was 56697 seconds ago, which  is longer than the server configured value of ‘wait_timeout’. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property ‘autoReconnect=true’ to avoid this problem.


As the description says, we can increase the wait_timeout property of MySQL server but it is not a good idea to keep stale connections open for long time. The 'autoReconnect=true' property did not work for me.

I figured out the below solution which worked perfectly for me.
First we need to modify our hibernate.cfg.xml file and add the following lines -

5
20
1800
50
org.hibernate.connection.C3P0ConnectionProvider

You have to add a new file in your classpath called c3p0.properties. The file contains -

c3p0.testConnectionOnCheckout=true

The last but most important part is to add the required jars in the classpath. You must have hibernate.jar (contains org.hibernate.connection.C3P0ConnectionProvider class) and c3p0.jar.

This should solve the auto disconnection issue permanently.

P.S. To test the fix, you can change the wait_timeout property in my.cnf file (/etc/mysql/my.cnf in Linux) of MySQL server. Specify a small timeout value of few minutes.


Wednesday, May 11, 2011

A simple REST based web service

REST stands for Representational State Transfer. Roy Fielding, the father of HTTP protocol, had coined the term in his doctoral thesis. REST is not a software, rather it is a concept or an architecture. The biggest use of REST architecture is in WWW (World Wide Web). The services that conforms to REST standard are termed as RESTful services.

REST based services contain a client and a server. The server contains resources that can be represented by various states. A client initiates the request to server when it is ready to transition it's state. The server responds with the required state.

The REST based web service is quite popular now. This type of web service is called RESTful web service. In RESTful web service we represent a service by an URI. Common HTTP methods are used to request for a specific state transfer.


CreatePUT
UpdatePOST
ReadGET
RemoveDELETE

I have used an open source java library called Jersey for creating this simple RESTful web service. Jersey is a production quality JAX-RS (JSR311) implementation. The jars from Jersey distribution need to be added to the WEB_INF/lib directory of your web application. Below is the code for a hello service -


import javax.ws.rs.GET;
import javax.ws.rs.Path;
import javax.ws.rs.Produces;
import javax.ws.rs.core.MediaType;

@Path("/hello")
public class HelloRest {
 @GET @Produces(MediaType.TEXT_PLAIN)
 public String helloText()
 {
  return "Hello world";
 }
 @GET @Produces(MediaType.TEXT_HTML)
 public String helloHtml()
 {
  return "<html><body><h1>Hello world html</h1></body></html>";
 }
}


Here the service URI will be <web application URL>/hello as defined by the Path annotation. The GET annotation marks the service to be called on a HTTP GET request from client. The Produces annotation filters the accept type header in the request to match the correct state to be returned. It takes care of the content that is returned.


There is one more way to make the same web service. This time I'll only use URI as a representation differentiator. Below is the code-


import javax.ws.rs.GET;
import javax.ws.rs.Path;

@Path("/hellowithpath")
public class HelloRestWithPath {
 @GET @Path("/text")
 public String helloText()
 {
  return "Hello world";
 }
 @GET @Path("/html")
 public String helloHtml()
 {
  return "<html><body><h1>Hello world html</h1></body></html>";
 }
}

To get a HTML output the client has to make a GET request with <web application URL>/hellowithpath/html URI. Here I have only used the Path annotation.

One more thing remaining is, adding a servlet mapping in the web.xml file of the web application. Add the below mapping-

<servlet>
    <description></description>
    <display-name>ServletContainer</display-name>
    <servlet-name>ServletContainer</servlet-name>
    <servlet-class>com.sun.jersey.spi.container.servlet.ServletContainer</servlet-class>
  </servlet>
  <servlet-mapping>
    <servlet-name>ServletContainer</servlet-name>
    <url-pattern>/rest/*</url-pattern>
  </servlet-mapping>



Once you deploy the web application in a server the RESTful services will be started. They can be tested using a browser or a http client program.

Saturday, April 2, 2011

Using web service client behind a http proxy server

Web service is a popular medium of communication between devices over a network. But most of the time you find yourself behind a http proxy server to connect to the network. Proxy servers are often found in company networks. The main reasons for their introduction are - security, resource caching, auditing etc. But ultimately they block unauthorized access to internet from the inside network. This leads to the problem we are going to solve now.

We are going to use w3schools temperature converter service (WSDL download) for testing. Make sure you are actually running this code behind a http proxy server. You also need to know the proxy server's host name and the port no.  I'll assume that you can generate the client stubs using JAX-WS (or some other tool). I have used wsconsume tool to generated my stubs.

In my case, com.w3schools.TempConvert is the implementation class and com.w3schools.TempConvertSoap is the interface. Then I have a com.w3schools.client.WSClient class which is the actual client.

The trick here is to initialize a new instance of java.net.ProxySelector class in WSClient code with your proxy settings. To achieve that you need to write a class which extends java.net.ProxySelector. See below -




package com.w3schools.client;

import java.io.IOException;
import java.net.InetSocketAddress;
import java.net.*;
import java.util.*;

public class MyProxySelector extends ProxySelector {

    @Override
    public List select(URI uri) 
    {
        // Setting up a new ProxySelector implementation
        Proxy proxy = new Proxy(Proxy.Type.HTTP, new InetSocketAddress("mywebproxy.com", 8080));
        ArrayList list = new ArrayList();
        list.add(proxy);
        return list;
    }

    @Override
    public void connectFailed(URI uri, SocketAddress sa, IOException ioe) {
        System.err.println("Connection to " + uri + " failed.");
    }
}


With MyProxySelector class being ready we can set this default proxy in the WSClient code. Like this -


package com.w3schools.client;

import java.net.ProxySelector;

import com.w3schools.TempConvert;
import com.w3schools.TempConvertSoap;

public class WSClient {
 public static void main(String[] args) {
  ProxySelector.setDefault(new MyProxySelector()); 
  TempConvertSoap service = new TempConvert().getTempConvertSoap();
  String cel = service.fahrenheitToCelsius("40");
  String fah = service.celsiusToFahrenheit("100");
  System.out.println(cel + "  " + fah);
 }
}


Now you can invoke the WSClient to use w3school web service even from behind a proxy.

Tuesday, March 15, 2011

Write your first JAXWS web service

Today I'll try to explain how easily one can build a JAX-WS web service. Web Services are very popular now-a-days. They are used every where. But I found it difficult to write my first web service with jax-ws, partly because I was trying myself and partly because I could not get a simple explanation of steps. That is why I'll try to show the simplest web service possible using jaxws.

First thing first, what do you need to build your first jax-ws web service.
  1. JDK 1.6 (I used update 23)
  2. Eclipse IDE (you can use some other IDE as well)
  3. Are you kidding? nothing else.
JAX-WS 2.0 is actually Sun's (now Oracle) reference implementation of Web Service stack. It is bundled with JDK 6 and above.

First you need to create a Java project in Eclipse. Then create a class called com.ws.HelloService. This will be our web service class. Have a public method in this class which will be exposed as web service. Below is the code -


package com.ws;

import javax.jws.WebService;

@WebService
public class HelloService
{
   public String sayHello(String name)
   {
      return ("Hello " + name);
   }
}

The catch here is the @WebService annotation. This is the key. It tells jaxws engine that the HelloService is a web service class. By default jax-ws exposes all public methods of a class. So, in our case it will expose sayHello operation.
Now our service code is ready. It needs to be deployed to some HTTP server for testing. Fortunately, JDK 6 comes with Jakarta Commons HTTP client. We can directly deploy the service in this server. Create a separate class for this -

package com.ws;

import javax.xml.ws.Endpoint;

public class PublishWS
{
   public static void main(String[] args)
   {
      String url = "http://localhost:9999/helloservice";
      HelloService service = new HelloService();
      Endpoint.publish(url, service);
   }
}

Run this class. Thats it. We are done with deployment. You can check the WSDL document at <ws url>?wsdl location. Invoke the service with some name. It will return Hello + name.

Saturday, January 29, 2011

Get JSON data response with JQuery Form plugin for a file upload form


My latest project involves JQuery for UI development. Frankly speaking, I did not have much idea of JQuery before. But I found it very easy to understand. All APIs are well documented with lots of examples available on Internet. The best feature of JQuery is its plugins. There are thousands of plugins available to play with. For every need of my web app I could find a plugin.



In my web app I am using JQuery Form plugin to submit the forms through AJAX. The return from my servlet is JSON object. The form plugin works great for all my forms. Well almost all, there is a HTML form which has a file upload box along with other input fields. This form was not able to get the response in JSON format. Though it worked in Google Chrome, but Firefox was presenting me with a file save dialog box.
I did a lot of googling to find out whats going on. Finally I observed that the problem is with HTTP headers that the form submit request has. Here are the headers captured by Chrome



Request headers captured by Chrome




As you can see the Accept request header does not contain application/json. That is why the response JSON object is not understood by the browser and it pops up a file save dialog.
When I checked the source code of JQuery Form plugin, I found that in case of a multipart form (i.e. form  having file items) the plugin does not use ajax to submit the form. Rather it uses an IFRAME element. So the form submission is a normal GET/POST request. Here is the part of plugin code that does it -

// are there files to upload?
 var fileInputs = $('input:file', this).length > 0;
 var mp = 'multipart/form-data';
 var multipart = ($form.attr('enctype') == mp || $form.attr('encoding') == mp);

 // options.iframe allows user to force iframe mode
 // 06-NOV-09: now defaulting to iframe mode if file input is detected
   if (options.iframe !== false && (fileInputs || options.iframe || multipart)) {
    // hack to fix Safari hang (thanks to Tim Molendijk for this)
    // see:  http://groups.google.com/group/jquery-dev/browse_thread/thread/36395b7ab510dd5d
    if (options.closeKeepAlive) {
     $.get(options.closeKeepAlive, fileUpload);
  }
    else {
     fileUpload();
  }
   }
   else {
    $.ajax(options);
   }

Basically its a normal HTTP request not ajax call. So the Accept header is set to application/xml, text/html etc. But the response from the servlet has a Content-Type as application/json. So, browser opens a save as dialog.

To solve this issue in a proper fashion we need to modify the plugin code to upload the file through an ajax request. But files can not be transmitted through ajax. Due to this limitation I had to find another solution which is not as clean as the above one but it will do the job.

The solution is to set the response Content-Type as text/plain. To do this I used below code

if ((null != req.getHeader("Accept"))
                  && req.getHeader("Accept").contains("application/json"))
         {
            resp.setContentType("application/json");
         }
         else
         {
            resp.setContentType("text/plain");
         }
         resp.getWriter().write(jsonResponse);

I checked the request header Accept to see if browser can accept a JSON. If yes, then set the response Content -Type to application/json. Otherwise set it to text/plain. This technique will fool the browser as it falls among the requested types. After this change I captured the response headers

Response headers captured by Chrome



We need to make one more change in the client side code.

var options = { 
         dataType: 'json',
         success: processResponse
     }; 
$('#upload_form_id').ajaxForm(options);


This is important because, without the dataType property, the response will be treated as a text by the plugin. So, the client side script that works on the response of the form submit will not work as it will get a string instead of JSON object.

I checked the method in Firefox 3.6, Chrome 8.0, Internet Explorer 8 and Opera. It worked. I could also maintain my servlet's response type as application/json for all other forms (which does not have a file upload). I hope this works for you too.