Archive for the ‘Web Development’ Category

We had an issue recently where one of our Excel import routines was putting garbled data into the database. By “garbled” I mean the field contained a few normal words then broke into a bunch of strange characters. This Excel file was read in using an ODBC datasource.

If you are not familiar with this method of reading Excel files, here is a short summary.
1) Create a system ODBC DSN using the MS Excel driver in your Windows ODBC control panel. Point it to an empty .xls file somewhere on your drive.
2) Create a datasource in ColdFusion (driver: “ODBC Socket”) and select your Windows ODBC DSN from the drop down.
3) Copy the XLS file you want to read on top of the empty file you setup in your ODBC DSN.
4) Use it like this in your CFML:

<cfquery name="myQuery" datasource="XLSPassThroughDSN">
FROM [Sheet1$]

This usually works well but for some reason we were having problems. Digging through the Excel file turned up nothing out of the ordinary, except some rows (and not the ones causing problems) looked like they were double byte encoded (unicode).

Turns out this XLS driver determines the data type of each column in the spread sheet by looking through the first couple of rows and guessing a data type based on that data. In our case column B only contained values with less than 100 characters in the first few dozen rows. So the driver assumed it was a varchar type of some length. But much further down in the spread sheet, in a different row, that same column contained a value that was several hundred characters, thus overflowing the data type.

Normally this only results in truncated data. If that would have been the issue I probably would have found the fix much sooner. But instead we were ending up with garbled data, which was truncated as well but it was impossible to tell.

The fix is to adjust your registry to tell the driver to scan through more, preferably all, of the rows before guessing at a data type. In your odbc control panel you’ll notice a setting for this:
ODBC DSN screen shot

But, that doesn’t work! You need to change the registry value manually. I set mine to 1000, since that will cover the length of any of the spreadsheets we upload.

The registry keys are located at:

For Excel 97:
For Excel 2000 and later versions:

Here is the MS knowledge base article for more detail:

Now the driver scans through all the rows before determining a data type, and probably uses something like a “text” type for column B. I think our data may have been getting garbled, instead of just truncated, because of the double byte data.

After installing Open Blue Dragon on Tomcat and hooking it up to Apache, I did some poking around and found I was able to pull up my bluedragon.xml configuration file directly in my browser. Now, this may not happen in all configurations (there are many ways to setup a J2EE web application), but it my case, running through Apache and having copied Blue Dragon’s WEB-INF directory to my webroot, I was able to browse this file. In case you didn’t know, all OpenBD’s settings are stored in a single xml file (which I find very convenient, by the way).

It didn’t work when going directly through Tomcat, i.e. browsing on port 8080 would not pull it up, I think Tomcat is smart enough to know not to serve files from the WEB-INF directory. But browsing through Apache on port 80 bypasses Tomcat for anything thats not a .cfm or .cfc file, so it would happily return the xml file. Datasource passwords are stored encrypted but the administrator password is clear text. Its easy to lock this down, just add this to your Apache config file:

<location "/WEB-INF/">
deny from all

I was installing ColdFusion 7.02 on a client’s CentOS 5.2 Linux server the other day and received an error when running the installer. Note that this OS is NOT supported by Adobe for this version of ColdFusion. This is a fairly old version of CF being installed on a pretty current version of CentOS. The error was:

[root@server tmp]# ./coldfusion-702-linux.bin
Preparing to install...
Extracting the JRE from the installer archive...
Unpacking the JRE...
Extracting the installation resources from the installer archive...
Configuring the installer for this system's environment...
awk: error while loading shared libraries: cannot open shared object file: No such file or directory
dirname: error while loading shared libraries: cannot open shared object file: No such file or directory
/bin/ls: error while loading shared libraries: cannot open shared object file: No such file or directory
basename: error while loading shared libraries: cannot open shared object file: No such file or directory
dirname: error while loading shared libraries: cannot open shared object file: No such file or directory
basename: error while loading shared libraries: cannot open shared object file: No such file or directory
Launching installer
grep: error while loading shared libraries: cannot open shared object file: No such file or directory
/tmp/install.dir.3348/Linux/resource/jre/bin/java: error while loading shared libraries: cannot open shared object file: No such file or directory

There is a hard coded value in the installer thats causing things to get messed up. Fixing this is fairly easy, you can modify the installer like this:

cp coldfusion-702-linux.bin coldfusion-702-linux.bin.backup
cat coldfusion-702-linux.bin.backup | sed "s/export LD_ASSUME/#xport LD_ASSUME/" > coldfusion-702-linux.bin

That comments out the offending line in all places in the installation script.

More discussion on this here:

You will probably run into more issues installing CF7 on this version of CentOS but it can be done. I also had to upgrade the connector to work with Apache 2.2 (when CF7 came out the connector was designed to run on Apache 2.0). The Adobe KB article that discusses this is here: Their example didn’t quite work for me because its defaulting to the version of Java thats on your OS, which won’t work unless its a Sun 1.4.2x version. So I just used the JRE that is bundled with ColdFusion to install the new connector:

/opt/coldfusionmx7/runtime/jre/bin/java -jar ../../runtime/lib/wsconfig.jar \
        -server coldfusion \
    -ws apache \
        -dir /etc/httpd/conf \
        -bin /usr/sbin/httpd \
        -script /etc/rc.d/init.d/httpd \
        -coldfusion -v

I thought that was the last hurdle but when we tried to log in to the ColdFusion administrator we got an error about “The Graphing service is not available”. The message in the log file was “Unable to initialize Graphing service: java.lang.UnsatisfiedLinkError: /opt/coldfusionmx7/runtime/jre/lib/i386/ cannot open shared object file: No such file or directory”. This was fixed by installing the libXp library:

yum install libXp

Sometimes when writing JavaScript I need to have something run as soon as the page has finished loading. This is usually done by placing a call to the function in the body’s onload attribute like:

<body onload="myFunc()">

But this is not always possible. For example by the time you get to your logic that decides it necessary to call a function onload, the header may have already been displayed by a cfinclude or by your framework.

You could use JavaScript to set the onload event, like

window.onload = myFunc;

But what if there was already something in the onload attribute of the body tag? The above code will reset whatever was there. But here is a nice snippet of code that will add functions to the onload event. I can’t take credit for it, and I don’t remember exactly where I found it but its been quite useful to me. It works in all the popular browsers.

function addLoadEvent(func) {
	var oldonload = window.onload;
	if (typeof window.onload != 'function') {
		window.onload = func;
	} else {
		window.onload = function() {
			if (oldonload) {

Then you can add as many functions as you want to be called when the page loads. Note you do not use parenthesis when specifying the function names – you aren’t calling then, just referencing them.


The other day I had a need to be creating dates in a SQL Server SQL statement, made from a few different text fields. I could not find a function to create a date from these, so I wrote one (with the help of our in house SQL guru).
Continue reading ‘A ‘MakeDate’ function for MS SQL Server’ »

Railo 3.1, the much anticipated open source release of the Railo CFML engine was released yesterday. I’ve been playing with it the last two evenings. So far I’m very impressed. They have an “Express” version which you can get running almost instantly. I tried that, but then opted to get it working as I would for a real site – using Tomcat and Apache. It was much easier than I thought.

The administrator is very full featured with everything you would expect – scheduled tasks, ability to create database connections to MySQL and MSSQL (among several others), and search! Railo has Apache Lucene built right in. Creating a new Lucene index is as easy as creating Verity collection in Adobe ColdFusion. The cfsearch/cfindex tags work like you would expect them to, with a few exceptions. You can even populate and search the collection right from within the administrator.

I was happy to see that you can define multiple SMTP servers. Railo will try each of them in order if any of them are unavailable.

I also really like the way Railo has done the administrator – with one global administrator (called the server administrator) and then administrators for each site (called a web administrator). I think this is going to make it much easier for hosting companies to offer CFML support.

I just finished one of the books I received for Christmas, More Joel on Software by Joel Spolsky. This book is the 2nd compilation of blog articles from Joel’s popular software development blog.

I really enjoyed this book. I was surprised how often Joel had me laughing out loud, he really is a good writer. The book is broken down into nine sections.

  • Managing People
  • Advice to Potential Programmers
  • The Impact of Design
  • Managing Large Projects
  • Programming Advice
  • Starting a Software Business
  • Running a Software Business
  • Releasing Software
  • Revising Software

Joel has a lot of experience in programming and in running a software business, and has lots of interesting stories to tell. In fact chapter one is titled My First BillG Review, its a story about Joel’s first meeting with Bill Gates, where Bill reviewed Joel’s 500 page spec on Excel Basic.

Joel covers a lot of interesting topics, including finding and keeping great developers, different management styles, running a software business, and revising code.

I especially liked his thoughts on estimating. He uses something called Evidence Based Scheduling. EBS is a system where you keep track of estimated hours vs actual hours. The math gets a little complicated, you can read about in detail on Joel’s site (or the book), but basically its a way to accurately estimate projects. Regardless of if the developer estimating tends to over or under estimate, the formula works all this out. It sounds like a really neat system. Joel’s popular project management software FogBugz supports EBS.

All the content of the book is available on his blog for free, but it is nicely organized in the book and it has some updates. Its a bargain at $16 on Amazon, and I enjoy reading stuff like this in book format rather than on a computer screen. This is a book that I will be hanging on to.

I was trying to execute a .cfm page on a new CentOS 5.2 server and I received this error. Not all requests were erroring out, the CF Administrator was working fine as were other simple pages. But when I tried to load a page that instantiated some java objects, I got this error.

"coldfusion.jsp.CompilationFailedException: Errors reported by Java compiler: jikes: error while loading shared libraries: cannot open shared object file: No such file or directory

I checked to make sure libstdc++ was installed, and it was, although not version 5. I checked another one of our Linux CF8 servers, it didn’t have version 5 installed either, so I didn’t think that was the problem. I noticed the other server had some compat libs installed that I was missing though. I ran this command: yum install compat-libstdc\* which installed two RPMs: compat-libstdc++-296 and compat-libstdc++-33. Then I restarted ColdFusion, and was able to execute the page with no errors.

Update 8/27/09: I am available for consulting to help with this issue, I’ve helped quite a few people out already. My rate is $75/hr, and you can contact me at -thanks

If you use the CFX_PayFlowPro tag to connect to PayPal’s PayFlowPro service (formerly owned by Verisign) you should be aware that it will stop working in September of 2009. You must transition to one of their newer connection methods before then.

Here is a drop in replacement custom tag you can use. You should be able to pretty much just change your code from “CFX_PayFlowPro” to “CF_PayFlowPro”. If you run into any issues and end up modifying the tag, please let me know and I’ll get the changes worked back into the original.
Continue reading ‘CFX_PayFlowPro to stop working in 2009 – here’s a drop in replacement’ »

There may be something buggy here, or maybe I’m misunderstanding how this should work.

This code executes fine:

<cfquery name="qryListings" dbtype="query">
SELECT 1 as OrdBy, * FROM qryListings
SELECT 2 as OrdBy, * FROM qryListingsTmp

But when I add a WHERE clause to the second part to filter out records that were in the first group:

<cfquery name="qryListings" dbtype="query">
SELECT 1 as OrdBy, * FROM qryListings
SELECT 2 as OrdBy, * FROM qryListingsTmp WHERE ref_num NOT IN

I get a ColdFusion error about the number of columns being different between the two queries.

Continue reading ‘A bug with Query of Query and * ?’ »