Software Salariman

Introduction

A pivot table or cross tabulation is a reporting feature that BAs love to use to summarise transaction data, such as server logs and sales figures. Spreadsheet programs such as Microsoft Excel or OpenOffice.org Calc have nifty wizards to help you create a pivot table. You can also create pivot tables in databases. For example, Microsoft Access has a TRANSFORM … PIVOT SQL statement for generating a crosstab or pivot table.

What if you're using a database program that doesn't directly support pivot tables? For example, Sqlite 3 and MySQL don't seem to have any SQL statements for pivot tables.

All is not lost; another way to express a pivot table is to use aggregate functions, condition clauses and GROUP BY clause in this template:

SELECT col1, col2, … <aggregate>(<condition>) … FROM table1 GROUP BY col1, col2, ….

For Sqlite 3, the aggregate functions and GROUP BY is similar to SQL in other datbase programs. The condition clause we can use has this syntax: case when <expression> then <expression> end.

In the next section, we'll demonstration how to create pivot tables in Sqlite 3 using this template. All examples will be shown using Sqlite 3's command line interface, sqlite3.exe.

Sqlite 3 Pivot Table Demonstration

First, you have to download some sample transaction data. I used the NumberGo Pivot Table Tutorial AcmeShirtsCompany.xls spreadsheet as the raw data for this demonstration.

We start sqlite3.exe and use the -column -header arguments make the output of queries more readable.

sqlite3 -column -header test.db
SQLite version 3.5.9
Enter ".help" for instructions

Now we create a shirt table based on the headings in that spreadsheet:

create table shirt (Region varchar(8), Category varchar(8), Shirt_Style varchar(8), ShipDate date, Units integer, Price double, Cost double);

Next we load some transaction data into the shirt table:

insert into shirt values ('East','Boys','Tee',date('2005-01-01'),11,5.25,4.66);
insert into shirt values ('East','Boys','Golf',date('2005-01-01'),12,5.26,4.57);
insert into shirt values ('East','Boys','Polo',date('2005-01-01'),13,5.27,5.01);
insert into shirt values ('East','Girls','Tee',date('2005-01-01'),14,5.28,5.01);
insert into shirt values ('East','Girls','Golf',date('2005-01-01'),15,5.29,5.10);
insert into shirt values ('East','Girls','Polo',date('2005-01-01'),16,5.30,4.67);
insert into shirt values ('West','Boys','Tee',date('2005-01-01'),33,6.25,5.36);
insert into shirt values ('West','Boys','Golf',date('2005-01-01'),35,6.26,6.24);
insert into shirt values ('West','Boys','Polo',date('2005-01-01'),36,6.27,6.03);
…

Let's begin our analysis with a simple question: How many shirts were sold in each region?

select Region, sum(Units) from shirt group by Region;
Region      sum(Units)
----------  ----------
East        21841
North       27275
South       29994
West        23984

Next: in each region, how many Boys' and Girls' shirts were sold? Here's where a pivot table is useful:

select
  Region
  , sum(case when Category = 'Boys' then Units end) as Boys
  , sum(case when Category = 'Girls' then Units end) as Girls
  , sum(Units) as SubTotal
from shirt
group by Region;
Region      Boys        Girls       SubTotal
----------  ----------  ----------  ----------
East        10586       11255       21841
North       14049       13226       27275
South       14312       15682       29994
West        10763       13221       23984

We can drill further into the data: How many of each shirt style were sold?

select
  Region
  , Category
  , sum(case when Shirt_Style = 'Tee' then Units end) as Tee
  , sum(case when Shirt_Style = 'Golf' then Units end) as Golf
  , sum(case when Shirt_Style = 'Polo' then Units end) as Polo
  , sum(Units) as SubTotal
from shirt
group by Region, Category;
Region      Category    Tee         Golf        Polo        SubTotal
----------  ----------  ----------  ----------  ----------  ----------
East        Boys        3458        3096        4032        10586
East        Girls       3688        3481        4086        11255
North       Boys        4597        4702        4750        14049
North       Girls       4196        4598        4432        13226
South       Boys        5192        4670        4450        14312
South       Girls       5113        5377        5192        15682
West        Boys        3722        3791        3250        10763
West        Girls       4472        4235        4514        13221

The pattern becomes obvious, if rather tedious, when you want to use a specific values as a new virtual column.

Discussion

In this article, I've presented a SQL template for generating pivot tables for database programs, such as Sqlite 3, that do not have explicit support for this feature. While this template is extensible, it relies on the developer knowing beforehand the possible values (e.g. Category has 'Boys' and 'Girls', or Shirt Styles has 'Tee', 'Golf' and 'Polo') to use in the condition clause of the template. If there are many possible values, then it becomes very tedious to enumerate each of them in the SQL case when … then … end clause.

2008-06-01: I had a play with MySQL and found that I can use the same SQL statements to create the pivot tables.

I had saved the data in AcmeShirtsCompany.sql, so to set up my MySQL database, I created the shirt table using mysql.exe, exit the interpreter, then loaded the data into the database using this cmd.exe command: mysql -u root -p -D test < AcmeShirtsCompany.sql.

2008-06-06: See also SQL Cookbook by Anthony Molinaro, O'Reilly Media.

Labels: Database, Programming

link posted by Kam-Hung Soh @ 7:07 PM   0 comments
Digg it!   Del.icio.us   Earthlink   Furl it!   Reddit!   Stumble It!   Slashdot It!   Yahoo MyWeb

I converted my earlier PowerShell script to prune directories to Python:

     1  from os import listdir, rmdir
     2  from os.path import isdir, exists, join
     3
     4  def prune_directory(path):
     5    if len(path) < 1:
     6      print "Empty path"
     7      return
     8    if not exists(path):
     9      print "Invalid path:", path
    10      return
    11    if not isdir(path):
    12      return
    13    if len(listdir(path)) <= 0:
    14      rmdir(path)
    15      return
    16    for elem in listdir(path):
    17      prune_directory(join(path, elem))
    18    if len(listdir(path)) <= 0:
    19      rmdir(path)

It's almost a one-to-one translation from the initial PowerShell version. The difference is that in Python, you have to ensure that path is a directory before you call listdir(path).

Python 2.5 has a new generator function os.walk() for traversing a directory tree. Below, the recursive function call in lines 13-19 is replaced by a for loop in lines 13-14.

     1  from os import listdir, rmdir, walk
     2  from os.path import isdir, exists
     3
     4  def prune_directory_walk(path):
     5    if len(path) < 1:
     6      print "Empty path"
     7      return
     8    if not exists(path):
     9      print "Invalid path:", path
    10      return
    11    if not isdir(path):
    12      return
    13    for curr, dirs, files in walk(path, topdown=False):
    14      if len(listdir(curr)) < 1: rmdir(curr)

In this sample, os.walk() returns a tuple (curr, dirs, files) for each directory it visits. curr is the current directory being traversed, and dirs and files are the directories and files in that directory. Using the parameter topdown=False, os.walk() starts producing these tuples from the lowest descendant directory and working up to the start directory, path.

Note that the loop's conditional statement uses len(listdir(curr)) instead of just len(dirs). os.walk() generates the dirs list before it visits each of the directories in dirs; if all child directories in dirs have been deleted, dirs would still contain an unchanged list and the parent directory, curr, would not be deleted. At least, that's what I think happens; the Python help doesn't say so explicitly.

In earlier versions of Python, there is a similar function called os.path.walk() but os.walk() is much easier to use.

Labels: Programming, Python

link posted by Kam-Hung Soh @ 7:54 PM   0 comments
Digg it!   Del.icio.us   Earthlink   Furl it!   Reddit!   Stumble It!   Slashdot It!   Yahoo MyWeb

I made a backup of all files with a certain pattern files from one directory to another. If the pattern was, say PostScript (*.ps) files, you can use the following PowerShell statement:

Copy-Item -recurse -filter *.ps <source> <destination>

Now I had a new directory with the same structure as the original one, but Copy-Item made many new empty directories because there were files in the source directories but these files were not copied. Just to be tidy, I wanted to prune the empty directories in the destination path. The Remove-Item cmdlet does not have an option to remove empty directories, so I wrote the following short PowerShell script:

     1  function prune-directory {
     2    param ([string]$path)
     3    if ($path.Length -le 0) {
     4      write-host "Empty path."
     5      return
     6    }
     7    if (-not (test-path -literalPath $path)) {
     8      write-host "Invalid path:", $path
     9      return
    10    }
    11    if (@(get-childitem $path).Count -le 0) {
    12      remove-item $path
    13      return
    14    }
    15    get-childitem $path | where-object { $_.PsIsContainer} | foreach { prune-directory $_.FullName }
    16    if (@(get-childitem $path).Count -le 0) {
    17      remove-item $path
    18    }
    19  }

To use it, just enter:

prune-directory <path>

You should verify that the function works the way you expect before using it. Once your directories or files are deleted, they're GONE.

prune-directory() is a recursive function that walks a directory tree and deletes any empty directory it finds. Lines 3-10 check for invalid parameters, lines 11-14 delete the current directory if it is empty and line 18 calls this function for all children which are containers in the current directory. Lines 19-22 are required in case the current directory has no children because they were all deleted by line 15.

In line 11 and 16, we use @(…) to force the result of get-childitem $path to be an array, otherwise we may not be able to count the number of children in a directory. It's a known - uh - nuance in PowerShell that if a cmdlet finds zero or one object, it returns an scalar value rather than an array.

2008-05-15: This change should fix the problem of escape characters in the path string: test-path -literalPath $path.

Labels: PowerShell, Programming

link posted by Kam-Hung Soh @ 12:31 PM   2 comments
Digg it!   Del.icio.us   Earthlink   Furl it!   Reddit!   Stumble It!   Slashdot It!   Yahoo MyWeb

Wrote an article about converting an simple Java program to C# and Windows Forms and discussed development features in Visual Studio C# 2008 Express.

This blog entry is used for discussion, if any.

Labels: CSharp, DotNet, Programming

link posted by Kam-Hung Soh @ 10:02 PM   0 comments
Digg it!   Del.icio.us   Earthlink   Furl it!   Reddit!   Stumble It!   Slashdot It!   Yahoo MyWeb

Finished a longish article, with sample source code, about writing a simple desktop application in Java and Swing. It discusses model-view-controller, Java resource bundles and Swing AbstractAction.

This blog entry is used for discussion, if any.

Labels: Java, Programming

link posted by Kam-Hung Soh @ 4:23 PM   0 comments
Digg it!   Del.icio.us   Earthlink   Furl it!   Reddit!   Stumble It!   Slashdot It!   Yahoo MyWeb

Jon Bentley's Programming Pearls describes the following pipeline for finding anagrams from a list of words: generate a signature for the word, then group together all words with the same signature. The signature is just a sorted list of all letters in a word. For instance, dame, edam, made and mead all have the same signature adem.

Associative Arrays

To implement an anagram-finder in PowerShell, let's use an associative array and we store the signatures in the keys and each word that has the same signature is stored in an array related to that key. Below is a concrete example of what we plan to do:

> $a = @{adem:("dame","edam","made","mead")}
> $a
Name                           Value
----                           -----
adem                           {dame, edam, made, mead}

Notes

• The key for an associative array does not have to be quoted if it is a string without a whitespace.
• Oddly, arrays in the value column are printed delimited by braces instead of parentheses.

Generating Word Signatures

A word signature is just a string with the letters in the original word sorted. We split a string into a char[] type, sort it, then make it into a string again:

> $sig = [string]("edam".ToCharArray() | sort-object)
> $sig
a d e m
> $sig.length
7

Note that the signature sig is a string with a space between each character. We can prettify the signature but it doesn't hurt because all the signatures will have the same format.

Find Anagrams in a Word List

Now that we can create a signature, we can find all anagrams in a word list by assigning each word's signature as a key in the associative array's value and adding the word to that key's array:

> get-content <test.txt> | foreach-object { $h = @{} } { $t = $_.clone(); $sig = [string]($t.ToCharArray() | sort-object); if (!$h.containsKey($sig)) { $h[$sig] = @() } $h[$sig] += $t } { $h }

Name                           Value
----                           -----
adem                           {dame, edam, made, mead}

Let's decompose this longish statement to understand what it is doing:

get-content <test.txt> |

Send every line in the input file into a pipeline.

foreach-object

Apply some operation on each object in the pipeline.

{ $h = @{} }

Initialize the associative array h in the begin script block.

{ process block }

Here is where words with the same signature are grouped together in the associative array.

$t = $_.clone();

Copy the input word from the current pipeline object before it is overwritten in the next statement.

$sig = [string]($t.ToCharArray() | sort-object);

Get the signature of the input word.

if (!$h.containsKey($sig)) { $h[$sig] = @() }

Create an array of anagrams if the signature does not already exist.

$h[$sig] += $t

Add the current word to the array of anagrams.

{ $h }

Output the associative array h in the end script block.

Conclusion

This article presented an imperative approach to grouping data with the same property using a loop and an associative array. You could apply the same style to any programming language and get the same result. In a future article, I will explore how to use a more streamlined approach to solve similar problems.

Labels: PowerShell, Programming

link posted by Kam-Hung Soh @ 6:25 PM   0 comments
Digg it!   Del.icio.us   Earthlink   Furl it!   Reddit!   Stumble It!   Slashdot It!   Yahoo MyWeb

Using Scott Hickey's article on reducing code noise in Groovy as a starting point, can we measure of the noisiness of a programming language and environment? Let's say that the quietest code where the developer has to add the least amount boilerplate code to implement a particular feature. Just to keep the metric simple, let's just measure the size of the source files for different implementations of the same feature and assume that the developer is trying to write sensible code. We assume that the shortest version is the quietest and calculate the ratio between the shortest version and all other versions.

Let's test this ratio on several versions of Hello World implemented using different scripting languages in previous articles. What is the noisiness of each implementation?

What does this table tell us about reducing code noise for small programs?

• Script environment should pre-import common classes. Groovy + SwingBuilder and plain Groovy makes it very easy to write a small GUI program because all the Java Swing references are pre-imported. The Jython and IronPython implementations are nearly the same but the IronPython version is longer because it has to load .Net references.
• Use class aliases. One reason the PowerShell version is very long is you can't add a class name into the current namespace (such as Python's from <library> import <class> or C# using namespace <blah>). You can define class aliases like this: $Form = [System.Windows.Forms.Form] but that only starts reducing noise when you use that class more than once.
• Define properties in constructors. Another reason the PowerShell version is long is that only the .Net constructors for GUI controls are available through the platform interface, so to define a control, you have to write a sequence of statements starting with creating a new object followed by some SetX() methods. I wonder if PowerShell adaptors can overload constructors?

Labels: Programming

link posted by Kam-Hung Soh @ 6:00 PM   0 comments
Digg it!   Del.icio.us   Earthlink   Furl it!   Reddit!   Stumble It!   Slashdot It!   Yahoo MyWeb

If you're using Sax BASIC 6.4.x, you can only initialize an array of Variant using the Array(), not any other type. For example:

Option Explicit
Dim s1() As Variant
s1 = Array("a", "b", "c") 'Works
Dim s2() As String
s2 = Array("a", "b", "c") 'Fails with (10902) Builtin function/instruction is not implemented

Labels: Programming

link posted by Kam-Hung Soh @ 2:09 PM   0 comments
Digg it!   Del.icio.us   Earthlink   Furl it!   Reddit!   Stumble It!   Slashdot It!   Yahoo MyWeb

Convert a number from base-10 to another base using the .Net Convert.ToString(number, target-base) static function. Test in PowerShell:

> [Convert]::ToString(15,16)
f
> [Convert]::ToString(15,2)
1111

To convert from base-n to base-10, use .Net Convert.ToInt32(String, source-base). There are also ToInt16() and ToInt64() methods. Examples using ToInt32() below:

> [Convert]::ToInt32("f",16)
15
> [Convert]::ToInt32("1111",2)
15

If you use the wrong source-base, PowerShell shows this error:

Exception calling "ToInt32" with "2" argument(s): "Could not find any recognizable digits."
At line:1 char:19
+ [Convert]::ToInt32( <<<< "f",2)

Note: Don't mix this class with DOS convert command which converts a filesystem from FAT32 to NTFS!

Labels: PowerShell, Programming

link posted by Kam-Hung Soh @ 6:00 PM   0 comments
Digg it!   Del.icio.us   Earthlink   Furl it!   Reddit!   Stumble It!   Slashdot It!   Yahoo MyWeb

E-books from Project Gutenberg are broken into 70-character lines. Fixed length lines can be hard to read on a mobile phone, so I wanted to let the phone's viewer break the lines and use only a blank line between paragraphs. Here's some text from Arthur Conan Doyle's A Scandal in Bohemia originally fro Project Gutenberg:

"Wedlock suits you," he remarked. "I think, Watson, that you have
put on seven and a half pounds since I saw you."

"Seven!" I answered.

"Indeed, I should have thought a little more. Just a trifle more,
I fancy, Watson. And in practice again, I observe. You did not
tell me that you intended to go into harness."

Below is how this text should become (paste the before and after text into your text editor to see the difference):

"Wedlock suits you," he remarked. "I think, Watson, that you have put on seven and a half pounds since I saw you."

"Seven!" I answered.

"Indeed, I should have thought a little more. Just a trifle more, I fancy, Watson. And in practice again, I observe. You did not tell me that you intended to go into harness."

The following command in Vim did the required conversion: %s/\(.\)\n\(.\)/\1 \2/. Basically, for every line, replace any new line character between any two characters with a whitespace.

Labels: Programming, Software

link posted by Kam-Hung Soh @ 1:37 PM   0 comments
Digg it!   Del.icio.us   Earthlink   Furl it!   Reddit!   Stumble It!   Slashdot It!   Yahoo MyWeb

During the development and test phase of a project, I have to install and uninstall daily program builds for testing, so I'm pretty interested in reducing the amount of time and effort required to uninstall programs. Here's how my uninstallation process evolved:

In the beginning, use Start / Control Panel / Add or Remove Programs.

Spend less time by starting Windows' Add or Remove Programs dialog using the Run dialog by typing Windows+R appwiz.cpl.

Then you might notice that the Windows XP Add or Remove Programs dialog takes up to 30 seconds to start and there's no quick way to find the desired program. It's annoying that the list of programs doesn't scroll when you hit the PageDown and PageUp keys or when you type the first few letters of a program name. (The Vista equivalent, Programs and Features, doesn't have these limitations.)

Finally, using Windows Installer, msiexec, you could uninstall any program if you have the ProductCode. Just create a desktop shortcut with the following string in the Target field:

C:\WINDOWS\system32\msiexec.exe /uninstall <ProductCode>

P.S. You can find the ProductCode of your program using this script.

Labels: Programming, Windows

link posted by Kam-Hung Soh @ 9:46 AM   0 comments
Digg it!   Del.icio.us   Earthlink   Furl it!   Reddit!   Stumble It!   Slashdot It!   Yahoo MyWeb

Quick note. JUnit 4.1 is very different from JUnit 3.8. See …

• JUnit 4 Primer
• JUnit Reloaded.
• Get Acquainted with the New Advanced Features of JUnit 4

JUnit 4.1 is supported by Eclipse 3.2. Good stuff.

Labels: Programming

link posted by Kam-Hung Soh @ 11:47 AM   0 comments
Digg it!   Del.icio.us   Earthlink   Furl it!   Reddit!   Stumble It!   Slashdot It!   Yahoo MyWeb

Strange Syntax for Variable Definition

My quickie script to export Oracle tables defined the for command variable using two percent symbols, %%i, instead of the expected %i% in Windows command shell. Even more strange is that only one % is required when the for command is written interactively in the command shell.

Cmd Variables

To demonstrate and understand how variables are defined in cmd, try the following script in a batch file (i.e. save the commands in a file and run it) and interactively (i.e. type in each command). Note that homedrive is an pre-defined environment variable while i is not defined.

echo %homedrive
echo %i
echo %homedrive%
echo %i%
echo %%homedrive%
echo %%i%
echo %%homedrive%%
echo %%i%%

Here's the results of using a batch script:

> echo homedrive
homedrive
> echo i
i
> echo C:
C:
> echo
ECHO is on.
> echo %homedrive
%homedrive
> echo %i
%i
> echo %homedrive%
%homedrive%
> echo %i%
%i%

Here's what happens when you enter these commands one at a time:

> echo %homedrive
%homedrive
> echo %i
%i
> echo %homedrive%
C:
> echo %i%
%i%
> echo %%homedrive%
%C:
> echo %%i%
%%i%
> echo %%homedrive%%
%C:%
> echo %%i%%
%%i%%

When cmd reads a script in batch mode, it always consumes the leading % for each string as it looks for variables or has to escape a % (see %%i). If a variable is found, that string is always replaced, even if it is not defined (see echo %i%).

On the other hand, when cmd processes a command interactively, it only consumes the %'s in a string when that string is delimited by % (compare echo %homedrive and echo %homedrive%) and if that string maps to a variable name (compare echo %homedrive% and echo %i%). Also, % by itself is treated as a literal "%".

For Command

Back to the for command from the start of this article. In a batch script, a variable, %i, for the for command has to be entered as %%i so that cmd will replace %% with just %. In interactive mode, you only need %i because cmd does not regard this string as a variable. So here's the batch script version of a for command …

for %%i in (a, b, c) do echo %%i

… while here's the interactive version of a for command …

for %i in (a, b, c) do echo %i

The for command defines a variable only if a string starts with %. If you use %i%, then cmd replaces it with a value if i is defined (as expected) and you may encounter some other unexpected error depending on the value of i. However, if i is not defined, for command exits with this message:

%i% was unexpected at this time.

Another unexpected limitation of the for command is that a variable can be only one character long. If you try a variable name that is two or more characters long, you will get this …

for %xy in (a, b, c) do echo %xy
%xy was unexpected at this time.

Labels: Programming, Windows Cmd

link posted by Kam-Hung Soh @ 8:10 AM   0 comments
Digg it!   Del.icio.us   Earthlink   Furl it!   Reddit!   Stumble It!   Slashdot It!   Yahoo MyWeb

I wanted an Excel nested conditional worksheet statement to produce different output in a cell depending on whether another cell in the row had particular sub-string. For instance, if the source cell had "Problem in XYZ" or "Issue found in product ABC", then the dependent cell would show "XYZ" or "ABC". Then I can filter the rows in the worksheet based on the name of the product. Below is how I thought I could write the statement (formatted for readability):

=IF(FIND("XYZ",C1)>=0, "XYZ",
    IF(FIND("ABC",C1)>=0, "ABC", "Unknown")
    )

This statement worked for a source cell containing "XYZ" but would generate a "!Value" error otherwise. The find() function returns the position where it found a sub-string but would return an error if the sub-string could not be found. So I have to test the return value of the function instead of the position, resulting in a more cluttered statement:

=IF(ISNUMBER(FIND("XYZ",C1)), "XYZ",
    IF(ISNUMBER(FIND("ABC",C1)), "ABC", "Unknown")
    )

It's a rather annoying gotcha because returning an error is different from the VBA's InStr() function, which returns 0 when the sub-string can't be found.

Labels: Annoyances, Programming

link posted by Kam-Hung Soh @ 11:03 AM   0 comments
Digg it!   Del.icio.us   Earthlink   Furl it!   Reddit!   Stumble It!   Slashdot It!   Yahoo MyWeb

I was looking for a free application test program to regression test software. Our tester suggested Watir for testing Web application using MSIE. Watir is based on Ruby, which would be a fun language to learn in the future. For the present, there's a Java version called Watij.

To start Watij, just unpack the archive, start a Cmd console and enter launchWatijBeanShell.bat (simply clicking on launchWatijBeanShell.bat in Explore doesn't work). A Java BeanShell console opens where you can interactively enter Java statements to start MSIE and execute commands. Here's a sample session to open a Google search page and make a query:

IE ie = new IE();
ie.start("http://www.google.com");
ie.textField(name, "q").set("Hello World");
ie.button(name, "btnG").click();

How do we find the controls on a Web page (e.g. the name of the query field)? If you use Chris Pederick's Firefox WebDeveloper add-in, select Forms / Display Form Details feature to see the attributes of each form's element.

Having made a query, we should verify that we get the expected result. For a simple test, we want to know if Wikipedia can be found in the search results. Here, I combined a Java if-else statement with BeanShell's print():

if (ie.containsText("Wikipedia")) {
  print("Yes");
} else {
  print("No");
}
Yes

Watij doesn't have a way to record events like WinRunner. Scott Hanselman has written WatirMaker but it doesn't look like it's quite ready for general use.

Last minute: FireWatir for Firefox is also available.

Labels: Programming

link posted by Kam-Hung Soh @ 11:03 AM   0 comments
Digg it!   Del.icio.us   Earthlink   Furl it!   Reddit!   Stumble It!   Slashdot It!   Yahoo MyWeb

Here's a simple regular expression search and replace in Javascript (for JScript, replace alert with WScript.Echo). I wrote it as a test for another script to modify a configuration file.

var s1 = "Hello World"
var s2 = s1.replace(/Hello/, "Goodbye")
alert(s1 + "," + s2)
var s3 = s1.replace(/(Hello)/, "$1 Googly")
alert(s1 + "," + s3)

If you run this script in a Web page, you should see the first alert dialog containing "Hello World,Goodbye World" and a second alert dialog containing "Hello World,Hello Googly World".

In this example, the parentheses (()) enclose a match pattern and the dollar ($) symbol represents the matched string (i.e. $1 is the first matched string, $2 is the second matched string, etc.).

Labels: Javascript, Programming

link posted by Kam-Hung Soh @ 4:33 PM   0 comments
Digg it!   Del.icio.us   Earthlink   Furl it!   Reddit!   Stumble It!   Slashdot It!   Yahoo MyWeb

Here's a quickie JScript program to launch a set of applications in Windows. I wrote it because I didn't want to always start these applications when I logged in to my workstation (which is what happens to programs in the Windows Startup folder). Save the text in a *.js file in your desktop, edit the list of applications, then click on the icon to run it.

var apps = new Array()
apps[0] = '"C:\\Program Files\\Bozinis\\2xExplorer\\2xExplorer.exe"'
apps[1] = '"C:\\Program Files\\Mozilla Firefox\\firefox.exe"'
apps[2] = '"C:\\Program Files\\Microsoft Office\\Office\\EXCEL.EXE"'
apps[3] = '"C:\\Program Files\\Microsoft Office\\Office\\OUTLOOK.EXE"'

function runApps() {
  var wshell = WScript.CreateObject("WScript.shell")
  for (var i = 0; i < apps.length; ++i) {
    var appPath = apps[i]
    wshell.Run(appPath, 1, false) // Don't wait for command to finish
  }
  wshell = null
}

runApps()

JScript and Windows notes

If your application file paths have white spaces, you need to delimit them by double quotes so that the Windows shell can find them. The outer-most single quotes tell JScript the start and end of a string. Backslashes in Windows paths need to be escaped by another backslash.

Labels: Javascript, Programming

link posted by Kam-Hung Soh @ 10:30 AM   2 comments
Digg it!   Del.icio.us   Earthlink   Furl it!   Reddit!   Stumble It!   Slashdot It!   Yahoo MyWeb

Inspired by this Kuro5hin article, I hacked up a slightly nicer JScript implementation to read database tables and generate HTML tables in a browser using DOM functions such as insertRow() and insertCell().

Try it out

If you want to try it out, first create an Excel workbook called Test.xls with a page named DataTest. In DataTest, create one column with a heading StringValue and another column with a heading NumericValue. Enter some test data in those columns, then save the file.

Next, copy the HTML code at the end of this article, change the DSN path appropriately and save it in a file.

Finally, load the HTML file in IE6. When you select the Test button, the data in your workbook should be displayed in the browser.

Notes

If you start Excel and the user interface doesn't initialize properly, the cause is usually the Excel automation server not exiting. Just start Windows Task Manager, and kill the errant Excel.exe process in the Processes tab.

I chose Excel instead of Access as the database because it is easier to test simple programs without having to create a table design.

Sample JScript and HTML to read database

<html>
  <head>
    <title>DBTest for IE6</title>
    <script type="text/javascript">
      function readDb(tableId) {
        var conn = new ActiveXObject("ADODB.Connection")
        var dsn = "Provider=Microsoft.Jet.OLEDB.4.0;"
        dsn += "Data Source=C:\\CVS_STUFF\\HTML\\Database\\Test.xls;"
        dsn += 'Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"'
        conn.Open(dsn)

        var rs = new ActiveXObject("ADODB.Recordset")
        rs.Open("[DataTest$]", conn)

        var outputTable = document.getElementById(tableId)
        if (!outputTable) {
          alert("Cannot find " + tableId)
          return
        }

        if (!rs.bof) {
          var rowIndex = 1
          rs.MoveFirst()
          while (!rs.eof) {
            var row = outputTable.insertRow(rowIndex)

            var c1 = row.insertCell(0)
            var data1 = rs.Fields('StringValue').value
            var text1 = document.createTextNode(data1)
            c1.appendChild(text1)

            var c2 = row.insertCell(1)
            var data2 = rs.Fields('NumericValue').value
            var text2 = document.createTextNode(data2)
            c2.appendChild(text2)

            ++rowIndex
            rs.MoveNext()
          }
        }

        rs.Close()
        conn.Close()
      }

    </script>
  </head>
  <body>
    <h1>DBTest for IE6</h1>
    <form action="javascript:readDb('testOut')">
      <input type="submit" value="Test"/>
    </form>

    <table id="testOut">
      <tr><th>StringValue</th><th>NumericValue</th></tr>
    </table>

  </body>
</html>

Labels: Internet Explorer, Javascript, Programming

link posted by Kam-Hung Soh @ 1:52 PM   0 comments
Digg it!   Del.icio.us   Earthlink   Furl it!   Reddit!   Stumble It!   Slashdot It!   Yahoo MyWeb

import java.text.NumberFormat;

public class CurrencyFormatExample {
  public static void main(String[] argv) {
    NumberFormat cf = NumberFormat.getCurrencyInstance();
    double cost = 19.51;
    System.out.println("Cost = " + cf.format(cost));
  }
}

Cost = $19.51

import java.text.DateFormat;
import java.util.Date;

public class DateFormatExample {
  public static void main(String[] argv) {
    DateFormat dfShort = DateFormat.getDateInstance(DateFormat.SHORT);
    DateFormat dfMedium = DateFormat.getDateInstance(DateFormat.MEDIUM);
    DateFormat dfLong = DateFormat.getDateInstance(DateFormat.LONG);
    DateFormat dfFull = DateFormat.getDateInstance(DateFormat.FULL);
    Date today = new Date();
    System.out.println("Today is ...");
    System.out.println(dfShort.format(today));
    System.out.println(dfMedium.format(today));
    System.out.println(dfLong.format(today));
    System.out.println(dfFull.format(today));
  }
}

Today is ...
26/04/05
26/04/2005
26 April 2005
Tuesday, 26 April 2005

Labels: Java, Programming

link posted by Kam-Hung Soh @ 3:09 PM   0 comments
Digg it!   Del.icio.us   Earthlink   Furl it!   Reddit!   Stumble It!   Slashdot It!   Yahoo MyWeb