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:
Date :

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:

Date :

Check it below:
Date :

Here is the jsfiddle for this.

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" + "\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

MySql select the first row for each group

In the following table the enrollment of students in different program are shown. I want to select the last programme (ordered by date) in which each of the student is enrolled in.

 

  
id
  
studentId
  
programmeId
  
startDate
  
endDate
 

 

  
1
  
444
  
101
  
2012-01-01
  
2012-06-01
 

 

  
2
  
555
  
101
  
2012-01-01
  
2012-06-01
 

 

  
3
  
666
  
101
  
2012-01-01
  
2012-06-01
 

 

  
4
  
444
  
201
  
2012-06-15
  
2012-12-20
 

 

  
5
  
555
  
301
  
2011-07-01
  
2012-02-01
 

 

  
6
  
666
  
201
  
2012-06-15
  
2012-12-20
 

 

  
7
  
444
  
301
  
2011-07-01
  
2012-02-01
 

We have to make group of students and sort the programmes for each student by the order (descending) of their programme-end-Dates, and then select the first programme from each student-group. And the result should look like this.

 

  

   studentId
  
  

   programme
  
 
 

  

   444
  
  

   201
  
 
 

  

   555
  
  

   101
  
 
 

  

   666
  
  

   201
  
 

To generate the above result the query can be written like this:

select
    R.studentId,
    (
        select
            R1.programmeId
        from
            Registration R1
        where
            R1.studentId=R.studentId
        order by
            R1.endDate DESC limit 1
    ) as "programme"
from
    Registration R
group by
    R.studentId;

MySql: Select Query- Make A Cumulative Sum Column

Suppose we have a table like following:

 

  
transaction_id
  
acc_no
  
amount
 
 

  
1
  
100001
  
20000
 
 

  
2
  
100002
  
30000
 
 

  
3
  
100003
  
30000
 
 

  
4
  
100004
  
10000
 
 

  
5
  
100005
  
50000
 

And now we want a query that select a cumulative sum column along with other columns like following:

 

  
transaction_id
  
amount
  
cumulative
 
 

  
1
  
20000
  
20000.0
 
 

  
2
  
30000
  
50000.0
 
 

  
3
  
30000
  
80000.0
 
 

  
4
  
10000
  
90000.0
 
 

  
5
  
50000
  
140000.0
 

We can use pl/sql variables like below to generate the above result:

select
    transaction_id,
    amount,
    (@cum_sum:=@cum_sum+amount) as "cumulative"
from
    account
    JOIN (select @cum_sum := 0.0) B;

MySql Query: PLSQL Conditions within query

Suppose we have a table like:

 

  
transaction_id
  
acc_no
  
amount
 
 

  
1
  
100001
  
20000
 
 

  
2
  
100002
  
30000
 
 

  
3
  
100003
  
30000
 
 

  
4
  
100004
  
10000
 
 

  
5
  
100005
  
50000
 

Now the query

select
    acc_no,
    SUM(IF(amount>20000,amount,0)) as total_value
from
    account
where
    acc_no=100002
group by acc_no;

will produce the following result:

 

   

      
acc_no
      
amount
   
   

      
100002
      
80000
    

Case statements can also be used within a query like shown in the answer of this stackoverflow question

Load/Import huge database in MySQL

phpMyAdmin doesn't allow importing database of huge size, so we have to do it from command prompt.

Go to command prompt:

cd ".../mysql5.5.16/bin"
mysql -u root -p[password if any, otherwise keep blank]
use dbname;
source .../.../dbname.sql;

Here dbname is the name of your database.

wxPython TextCtrl Handling KayEvent 1

The following code shows how we can Bind a wx.EVT_KEY_UP KeyEvent with a TextCtrl widget:

import wx

class MyForm(wx.Frame):

    def __init__(self):
        wx.Frame.__init__(self, None, wx.ID_ANY, "Show Caret Position", size=(400, 140))
        panel = wx.Panel(self, wx.ID_ANY)
        sizer = wx.BoxSizer(wx.VERTICAL)
        text = wx.StaticText(panel, -1, "Text:", (10, 22))
        self.textCtrl = wx.TextCtrl(
                panel,
                -1, "",
                (50,5),
                size=(250, 50),
                style=wx.TE_MULTILINE
            )
        self.textCtrl.SetInsertionPoint(0)
        self.textCtrl.Bind(wx.EVT_KEY_UP,self.onTextKeyEvent)
        self.statusbar = self.CreateStatusBar(1)
        panel.SetSizerAndFit(sizer, wx.VERTICAL)

    def onTextKeyEvent(self, event):
        statusText = "Caret Position: "+str(self.textCtrl.GetInsertionPoint())
        self.SetStatusText(statusText,0)
        event.Skip()


#Run application
if __name__ == "__main__":
    app = wx.PySimpleApp()
    frame = MyForm()
    frame.Show()
    app.MainLoop()

Create and use preverified JAR files in Blackberry applications

This post "blackberry-tips-bikas.blogspot.com: how-to-create-library-jar-file-and-use" describes how a JAR file can be created and used as a library in Blackberry applications. But I faced some problem after following the approach described there. Then I've tried by skipping the step 5 of "How to create the JAR file" and succeeded. I'm describing the full process again here for both Eclipse and JDE:

For Eclipse:

Create the JAR file:

-- Create your Blackberry project (e.g. "MyLib")
-- Clean and build the project after adding all necessary files in the "src" folder and all necessary
    resource files in the "res" folder.
-- Package the project: right click on the project--> BlackBerry --> Package Projects
-- The "MyLib.jar" file will be created in the "ProjectDirectory\deliverables\Standard\X.0.0\" directory
    (here 'X' will be different depending on the version of OS set for your project.)
-- Go to your Eclipse JDE plugins installation folder (e.g. "C:\Eclipse"

-- Go to "C:\Eclipse\plugins\net.rim.ejde.componentpack5.0.0_5.0.0.25\components\bin\"
    The "componentpack5.0.0_5.0.0.25" can be different depending on your Eclipse JDE plugin version.

-- Copy MyLib.jar file into the "...\components\bin\" folder.
-- Open command prompt and change your current directory to the bin directory
    (>cd "C:\Eclipse\plugins\net.rim.ejde.componentpack5.0.0_5.0.0.25\components\bin\")
-- Then execute the following command:
    preverify -classpath "C:\Eclipse\plugins\net.rim.ejde.componentpack5.0.0_5.0.0.25\components\lib\net_rim_api.jar"  "MyLib.jar"
-- A folder named "output" will be created in the bin directory. The preverified jar file will be found in
   the output directory. 
-- Now extract the jar file with WinZip or such other application. Your project folders and files will be
    extracted there.

-- Delete the .cod, .cls, .cso files from there.
-- Make jar again with the remaining files and folders (i.e. make zip and rename to .jar).
-- Now you can use this jar file in another project as a library.

Use the JAR in other projects:

-- Copy the jar and paste into the lib folder of your project.
-- Right click on the jar file just pasted, and click "Add to Build Path"
-- Right click on the project, go to Build Path --> Configure Build Paths --> Java Build Paths. Select the "Libraries" tab and you will see your jar file in the list of libraries there.
-- Select the "Order and Export" tab and check the checkbox of "MyLib.jar". Press 'Ok'.
-- Your jar is added to your project.

For JDE:

 Create the JAR file:

-- Create your workspace (e.g. MyLib.jdw)
-- Create your project (e.g. MyLib.jdp)
-- Add your classes that you want your jar to contain.
-- Build your project.
-- A .jar file named MyLib.jar will be created in the "jdeworkspace-->MyLib" folder.
-- Go to your JDE installation folder
    (usually: "C:\Program Files\Research In Motion\BlackBerry JDE 5.0.0\bin")
-- Copy MyLib.jar file to the bin folder.
-- Open command prompt and change your current directory to the bin directory
    (execute command > cd "C:\Program Files\Research In Motion\BlackBerry JDE 5.0.0\bin")
-- Then execute the following command:
    preverify -classpath "C:\Program Files\Research In Motion\BlackBerry JDE 5.0.0\lib
        \net_rim_api.jar"  "MyLib.jar"
-- A folder named "output" will be created in the bin directory. The preverified jar file will be found in
   the output directory. 
-- Now extract the jar file with WinZip or such other application. Your project folders and files will be
    extracted there.
-- Delete the .cod, .cls, .cso files from there.
-- Make jar again with the remaining files and folders.
-- Now you can use this jar file in another project as a library.

Use the JAR in other project:

-- Right click on the project on JDE
-- Click properties and click on the "Build" tab.
-- Add "MyLib.jar" as Imported jar files.