Wednesday, November 16, 2005

How to create a SQL query for selecting most frequently occurring values in a column

To select values for a column with the highest recurrence

SELECT COL1 FROM TABLE1 GROUP BY COL1 HAVING COUNT(*) >= ALL(SELECT COUNT(*) FROM TABLE1 GROUP BY COL1);

For DB2 the following query may be a bit more optimized

SELECT COL1 FROM TABLE1 GROUP BY COL1 HAVING COUNT(*) = (SELECT COUNT(*) FROM TABLE1 GROUP BY COL1 ORDER BY COUNT(*) DESC FETCH FIRST ROW ONLY);

Friday, November 19, 2004

Print out all members of a collection in JDK 5.0

In JDK 5.0, there is a new Arrays class which provides a deepToString() method, which will print out all members of an array.

For example, the following code will printout the multi-dimensional array values:

String[][] strings = {{"I", "love", "lucy"}, {"I", "hate", "bart"};
System.out.println(Arrays.deepToString(strings));

The output of the code above is:

[[I, love, lucy], [I, hate, bart]]

One can use the same method to print out single-dimension collections such as ArrayList and HashSet. The below examples show how to do get the printout:

// print out all members of a vector
System.out.println(Arrays.deepToString(vector.toArray(new Object[0]))));

// print out all members of a hash set
System.out.println(Arrays.deepToString(set.toArray())));


Saturday, October 30, 2004

ANT logging PART II

If you are using ANT for daily EOD build, then you may wish to get a pager or e-mail notification when an error occurs during the build process. Integrating log4j with ANT is fairly straight-forward, as the previous part has explored. But there might be scenarios where using the default log4j configuration may not be desirable.

1. If you have more than one build routines running under the same project, you may not wish to maintain multiple log4j.properties files.

2. If you are deploying an enterprise application, and your EOD build script requires the loading of databases, you may have additional interested parties such as DBAs and network administrators who will monitor your process and potentially make changes. Maintaining a separate properties file for logging may not be acceptable to them. In that case, you may find yourself having to consolidate the log4j.properties file into a properties file configuring the major aspects of your ANT program.

The following code demonstrates how to write a custom ANT task to hook a particular ANT script to log4j, and to read the main ANT properties file for that program for the log4j settings.


Properties props = new Properties();
try {
//props.load(Thread.currentThread().getContextClassLoader().getResourceAsStream(
// "ant_config.properties"));
props.load(new FileInputStream(configFile));
Enumeration enum =
props.propertyNames();
while(enum.hasMoreElements()){
String name =
(String) enum.nextElement();
if(!name.startsWith("log4j.")){
props.remove(name);
}
else{
log(name + "=" + props.getProperty(name));
}
}
PropertyConfigurator.configure(props);
} catch (IOException e) {
e.printStackTrace();
}
project.addBuildListener(new Log4jListener());

After this is done, then there is no mystery in getting log4j to send out e-mail or pager notifications when something goes wrong with your build process. Below is an example log4j configuration that enables the SMTPAppender for e-mails.

# log4j configuration
log4j.rootLogger=INFO, R, mail
log4j.appender.R=org.apache.log4j.RollingFileAppender
log4j.appender.R.File=build.log
log4j.appender.R.MaxFileSize=10000KB
log4j.appender.R.MaxBackupIndex=1
log4j.appender.R.layout=org.apache.log4j.PatternLayout
log4j.appender.R.layout.ConversionPattern=%p %t %c - %m%n
log4j.appender.mail=org.apache.log4j.net.SMTPAppender
log4j.appender.mail.threshold=ERROR
log4j.appender.mail.SMTPHost=youmailhost.com
log4j.appender.mail.BufferSize=1
log4j.appender.mail.subject=Build failure
log4j.appender.mail.from=yourbuild@build.com
log4j.appender.mail.to=xyz@zyx.com,zzz@att.mobile.com
log4j.appender.mail.layout=org.apache.log4j.HTMLLayout
#log4j.appender.mail.layout.ConversionPattern=%d{ISO8601} %5p [%t] (%F:%L) - %m%n

Friday, October 08, 2004

Dynamic resize of iframe in IE 5.5 with HTML documents formatted with
tags

I did a few google searches, and there doesn't seem to have any ready-made javascript for dynamically resizing an iframe in Internet Explorer 5.5 where the document layout is done using
tags. So here is the script for that:


var dhtml_frame = document.frames(frame_id);
var dhtml_frame_doc = dhtml_frame.document;
while(dhtml_frame_doc.readyState != "complete"){};
var height = 0;
var width = 0;
for(var i = 0; i < tagname ="="> height)
{
height = dhtml_frame_doc.all[i].offsetHeight;
}
if(dhtml_frame_doc.all[i].offsetWidth > width)
{
width = dhtml_frame_doc.all[i].offsetWidth;
}
}
}
iframe.style.height = height + 20;
iframe.style.width = width + 20;

Tuesday, October 05, 2004

Interim addition on ANT

Before I finish my second part on error-handling/reporting in ANT, I will explore how to deploy ANT in a production environment.

The default distribution of ANT provides platform independent scripts that enable ANT to be deployed to different platforms. However, the default installation instructions pollute the host environment with the ANT_HOME variable. In a production environment, having multiple applications depend on a single ANT instance may create undesired dependencies.

One solution to this problem is to specify only the ANT_HOME and ANT_HOME/lib variables at execution time, so that each application can have a different ANT location.

In Windows, you can wrap around your ANT execution by creating a .bat file with the following commands:

set ANT_HOME=${ACTUAL_ANT_HOME_DIR}
set PATH=%PATH%;%ANT_HOME%/bin
ant ${TargetName}

In Unix, you can create a shell script that duplicates the functionality above:

ANT_HOME=${ACTUAL_ANT_HOME_DIR}
PATH=%PATH%;%ANT_HOME%/bin
ant ${TargetName}

One disadvantage of this approach is that the actual command is not platform independent. As of version 1.6.2, ANT allows you to specify the above information in a single line of code which is compatible with both Windows and Unix. You can simply launch the ANT environment by issuing the command:

java -classpath "${ANT_LIB_DIR}/ant-launcher.jar" -Dant.home="${ANT_HOME_DIR} -Dant.Dant.library.dir="${ANT_LIB_DIR}" org.apache.tools.ant.launch.Launcher ${TargetName}

Wednesday, September 22, 2004

ANT logging PART I

Imagine a scenario where at the end of day the application you are developing needs to be built from source. Furthermore, the application data needs to be synced with the development database and LDAP server. One can do all this using ANT scripting thanks to the powerful and extensible architecture of ANT. But what if something, anything goes wrong with the script at runtime? What ANT safeguards exist in place for error-reporting and error-handling?

One solution is to write custom tasks during critical junctures in the ANT script to validate the results of the build process. Any errors caught by the validation process can then be logged/e-mailed to the developers for troubleshooting. Since custom ANT tasks are written in Java, the tasks are free to use JavaMail, Apache Commons Logging, or Log4j for error handling and logging.

A critical problem exists in this approach. Suppose your scripts use built-in ANT tasks or third-party tasks that terminate abnormally. In the worst case scenario, as when the tasks throw the BuildException, the entire build script would terminate with an error return code. If your validation tasks is executed after the point of error, they will never be run. In other words, when an ANT script terminates abnormally, you may never get to run your validation code, and never receive notifications of the failure.

A better solution is to configure log4j as the appender for ANT. This way you have the full gamut of Log4j appenders at your disposal. For example, you can log at the INFO-level all the ANT script messges using the RollingFileAppender, and you can e-mail all ERROR-level messages using SMTPAppender. To use Log4j as the default logger, simply place the log4j.jar file along with the log4j.properties file in the execution classpath of the ANT script, and type in the following command to execute the script:

ant -listener org.apache.tools.ant.listener.Log4jListener [target name]

Once you configure Log4j as the logger, ANT environment would still send out a one-line error message in the case of an abnormal termination. That line can then be e-mailed to the proper personnel.

Two drawbacks exist with this approach.

First, you may have more than one ANT build scripts running under the same environment. You may not wish them to have the same Log4j configurations. You may, for example, wish to e-mail different people depending on which ANT script failed. That information cannot be specified using the one-line command above.

Second, you may not wish to maintain multiple configuration files for ANT and Log4j under a single application.

The next part of this series would explore how you can overcome the abovementioned problems.


Thursday, September 16, 2004

Recall your previous command in Unix

1. set -o vi
2. Escape + k for previous command
3. Escape + j for next command

SQL query to identify list of duplicate rows

First technical post. I am going to make it a bit lighter fare. The following SQL script example shows how to identify duplicate rows in a data table.

SELECT col1, col2, count(*)
FROM t1
GROUP BY col1, col2
HAVING count(*) > 1

The query was ripped from a Microsoft article, which has further information on how to remove duplicate rows.
http://support.microsoft.com/default.aspx?scid=kb;en-us;139444

Objectives for this blog

First, the primary audience for this blog is me. I keep my accumulated technical reference for future reference.

Those who are in my field may find some snippets interesting and helpful.

Those are the goals I have set out to achieve:

1. The purpose of the journal should remain technical. In other words, no bullshit personal stuff and random ramblings except maybe this post.
2. The technical articles should remain technical. Personal opinions of specific products/companies/technologies should be expressed only with evidence.

That's it.