Saturday, October 25, 2008

Booleans in Hibernate

As the following helpful article ( points out:

"There's a problem persisting boolean fields using Hibernate 3.2.2 to MySQL 5.0, if you allow Hibernate to generate your schema and you leave Hibernate to generate the schema in the default way". The problem is, by default, it generates a MqSQL field of type "bit(1)" - which causes Hibernate to throw:

could not insert: [] org.hibernate.exception.DataException: could not insert: [] at org.hibernate.exception.SQLStateConverter.convert( at org.hibernate.exception.JDBCExceptionHelper.convert( .... Caused by: java.sql.SQLException: Data too long for column 'my_field' at row 1 at ....

When I try to save.

That's causing my tests to fail… so what to do?

Well the article I mention above suggests mapping the field using

<column sql-type="BOOLEAN" />,

which does work.

However, I'm using annotations, so my preferred mapping for booleans is:

@Column(columnDefinition = "tinyint", nullable = false)
private boolean exampleBooleanValue;

An alternative is to use:

private Boolean exampleBooleanValue;

But I prefer to work with primitives rather than boxed primitives wherever possible.
Anyway, the first solution above works wonderfully.

Log4J Warnings

Annoyingly, when I run a junit test that is testing my Hibernate DAO objects, I get the following warnings:

log4j:WARN No appenders could be found for logger (jpt.dao.impl.test.GenericDaoImplTest). log4j:WARN Please initialize the log4j system properly.

This shouldn't be surprising, it's saying that Log4j hasn't been initialized properly. Why not? Well, It can't find my file. That's because hibernate needs it to be in the class path. So, I need to put into the classpath.

Well that's fine, and doing so does fix the problem - I just put into my src folder (or, in this case, specifically my test-src folder).

However, the problem is that I already have in the WebRoot/WEB-INF folder. When running my web-app (not the tests, but the web app itself), hibernate knows to look for it there because web.xml has:


What's more, that's exactly where I want my to be. Sure, I could change this to point somewhere else, but I don't want to do that. I want to be located in this directory for my web-app and configured just so.

So… the question is, how do I get Junit to initialize using this, rather than looking into src (on the classpath) and finding it missing. Well, I haven't been able to do it yet. I can manually configure log4j in my test class - but that's after the warnings are already shown. The only way I've been able to avoid those warnings is by placing into the classpath.
So, in the end, I've now got two versions of One is in test-src and defines the logging levels etc for the debug running. One is in WebRoot/WEB-INF and defines logging levels for the webapp.

This is fine - and some might even say the best of both worlds - because I can have different default logging for my tests and my web-app. However, I still don't really like it. It could be confusing having two files named log4j.properites in the one project. What if I want to have only one!

Surely it shouldn't be THAT hard to configure my Junit test runs (that extend AbstractTransactionalDataSourceSprignContextTests) so that they search for the in the web-inf directory rather than on the classpath?!?!?!? Any ideas? (By the way, trying to add web-inf to the classpath doesn't work, as I'm using MyEclipse and it doesn't allow this!)

Hibernate "PreInsertEvent.getSource()" NoSuchMethodError.

I recently obtained the following error:

java.lang.NoSuchMethodError: org.hibernate.event.PreInsertEvent.getSource()Lorg/hibernate/engine/SessionImplementor;

This occurred when attempting to run a test on one of my Hibernate DAO entities.

The problem started when I updated some (but not all) of my hibernate .jar libraries, to enable the use of spring-test.jar to provide access to the helper org.springframework.test.AbstractTransactionalDataSourceSpringContextTests methods.

It turns out ( that the problem was:

Method getSource from an older version of the Hibernate Core is referenced.

It is stated within the compatibility matrix that Hibernate Validator 3.1.0.CR1 is compatible only with Hibernate Core 3.3.x version(s).Exception in thread "main" java.lang.NoSuchMethodError: org.hibernate.event.PreInsertEvent.getSource()Lorg/hibernate/engine/SessionImplementor;

That is, I was using the latest Hibernate Core (3.3.x), but didn't have the most up to date Hibernate Validator. I downloaded a new hibernate-validator.jar and everything worked again.

Problems with @Test annotations with AbstractTransactionalDataSourceSpringContextTests.

I've recently been writing tests, most of which use the Junit @Test ( annotations to identify the methods for testing as junit tests. This works really well, and also allows me to use other great annotations like @Before.

However I decided I needed to do some integration testing of my DAOs with the database. So, to do this, I took advantage of Spring's AbstractTransactionalDataSourceSpringContextTests class (

This is great, however, I noticed that all of my test annotations for classes extended AbstractTransactionalDataSourceSpringContextTests were being ignored. This means that tests that were named "testExample" would run, but those named "doTestExample" would not - even if annotated. More to the point, something called "testExample" would run as a test even if not annotated.

The reason soon became clear. AbstractTransactionalDataSourceSpringContextTests extends the Junit.Framework.TestCase class. Now JUnit4 and JUnit3 tests really should not be mixed and they don't go together. So, because I'm extending TestCase (although I don't really want to), I need to go back to old fashioned Junit Tests, without test annotations. This is commented, briefly in:

Tuesday, September 16, 2008

MySQL Database Recovery

I recently had a key application server die (hardware). One of the key applications running on this machine was a web-application that used a MySql database. We did have some specific MySql backup files, but the most recent backup of the data was a complete server backup of all files.

This meant that I had to work out how to restore a MySql database from the files stored in the data folder (under MySQL Server5.0/data/)

The files I had were:

and a folder, my_application, which contained a whole bunch of .frm files describing the table structures.

To restore MySql I started by copying these three files and one folder into a newly installed MySql folder on a replacement server. However, on starting mySql as a service, I got:

Could not start the MySQL service on Local Computer.
Error 1067: The process terminated unexpectedly.

I then checked out more information about this error in the file named machineName.err in the MySql/data folder.

This showed that:

InnoDB: Error: log file .\ib_logfile0 is of different size 0 25165824 bytes
InnoDB: than specified in the .cnf file 0 1782 57920 bytes.
080917 12:30:17 [ERROR] Default storage engine (InnoDB) is not available
080917 12:30:17 [ERROR] Aborting.

In this case, I went to my.ini file and changed:




I was then able to start the MySql Server and create a normal backup for later retrieval on other servers.

Sunday, September 14, 2008

Tomcat - Error starting.

I recently have been installing Tomcat 6 on a new server.

After installing a fresh JRE and then apache-tomcat-6.0.18.exe I got the error:

Windows could not start the Apache Tomcat on Local Computer. For more information, review the System Event Log. If this is a non-Microsoft service, contact the service vendor, and refer to service-specific error code 0.

To fix this I copied the file msvcr71.dll from my Java/bin directory into the Apache-Tomcat/bin directory.

It worked... though why it didn't work "out of the box" without such "black magic" I really don't know!!!!. I spent ages trying a range of different Tomcat installations, JAVA versions and classpath settings.

Aaaargh! Those were precious hours of my life I'm never going to get back...

Wednesday, September 10, 2008

Upgrading Hibernate Annotations from V3.2 to V3.4.0

I recently blooged about a Hibernate Annotations bug that had been fixed in Hibernate Annotations 3.4.0, but not in 3.2.

While upgrading fixed this problem, I did have some problems with changing to Hibernate Annotations 3.4.

I tried downloading hibernate-annotations-3.4.0.GA.tar.gz and just swapping over the hibernate-annotations.jar file in my project. Seemed like a simple thing to do, however, using the new hibernate-annotations.jar file caused a new error:

Caused by: java.lang.NoClassDefFoundError: org/hibernate/annotations/common/reflection/ReflectionManager

This problem is discussed at this post. It turns out that, in upgrading, I need also to include hibernate-commons-annotations.jar.

Then I got another problem that is due to the lack of the slf4j-api.jar:

Caused by: java.lang.NoClassDefFoundError: org/slf4j/LoggerFactory

After that, I decided I'd better trust the README.txt file that comes with hibernate-annotations-3.4.0 and pulled across a bunch of (I think) required libraries:


which fixed one problem... but led to another new error:

Caused by: java.lang.NoSuchFieldError: TRACE

The problem here seems to be that I need log4j.jar to be of version 1.2.12 or higher. (I've got 1.2.11 - missed it by that much!)

Having upgraded to log4j-1.2.15 that fixed that problem - but, a new one was waiting for me:

Caused by: java.lang.NoClassDefFoundError: org/apache/commons/collections/map/LRUMapat org.hibernate.util.SimpleMRUCache.init(

I realized (following a bit of reading I probably should have done right at the start!) that I needed to upgrade not just the hibernate-annotations, but also the hibernate core packages themselves to be compatible with the new annotations.

I downloaded the hibernate-distribution-3.3.0.SP1-dist.tar.gz file (something I probably should have done right from the start).

With Hibernate and Hibernate Annotations both updated, everything worked just fine.

The Moral of the Story: Check the dependencies when you upgrade one part of a system and make sure you read the documentation!

Now... I won't make that mistake again (not a guarantee!)

@Transient Annotation and Generics in Hibernate Superclasses

I'm working with Hibernate Annotations, and have a transient field in one of my entity superclasses that is mapped as @MappedSuperclass. That is:

public abstract class MyEntity<T extends MyEntity<T>> implements Serializable {
protected Class<T> domainClass = getDomainClass();

This is throwing the following error:

org.hibernate.AnnotationException: Property my.entity.MyClass.domainClass has an unbound type and no explicit target entity. Resolve this Generic usage issue or set an explicit target attribute (eg @OneToMany(target=) or use an explicit @Type

I found the following Hibernate bug report that seems to refer to the same problem, and indicated it is a bug in Hibernate:

This bug was recently fixed. Seeing as I've been using Hibernate 3.3, I decided to upgrade to Hibernate Annotations 3.4 to fix this.

It worked! Just thought I'd mention this as a fix to that problem, in case others have it.

PS: Sounds easy, huh ?- but I did have some issues with changing to Hibernate Annotations 3.4 which you can read about here.

Tuesday, September 9, 2008

@Entity annotations

I've just been scratching my head, attempting to figure out why some of my Java classes, marked with the @Entity annotation, weren't being generated (by Hibernate) as tables in my database.

They were correctly included in my hibernate.cfg.xml file with mappings:

<mapping class="mypackage.entity.myclass" />

However, while most of my classes were being correctly generated, some were not.

It turns out that in the classes which were failing, I'd accidently imported org.hibernate.annotations.Entity rather than the correct class (javax.persistence.Entity)

I guess this is a pretty easy trap to fall into - especially if you're using nice editor features (like the myEclipse "organize imports" plugin) and not paying sufficient attention (I did it once in a template and then copied this to a bunch of other locations!). Once done it can be kinda hard to see what isn't right .. Of course, the documentation is clear when it states:

"Note: @javax.persistence.Entity is still mandatory, @org.hibernate.annotations.Entity is not a replacement. "


Monday, September 8, 2008

Hibernate and SQL keyword Column Names

I have recently experienced some problems with Hibernate and the generation of SQL Tables. I am using Hibernate Annotations, and some of my entities have annotations that look something like:

@Column(nullable = false)
String myProperty;

@GeneratedValue(strategy = GenerationType.AUTO)
Date timestamp;

boolean insert;

boolean update;

boolean delete;

The problem is that Hibernate is throwing the following errors:

DEBUG [org.hibernate.tool.hbm2ddl.SchemaUpdate] - <create table MyTable (id bigint not null auto_increment, modifyingUsername varchar(255) not null, timestamp datetime, insert bit, update bit, delete bit, primary key (id))>

ERROR [org.hibernate.tool.hbm2ddl.SchemaUpdate] - <Unsuccessful: create table TextMetaDataHistory (id bigint not null auto_increment, modifyingUsername varchar(255) not null, timestamp datetime, insert bit, update bit, delete bit,primary key (id))>

ERROR [org.hibernate.tool.hbm2ddl.SchemaUpdate] - <Syntax error or access violation message from server: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'insert bit, update bit, delete bit' at line 1">

Upon further investigation, it seems the problem is with the SQL that Hibernate is generating. Unfortunately some of my Java variable names (such as "timestamp", "insert", "update" and "delete") are themselves SQL keywords.

The Debug information above shows that the SQL being generated is not correct SQL syntax. In particular, if you type in MySQL:

create table MyTable (id bigint not null auto_increment, modifyingUsername varchar(255) not null, timestamp datetime, insert bit, update bit, delete bit, primary key (id))

you'll get an error.

If you manually edit this so it, instead, reads:

create table MyTable (id bigint not null auto_increment, modifyingUsername varchar(255) not null, `timestamp` datetime, `insert` bit, `update` bit, `delete` bit, primary key (id))

the SQL will run beautifully. All I've done here is insert quotes (the "`" quote, which is found on a standard keyboard above Tab, and using Shift on this key produces "~").

All I need to do now is work out how to get Hibernate to generate SQL with this included!!!
More news soon... (I hope!)

Update: Ok, so I decided, in the end, to fix this just by manually changing my Hibernate annotations to give an explicit name for each "questionable" column that includes the quotes. Thus the modified annotations are now:

@GeneratedValue(strategy = GenerationType.AUTO)
Date timestamp;

boolean insert;

boolean update;

boolean delete;

NB: The "`" quote I'm using is the one above on a US keyboard, sharing the key with "~". Other quotation marks didn't work for me.

This now works - but it does seem unusual that I have to do this. I'm surprised Hibernate doesn't do this automatically for all Column names to avoid such "keyword" problems. Having said that, some "newbie" Hibernate users who have experienced similar problems, have suggested modifying Hibernate to do this, and the response from more experienced Hibernate programmers in at least one case was "Read the manual - stop wasting time" or something to that effect. Makes me wonder if I'm missing something in proposing this solution?

If you know of a better way to fix this problem, please post a comment on this blog! In the meantime, using the @Column(name="`whatever`") annotation is fixing my immediate problem, and doesn't seem to have any bad side effects, so I'll just stick with it!

PS: By the way, to get Hibernate to log the SQL it was using, I set:

in the file I'm using..

Incorrect Version of ehcache

Within a web application I'm currently working on, I've just added the Hibernate entity mappings into the hibernate.cfg.xml file. These look something like:

<mapping class="project.entity.MyClass" />

and follow a cache configuration line that says:

<property name="cache.use_second_level_cache">true</property>
<property name="hibernate.cache.provider_class">


However, when I launch Tomcat, I get the following errors:

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'sessionFactory' defined in ServletContext resource [/WEB-INF/bean-defs-6-session.xml]: Invocation of init method failed; nested exception is java.lang.NoSuchMethodError: net.sf.ehcache.CacheManager.getEhcache(Ljava/lang/String;)Lnet/sf/ehcache/Ehcache;
Caused by:
java.lang.NoSuchMethodError: net.sf.ehcache.CacheManager.getEhcache(Ljava/lang/String;)Lnet/sf/ehcache/Ehcache;
at net.sf.ehcache.hibernate.SingletonEhCacheProvider.buildCache(
at ...

It turns out that the application is using an old version of ehcache.jar. Further investigation revealed that the reason for this is that the MyEclipse "Hibernate 3.1 Core Libraries" includes a copy of "ehcache-1.1.jar". Even though I had "ehcache-1.3.0.jar" included as a project library (and even changed its order above the Hibernate Core Libraries in the "Order and Export" tab), the application was still defaulting to the ehcache-1.1.jar version.

To fix this, I removed the Hibernate 3.1 Core Libraries from the project libraries. (Subsequently you can add it back in, if you want, as it will now be below the ehcache-1.3.0.jar so the correct version of ehcache will now be used, if you need other bits from the Hibernate 3.1 Core libraries).

Sunday, September 7, 2008

Defining DAO Objects

A little while back in an earlier blog post I mentioned a web application I've been developing which uses Hibernate Annotations and Spring MVC.

As mentioned then, the framework I'm using is based on one described in this tutorial.

In the section of this tutorial that discusses defining the DAOs (Database Access Objects) it suggests creating your DAOs using code that looks like:

<bean id="daoTmpl">
<property name="sessionFactory">
<ref bean="sessionFactory"/>

<bean id="myObjectDao" class="org.annotationmvc.dao.MyObjectDaoTmpl" parent="daoTmpl" />

A similar pattern is repeated in a number of other locations on the internet, suggesting using a daoTmpl bean so you don't have to include the sessionFactory object individually in each one of your DAOs.

This is great advice, but in every case you get an error something like:

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'daoTmpl' defined in ServletContext resource [/WEB-INF/bean-defs-5-dao.xml]: Instantiation of bean failed; nested exception is java.lang.IllegalStateException: No bean class specified on bean definition
Caused by:
java.lang.IllegalStateException: No bean class specified on bean definition

The problem is that you can only define a bean without a class if you declare it as abstract. To do this, change the bean definition to:

<bean id="daoTmpl" abstract="true" >
<property name="sessionFactory">
<ref bean="sessionFactory"/>

and it will work as expected.

Tuesday, August 26, 2008

Hibernate Inheritance Mapping Strategies

If you've played with Hibernate for a while, one thing you'll really appreciate is how it helps you avoid the "object/relational mapping" paradigm mismatch.

Now, this is great, because it frees you up to focus on getting your object hierarchy / object models right, without (too much) focus on the underlying persistence layer. However, this doesn't come completely for free.

Inheritence is one area where the mistmatch between object-oriented and relational world becomes particularly visible. What this means is that you have to undertake some thought when it comes to mapping classes to tables, in the case where the classes exhibit inheritence.

A number of references (for instances, I read about this in Chapter 3 of the excellent book "Hibernate In Action") talk about the "three different approaches to representing an inheritance hierarchy", as classified in a paper "Mapping Ojects to Relational Databases" by Scott Ambler in 2002. These are:
  • Table per concrete Class ("Table per class") - One table is used for each (non-abstract) class.
  • Table per class hierarchy ("Single table per class hierarchy") - One table is used to map an entire class hierarchy.
  • Table per subclass ("Joined subclasses")- One table is used for every subclass (including abstract classes and interfaces). These only contain columns for the non-inherited properties.
Each of these is mapped (using Hibernate annotations) using the @Inheritance annotations. That is, @Inheritance(strategy=InheritanceType.TABLE_PER_CLASS), @Inheritance(strategy=InheritanceType.SINGLE_TABLE) and @Inheritance(strategy=InheritanceType.JOINED) respectively.

However, recently I found about another way of handling inheritance in my object model being mapped to the persistence layer. It is useful for the case where you have a superclass that does not need to have any corresponding tables in the underlying database, and does not need to be queryable. In this case, a superclass can be annotated with the @MappedSuperclass annotation.

I thought I should mention this, as most of the information about the three strategies for inheritance doesn't seem to mention this as a "fourth" option - probably because in this case the superclass is not actually an entity.

In fact, I had a bit of difficulty understanding the difference between @MappedSuperclass and the @Inheritance(strategy=InheritanceType.TABLE_PER_CLASS) strategy. They are, in fact, very similar.

@MappedSuperclass reference states:

"A mapped superclass designates a class whose mapping information is applied to the entities that inherit from it. A mapped superclass has no separate table defined for it."

So, the MappedSuperclass annotation is similar to "table per class" inheritance, but (the difference is) it does not allow querying, persisting or relationships to the superclass. Table per class does allow these (but, if I understand correctly, is fairly inefficient at these compared to the other two inheritance strategies.)

  • Mapped superclasses can't be targets of entity relationships
  • They can be either abstract or concrete. (Some references suggest that a mapped superclass normally should be an abstract class).
  • A mapped superclass is not an Entity

More information on these can be found at

The advantage of using MappedSperclass annotation for me, at this time, is that it seems the simplest approach if I don't want the superclass to be queried or relationships going directly to it. For the top level of my hierarchy this seems to be a good way to go.

Tuesday, August 19, 2008

Changing Web Context-root in MyEclipse

As you would expect, when you create a web application in myEclipse, by default the web context-root is the name of your web application.
(The web context-root defines the URL you'll use to access your web-app).

To change the Web Context-root, right-click on the project and select properties. Then find the MyEclipse->Web tag. Now you can change this property!

You'll then need to re-deploy!

Easy, I know - but it took me a while to find, so why not record it here for posterity...

Problems with the webapp.root system property when using Tomcat & Log4J.

java.lang.IllegalStateException: Web app root system property already set to different value:

I've been developing a web application that uses Log4J for logging. Recently I started a new web app, using essentially the same framework.
(By the way, the framework - which uses Hibernate Annotations and Spring MVC - is based on the one descrbed in this tutorial)

Anyway, in creating this new web app and starting Tomcat, I immediately encountered the following error:

SEVERE: Exception sending context initialized event to listener instance of class org.springframework.web.util.Log4jConfigListener
java.lang.IllegalStateException: Web app root system property already set to different value: 'webapp.root' = [MyPath\EclipseProjects\.metadata\.plugins\com.genuitec.eclipse.easie.tomcat.myeclipse\tomcat\webapps\app1Name\] instead of [MyPath\EclipseProjects\.metadata\.plugins\com.genuitec.eclipse.easie.tomcat.myeclipse\tomcat\webapps\app2Name\] - Choose unique values for the 'webAppRootKey' context-param in your web.xml files!
at org.springframework.web.util.WebUtils.setWebAppRootSystemProperty(
at org.springframework.web.util.Log4jWebConfigurer.initLogging(

Ok - so why is this??? Well, I'm glad you asked...

First things first, I should note that the L4JWebConfigurer class is:

"A convenience class that performs custom Log4J initialization for web environments, support 2 init parameters..." that are set as context-param in web.xml.


However, this convenience class comes with a warning:

"WARNING: Some containers (like Tomcat) do not keep system properties separate per web app. You have to use unique "webAppRootKey" context-params per web app then, to avoid clashes. Other containers like Resin do isolate each web app's system properties: Here you can use the default key (i.e. no "webAppRootKey" context-param at all) without worrying. "

So... given I'm using Tomcat, what does this mean?

It turns out that the webAppRootKey is a Spring context parameter. It exposes the web application's root directory as a system property. So, it is the value of the webAppRootKey that provides the name for the system property to use. That's fine if you've just got one webapp in Tomcat. Spring will just choose a default value for this (app.root) and off you go. However, as the warning above notes, Tomcat does not keep system properties separate per webApp. This means that when we get to the second application, the same system property (app.root) is being set to a new value (our second application's root directory, rather than the first!)

The reason this is occurring at all is that Log4JConfigListener is trying to set the webAppRootKey in both cases. This allows log4J to place log and config files in directories relative to the application root directory, rather than in a fixed path.

Anyway, the real question is how to fix this. One option would be to remove the Log4JConfigListener, or re-configure Log4J so that this doesn't happen - that is by setting 'log4JExposeWebAppRoot' to false. (This is a servlet context parameter that eliminates the use of log file locations relative to the web application's root directory.)

However, in my case, I decided the easiest thing was just to use a non-default name for the webAppRootKey for each of my applications.

That is, I fixed this by placing the following context-parameter into each of the web application's web.xml files:


This fixed the problem, and everything's working again... Hoorah!

Sunday, August 17, 2008

AVG Free Edition - .bin file is missing

If you're using AVG Anti-Virus 8 (Free edition), you may have just got a message saying that the update failed and a .bin file is missing.

It turns out this is because the update that is trying to occur is, errr, missing. Don't worry, though, it's not your fault. It's a problem with AVG. There are lots of people finding that problem today (16th/17th August 2008), and lots of people wasting time re-installing AVG with no improvement. My advice is wait a 24 hours and see if AVG fix it.

If you're in a real hurry, I can tell you which of the updates is missing and you can then do the other updates manually. To do this:
(1) Start AVG
(2) Click on Update Now.
(3) When the updates are shown in the popup window, click on the link to tell you more.
(4) Save the updates to your computer to do a manual update later. The one you want to avoid is (currently) the last one: - but it isn't there!!!.
(5) use "Tools->Update From Directory" and browse to the other updates to do them manually.

Of course... you could just wait for AVG to fix this. Can't be long now, surely :-)

Monday, August 11, 2008


I just got the following error when trying to start Tomcat for an Eclipse project:

java.lang.UnsupportedClassVersionError: Bad version number in .class file

It turns out my Tomcat installation seemed to be using an older version of Java to run. To fix this I went into Eclipse Windows->Preferences->Java and made sure that both the "Installed JRE" and "Compiler" tags had the same Java version (Java 6).

I also checked that in the Project preferences under "MyEclipse->Application Servers->Tomcat->MyEclipseTomcat->JDK" that I had the correct JDK selected (6.0.03, in my case).

I also deleted the Java 5 JDK (even though it wasn't selected) and (for good measure) deleted all my .class files... but this was probably a bit of overkill.

Following a complete rebuild it all worked again... Phew!

Tuesday, July 15, 2008

VBScript and Custom Forms in Outlook

1. Background:

I've recently been given a task to develop some custom task forms in Microsoft Outlook. These forms needed to have some additional fields (specialized text entries with information on the "last action taken", "outstanding actions" and "comments" in addition to standard task fields and categories. More challengingly, they also needed to have some other behaviours such as "automatically emailing people of changes" when they're updated.

If you've ever worked with Microsoft Outlook, you'll know that doing this from scratch isn't as easy as you otherwise might expect. It sounded like the obvious thing to build on Outlook "Tasks" to just enable this extra functionality, but it turns out Outlook can be very restrictive when you get to something it isn't used to doing.

I've done a reasonable amount of VBA coding in Excel and Powerpoint before, and was expecting this to be relatively similar (and straightforward). As it turns out, there were a number of new things to learn before getting this new custom form to work.

This post outlines some of the things I had to work out in order to get this particular script to work. Not all of these "lessons" are profound - some were very quick to find solutions to. Others posed a more significant problem.

2. Outlook Forms - Basics
(or how to start if you've never worked with Outlook forms before)

The actual starting process of creating a new form for entering task information is actually very easy. Just go to Tools->Forms->Design A Form and choose a "base form" from the "Standard Forms Library". In my case, "Task". This will give you a standard-looking "Task" form that you can start modifying in a fairly typical way. However, let me at least note some aspects that surprised me at first:

  • You can't actually modify the first Tab (Task) tab on the form. It is fixed. You can't add new buttons or features.
  • It is probably not very profitable to try to "get around" this limitation by hiding this first tab (which you can do) and putting all of these features onto a user-modifyable tab. The main "tasks" tab is actually different from the user-definable ones, and if you do attempt to put all of the tasks features on your own user-tab, you will find you've lost some pretty important abilities (like having a "calendar" pop-up allowing users to choose dates/times for these fields.) The overall look and feel also looks different and not as "professional". The issue with Calendar etc can be overcome, but it is going to take some effort!
  • Don't get confused by the difference between an outlook Form created in this manner and the Forms you get in the Visual Basic Editor (Tools->Macros->Visual Basic Editor). If you create a Form in the Visual Basic Editor it is a nice, plain, blank form with no specific limitations, but it isn't going to be very useful for most Outlook things you want to do - like generating an Outlook Task, Email or Calendar entry.

Noting this distinction between going into Visual Basic Editor, and the process for creating the form, let us pause for a moment to discuss VBA and Outlook (especially for those people, who like me, might assume it will be similar to Excel, Word or Powerpoint in the Office suite)

3. Aside - Visual Basic For Applications and Outlook (If you're used to Excel/Word/Powerpoint):

When you are using Outlook, you might notice that (like other Office applications) there is the usual Tools->Macros->Visual Basic Editor menu options. You might expect that, like with other Office applications you will be able to record VBA macros and then modify them to work with Outlook. However, you'll note that Outlook does NOT have a Macro recorder.

You have to write your macros from scratch. This is because (see "Outloook does not support a full object model, such as those in Word or Microsoft Excel", so not everything you may want to do may be possible in VBA within Outlook.

The key to using VBA with outlook is understanding this Outlook Object model, which defines what objects, properties, methods and events are available.

Outlook Object Model:

4. Outlook Scripting - VBA and VBScript:

Having discussed VBA briefly, above, let's now return to the task at hand - namely creating a custom task form. In this particular case, we'll find that VBA Macros actually have very limited utility, in any case.

To quote

"Visual Basic for Applications (VBA) is actually only one of the two programming languages available for writing code in Outlook. The other is VBScript. VBScript is used by Microsoft outlook custom forms."

So, if we're going to have our custom task form work for us, we're going to be working in the special Outlook forms (not a VBA form), with VBScript (not VBA) doing the scripting work behind it.

Luckily, however, VBScript is very similar to VBA as a language, and isn't very hard at all to pick up (even if you aren't familiar with VBA). The following references may help, however:

5. Developing the custom task form

In (2), above, we discussed how to open the standard outlook task form and some limitations on modifying these. Useful things you'll need when creating the custom form include:

  • Control Toolbox - A list of controls (like "listbox", "radiobutton", "textbox") you may like to put on your form.
  • Field Chooser - A list of "fields" (such as "notes", "owner", "Bcc", "Complete", "Actual Work" etc) that individual items have and you may like to display.

These two items can both be obtained by right-clicking on the form and checking the "Control Toolbox" or "Field Chooser" item, respectively, in the menu.

Most basic functionality is very simple to obtain. For instance to create a new text field that is linked to a user-defined field either drag that field onto the form-palete, or drag the control you wish to use onto the form-palete. If you have dragged a particular control (eg a "textbox" or "label") onto the form, and then decide later you wish to link it to a particular field (say "owner" to display the outlook owner of the item), right-click on the control and select "properties". On the "Value" tab, choose the field (in this case, "owner") you wish to link to. For basic properties, everything happens pretty-much automatically.

You can use the "Properties" and "Advanced Properties" options on any control to set its look and feel or modify this "data binding".

For simple forms, that's probably all you need to know. Plenty of information is also available at:

However, in my case there was some more advanced behaviour required. This included a list-box allowing multiple people who were "responsible" for a task to be selected and then behaviour with a series of radio buttons that would email those responsible when the new task was created. The default values of these radio buttons needed to modify based on whether it was a "new task" or an update on an old task - and whether the task had actually been modified since being open, in the latter case. This required use of vbScript.

6. Adding VbScript behind Outlook Forms:

Starting with the simple things first, to get to the VBScript, just go to the Form->View Code option on the menu. You'll get a basic script editor.

Here's some important, but basic, standard-named methods in vbScript to get you started:

  • Create a method "Function Item_Open()" that will run when a new item is opened. Use this to perform a range of initialization steps.
  • Create a method "Function Item_Close()" that will run immediately before closing an item (task). In my case, this function could be used to send an email to those who were made "reseponsible" in the task if the appropriate options were chosen.
  • Create a method "Function Item_Write()" that will be called prior to saving.
  • Create a method "Sub Item_CustomPropertyChange(ByVal Name)". This method will be called whenever any custom property (one of your custom fields, mentioned above) is changed by the user of the form. You need to test the "Name" attribute to handle a particular item being changed.
  • Create a method "Sub Item_PropertyChange(ByVal Name)". This method will be called whenever a standard (non-custom) property is changed.

Further, here are some of the most common method calls from the VBScript object model that I used:

  • Item.UserProperties("myUserFieldProperty") - is a function that returns the value of a particular user property/field on the form. Can also be used to set that property.
  • Set controlPage = Item.GetInspector.ModifiedFormPages("myPageOnForm") - is a function that returns an object that represents your custom page (tab) on the form.
  • Set myControl = controlPage.Controls("myControlName") - is a function that uses a page of your form (obtained as above) and gets an object that represents a particular control on the form. You can then usee this control to obtain information about it. For instance, if it is a label use "myControl.Caption" to get its current caption.
  • Use the vbScript "Split" function to break a comma separated string (for instance, obtained from a control that represents a multiple selection of items) into an array that can be looked through.
  • Use the vbScript "For Each ... Next" method to loop through each item in the array that is created.
  • Use Application.getNameSpace("MAPI").CurrentUser to get the current username of the person logged in.

7. Some Problems I encountered:

All of this looks relatively simple (and, indeed, is). However, I did encounter a number of specific problems in coding up my solution to this problem that took a while to sort out and are probably worth discussing briefly.

(A) How to put a carriage return into MsgBox in VBScript:

I was wondering how you put a carriage return into a MsgBox in VBScript. It turns out one way is to do the following:

MsgBox "This is Line 1. " & vbNewLine & "This is Line 2." & vbNewLine & "etc..."

(B) Creating an email in VBScript:


One thing you may wish to do is create an email using VBScript. There are a number of places on the internet that suggest something like the following code:

Set MyApp = CreateObject("Outlook.Application")
Set MyItem = MyApp.CreateItem(0) 'olMailItem
With MyItem
.To = a@b.c
.Subject = "Subject"
.ReadReceiptRequested = False
.HTMLBody = "Message
End With

However, I was finding that the line "set olApp=CreateObject("Outlook.Application") was throwing an error.

To fix this, I used set "objMail = Application.CreateItem(olMailItem)" without the need to create the olApp object.

(C) Calling VBA methods from VBScript:

I had a problem of wanting to store some of the data (such as a list of usernames the form would display) locally for each user. I didn't want to use a database, but just store these somewhere in the "Outlook Session" of the user. I was able to do this by placing a VBA function that returned an array into the "Outlook Session" module of the user and calling this function directly from the form's VBScript. To do this, I use:

On Error Resume Next
Set olApp = CreateObject("Outlook.Application")
userInformation = olApp.methodCallOnSessionObject()
If (Err.Number <> 0) Then
' the method wasn't found in VBA on the users session
userInformation = defaultVbScriptMethodCallToFillDefaultData()
End If
On Error Goto 0

It is worth noting here that olApp.methodCallOnSessionObject() is required to call the VBA method. For me it did NOT work to use "Application.methodCallOnSessionObject()" though this was recommended elsewhere on the internet.

It is also worth noting that you may get "Type Mismatch" errors when trying to pass array data between VBA and VBScript. Outlook VBA uses a different variation of the array data type than that supported by VBScript, and therefore a "Type mismatch" error will occur if you attempt to "Type" your VBA output. In the example above, for instance, I receive an error if I attempt to "Dim" the variable "userInformation".

(D) How to get a bound, multi-select Listbox to work?

I mentioned, above, how easy it is to bind a control (say a label on your form) to a field (say, a custom-defined user comments field) and have the control and the bound value stay nicely in synch.

The only exception to this for me was in getting a Listbox to work. It was all very easy when I only had a single-select list-box. However, as soon as I wanted to allow the user to select multiple values in the listbox, I had problems. The most obvious way to create a multi-select listbox is to go into the "AdvancedProperties" of the Listbox and set the "multiple select" attribute to "1-Multi". However, while this did correctly show multiple selections on the form, this seemed to "break" the link to the underlying field on the form when saved. I couldn't get it to work by linking to a standard user-defined field.

My fix: In the end, the only way I was able to make this work and still have the selection show on the form when re-opened was to use a "Keywords" field. Most of the fields I was using were "Text" fields. If you create a user-defined field, say "responsibleUser" and make it a keywords field type, you're on your way to success with a listBox. Then just bind the listbox control to the "responsibleUser" and make it "multi" and it will automatically show a set of checkboxes for multiple selection that are nicely bound to the appropriate field as a comma-separated list.

There is, however, one problem with this approach. You're now likely to get "Type Mismatch" errors.
To quote from

"It is not possible to directly modify the contents of a user-defined keyword field using VBScript. Outlook uses a different variation of array data type than that supported by VBScript, and therefore a "Type mismatch" error message will appear if you try to display the text of the field in a message box, assign the field to an array variable, or perform any string-related function on it. The simplest way to work around this limitation is by accessing the text via a control or by using a standard keywords field."

Not too hard to do... once you know :-) To see how you can get access to the control to do this, see my "useful vbscript functions" section above!

One final thing, before I leave listboxes. I spent ages trying to work out how to set the selected values on a listbox. I just couldn't find the property or way of doing this anywhere... Finally the lightbulb lit... It's easy - just set the bound value (of the field the listbox is bound to) properly. ie : Item.UserProperties("Responsibility") = myResponsibilityString. Really obvious in retrospect, but seeing as it stumped me for hours, I'll just mention it here for next time!

(E) Issues with "one-off" forms:

Another problem that stumped me for a while (at least, until I started reading about "one-off" forms) was that every time I saved my (now quite complex) forms, they would show up as having the wrong "MessageClass".

Now, a diversion here is probably useful. Every item in Outlook has a MessageClass. The base MessageClass values are things like IPM.Task (for tasks) or IPM.Post (for mail), IPM.Appointment (for calendar entries) etc. When you start extending these base forms, you'll get items with message classes such as "IPM.Task.mySpecialTaskType". When you double-click on these it'll know exactly what form to use to display the information.

However, I was finding that my MessageClass, rather than being IPM.Task.mySpecialTaskType was always showing up as IPM.Task. This was causing me some problems.

It turns out that this is a feature! Basically, there are two different types of (in this case) tasks possible. What I'd created was what's known as a "one-off" form. For information, look at:

To quote: "If code on a received message or a saved item doesn't run, it probably means that the form is 'one-offed.' This is a condition in which the form definition is saved with the item. Such an item has lost the connection with its published form. Other symptoms for one-off form items are an increase in the size of the item, loss of the custom icon for the item, and reversion of the MessageClass value to the default for that type of item."

It turns out that one of the most common ways of creating a one-off form is if you change the form-design in code. In my case, this meant populating the list-box at run-time with a list of users (as discussed above). This automatically caused the form to become a "one-off" form and caused some problems for this.

In my case, the simplest way around the symptoms I was getting from this being a one-off form was to reset the MessageClass to the correct value for my form. That is, set "Item.MessageClass = IPM.Task.mySpecialTaskType". It isn't perfect (as the increased task-file size issue remains, even after doing this as the form definition remains with the object, but now unattached - however, Outlook documentation suggests there's no good way to fix this without recreating a new task from scratch - and recommends the MessageClass attribute as a valid way to get around the symptoms of the one-off form).

Another way of changing the messageclass of a form is discussed below:;[ln];q201089

(F) "This function cannot be performed because the message has been changed"

Another problem that became increasingly frustrating was that, in some instances after editing (or creating) a task, if you try and save that task an "This function cannot be performed because the message has been changed" error would occur. Alternatively, if instead of saving in VBScript, I saved the task manually, it would throw an error stating ""The item could not be saved because it has been changed by another user or in another window."

Both of these errors were "intermittent" in that they go away if one clicks off the task and then back onto it. This error is caused by the form being changed onOpen, and seems only to occur when the task that is being opened does not have focus prior to the actual double-click event that opens it. Presumably the vbscript that modifies the form is for some reason being interpreted as being undertaken by a different user.

To date, I haven't been able to "fix" this problem. However, the problem can be found as soon as the form opens. As such, I have an "OnError" code in the "OnOpen" method that runs whenever this error occurs. In my current code, if this error occurs it just shuts down the new task form. This, gives focus to the appropriate task, meaning that the next time someone opens it, the form will display properly and everything works. To the user, it just looks like the form isn't opening when they double-click. However, in the background it is starting to open, finding a problem and closing itself as a result. This is a pretty ugly solution, I know, but it does seem to work in the absence of more information. A post about this problem (experienced by others as well) is available at:

8. Conclusions:

Overall, this just summarizes some of my experiences in developing custom forms in Outlook. Apart from some problems that took the bulk of my time to fix, it was actually quite easy to do, even with no prior knowledge of Outlook or VBScript.

Monday, July 14, 2008

Hibernate Query Language

I've recently become interested in the Hibernate Query Language (HQL) and some of its capabilities. This post summarizes some of my initial experiences for my future reference.


Until recently, my Data Access Objects (DAOs) would pretty much just use the most basic HQL queries to load an object from my database by its ID:

Query q = session.createQuery("from Clazz c where = 1");
List result = q.list();

or alternatively, load a complete list of all database objects for a particular table:

List result = session.createQuery("from Clazz").list();

If I needed to retrieve a list of objects using a more complex criteria, I could always load the full list, as above, and then return the appropriate objects programmatically, but this is terribly inefficient.

In the past I have used SQL queries (rather than HQL) to more efficiently retrieve just the data I was after. Eg:

session.createSQLQuery("select {c.*} from Clazz {c} where NAME like 'searchName'","c",Clazz.class);

For simple mappings this works fine and uses plain old SQL. However, once you begin to use the more powerful abilities of Hibernate (such as mapping Polymorphic associations), the SQL you need to write becomes more and more difficult and convoluted.

That's when it's finally time to bite the bullet and find out that HQL is actually surprisingly easy to use and very powerful.

HQL Statements - structure:

The structure of a general HQL statement takes the form:

[select ...] from ... [where ...] [group by ... [having ...]] [order by...]

Seeing as the only required clause is from, the simplest HQL query (to retrieve a list of all the items in the table mapped by the class clazz) is:

from clazz

To build on this to create much more complex queries (even when you have Polymorphic associations) is very easy. To describe the queries I've been working with lately, I'm only going to use a very small subset of HQL query options, namely:
  • select clazz - tells Hibernate to restrict the objects it returns to the specific class you are interested in.
  • select distinct clazz - tells Hibernate to remove duplicates in the list returned.
  • join - performs an inner join.'
  • left join fetch - performs a left outer join (for eager fetching).

Suppose we have a class, ParameterValue. There are two different types of ParameterValues, which are subclasses of ParameterValue. Namely "ElementParameterValue" and "UnitParameterValue". We use the Hibernate "Table per class hierarchy" mapping strategy to map this to a single table in our database.

All ParameterValues have a Unit class reference (so each ParameterValue belongs to one and only one Unit). In addition, a ParameterValue has a SaveTag reference (so each ParameterValue also has a unique SaveTag).

In my example, I needed to find a list of SaveTags for a given Unit. That is, the question was "for a particular Unit what SaveTags were represented amongst its ParameterValues?"

The simple HQL that achieves this is:

select distinct saveTag from ParameterValue pv join pv.unit u join pv.saveTag saveTag where = :unitId

The select distinct saveTag tells HQL that the result you want sent back is a List of SaveTag objects with no single SaveTag object represented more than once in the list.

The joins perform inner joins with the required Unit data and the SaveTag data, allowing us to reference (in the where clause) or retrieve these objects by name, respectively.

The where clause defines the search criterion.

All pretty easy stuff - nothing too profound - but I've been playing around with a range of other HQL queries, and they're all as easy as this - even for quite complex datasets, and polymorphic mappings.

The take-home message: Have a look at HQL and don't assume that just sticking with SQL (even if you know it well) is going to be easier.

[NB: This post is a placeholder - I might put some more information about useful HQL queries in future].

Wednesday, July 9, 2008

Hibernate - Mapping booleans using annotations.

Using Hibernate annotations to map a Java boolean in MySQL.

The Problem:

I've recently experienced some problems with mapping boolean values in a Hibernate entity to mySQL. The problem arose because the "boolean" type in mySQL is not actually a boolean. It's a tinyint.

MySQL does support the SQL Standard BOOLEAN or BOOL keywords, but treats these as synonyms for TINYINT(1). A value of zero is considered false. Non-zero values are considered true.

The solution:

Luckily, this isn't a problem for Hibernate, once you know what you're doing. Indeed, if Hibernate is creating your database, all you need to do is annotate your booleans with correct annotations, namely:

@Column(columnDefinition = "tinyint", nullable = false)
boolean myTrueOrFalseValue;

or, if you need to allow potentially null columns:

@Column(columnDefinition = "tinyint")
Boolean myTrueOrFalseValue;

Both of these will work, however the first one does not allow null values.

WARNING: This means that if you have been trying a number of different attempts to map "myTrueOrFalseValue" you'll probably already have a column in your mySql table and it will be full of null values. You'll need to delete this column manually (and allow hibernate to regenerate), or use some other manual method to ensure there are no null values in the column. If you do have null values in your database, these annotations will cause a "
org.hibernate.PropertyAccessException: could not set a field value by reflection setter of..." error on startup.