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.
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”.
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.
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.
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.
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.
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"> <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>