ColdFusion 7.x & MySQL 4.1.x Connection
I will try to explain connecting MySQL from ColdFusion on Linux and Windows platform.
Why This Tutorial?Because MySQL is one of the best known database solution and a good, powerful and free alternative while working for any kind of application.
Before StartFirst we need to install ColdFusion. If you have not tried a ColdFusion installation specially in a Linux box, I would suggest my simple tutorial "ColdFusion 7.x Installation on Debian Sarge (3.1r1) Linux".
MySQL InstallationWe also need to install MySQL server. I would suggest to use MySQL version 4.1.x or a later because of Unicode support. You can download required MySQL server via http://mysql.com site but if you have not installed yet, on a Debian Linux you can use following command to install MySQL.
debian:~# apt-get install mysql-client-4.1 mysql-common-4.1 mysql-server-4.1
You can check MySQL installation by nmap or netstat commands.
debian:~# nmap localhost
As you can see we will use MySQL 4.1.x because there is not any MySQL 5 package for Debian Linux yet. But on other Linux distributions or Windows systems you may prefer to use MySQL 5.x.
JDBC ConnectionAs you know ColdFusion is a Java based application server and we will use JDBC to connect MySQL. In order to connect via JDBC we need to use JDBC connector that we can download from MySQL page.
I prefer to use current recomended version MySQLÂ® Connector/J 3.1.12. You can download "mysql-connector-java-3.1.12.tar.gz" or "mysql-connector-java-3.1.12.zip" file.
We extract this file and find a file as "mysql-connector-java-3.1.12-bin.jar". We need to copy this file into "[cfmx_root]/runtime/lib" directory. I have "E:\CFusionMX7\runtime\lib" on Windows and "opt/coldfusionmx/runtime/lib" on my Linux system.
Of course we also need to restart the ColdFusion server.
Sample MySQL DatabaseI create a sample MySQL database "unicodetest" with the following script.
I will test also Unicode support and just because of that I have created a database which has Unicode as standard charset and some Unicode data to test.
Database DefinitionNow we need to define our new database in ColdFusion administrator.
Open ColdFusion administrator as "http://[machinename]:8500/CFIDE/administrator/index.cfm" and go to "Data Sources" section in menu. As we use MySQL 4.1.x and it is not supported defaul we give a name for new database connection and select "other" and define required fields as below.
BE CAREFUL: This settings are working without any problem on Windows systems BUT if you want to get Unicode support on Linux systems, you must define JDBC URL like below
These settings are working for me but in same cases as server name, instead of "127.0.0.1" you may try to use real server IP.
Do not forget that this cfmx user is not a default user. I have already created this user on MySQL.
After these settings we can submit the form and if everything is ok we can get a success message as "datasource updated successfully."
I want to also put here my database test script which is basic but useful.
<cfset request.mydsn = "unicodetest">
If you have any question, please do not hesitate to contact with me via "email@example.com".
Original version: http://demirkapi.net/tutorials/coldfusion_mysql.cfm