DSN-Less Database Connection to MySQL in Coldfusion

Have not tried this myself yet, but wanted to drop it here so we can play with it in the future.

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>

Image Expired: Please Refresh The Page to View This Image

We've run into this problem before and so I've stolen the solution from the cfchart blogspot blog site:

Problem

The following message is shown instead of the graphs:
"Image Expired
Please refresh the page
To view the image"


Lot of people have faced this problem, the reason is the timeout in the caching engine of the Coldfusion keeps the charts for any request only for a default value of 5 seconds.
We missed providing an interface to change this value in the CF Admin, but you can still change this value by:

1. Stop the CF server.
2. Open <cf install path>\lib\webcharts3d.xml
3. You can increase the timeout for keeping the graphs in the cache by editing the minTimeout and maxTimeout attributes:

<?xml version="1.0" encoding="UTF-8"?>
<server image="PNG" cache="Memory" minTimeout="5000" maxTimeout="30000"....

change this to whatever values you want.(Values of minTimeout and maxTimeout are in milliseconds.)

The minTimeout and maxTimeout are per each request, i.e. the graphing cache will keep the graphs for each request for minTimeout amount of time. So if there are 4 graphs in one page then request for the page arrives at lets say 1'o clock and the min timeout is 5 secs, then each graph for the request will be kept only in cache for 1'o clock + time to generate graph + minTimeout. So a problem could be if the last graph takes lets say 10 secs to generate, then by the time it is ready all the previous graphs will timeout and will be removed from the cached.

This can create a problem when:

1. There are a large number of charts one page (> 200). In this case, some charts will be drawn but remaining charts will display the error message


2. There are only a few charts on a page but some charts take more than minTimeOut amount of time to generate in this case the remaining charts will not be generated and the error message will be thrown.