MySQL Datasources - One thing leads to another ..

After installing CFEclipse, my next step was to create a few datasources. I have several databases lying around but for now I started with MySQL.

It turns out that setting up the MySQL datasource was an interesting experience for me. Not only because it was my first exposure to installing JDBC drivers for ColdFusion. I also learned a bit about MD5 checksums and cryptographic signatures in the process.

Since I am using Colfusion MX 7 and MySQL 5, I first read the instructions on the Adobe site Configuring a JDBC Driver for MySQL 4.1 or 5 for use with ColdFusion MX . Knowing I needed to download an updated JDBC driver first, I went the MySQL downloads section. There were several versions of the MySQL Connector J driver available. I decided to download a version 5.x driver, since the TechNote mentioned an incompatibility with MX7 and some of the 3.1.1x versions.

MD5 checksums and Signatures oh my!

On the download page I noticed two things: an MD5 value and Signature. Now I had heard of both, but must admit I had never used either. Thankfully the MySQL site provides a good description of how you can use the MD5 checksums and GnuPG signatures to verify the integrity of the packages you download. Since I am a curious type I decided to check both.

First I downloaded the GNU Core Utilities package for Windows after reading a helpful article on Peter Leung's blog mentioning the md5sum utility included in that package. Using the command line utility, I quickly verified the MD5 checksum of the MySQL download

  c:\download> md5sum c:\download\mysql-connector-java-5.0.7.zip \b7c9d9a274aa93af3d9111be567a5696 *c:\\download\\mysql-connector-java-5.0.7.zip

GnuPG (Oh great.. another acronym)

Next up was downloading GnuPG (GNU Privacy Guard) so I could verify the cryptographic signature. I started downloading the file when I noticed the checksum value listed was SHA-1 (not MD5). Fortunately the GNU Core Utilities package includes a utility for this too. Using sha1sum.exe I was able to verify the GnuPG file checksum.

   c:\download>sha1sum c:\download\gnupg-w32cli-1.4.7.exe\b806e8789c93dc6d08b129170d6beb9e1a5ae68f *c:\\download\\gnupg-w32cli-1.4.7.exe

After installing GnuPG, I tried to run gpg.exe from the command line, but got the infamous "not recognized as a command" error.

  c:\download> gpg
'gpg' is not recognized as an internal or external command, operable program or batch file.

I checked the README-W32.txt file and it confirmed the installer does not change the PATH environment variable. As I am lazy, I manually added the installation directory (c:\program files\GNU\GNUPG) to my PATH environment variable. That way I can run gpg.exe from the command line without having to type out the full path to the .exe every time I use it.

Next I copied the MySQL build key from the download site and saved it to a file: c:\download\mysql_pubkey.asc. Then I fired up another command prompt window and imported the key

  c:\download> gpg --import mysql_pubkey.asc

Next I downloaded the driver jar signature and saved it to: c:\mysql-connector-java-5.0.7.zip.asc. Then verified the signature with gpg. As you can see the verification was successful.

  c:\download> gpg --verify mysql-connector-java-5.0.7.zip.asc
    gpg: Signature made 07/18/07 14:31:37 using DSA key ID 5072E1F5
    gpg: Good signature from "MySQL Package signing key (www.mysql.com) <build@mysql com="">

Wow, I have learned all this great stuff and have not even created the datasource yet.

Creating a MySQL Datasource (Finally!)

Now that I had verified all of my download files, it was time to install the driver. I unzipped the archive and immediately noticed more files than I was expecting



But I did not panic. The TechNote said to look for a file name like: mysql-connector-java-3.{n}-bin.jar. Since I had downloaded a version 5.x driver, my jar happened to be named mysql-connector-java-5.0.7-bin.jar. So I copied it into the cf_root/WEB-INF/lib directory and restarted ColdFusion. After the server restarted, I created my datasource according to the instructions. Here are the settings I used

Datasource Name:     MySQL5DSN 
Driver:              Other
JDBC URL:            jdbc:mysql://127.0.0.1:3306/sakila
Driver Class:        com.mysql.jdbc.Driver
Driver Name:         com.mysql.jdbc.Driver
User Name:           testUser
Password:            ********

Note, my database is running on port number 3306 and the database name is "sakila"

After entering the settings, I clicked the submit button and received this lovely error



Turns out the MySQL service was not running (duh!). After starting the service, and configuring my firewall to allow communication between CF and MySQL, the datasource verified successfully.

The only thing left to do was create a test page in CFEclipse. I ran it and eureka, it works!



I know some of this has nothing to do with either ColdFusion or MySQL, but I found it interesting and hope some of you might as well. As always, comments/suggestions/corrections are welcome.

... we now return you to your regularly scheduled ColdFusion programming.

...Read More

MySQL: You've come a long way baby

I think I downloaded my first version of the MySQL database a few years ago. Just to mess around with in my spare time. I probably downloaded a GUI as well, but do not recall being very impressed with it. Evidenced by the fact that its not still installed on my computer and I have little to no memory of it.

Well, there have been many improvements since then. Over the years I upgraded to MySQL 4 then 5. But in all that time I never got around to downloading a GUI client. Yep .. I have been using the command line client all this time. Say what you will, but it does force you to learn commands and syntax, in a way that a GUI does not.

Yesterday I downloaded the MySQL GUI Tools Bundle for 5.0. It consists of the MySQL Administrator, Query Browser, System Tray Monitor and Migration Toolkit. The install was fast and I was connected within minutes. I have to say I was impressed when I opened up the query browser. Its sleek, fast and has some great features. I am sure it is obvious by now I that I do not usually use drag & drop for building queries (and before you ask, yes I actually have used a GUI for other databases, just not MySQL) Despite my command line bias, I was impressed with the visual query building features.

Drag a table from the Object browser to one of the buttons beneath the Query Area and MySQL automatically generates the base sql statement. Drag another related table onto the "Join Table(s)" button and the query browser will automatically generate the JOIN code on the related columns.



Drag a table or view onto a Resultset tab and the data is automatically displayed in the Resulset window. You can make modifications directly in the Resultset grid. Changed values are highlighted and committing or rolling back the changes is as simple as clicking the apply or discard button.

The Export Resultset feature is a real time-saver. I was pleased to see it supports CSV, HTML, XML, Excel and PList format. Though I think the Excel format is simply XML and is supported for XP and higher.

The query browser also provides create procedure / function templates.



All in all some great features and that is without mentioning the Administrator, System Tray Monitor or the MySQL Workbench tool for data modeling, which is available as a separate download.

As I shutdown my computer last night I thought MySQL, "you've come a long way baby!"

...Read More

HouseOfFusion.com is my favorite comedy club

www.houseoffusion.com is a great resource for ColdFusion questions. You can find everything from beginners to experienced programmers on the various lists. But some days I go there just for the comedy. My highly unofficial award for best line of today goes to Dave Watts:

"It sounds like you live in a Dilbert strip, or, alternatively, hell."
http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:53671#289910

...Read More

Firefox 2.0.0.7 has issues with CODE tags?

I'm using Firefox 2.0.0.7 and I love it. But I think I may have discovered a "feature" today. At least I think its a Firefox problem.

When reading blogs I often like to copy and paste the code samples to test them out. But I've noticed when I paste some of the code samples the line breaks are gone. So the result is one long unreadable line of text.

As far as I can tell it only seems to happen with pages using <code> tags, such as the CFCUnit documentation

If the code is inside <pre> tags, the line breaks are preserved. For example the CFUnit primer uses <pre> tags.


Anyone else notice this problem?

...Read More