<!---
Developer: D'ontreye Nero
Date: 9/16/2005 10:00 AM
Description: UDF used to connect directly to a mySQL server; bypassing a DSN
--->
Place this function within reach of any page you wish to use the mySQL() function on. Example: application.cfm
<cffunction name="mySQL" description="Connects to a mySQL server. No DNS needed">
<cfargument name="JDBC_URL" required="yes" hint="JDBC URL of the mySQL server to connect to. With or without the leading 'jdbc:mysql://'">
<cfargument name="username" required="yes" hint="mySQL username">
<cfargument name="password" required="yes" hint="mySQL password">
<cfargument name="sql" required="yes" hint="SQL to process">
<cfargument name="queryName" default="mysql" hint="Name of the query results (optional); Defaulted to 'mysql'">
<cfargument name="driver" default="org.gjt.mm.mysql.Driver" hint="Driver to use when connecting to server (optional)">
<cfscript>
class = createObject("java", "java.lang.Class");
class.forName(driver);
dm = createObject("java","java.sql.DriverManager");
con = dm.getConnection("#iif(JDBC_URL contains 'jdbc:mysql://',de(JDBC_URL),de('jdbc:mysql://'&JDBC_URL))#","#username#","#password#");
st = con.createStatement();
resultSet = st.executeQuery(sql);
"#queryName#" = CreateObject("java", "coldfusion.sql.QueryTable").init(resultSet);
resultSet.close(); st.close(); con.close();
return "#queryName#";
</cfscript>
</cffunction>
All you need to do is run mySQL() wherever you would usually need a query variable.
Example:
<!--- mySQL('path_to_database','username','password','sql'[,'query_name'][,'driver'] --->
There are two easy ways to utilize mySQL().
| 1) | <cfoutput query="#mySQL('localhost/myDB','root','pword','SELECT * FROM users')#"> #firstName#<br /> </cfoutput> |
| 2) | <cfset mySQL('localhost/myDB','root','pword','SELECT * FROM users','myResults')> <cfoutput query="myResults"> |
Both ways have the same effect.
-
DNS-less connection to MySQL
Ever wondered if there was a way to bypass setting up a datasource for every mySQL database? This tutorial shows you a simple way to connect to the mySQL server, directly from your page, using a small cffunction.
Author: Dontreye Nero
Views: 14,312
Posted Date: Friday, September 16, 2005 -
DNS-less connection to MySQL (Part 2)
Ever wondered if there was a way to bypass setting up a datasource for every mySQL database connection? This tutorial shows you a simple way to connect directly to the mySQL server from your page using a small cffunction. Features examples, as well as support for inserts, updates, and deletes.
Author: Dontreye Nero
Views: 8,651
Posted Date: Thursday, December 8, 2005
 |
