I’ve had to battle with this recently, but this technique seems to work for me. Here we go…
Firstly, Coldfusion 7 needs an extra driver (this is fixed in CF8) for MySQL 4/5 -(3 is ok though)
To connect to a MySQL 4.1 or 5 database from ColdFusion MX:
- Download a current, stable version of the MySQL Connector J JDBC driver, available at: http://dev.mysql.com/downloads/
Note: Connector/J version 3.1.11 and higher cannot be used with ColdFusion MX 7 because of MySQL bug 13469. Connector/J 3.1.10 should be used instead for ColdFusion MX 7 until this MySQL bug is fixed. All Connector/J version 3.0 releases and all version 3.2 releases work with ColdFusion MX 7.
- Extract the mysql-connector-java-3.{n}-bin.jar file from the downloaded archive file.
- Save the mysql-connector-java-3.{n}-bin.jar file in thecf_root/WEB-INF/lib directory.
- Restart the ColdFusion MX server.
- Add a new data source to the ColdFusion MX Administrator, using the driver option Other
- Enter the JDBC URL:
jdbc:mysql://[host]:[port]/[database]?useUnicode=true&characterEncoding=UTF8
i.e for a local installation
jdbc:mysql://localhost:3306/playpen?useUnicode=true&characterEncoding=UTF8
NOTE – this is case sensitive!
- Next, enter the driver class
com.mysql.jdbc.Driver
- Complete username/password, and adjust other data source settings, if needed.
- Submit the data source for verification.
- Remember, you’ll have to set up the user’s access rights via Navicat or some other program!