ColdFusion 7.x & MySQL 4.1.x Connection

Want to support HowtoForge? Become a subscriber!
 
Submitted by demirkapi (Contact Author) (Forums) on Sun, 2006-01-29 23:05. :: Other
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".
About the author
 
Oğuz Demirkapı is a a Senior Application Developer at TeraTech. He started developing CGI web applications in 1994 and has been developing ColdFusion since 1997. He has either been CTOs or Senior Developer of many well known commercial and blue chip companies both in Turkey and Germany. He ran the CFTR – ColdFusion Turkey User Group for six years before moving to the US. He is a well known ColdFusion evangelist and has spoken at many user group meetings in Europe. His main areas of interest lie in ColdFusion, Flex, Frameworks technologies and L10N & i18N, CMS, portal, e-commerce solutions.
 

Please do not use the comment function to ask for help! If you need help, please use our forum.
Comments will be published after administrator approval.
Submitted by markthickpaddy (registered user) on Wed, 2006-12-06 12:50.

Thanks for the tutorial, I couldn't remember the syntax and found this after a quick google. Everything is working fine for me now.

One minor thing though - I'm running CFMX 6.1 on Linux and adding the connection string to the JDBC URL didn't work for me, but adding it to the connection string field in the advanced settings did. Might be worth changing your Linux note to say that you "may have to define" the JDBC URL with a query string rather than "must define".

Submitted by Anonymous (not registered) on Sat, 2006-06-24 09:12.
Only i cant see the 3 picture about the connector settings in de CF admin.
Submitted by Anonymous (not registered) on Wed, 2006-06-21 05:39.

Make sure you set your "driver class" field for the data source to:
com.mysql.jdbc.Driver