<!---
Title: DNS-less connection to MySQL 2
Developer: D'ontreye Nero
Company: TruElement Designs, LLC (http://truelement.com)
Date: 12/8/2005
Description: UDF used to connect directly to a mySQL server bypassing DSN usage.
--->

Place this function within reach of any page you wish to use the mySQL() function on. Example: "application.cfm" There are six arguments that must be present for this function to operate correctly. They can be placed in the "default" attribute of the corresponding cfargument tag or passed through the function as a parameter.

If passed in as a parameter, values will override defaults.

1) sql: Valid mySQL statement (required)
2) query_name: Name of query. [Same as name attribute of <cfquery>]
3) JDBC_URL: JDBC URL of the mySQL server to connect to. Example: jdbc:mysql://127.0.0.1/myDatabase
4) mySQL_username: mySQL username to connect to database with
5) mySQL_password: mySQL password to connect to database with
6) driver: Driver to use when connecting to server

<cffunction name="mySQL" description="Connects directly to a mySQL server and processes SQL">
    <cfargument name="sql" required="yes">
    <cfargument name="query_name" default="mySQL">
    <cfargument name="JDBC_URL" default="jdbc:mysql://[IP address OR servername]/YOUR_DB_NAME">
    <cfargument name="mySQL_username" default="YOUR_USERNAME">
    <cfargument name="mySQL_password" default="YOUR_PASSWORD">
    <cfargument name="driver" default="org.gjt.mm.mysql.Driver">
    <cfscript>
        class = createObject("java", "java.lang.Class"); class.forName(driver);
        dm = createObject("java","java.sql.DriverManager");
        con = dm.getConnection("#JDBC_URL#","#mySQL_username#","#mySQL_password#");
        st = con.createStatement();
        if ( left(trim(sql), 6 ) is 'select'){//select statements
            resultSet = st.executeQuery(sql);
            "#query_name#" = CreateObject("java", "coldfusion.sql.QueryTable").init(resultSet);
            return "#query_name#";
            resultSet.close();
        }
        else st.executeUpdate(sql);//insert, update, & delete statements
        st.close();
        con.close();
    </cfscript>
</cffunction>

======================================================

Run mySQL() wherever you would usually execute an SQL statement.

Here are some easy examples of how to utilize mySQL().

1) Using only required parameters [sql].

Default attributes of <cfargument> MUST be valid in mySQL() for this method to work.

(a and b produce identical results)

a. mySQL() is placed directly inside of the <cfoutput> query attribute using only the [sql] parameter erasing the need for <cfquery>.

<cfoutput query="#mySQL('SELECT * FROM users')#">
   #firstName# #lastName#<br />
</cfoutput>

b. mySQL() is placed in a <cfset>. This method is effective when using single quotes in your SQL statement (note the use of double quotes).

<cfset mySQL("SELECT * FROM users")>
<cfoutput query="mySQL">
    #firstName# #lastName#<br />
</cfoutput>

2) Using additional parameters that override default settings.

Additional parameters MUST be used in this order: mySQL("sql"[,"query_name"][,"JDBC_URL"][,"mySQL_username"][,"mySQL_password"][,"driver"])

To change this order, <cfargument>'s must be rearranged in the <cffunction>.

All parameters used must have preceding parameters included.

a. mySQL() containing all parameters. query_name, JDBC_URL, mySQL_username, mySQL_password, and driver override defaults set in <cfargument>.

<cfset mySQL("SELECT * FROM users","getUsers","jdbc:mysql://23.9.28.82/myDatabase","root","myPassword","my.special.Driver"])>
<cfoutput query="getUsers">
   #firstName# #lastName#<br />
</cfoutput>

b. mySQL() with JDBC URL override. query_name parameter MUST be provided to keep parameters in order.

<cfset mySQL("SELECT * FROM users","getUsers","jdbc:mysql://mysql.server.com/myDatabase")>
<cfoutput query="mySQL">
   #firstName# #lastName#<br />
</cfoutput>