ColdFusion 7.x & MySQL 4.1.x Connection

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".
Original version: http://demirkapi.net/tutorials/coldfusion_mysql.cfm
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.
 

Copyright (c)
GNU Free Documentation LicenseA copy of the licenseGNU Free Documentation License

Copyright (c) 2006 Oğuz Demirkapı
Permission is granted to copy, distribute and/or modify the content of
this page under the terms of the GNU Free Documentation License, Version 1.2
or any later version published by the Free Software Foundation; with no
Invariant Sections, no Front-Cover Texts, and no Back-Cover Texts.
A copy of the license is available at http://www.gnu.org/licenses/fdl.html
add comment | view as pdf view as pdf | Display a printer-friendly version of this page. print |
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.
Thanks for the tutorial, I
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".

reply | view as pdf view as pdf
Nice work
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.
reply | view as pdf view as pdf
adding the data source - driver class field
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

reply | view as pdf view as pdf