Sunday, January 30, 2011

Inserting Into Sql Server Identity and Default Columns

For some reason I always try to insert default or null into Sql Server identity or default columns. So that I remember here is how to do it when not explicitly listing the columns list.
  • When inserting into a table with an identity column, exclude the identity column from the values column list. e.g. Pretend the columns doesn't exist.
  • When inserting into a table with a default column, use the DEFAULT keyword.
     id INT IDENTITY(1,1),
     fname VARCHAR(30) not null, 
     lname VARCHAR(30) not null,

INSERT INTO @Test VALUES('Joe', 'Somebody', DEFAULT)
Which results in:
id   fname lname     ts   
---  ------ -------- -----------------------
1    Joe   Somebody  2011-01-30 12:37:20.353

(1 row(s) affected)

Saturday, January 22, 2011

AutoItX4Java - Java AutoIt Bridge

AutoIt is a very useful automation scripting language for Microsoft Windows. It allows for GUI automation using a very simple syntax and can be useful for testing applications. It is packaged with AutoItX which supports accessing AutoIt functions through COM objects.

I just created a new project on Google Code for Java AutoItX bindings. The project is called AutoItX4Java and uses the JACOB Java COM Bridge to access AutoItX functions. Currently the project is in an unstable state but hopefully as time goes on things will get better tested.

Using AutoItX4Java is simple:
  1. Download JACOB.
  2. Download and install AutoIt. (or register the dll. See below)
  3. Add jacob.jar and autoitx4java.jar to your library path.
  4. Place the jacob-1.15-M4-x64.dll file in your library path.

A simple example of using this library is below:
        File file = new File("lib", "jacob-1.15-M4-x64.dll"); //path to the jacob dll
        System.setProperty(LibraryLoader.JACOB_DLL_PATH, file.getAbsolutePath());

        AutoItX x = new AutoItX();
        String notepad = "Untitled - Notepad";
        String testString = "this is a text.";"notepad.exe");
        Assert.assertTrue(x.winExists(notepad, testString));
        x.winClose(notepad, testString);
        Assert.assertFalse(x.winExists(notepad, testString));
Troubleshooting: Be sure to use the correct version of the Jacob and AutoItX3 dlls. Both come in x86 and x64 versions.

If you need to test whether AutoItX is setup, just enter powershell and do the following:
$x = New-Object -comObject "AutoItX3.Control"
If that does not work ensure the AutoItX3.dll is loaded.
regsvr32.exe AutoItX3.dll
regsvr32.exe AutoItX3_x64.dll
You will also have to be in the x64 version of powershell for this test to work.

If your program never stops you can control how Jacob threading works as described here:
        AutoItX x = new AutoItX();"calc.exe");

Monday, January 3, 2011

Automated web testing with Java, Selenium RC, LoggingSelenium, HtmlUnit and TestNG

I used the Selenium web automation framework extensively for a suite of websites I had tested. Over the course of a year I had ended up with a complete automation framework for a suite of websites. This resulted in lots of project specific code. Now that I need to use Selenium for other sites I thought I would write about setting things up at a basic level which includes using HtmlUnit, Selenium, LoggingSelenium, starting the Selenium server through java and using TestNG.

Getting Setup
Download Selenium RC and Selenium IDE from Selenium IDE will install right into Firefox as an add-on. I am using Selenium RC 1.0.3.

Download LoggingSelenium from

Download TestNG from

Extract the files into your projects library folder and add the following 4 jars to your project:
  1. selenium-server.jar
  2. selenium-java-client-driver.jar
  3. logging-selenium-1.2.jar
  4. testng.jar
Configuring Selenium using a properties file
I use a properties file for all my Selenium settings. I like controlling things from a GUI interface and the properties file makes it easy to add a Swing dialog. Below is a simple class which facilitates this.

import java.util.Properties;

public class Config {

     * Singleton instance.
    private static Config instance;

     * Location of properties file.
    public static final String PROPERTIES_FILE = "config/";

     * Properties file var for selenium browser.
    public static final String SELENIUM_BROWSER = "selenium.browser";

     * Properties file var for selenium server port.
    public static final String SELENIUM_SERVER_PORT = "selenium.server.port";

     * Properties file var for location of the Firefox Profile
    public static final String SELENIUM_FIREFOX_PROFILE = "selenium.firefox.profile";

     * Properties file var for selenium server timeout setting.
    public static final String SELENIUM_SERVER_TIMEOUT_SEC = "selenium.server.timout_in_sec";

     * Properties file var for encoding
    public static final String ENCODING = "encoding";

     * Properties file var for the selenium server host.
    public static final String SELENIUM_SERVER_HOST = "";

     * Properties file var for the reports output path
    public static final String SELENIUM_REPORTS_PATH = "selenium.reports.output_path";
     * Properties file var for the Selenium base url.
    public static final String BASE_URL = "selenium.base_url";

     * Properties file var for thread count.
    public static final String THREAD_COUNT = "thread.count";

     * Properties file var for thread pool size.
    public static final String THREAD_POOL_SIZE = "thread.pool.size";

    private Properties properties;

     * Constructor
     * Loads the Properties file.
    private Config() {
        properties = new Properties();
        File file = new File(Config.PROPERTIES_FILE);
        try {
            properties.load(new FileInputStream(file));
        } catch (Exception ex) {
            System.err.println("Could not load properties file: " + file.getAbsolutePath());

     * Singleton access to this class
     * @return Config
    public synchronized static Config getInstance() {
        if (instance == null) {
            instance = new Config();
        return instance;

     * Proxy to properties getProperty
    public String getProperty(String key) {
        return properties.getProperty(key);

     * Proxy to properties getProperty
    public String getProperty(String key, String defaultValue) {
        return properties.getProperty(key, defaultValue);

     * Proxy to properties setProperty
    public void setProperty(String key, String value) {
        properties.setProperty(key, value);

Next create the Properties file.
selenium.browser=*firefox3 c:\\Program Files (x86)\\Mozilla Firefox\\firefox.exe
In the properties file I set the browser to Firefox. On Windows if I do this, Selenium complains that it can't find Firefox so I include the path afterwords.

There is also a setting for Firefox profile. If you are testing a site with a self signed certificate, you may get stuck accessing the website because Selenium launches an anonymous Firefox profile without your settings. When the web page loads, Selenium tests fail on the "This connection is Untrusted" page. If you create a profile specific to Selenium and create an exception for the self-signed certificate you can get past this untrusted connection warning:
  1. Launch Firefox from the command line with: firefox.exe -ProfileManager
  2. Create a new profile and specify the location of the profile. I named my profile Selenium and specified c:\selenium as the profile location for ease.
  3. Launch the profile, navigate to the page with the self signed cert. Click, "I understand the risks", "Add Exception" and add the page exception.
  4. Next go to c:\selenium and find cert.db and cert_override.txt
  5. I copy these files to a location in my project folder and specify that location in the properties file key: selenium.firefox.profile.

Creating the Selenium Server
You can start the Selenium Server from the command line by navigating to the selenium-server.jar and entering: java -jar selenium-server.jar using options specified on SeleniumHq however I prefer to save myself the hassle of having to start and stop the server every time I want to run tests and do this through code.
import org.openqa.selenium.server.RemoteControlConfiguration;
import org.openqa.selenium.server.SeleniumServer;

public class MySeleniumServer {

     * The Selenium server instance.
    private SeleniumServer seleniumServer;

     * Starts Selenium Server
    public void start() {

        Config config = Config.getInstance();

        RemoteControlConfiguration rcc = new RemoteControlConfiguration();
        String browser = config.getProperty(Config.SELENIUM_BROWSER, "*chrome");
        if (browser.contentEquals("*chrome") || browser.contentEquals("*firefox") ||
            browser.contentEquals("*firefox2") || browser.contentEquals("*firefox3")) {
            String path = config.getProperty(Config.SELENIUM_FIREFOX_PROFILE);
            rcc.setFirefoxProfileTemplate(new File(path).getAbsoluteFile());

        String sTimeout = config.getProperty(Config.SELENIUM_SERVER_TIMEOUT_SEC, "60");
        int timeout = Integer.parseInt(sTimeout);

        int port = Integer.parseInt(config.getProperty(Config.SELENIUM_SERVER_PORT, "4444"));


        try {
            seleniumServer = new SeleniumServer(false, rcc);
        } catch (Exception e) {
     * Stops Selenium Server
    public void stop() {
        if (seleniumServer != null) {

The above class initializes the Selenium server with the remote control configuration options from the properties file.

Creating a Selenium base test class
Now we can create a base test case class that initializes LoggingSelenium (Selenium with reporting capabilites).
import com.thoughtworks.selenium.HttpCommandProcessor;
import com.thoughtworks.selenium.SeleneseTestCase;
import com.unitedinternet.portal.selenium.utils.logging.HtmlResultFormatter;
import com.unitedinternet.portal.selenium.utils.logging.LoggingCommandProcessor;
import com.unitedinternet.portal.selenium.utils.logging.LoggingDefaultSelenium;
import com.unitedinternet.portal.selenium.utils.logging.LoggingResultsFormatter;
import com.unitedinternet.portal.selenium.utils.logging.LoggingUtils;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;

public class MySeleniumTestCase {

     * The results writer
    private BufferedWriter loggingWriter;

     * The Config instance
    private Config config = Config.getInstance();

     * The LoggingSelenium writer
    protected LoggingDefaultSelenium selenium;

     * Instance of SeleneseTestCase.
     * Did not subclass because may use JUnit4 or TestNG
    protected SeleneseTestCase selenese = new SeleneseTestCase();

     * Initializes Selenium
     * @param baseUrl The baseUrl of the website to test
     * @param browserString The browser to test with.
     * @throws Exception
    public void setUp(String baseUrl, String browserString) throws Exception {
        String encoding = config.getProperty(Config.ENCODING, "UTF8");
        String host = config.getProperty(Config.SELENIUM_SERVER_HOST, "localhost");
        String output = config.getProperty(Config.SELENIUM_REPORTS_PATH);
        int port = Integer.parseInt(config.getProperty(Config.SELENIUM_SERVER_PORT, "4444"));

        File logger = new File(output + File.separator +
                getResultsName(this.getClass().getName()) + ".html");
        loggingWriter = LoggingUtils.createWriter(logger.getAbsolutePath(), encoding, true);

        LoggingResultsFormatter htmlFormatter = new HtmlResultFormatter(loggingWriter, encoding);

        LoggingCommandProcessor htmlProcessor =
                new LoggingCommandProcessor(
                new HttpCommandProcessor(host, port, browserString, baseUrl), htmlFormatter);

        selenium = new LoggingDefaultSelenium(htmlProcessor);

    public void setUp(String url) throws Exception {
        String browser = config.getProperty(Config.SELENIUM_BROWSER, "*chrome");
        setUp(url, browser);

    public void setUp() throws Exception {

     * Takes a screenshot of the Desktop
    public void takeScreenshot() {
        selenium.captureScreenshot(config.getProperty(Config.SELENIUM_REPORTS_PATH) + File.separator +
                getResultsName(this.getClass().getName()) + ".png");

     * Tears down selenium and closes the logging file.
     * @throws Exception
    public void tearDown() throws Exception {
        try {
        } finally {
            if (loggingWriter != null) {

    * A datetime to use as a unique filename.
    * Filenames are unique per second. 
    * @param filename The output file name.
    * @return The unique filename. Exmample: 2011-01-03-19-20-11-filename
    public String getResultsName(String filename) {
        String name = filename.substring(filename.lastIndexOf(".") + 1, filename.length());
        Calendar cal = new GregorianCalendar();
        cal.setTime(new Date());
        DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd-HH-mm-ss");
        String date = dateFormat.format(cal.getTime());
        return date + "-" + name;


The bulk of the above class is the setUp method which pulls values from the properties file in order to configure an instance of LoggingSelenium. Notice how the class has a protected member: selenese which is an instance of SeleneseTestCase. This is a class that subclasses JUnit 3. Since I may want to use JUnit 4 I did not subclass SeleneseTestCase. SeleneseTestCase contains many useful testing helper functions including "verify" functions. Instead of AssertTrue, or AssertEquals, SeleneseTestCase has verifyTrue or VerifyEquals. This allows tests to continue even if they fail. When tests fail, SeleneseTestCase adds the failure exception messages to an array and then throws an Exception only when SeleneseTestCase.tearDown() or SeleneseTestCase.checkForVerificationErrors() is called. Therefore JUnit won't catch the test failure until one of these methods is called. Be sure to pay particular attention that one of these methods is called. (tearDown calls checkForValidationErrors).

Creating Tests
Finally, we can create our tests.
import com.gargoylesoftware.htmlunit.IncorrectnessListener;
import com.gargoylesoftware.htmlunit.NicelyResynchronizingAjaxController;
import com.gargoylesoftware.htmlunit.SilentCssErrorHandler;
import com.gargoylesoftware.htmlunit.WebClient;
import java.lang.annotation.Annotation;
import java.lang.reflect.Constructor;
import java.lang.reflect.Method;
import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.htmlunit.HtmlUnitDriver;
import org.testng.Assert;
import org.testng.IAnnotationTransformer;
import org.testng.TestNG;
import org.testng.annotations.AfterGroups;
import org.testng.annotations.BeforeGroups;
import org.testng.annotations.ITestAnnotation;
import org.testng.annotations.Test;

public class SampleSeleniumTest extends SeleniumTestCase {

    protected MySeleniumServer server;

    @BeforeGroups(groups = "Selenium")
    public void startServer() {
        server = new MySeleniumServer();

    @AfterGroups(groups = "Selenium")
    public void stopServer() {

    @Test(groups = "Selenium")
    public void googleSearch() throws Exception {
        selenium.type("q", "test");

    @Test(groups = {"HtmlUnit", "Concurrent"})
    public void webUnit() throws Exception {
        WebDriver driver = new HtmlUnitDriver(true) {

            protected WebClient modifyWebClient(WebClient client) {
                client.setCssErrorHandler(new SilentCssErrorHandler());
                client.setAjaxController(new NicelyResynchronizingAjaxController());
                client.setIncorrectnessListener(new IncorrectnessListener() {

                    public void notify(String arg0, Object arg1) {
                        //ignore messages
                return client;
        WebElement element = driver.findElement("q"));
        Assert.assertEquals("Test - Google Search", driver.getTitle());

    public static void main(String[] args) {
        TestNG ng = new TestNG();
        Config config = Config.getInstance();
        int threadPoolSize = Integer.parseInt(config.getProperty(Config.THREAD_POOL_SIZE, "1"));
        int threadCount =Integer.parseInt(config.getProperty(Config.THREAD_COUNT, "1"));
        ConcurrentConfig concurrentConfig = new ConcurrentConfig();
        ng.setTestClasses(new Class[]{SampleSeleniumTest.class});
        ng.setGroups("Selenium, HtmlUnit");;

class ConcurrentConfig implements IAnnotationTransformer {

    private int invocationCount = 1;
    private int threadPoolSize = 1;

    public void setInvocationCount(int invocationCount) {
        this.invocationCount = invocationCount;

    public void setThreadPoolSize(int threadPoolSize) {
        this.threadPoolSize = threadPoolSize;

    public void transform(ITestAnnotation annotation, Class arg1, Constructor arg2, Method testMethod) {
        Annotation[] methodAnnotations = testMethod.getAnnotations();

        for (Annotation methodAnnotation : methodAnnotations) {

            if (methodAnnotation instanceof Test) {
                Test test = (Test) methodAnnotation;

                for (String group : test.groups()) {

                    if (group.contentEquals("Concurrent")) {
The above example runs two very simple tests. One uses Selenium and the other uses HtmlUnit.

The Selenium test will launch the browser, execute the tests and output an html report with screenshots of the test.

The HtmlUnit test will launch an embedded java browser and run a test without launching a heavy weight browser. I often times fall back on HtmlUnit for stress testing. For example, I can set the TestNG annotations for thread pool and thread count to 50 and simulate 50 users accessing a web site at the same time.

I included the ConcurrentConfig class in the above code snipped just because it is boilerplate code that is useful in order to control the thread pool and count from a properties file. Aside from that the above class is mainly setup the way it is for demonstration purposes.