ColdFusion MX uses JDBC (Java Database Connectivity) as its native data source type. ColdFusion MX adds connection pooling and other enhancements to standard JDBC connections. At times it is useful to bypass these ColdFusion additions to test basic connectivity to a database.

The cfobject tag or the createobject function can be used to create a basic JDBC data source connection. This connection can verify if you are able to connect to a database outside of using standard ColdFusion data sources. It also provides a mechanism for creating result sets outside the standard ColdFusion query mechanism. These methods provide an alternative if standard methods fail or can be used as a troubleshooting step to gather information on a problematic database connection.

A JDBC connection usually requires two attributes, the JDBC URL and JDBC Classname. The format for these attributes is driver specific and supplied by the driver vendor. Check your driver documentation for this information if you are not using a Macromedia supplied driver. The following table gives URL and Classnames for the drivers shipped with ColdFusion MX.

Database Class and Driver information
MS SQL Server Class macromedia.jdbc.MacromediaDriver
Driver jdbc:macromedia:sqlserver://[host]: [port];databaseName=[database];SelectMethod= [selectmethod];sendStringParametersAsUnicode= [sendStringParametersAsUnicode]
Oracle (Macromedia) Class macromedia.jdbc.MacromediaDriver
Driver jdbc:macromedia:oracle://[host]:[port];SID= [sid];sendStringParametersAsUnicode= [sendStringParametersAsUnicode]
Oracle (Oracle 9.1 thin) Class oracle.jdbc.driver.OracleDriver
Driver jdbc:oracle:thin@[host]:[port]:[sid]:[args]
Sybase Class macromedia.jdbc.MacromediaDriver
Driver jdbc:macromedia:sybase://[host]:[port];DatabaseName= [database];SelectMethod= [selectmethod];sendStringParametersAsUnicode= [sendStringParametersAsUnicode]
MySQL Class org.gjt.mm.mysql.Driver
Driver jdbc:mysql://[host]:[port]/[database]?[args]
MS Access Class macromedia.jdbc.MacromediaDriver
Driver jdbc:sequelink:msaccess://[host]: [19998];serverDatasource=[datasource]
ODBC Socket Class macromedia.jdbc.MacromediaDriver
Driver jdbc:sequelink:odbcsocket://[host]: [19998];serverDatasource=[ODBC datasource]
Sun JDBC-ODBC Bridge Class sun.jdbc.odbc.JdbcOdbcDriver
Driver jdbc:odbc:[datasource];[UID=username]; [PWD=password];[args]

Insert the class name from this table into the Class.forName method in the CFML example provided. Insert the appropriate JDBC URL from the table into the Driver.getConnection method. If your connection or SQL statement fails, you will see a cfcatch structure dumped which should assist in troubleshooting. If the statement succeeds your will see your query object dumped. It is not recommended that you use cfdump on queries returning more than a few dozen rows given the resources used.

Download the jdbctest.zip file (3K). This file contains two sample templates, testJDBC.cfm and testJDBCTag.cfm. These templates have the same logic, but testJDBC.cfm does most of its work insidecfscript tags whereas testJDBCTag.cfm is mostly tag-based. Review the templates. To use the sample templates, update the data source attribute at the top of the file and then enter the proper JDBC class and URL for the driver you wish to use.

This work is licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License  Twitter™ and Facebook posts are not covered under the terms of Creative Commons.

Legal Notices   |   Online Privacy Policy