Friday, August 16, 2013

jQuery-UI Datepicker: Month and Year selector and the 'Done' button issue

jQuery ui datepicker can be customized to hide the calendar and show only the Month and Year selector dropdowns.
In this excellent StackOverflow answer Ben Koehler has shown how to do that by hiding .ui-datepicker-calendar
.ui-datepicker-calendar {
    display: none;
}
and overriding the onClose event method:
$(function() {
    $('.date-picker').datepicker( {
        changeMonth: true,
        changeYear: true,
        showButtonPanel: true,
        dateFormat: 'MM yy',
        onClose: function(dateText, inst) { 
            var month = $("#ui-datepicker-div .ui-datepicker-month :selected").val();
            var year = $("#ui-datepicker-div .ui-datepicker-year :selected").val();
            $(this).datepicker('setDate', new Date(year, month, 1));
        }
    });
});
Problem with the above implementation: the date field can not be cleared after it is populated once (without re-loading the page). This problem occurred because of overriding the onClose method. Notice that the onClose method is called not only on clicking the 'Done' button, but also on clicking outside the datepicker dialog box.
Check the problem here:


So, we need to find a solution where the date field is populated only if the 'Done' button is clicked. A good solution has been provided in this SO answer from the same question I mentioned above. It also has a problem: the datepicker doesn't load when clicked on the date text box for the first time.
I have combined the above two answers and have made it work perfectly.


Here is the final, flawless solution:
Javascript:
$(function() {
 $('.monthPicker').datepicker({
  changeMonth: true,
  changeYear: true,
  showButtonPanel: true,
  dateFormat: 'MM yy'
 }).focus(function() {
  var thisCalendar = $(this);
  $('.ui-datepicker-calendar').detach();
  $('.ui-datepicker-close').click(function() {
   var month = $("#ui-datepicker-div .ui-datepicker-month :selected").val();
   var year = $("#ui-datepicker-div .ui-datepicker-year :selected").val();
   thisCalendar.datepicker('setDate', new Date(year, month, 1));
  });
 });
});
CSS (same as above):
.ui-datepicker-calendar {
    display: none;
}
HTML:



Check it below:


Here is the jsfiddle for this.

Thursday, August 1, 2013

MySQL change column text to Title Case / Proper Case / Camel Case

Problem: Change the text-value of a specific column of a mysql table into 'Title Case'

MySQL doesn't support any function for Title-Case conversion.
If the text-values of the column are all of single-word then the conversion can be done using a combination of the functions 'UPPER', 'LOWER', 'SUBSTRING' etc as suggested in this StackOverflow answer by Anupam.
But it will not work If the  text-values are of multiple words.

1. Stored Procedures/Functions can be one solution as suggested in the answers of this StackOverflow question.

2. Writing server side scripts (JSP, Python, PHP etc.) can be another solution.

A PHP solution can be found at this post "CONVERTING TO TITLE CASE IN MYSQL – CAPITALIZING MYSQL (A PHP SOLUTION)".

I am showing here how we can write a JSP script to do this.

First we create our MySQL table and insert data into it:

CREATE  TABLE `test`.`Employee` (
 `id` BIGINT NOT NULL AUTO_INCREMENT ,
 `fullName` VARCHAR(45) NULL ,
 `designation` VARCHAR(45) NULL ,
 PRIMARY KEY (`id`)
);

insert into Employee
    (fullName, designation)
values
    ('ALBERT NASIMSTEIN', 'CUSTOMER RELATIVITY OFFICER'),
    ('SIR ISSAC NASIMTON', 'APPLEICATION MANAGER'),
    ('LOUISE PASTEUR', 'MILK PASTEURISATION MANAGER'),
    ('GALILEO', 'ASTRONOMICAL OFFICER'),
    ('NIKOLA TESLA', 'ELECTRO MAGNETIC FIELD OFFICER'),
    ('ARISTOTLE', 'CHEIF EXECUTIVE OFFICER');

It will result in the following table:

id fullName designation
1 ALBERT NASIMSTEIN CUSTOMER RELATIVITY OFFICER
2 SIR ISSAC NASIMTON APPLEICATION MANAGER
3 LOUISE PASTEUR MILK PASTEURISATION MANAGER
4 GALILEO ASTRONOMICAL OFFICER
5 NIKOLA TESLA ELECTRO MAGNETIC FIELD OFFICER
6 ARISTOTLE CHEIF EXECUTIVE OFFICER

Now, We can write the JSP file, convert_to_titlecase.jsp,  like this:
<%@page import="java.sql.*"%>
<%@page import="org.apache.commons.lang3.text.WordUtils"%>
<%@page contentType="text/html;charset=ISO-8859-15" language="java" %>
<%
response.setHeader("Cache-Control","no-cache");
response.setHeader("Pragma","no-cache");
response.setDateHeader ("Expires", 0);

Statement stmt = null;
Connection conn = null;

// Set response content type
response.setContentType("text/html");
String title = "Title Case Conversion";
String docType = "\n";
out.println(docType + "\n" + "" + title
  + "\n" + "\n"
  + "

" + title + "

\n"); try { // Register JDBC driver Class.forName("com.mysql.jdbc.Driver"); String dbName = request.getParameter("dbName"); // Open a connection conn = DriverManager.getConnection("jdbc:mysql://localhost/" + dbName); // Execute SQL query stmt = conn.createStatement( ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); String tableName = request.getParameter("tableName"); String columnName = request.getParameter("columnName"); out.println("Database: " + dbName + "
"); out.println("Table: " + tableName + "
"); out.println("Column: " + columnName); out.println("
"); String sql; sql = "SELECT * FROM " + tableName; ResultSet rs = stmt.executeQuery(sql); long countColumn = 0; long countFail = 0; while (rs.next()) { String columnValue = rs.getString(columnName); try { rs.updateString(columnName, WordUtils.capitalizeFully(columnValue) ); rs.updateRow(); } catch(Exception exc) { out.println("" + columnValue + " not updated; " + exc.getMessage() + ""); countFail++; continue; } countColumn++; } out.println(countColumn + " columns updated successfully.
"); out.println(countFail + " failed."); out.println(""); rs.close(); stmt.close(); conn.close(); } catch (SQLException se) { // Handle errors for JDBC se.printStackTrace(); } catch (Exception e) { // Handle errors for Class.forName e.printStackTrace(); } finally { // finally block used to close resources try { if (stmt != null) stmt.close(); } catch (SQLException se2) { }// nothing we can do try { if (conn != null) conn.close(); } catch (SQLException se) { se.printStackTrace(); }// end finally try } // end try %>

On Apache Tomcat server we can test it locally by calling this script by passing the parameters dbName, tableName and columnName as follows:
http://localhost:8080/myproject/convert_to_titlecase.jsp?dbName=test&tableName=Employee&columnName=designation

It will change all the texts of designation column to Title-Case as follows:

id fullName designation
1 ALBERT NASIMSTEIN Customer Relativity Officer
2 SIR ISSAC NASIMTON Appleication Manager
3 LOUISE PASTEUR Milk Pasteurisation Manager
4 GALILEO Astronomical Officer
5 NIKOLA TESLA Electro Magnetic Field Officer
6 ARISTOTLE Cheif Executive Officer