Web and Mobile Apps Dev

For a long time, I had been searching around online to find a solution to switch to MySQL database for my ColdFusion school server from Microsoft Access database. Since I have no control over the ColdFusion Administration settings, I can’t change the DSN connection.

Recently I have been finishing my remaining undone projects. I use Railo open source CF server and MySQL under Ubuntu to do the assignments for the class. The SQL code I was writing is a bunch of aggregate functions:

SELECT
	   b.BookId,
   	   b.BookTitle,
	   s.SalePrice,
          COUNT(s.SalePrice) AS bcount,
          SUM(sd.TotalSale) AS ts,
          SUM(sd.Quantity) AS qt
FROM tblBooks AS b,
     tblSales AS s,
     tblSalesDetail AS sd
WHERE b.BookId=s.BookId AND s.SaleId=sd.SaleId AND s.SalePrice IS NOT NULL
GROUP BY b.BookTitle
ORDER BY #variables.ob# #URL.s#;

When I was testing these code on the localhost which is MySQL-powered, the page was rendered fine without any errors. However, once I uploaded to the school server which is powered by MS Access, the errors began to appear. Noticeably, I can’t use the name such as b.BookId, b.BookTitle, s.SalePrice without using aggregate functions. It struggled me a bit because the final is due tomorrow.

I then remembered my unsolved solution for connecting MySQL database dynamically through CF code because most of the DSN-less code online only works in CF 5. Once again I began to searching for the solution to release my struggle to the MS Access. Finally, I found a CF custom tag to do just that. It is called query, similar to cfquery. Since it’s a custom tag, the use of it is cf_query. This tag is primary used to connect to the MS SQL server by its author. Fortunately he has given a code snippet to dump out the available JDBC drivers on the server.

After I tested this code, I was surprised to see that my school server supports lots of databases that I don’t know about. I chose the MySQL one and copied its JDBC URL to the CF test page. I noticed that I was unable to connect to my own MySQL server on this web server since my hosting provider doesn’t allow remote connection to MySQL. Therefore I googled for “free mysql hosting” and found a website called FreeSQL.org that specifically for hosting free MySQL databases. I connect to this newly created database by using the cf_query code:

<cf_query jdbcURL="jdbc:mysql://www.freesql.org/ColdFusion" username="username" password="password" qName="qGetData">

Note that ColdFusion is the database name. Within the cf_query beginning and ending tag, I wrote the SQL statements to import the data since the phpMyAdmin link does not work on FreeSQL.org. After that, I replaced the above cf_query code with cfquery in the project that I struggled with.

You can download the above code here along with the custom tag and the original code from its author. The project page that I was struggled with can be found here.