Thursday, October 13, 2005

DB access in word VBA

I was working on little program to extract data from a word document, and insert it into a database. Pretty straightforward VBA for a VBA developper... Except I'm not a VBA developper.

Actually it proved quite simple up to the point where I need to make an ODBC connection to the database. Then all hell broke loose.

In time honoured VBA programming practise, when you hit upon a problem, record a macro, and do the step you want to, then view and edit the code.. .

This I did and discovered the nice looking InsertDatabase [Link to msdn]
. Worked a treat.. Read data from the odbc, and displayed in it the doc. Wonderful!

Except for the teeny weeny problem thatI wanted to display the data in a userForm. Reading the docs in that link above, the InsertDatabase method can only be called on a Range object. Try as I might I couldn't find a Range object in the UserForm.

Long story short.. I found the ADO object which solved my problems.. Gives you a full API for talking to DB's. You will need to install the reference libraries Microsoft AxtiveX Data Object library (you will need your office install media for this), and include them in your references first.

I did briefly run into the infamous Error 80004005 "Data Source Name Not Found"

But with a bit of tinkering around I got this to work.

First step, create a systemDSN (userDSN should also work) of the name for the ODBC connection you want (vba in this instance)

Then the following code calls a table there and inserts the data into my UserForm (called UserInput)

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sql As String

Const ConnectString = "DATABASE=vba;DESCRIPTION=Vba Test;DSN=vba;OPTION=0;PORT=0;SERVER=localhost"

cn.ConnectionString = ConnectString
'cn.CursorLocation = adUseClient

' Find out if the attempt to connect worked.
If cn.State = adStateOpen Then
Set rs = cn.Execute("Select * From test")
If rs.EOF Then
UserInput.namesList.AddItem "No Entries returned from Database."
While Not rs.EOF
UserInput.namesList.AddItem rs("data") & ":" & rs("date")
End If
'MsgBox (rs("data") & ":" & rs("date"))
MsgBox "Sorry there was a problem connecting to the database."
End If



Any questions, drop me a comment,

Thursday, August 25, 2005

more xml schema validation

I thought I had Xml validation sorted after the following entries (java and xml, and schema validation).

So today when I went to validate some xml's against a Xsd I was a bit surprised when it didn't work

What was more surprising was that it was working, and when I changed something small, it stopped.

As part of a Unit Test I was validating an xml file against a schema, and it was working fine. However I was loading the Xsd via a webserver into the parser. This wouldn't do since other developpers would want to run the unit tests and would not want the hassle of starting up a webserver and dropping the xsd file in place initally before running the tests.

So I changed the URL to load the Xsd via the file:// protocal. See below


Running that however gave the following exception

org.xml.sax.SAXParseException: cvc-elt.1: Cannot find the declaration of element 'wishList'.
at org.apache.xerces.util.ErrorHandlerWrapper.createSAXParseException(Unknown Source)

But I knew the XSD was correct because it had been working before when I retrieved it via http:// on my webserver....

Do you see the problem? Well I didn't initially. After some poking around I found the solution.


See the difference. Subtle isn't it. Basically xerces insists that file needs 3 forward slashes in it url definition (file:///wishlist.xsd). That was the problem. Surely a better error message could have been generated there. (Maybe XSD not found or something)

Anyway I then started gettign the following error

org.xml.sax.SAXParseException: Invalid byte 1 of 1-byte UTF-8 sequence.
at org.apache.xerces.parsers.DOMParser.parse(Unknown Source)

Short answer to this problem was my file character encoding. I had saved my xsd using a free editor (crimson). By default it saves files in Ascii encoding. However my Xsd expected to be in UTF-8. Changing the character encoding and re-saving solved the problem.

Amazing how a simple little task can tie you down for hours..

Monday, August 15, 2005

Regular expressions tutorial

I can never remember Regular Expressions (regex).

So heres a good tutorial on the subject I found.

once you've read that, then pick one of the following results as a quick reference card.

---Added Jul 13 ----
Just found this great site for validating your regex's online.. Brilliant.

Strip html from strings
//javascript Makes <b>Hello</b> World => Hello World
tag = tag.replace(/(<([^>]+)>)/ig,"");

Split CamelCase word (Note performance might not be great for many iterations)
s.replaceAll("([A-Z])", ' $1'); // taken from here

Slight modification (don't split if more than one capital after another)
s.replaceAll("([A-Z]{1,})", ' $1');

Thursday, August 04, 2005

Remote Debugging Java with eclipse

This is a powerful feature which allows you to debug a remote server from an IDE, and step through the code to see what is happening. For example a remote web server like tomcat, or an app server like WebLogic or Websphere can be debugged.

Firstly the basis of this is a feature in the Java JVM. This means that any java executable can be debugged this way.

To start debugging on a java instance you need to include the following when you run the JVM

java -Xdebug -Xnoagent -Xrunjdwp:transport=dt_socket,server=n,suspend=n,address=8000 -Djava.compiler=NONE

For running Weblogic Server in debug mode subtle difference
java -Xdebug -Xnoagent -Xrunjdwp:transport=dt_socket,server=y,suspend=n,address=8000 -Djava.compiler=NONE

This sets the debugger listening on a socket on port 8000.

Debugging can also be set up using shared memory, but eclipse can't use the shared memory feature so I won't deal with it here (BTW, Netbeans can use the shared memory mechanism for debugging so this can be pursued if you are using Netbeans)

Simply then within eclipse you need to select Run/Debug.... From there select Remote Debugging, select the project where you have the source code for the project, and specify the server name where the service is running and the port (8000 in our case), and you're laughing.

Easy peasy.

If you are running tomcat (well version 5.5 anyway), the startup scripts already have the debugging information included, see this link

What I've done then is simply changed the startup.bat file.

At the start (line 9) I added the following, which specifies socket based debugging (shared memory is the default), and sets the listen port to 8000
rem ++++++++++++++++++++++++++++++++++++++
rem Enable Remote Debugging
set JPDA_TRANSPORT=dt_socket
rem ++++++++++++++++++++++++++++++++++++++

Careful of any extra spaces of tabs on these line. An extra space caused me some problems for a while.

Then change the 2nd last line where it calls the catalina.bat file from



Now when you call the startup.bat you can simply call startup jpda to start the debugger listening on port 8000. Then you can attach your IDE to port 8000 and debug the server.

Alternatively you can simply call the catalina.bat file directly using the command

catalina.bat jpda start

But you must declare JPDA_ADDRESS=8000 and JPDA_TRANSPORT=dt_socket as environment variable (or put them in the catalina.bat)


Remote debugging with eclipse

To enable remote debugging for weblogic the following must be done

In the setDomainEnv.cmd or you must assign the variable debugFlag to true. (Note no need for quotes etc as I discovered after some time debugging)


set debugFlag=true

local debugFlag=true

Wednesday, July 13, 2005

Website notice

Just want to publicise This is a website I developed for, Billy Eccles (surprise surprise). Billy is a potter based in Dublin. He also happens to be my girlfriends father. So no pressure there... Anyway have a look and let me know what you think, and like in restaurants/ shops etc.

"If you don't like something on the site tell me.... If you like something, tell everyone" (and me as well).

Technical aside
You may notice that the site is a PHP site, and yet all my postings before now, have been Java related. That's correct. For this site I realised that if I was to develop anything worthwhile I needed to leverage as much free code as possible. So I'm using an open source CMS called Mambo. It saved me a lot of time.

Tuesday, July 12, 2005

Autostart Vnc

Took me a while to find this so I thought I'd write it down. Of course its easy when you know how.

Edit /etc/sysconfig/vncservers

Add the following


Where 1 is the display to start the vncserver on, and username is the name of the user you want to start vnc as (Not root). Simple as that. You should also run vncpasswd first and set the vnc password on the system.

Now a quick explanation

The /etc/sysconfig dir contains information on user services to be started up (unlike the /etc/rx.d dir that starts up system services). Information on these can be found in

RedHat docs

or on


Cross platform Jsp issues

I find this hard to believe, yet it seems to be true

Take a simple html tag with a jsp tag

<input name="text" value="<%=new String("Hello")%>"/%>

That works fine on tomcat. No problems...

On weblogic however, no go...

The problem is the nested quotes """

To get it to work, obviously a few solutions

<input name="text" value="<%=new String(\"Hello\")%>"/%>


<input name="text" value='<%=new String("Hello")%>'/%>

Both easy solutions once you know the problem. Of course it took me a while to find out that this problem. In my defence, I wasn't doing something so simple as the above example. I was having problems with a tiles definition.
After a bit of effort I now have some basic JSF pages working.

One error that stumped me for a good while was the following

Can't instantiate class ....

This came about from a backing bean I was tryign to configure from my face-config.xml

<managed-bean-name> Menubar </managed-bean-name>
<managed-bean-class> </managed-bean-class>
<managed-bean-scope> application </managed-bean-scope>
<description>List of items to appear in the MenuBar and privilages, these get localised to menu.</description>

Basically I was trying to set the itemsAndPrivilegeLevel parameter in the bean.

The problem was I hadn't created a getItemsAndPrivilegeLevel method in the bean. I dodn't think I needed one (I don't), but apparently either JSF or the beans specification think different, so I have to have that method there.

The perils of globalisation

... from a programmers persepctive...

Call it globalisation or internationalisation, either way it not as straightforward as I'd like to believe.

As a java programmer it all seems easy. Use a ResourceBundle and store all your text in handy resource files. These can easily be translated as needs require. However once you've externalised all of your user displayed text to property files, thats when the fun really begins. Especially if you're dealing with a non Western script.

Funny how Japanese text looks like fractions, symbols and punctuation marks you may find yourself saying....
Once you get past that stage though, then its onto the world of character encodings.

Character encoding. It's something most Western language users blissfully ignore. Its only when you try and export your freshly translated text to say Japan that things come tumbling down.

The best links I found on this are here and here.

Ah ha, maybe that 3/4 symbol isn't in fact the japanese for Hello.

In fact its garbled character encoding. Looks like its time to instruct your webserver to serve up different character sets.

I started out setting up Tomcat to send UTF-8. There are a few distinct steps involved.

The JVM must be initiated using the correct charset. This sets the format of the files that java will use (includes, properties file, jsp pages etc.)

This is done with the following switch


so for Tomcat, I added the following to the cataline.bat file

set CATALINA_OPTS=-D=file.encoding=UTF-8

Its important to realise the standard encoding used by the OS that you are running. My version of windows is using a Western Latin-1 script. This is unable to display japanese characters. In order to get japanese characters displayed I had to install japanese character supprt.

This also means that files (for example properties files) may be stored in a different character set than you may be expecting.

In my case, I was using Crimson as an editor. Crimson saves all charcters as Ascii, thus cannot display japanese character, or even Western scripts such as é or ß characters. I had to find a unicode enabled editor. Unipad is a good one that I found.

Java properties files do not by default support unicode. They must be encoded.

Once your files are stored in a unicode mapping, you have to run it through the nativeToAscii program which escapes out any strange foreign characters and puts in the exact unicode code point instead. (Theres an ant task which can be used for this).

<native2ascii encoding="UTF-8" src="$%7Bsrc.dir%7D" dest="${build.dir}" includes="**/*.properties">.

Once your basic java environment is set, you need to be able to set the character encoding for the page. This can be set with a page directive

<%@ page contentType="text/html;charset=UTF-8" />

or in xml syntax

< contenttype="text/html;charset=UTF-8">

Finally (I think) set the encoding for the HttpResponse (So you can understand any text that gets sent back)

Using jstl

<fmt:requestencoding value="UTF-8">

Now in the above example I have used UTF-8 as the encoding, this encoding maps to a variable number of bytes (from 1 to 6). Western mappings e.g. ascii will normally be covered by 1 byte, but those foreign type scripts may take up to 6 bytes to be encoded.

If you are mainly directing pages to Japanese clients then UTF-8 will be quite wasteful. A fairer encoding might be UTF-16 (doesn't seem to be supported by IE, or Mozilla browsers), or even (I going out on a bit of a limb here) Shift-JIS.

Its all very nice, if it all works well , but if it doesn't..... Can be bit nasty.

What I'm looking at now is changing the encoding deplending on the locale. The serlvet 2.4 spec has a nice mapping


which hopefully will sort things out.

If theres no more entires on this subject then it all worked out swimmingly

Location independent loading of properties

Heres another little douzie that been slapping me across the face all day.

When writing and deploying code you don't want to have absolute paths listed for config files, resources etc, as these will invariably lead to problems when moving the application to another server.

Normally with java this entails creating a deployment directory, and including within it all classes and resources that you may need. You then load all files and properties using the deployment directory as the root.

The question then arises how do you access these properties.

For example with properties files the initial way one thinks of to load properties is as follows

FileInputStream inputStream = new FileInputStream("");
Properties prop = new Properties();

The problem with this is that the is an absolute value, which knows nothing of the deployment directory that you may be running from.

The solution is to use one of the following

Class.getResourceAsStream() or

The funny thing is that for me sometimes the first method works (e.g. when run from within Eclipse), and the 2nd one fails. Sometimes (from with tomcat) the first one fails and the 2nd one works.

In fact initially I had posted to use the 2nd method after spending a few hours tracking down why my program worked in Eclipse but failed in tomcat. It was only a day later that I discovered that the 2nd method (which works in tomcat) doesn't work in eclipse.

So now I have this slightly ugly construct to try and get around the problems

* The name of the properties files. N.B. the "/" at the start means it is an absolute
* package, not a relative path from this package.
* Default propertuies location /resources/ in the classpath may be
* overridden by setting the System property,
* as in the test ant target, or with java
static String propLoc="/resources/";
// First check system property to see if specific properties file is set
propLoc = System.getProperty("", propLoc);
prop=new Properties();
InputStream is = Utils.class.getResourceAsStream(propLoc);
log.debug("Failed to load Resource ["+propLoc+"] from Utils.class.getResourcesAsStream() trying classloader.");
ClassLoader cl=Utils.class.getClassLoader();
log.debug("ClassLoader = "+cl);

try {
//Throws IOException
} catch (Exception e) {
log.fatal("Exception loading Properties file.",e);

I'm at a loss to explain it, but I suspect (hope) that its some configuration problem.

Xml schemas and Namespaces?

With the amount of headaches I'm having doing a simple task like validating an Xml doc against a schema, I'm back to wondering if I shouldn't just throw this programming lark aside..


In the meantime heres the root cause of my most recent heartache....

The solution is ...

String xml ="<envelope><header2>Hi</header2></envelope>";
String xsd = "http://localhost:8080/xml/test.xsd";
SAXBuilder builder = new SAXBuilder("org.apache.xerces.parsers.SAXParser", true);
builder.setFeature("", true);
builder.setValidation(true); InputSource(new StringReader(xml)));

Simple isn't it

What could have been the problem?

The problem is that innocuous line builder.setProperty("",xsd);

To cut a long story short Xerces has 2 modes for validating external schemas (i.e. not the schemas referenced in the Xml doc itself). 1/ Namespace enabled 2/ No Namespace.

Simple huh?

Well it is once you know that simple fact. I on the other hand didn't . (Mind you I do think it should be possbile to validate an arbitary Xml doc without necessarily knowing if its namespace enabled or not. But thats for another day)

So in summary people

If you want to avoid cryptic exceptions like

org.jdom.input.JDOMParseException: Error on line 1: cvc-elt.1: Cannot find the declaration of element 'envelope'.
at XmlTest.main(
Caused by: org.xml.sax.SAXParseException: cvc-elt.1: Cannot find the declaration of element 'envelope'.

NB. This is the same exception you get if Xerces cannot locate your schema file, or if you don't configure the paramerters correctly. Something more intuitive would be more helpful.

do the following

1/ If your Xml doc uses namespaces

String xml ="<envelope><header2>Hi</header2></envelope>";
// Namespae of xsd1
String ns1 = ""
String xsd1 = "http://localhost:8080/xml/test.xsd";
//Namespace of xsd2
String ns2 = "";
String xsd2 = "http://localhost:8080/xml/test2.xsd";

SAXBuilder builder = new SAXBuilder("org.apache.xerces.parsers.SAXParser", true);
builder.setFeature("", true);
"", ns1+" "+xsd1+" + ns2+" "+xsd2);
builder.setValidation(true); InputSource(new StringReader(xml)));

You must set the Property with a space seperated String list of the schema(s) to validate against.
The list takes the following form namespace uri namespace uri .... Its composed of pairs of values, the namespace that the schema will validate and the actual location of the schema file. (It can be any uri http://, file:// etc)

This section is taken from the JDOM FAQ

Schema locations are given by setting the property "" to a list of whitespace separated name-value pairs. The 'name' is the namespace the schema is associated with, the 'value' is the location of the schema for that namespace. For example:

"", " soap-envelope.xsd" + " " + " weblog.xsd");

The above example shows how to validate against multiple schemas -- against the SOAP 1.2 schema where the namespace is and the and against a schema for namespace The files describing these schemas are in soap-envelope.xsd and weblog.xsd respectively. You can add as many of these name value pairs as necessary. The values themselves are URLs. The name value pairs follow the meaning given in the Schema recommendation ( ).

2/ If your Xml doc does not use namespaces

String xml ="<envelope><header2>Hi</header2></envelope>";
String xsd = "http://localhost:8080/xml/test.xsd";
SAXBuilder builder = new SAXBuilder("org.apache.xerces.parsers.SAXParser", true);
builder.setProperty("",xsd); InputSource(new StringReader(xml)));

Here we set the property (spot the difference) with simply the schema uri. (Not a List as there is only the default namespace) to validate against, and only a single items since there is no namespaces, (i.e. not a pair like before)

It may look simple but that wee little problem had me stumped for longer than I'd care to mention.

Heres some links on the stuff

The Jdom faq Jdom uses xerces so this applies to xerces as well.
xerces bugzilla entry. There was a bug with this prior to version 2.2.0

One aside. By default xerces will not throw exceptions for validation failures, the parser must be configured to do that.

Jdom will configure the parser to do this by default. (for a lazy programmer like me this simply means that I use jdom to wrap my calls to xerces, rather than actually looking up how to configure xerces)

More on xml classpaths

Just an extra note on the xml classpath issue. jdk1.4 comes with an old version of xerces that must be updated for the schema validation to work. I discovered this today when I started running into the same problem as last Friday after I changed the java.endorsed.dirs to be %JAVA_HOME%/lib/endorsed. Even though jdk1.4 ships with a version of xerces, and its in the endorsed directory, my validation failed, until I copied a newer version of the xercesImpl.jar into the endorsed dir. I've used xerces 2.6.1 which does work.

Java Xml and schemas growing paings

I've just spent the morning stumped on a problem I had working before. I was trying to validate an Xml doc against a schema but kept getting the following.

Exception in thread "main" java.lang.ClassCastException
at org.apache.xerces.impl.xs.XMLSchemaLoader.processExternalHints(
at org.apache.xerces.impl.xs.XMLSchemaValidator.reset(
at org.apache.xerces.parsers.BasicParserConfiguration.reset(
at org.apache.xerces.parsers.DTDConfiguration.reset(
at org.apache.xerces.parsers.DTDConfiguration.parse(
at org.apache.xerces.parsers.DTDConfiguration.parse(
at org.apache.xerces.parsers.XMLParser.parse(
at org.apache.xerces.parsers.DOMParser.parse(
at Utils.validate(
at XmlTest.main(

Some investigation on the internet turned up some red herrings

  • Problem with new version of xerces. I tried older version same problem

  • Problem with JDOM. (this is the api I normally use since it is far more intuitive than the basic jaxp or xerces classes). Re doing it in pure xerces didn't help.

  • Bug in xerces.

In the end (as is often the case) I took a break from it, and I had a hunch over lunch.

The problem is jdk 1.4. It includes some xml classes by default, and you need to override these to get xerces to work correctly.

This is done with the java.endorsed.dirs
property. Set this to be the dir where your xerces is stored and Voilia it works.


C:\dev\sandbox>java -Djava.endorsed.dirs=\apps\xerces-1_4_3\ -cp classes;\apps\xerces-1_4_3\xerces.jar XmlTest

Check out this article on Endorsed Dirs

Just be careful as well. By default <java-home>\lib\endorsed is the deafult endorsed dir and this may override it. This probably works (I'm just too lazy to bother testing it).

C:\dev\sandbox>java -Djava.endorsed.dirs=\apps\xerces-1_4_3\;%JAVA_HOME%\lib\endorsed -cp classes;\apps\xerces-1_4_3\xerces.jar XmlTest

Alternatively copy all such jars into the default endorsed dir \lib\endorsed.

Hopefully now by putting this up here, maybe I can save someone else a frustrating half day.

Let me know if I do.

Friday, April 15, 2005

Xpath Tester

This is a simple online Javascript Xpath validator. Type in your expression and your xml and hit the evaluate button.

To get you started click ont he clinks below to prepopulate the form with some example xpath expressions

List all year elements in the document
List employees elements containing "company" as a parent element
List turnover of 2nd year elements in the document. Note if you are using IE this will show the 3rd year. This is a bug in IE's implementation of XPath
List all year elements in the document
Handling namepaces
Selecting attributes (e.g. id of years)
Xpath functions