I dont know what this is, but I am participating anyway..
By: Dontreye Nero
<!---
Developer: D'ontreye Nero
Date: 9/16/2005 10:00 AM
Description: UDF used to connect directly to a mySQL server; bypassing a DSN
--->
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.
About This Tutorial
Author: Dontreye Nero
Total Views: 30,541
Submission Date: September 16, 2005
Last Update Date: June 05, 2009
All Tutorials By This Autor: 2
Skill Level: Intermediate
Platforms Tested: CFMX7Total Views: 30,541
Submission Date: September 16, 2005
Last Update Date: June 05, 2009
All Tutorials By This Autor: 2
Discuss This Tutorial
-
-
Thanks for providing such a cool code! Anyway, I've got a question, I tried an sql insert and it's workingn but if I'm using a ' sign, the code blocks. I've tried a #Replace(FORM.textfield, "'", "'")# or #Replace(FORM.textfield, "'", Chr(34))# But nothing does it... Do you have an alternate solution? Thanks for your help, Fred
-
Thank you for giving me that opportunity. I have not looked into the amount of effort required into what I am going to suggest, but here is the idea. 1. To design the function to accept parameters like "name of the db server" and options are: MySQL, PostGreSQL, SQL Server, Oracle. There is a list of dbs that can be accessed in this manner on Macromedia site with their driver names. PostGreSQL driver instructions are found here: http://cfguru.daemon.com.au/archives/000082.html 2. instead of calling the fuction, create a "custom tag" with an end tag, so it looks more like "
SQL here " This will process and create a "dataset" withand then dynamically generate the rest of statments in queryNew function built in function. This way we can write query and access dataset in a conventional manner. _________________________________________________________ Also note that mySQL driver "org.gjt.mm.mysql.Driver" is used to access the versions prior to 4.0, for 4.1 and 5.0 you will need to download the new driver on MySQL site. _________________________________________________________ Do you think this is possible & feasible ? I will be glad to help on this project Anang -
I'm going to begin coding version two of this function soon. I already have some enhancements in mind, and the addition of other db servers may be one of them. If you have any more suggestions, let me know. Thanks
-
Thank you for making a function out of this. I also wanted to add the following to this.... for completeness. There are other database servers that you can access with jdbc directly. The function for Oracle can be eaisly located at http://www.geocities.com/anangphatak (see 1st link). Thanks, Anang
Advertisement
