How do I make a connection to a MySQL database using ColdFusion?
The following article explains how to make a connection to a MySQL database using ColdFusion from a Windows server. You have the option of using a DSN-less connection or a DSN connection. Both methods are outlined below.
Note: Regardless of the method used, the MySQL database is stored on a Linux server, which means your SQL statements need to be case sensitive. For example, “SELECT * FROM Table” and “SELECT * FROM table” will be interpreted as two different SQL statements.
In the following examples, please substitute your information where the following data is referenced:
- server: enter the MySQL server that you are assigned to, for example, mysql4.safesecureweb.com
- username: enter the username provided for your database
- password: enter the password provided for your database
- database: enter the database name provided for your database
- DSN: enter the datasource name
DSN-less Connection
A DSN-less connection is made completely through the code, without the need for configuring anything within the ColdFusion Administrator. All of the information for the connection is specified within the code. This includes the driver, servername, databasename, username and password.
To make a DSN-less connection to a MySQL database, the following code snippet can be used:
<cfscript>
classLoader = createObject("java", "java.lang.Class");
classLoader.forName("sun.jdbc.odbc.JdbcOdbcDriver");
dm = createObject("java","java.sql.DriverManager");
con = dm.getConnection("jdbc:odbc:DRIVER={MySQL ODBC 3.51 Driver};
SERVER=server; PORT=3306; DATABASE=database; USER=username; PASSWORD=password;
OPTION=3;");
st = con.createStatement();
rs = st.ExecuteQuery("Select * FROM table");
q = createObject("java", "coldfusion.sql.QueryTable").init(rs);
//the query is stored in the variable q
</cfscript>
DSN Connection
A DSN connection involves first having a DSN configured within the ColdFusion Administrator and then specifying the DSN name within your code. The DSN controls the driver and database used. If you would like to use a DSN connection you will first need to contact us and request a DSN be configured. Make sure to include the name of the database as well as the DSN name you wish to use.
To make a DSN connection to a MySQL database, the following code snippet can be used:
<CFQUERY Name="test" DATASOURCE="DSN" USERNAME="username"
PASSWORD="password">
</CFQUERY>