I took a call from our help desk last night around midnight. One of our users in Asia had called in reporting an error in one of our applications. I checked our error logs, and sure enough there was an error - an SQL error: SQL0117 - Statement contains wrong number of values. This server happens to be an old ColdFusion server still running ODBC. We're in the process of upgrading t to MX, but that's another story. Anyhow, at first, I was puzzled by the error as we hadn't made any changes to the code in quite some time. Then it hit me. Earlier in the day, we added a new column to one of the database tables used by the application in order to facilitate the upgrade. Harmless enough, right? Wrong.

Turns out the application in question had an SQL insert that looked something like this:

INSER INTO myTable VALUES('#foo#', goo, '#boo#')

Now granted this code was written about 6 years ago, but the developer who wrote it took a shortcut that came back to bite us. Instead of explicitly declaring the fields to be inserted, he or she simply expressed the values to insert. This only works when the number of values exactly matches the number of fields in the database table. In my case, the extra field we added caused the application to break. Now granted this is something that could have been caught in testing/qa, but we didn't have that luxury in this particular instance.

The moral of the story here is that in order to future proof your application, it's best to always declare the columns you intend to insert:

INSER INTO myTable (foo, goo, boo) VALUES('#foo#', goo, '#boo#')

IBM released JTOpen 4.3 today. JTOpen is an opensource Type IV JDBC driver for accessing DB2 running on the IBM iSeries midrange computer. Version 4.3 adds several bug fixes, performance enhancements, etc.

Although this isn't really *new* (the driver was released about a month ago), I never got around to blogging it. For those of you who connect ot an AS400/iSeries with CFMX, you might be interested to know that IBM released version 4.2 of JTOpen, their open source JDBC driver for DB2/400. You can download the driver here.

A fellow ColdFusion developer is having database connectivity issues connecting CFMX 6.1 to DB2 on an IBM OS/390. I was wondering if any of you other ColdFusion developers out there were connecting to that platform and could offer up some help as far as what drivers you use, connection strings, and any other advice. Contact me via the comments or at rbils@amkor.com if you can help.

I recently setup a new development box running multiple instances of ColdFusion MX 6.1. I posted a question to the CFGURU mailing list asking about the best place to put my 3rd party JDBC driver. Sean Corfield and Simon Horwith both chimed in that they recommended placing it in:

{jrun.home}/servers/lib/

In my previous non J2EE installations of ColdFusion, I always placed my driver in c:\jt400\jtopen\bin. Whenever I setup a new server, I also had to add this location to the JAva CLASSPATH in the ColdFusion Administrator. What's my point? Well, using Sean/Simon's location, I no longer have to add the CLASSPATH to my ColdFusion server as the location is already known to the JRun/ColdFusion server. Very convenient!

Also for you iSeries fans out there, a new article by Jeremy Lyon is in this month's ColdFusion Developer Journal. The article, "MX to iSeries Demystified - A world-class database platform paired up with a world-class Web application server" gives some decent information on using CFMX to query data from DB2 on that platform.

A problem that's been plaguing a lot of us that use CF MX and IBM's JTOpen JDBC driver to connect to DB2 running on IBM's iSeries (AS400) has to do with CF MX hanging when a bad username or password is passed to the database. It's really frustrating on so many levels, especially since the only way to free the hang is to restart the CFMX service. Sometimes even this isn't enough, and the server must eb rebooted. Obviously, this isn't good in a production environment.

Jeremy Lyon emailed me today to let me know that there's a JDBC URL parameter that you can set to fix this problem. Basically, there's a parameter called prompt. The JavaDoc definition for prompt is:

"Specifies whether the user should be prompted if a user name or password is needed to connect to the server. If a connection can not be made without prompting the user, and this property is set to "false", then an attempt to connect will fail."

What this means is that by default, prompt is set to True. If you pass a bad username or password, the system attempts to "prompt" the user for a username/password. Since you aren't using an interactive application to make your connection, you never see this prompt, and the system "hangs" indefinitely.

The fix is to ALWAYS specify prompt=false in your JDBC url. This way, id a bad username/password is passed, the JDBC driver will report this back to ColdFuion, and an exception can be thrown.

After a nice long week off, I'm back from vacation. Man, I can't wait for retirement ;-) Of more important note is the release of JTOpen 4.1 by IBM (an open source JDBC driver for iSeries [AS400] DB2). This is basically a maintenance release that fixes all sorts of minor issues, as well as adds some enhancements to the driver. You can download it from IBM. I've already installed it on two machines, and it's been working for a few hours on a rather large application with no apparent issues.

I've often found the ODBC/JDBC error messages returned by DB2/400 to be less than helpful. While reading the JavaDoc for the JTOpen 4.0 driver today, I came across a property I hadn't noticed before. Apparently, you can instruct the driver whether to return a full or basic error description when an error occurs. Of course, the default value is basic. So, if you use JTopen with ColdFusion MX to connect to DB2 on an AS400, you can cause the driver to return the full error (often along with a fix recommendation) by adding errors=full to the JDBC URL you setup in the ColdFusion Administrator.

Stay tuned for more JTOpen/CF MX tidbits as I try to figure out why DB2/400 seems to perform like an old dog when compared to the likes of SQL Server and Oracle (Solaris). If you're currently a JTOpen user, or connect ColdFusion to DB2/400 in another way, I'd love to hear your comments!

IBM just released version 4.0 of their open source JDBC (TYPE IV) driver for DB2/400. I've been using JTOpen since CFMX was in beta, and have been generally happy with the results (sans a few bugs). This release boasts dozens of fixes and improvements. I plan to install and test the new driver next week to see how it performs.

If you connect to DB2/400 from CF MX, I highly recommend you check out JTOpen. You can download the latest build here:

http://www-124.ibm.com/developerworks/downloads/index.php?group_id=29

IBM recently released JTOpen 3.3, an open source JDBC driver for connecting ColdFusion to DB2/400. Believe it or not, I'm not the only person out there using DB2/400 as a backend database. Anyhow, you can download the latest version from http://oss.software.ibm.com/developerworks/opensource/jt400/downloads.html. Note that registration is required.

Well, after another day of experimenting, it looks like the empty recordset I described on 9/26/2002 is a problem with the 3.2 version of JTOpen. The issue only happens during certain complex SP operations, and shouldn't cause problems for relatively simple SPs (selects, selects with multiple joins, etc.). I've posted the problem to the JTOpen project site and will blog another update as soon as I have more info.

If you use CFMX to connect to DB2 on an AS400, you might be interested to know that IBM recently released a new version of the Open Source JTOpen JDBC driver. I have been using version 3.1 for a while now and it's been working fairly well (except for an issue when I run a SP that pulls back no records, the query object that gets created doesn't contain any of the column headers or the recordcount variable, but I'm not sure if this is a driver or a CF issue). I haven't tried 3.2 yet, but I'm planning to install it soon and give it a whirl. If you want JTOpen, you can get it here (registration required).