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 Start

First 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 Installation

We 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

Starting nmap 3.81 ( http://www.insecure.org/nmap/ ) at 2006-01-14 11:22 CET
Interesting ports on localhost.localdomain (127.0.0.1):
(The 1654 ports scanned but not shown below are in state: closed)
PORT     STATE SERVICE
21/tcp   open  ftp
22/tcp   open  ssh
25/tcp   open  smtp
53/tcp   open  domain
80/tcp   open  http
110/tcp  open  pop3
143/tcp  open  imap
953/tcp  open  rndc
3306/tcp open  mysql

Nmap finished: 1 IP address (1 host up) scanned in 0.809 seconds
debian:~# 
debian:~# netstat -a | grep mysql 
tcp        0      0 localhost.localdo:mysql *:*                     LISTEN     
unix  2      [ ACC ]     STREAM     LISTENING     4245     /var/run/mysqld/mysqld.sock
debian:~#

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 Connection

As 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 Database

I create a sample MySQL database "unicodetest" with the following script.

use unicodetest;

CREATE TABLE `turkce` (
  `id` int(4) NOT NULL auto_increment,
  `testfield` varchar(50) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert  into turkce values 
(1, 'Türkçe Test ğĞ üÜ ıİ şŞ öÖ çÇ'), 
(2, '이것은 한국어 이다');

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 Definition

Now 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.

CF_DB

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

jdbc:mysql://127.0.0.1:3306/unicodetest?useUnicode=true&characterEncoding=UTF-8

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">
<strong>Unicode Test</strong>
<br />
<br />
<cfoutput>
<a href="#script_name#">List</a>
<br />
<br />
<form action="#script_name#" method="post">
	<input name="testfield" type="text" value="Türkçe Test ğĞ üÜ ıİ şŞ öÖ çÇ" />
	<br />
	<input name="AddNewRecord" type="submit" value="Add" />
</form>
</cfoutput>
<cfif IsDefined("AddNewRecord")>
	<cfquery name="AddNewRecord" datasource="#request.mydsn#">
		INSERT INTO	turkce
			(testfield)
		VALUES
			('#trim(testfield)#')
	</cfquery>
</cfif>
<br />
<br />
<cfquery name="GetResults" datasource="#request.mydsn#">
	SELECT	id, testfield
	FROM	turkce
</cfquery>
<cfoutput query="GetResults">
	#testfield# <br />
</cfoutput>

If you have any question, please do not hesitate to contact with me via "howto@demirkapi.net".