ColdFusion in Context: Determine Your Database Engine

SQL is somewhat standard across database engines. However, there are differences that crop up even with common operations such as a search for data similar to a given input. Some query constructs will work with one database engine but not another. Sometimes in collaboration with other individuals, you may have several database engines that are used for development and one that is used for production. So how do you tell your ColdFusion code which database engine is being used in order to have it supply the correct syntax for a given database when those exceptions occur? Are you using MySQL? Microsoft SQL? Microsoft Access? This tip will show a way.

Consider Harmless Questions

The key is to try a harmless operation that works on one database but not another and learn from its success or failure. For example, you can ask MySQL to list current variables. You can declare variables in Microsoft SQL (MSSQL). Just try it.

Code

Put this code in SqlEngine.cfm after changing the datasource to one that's on your machine. Start by assuming that MySQL is in use. Within a try-catch combination, ask the database to "show variables". If this fails, assume that MSSQL is in use. Then within another try-catch combination, try to define a variable. If this fails, assume that Access is in use. 

<cfset SqlEngine="MySQL">
<cftry>
	<cfquery name="checkMySql" datasource="context">
	show variables
	</cfquery>
	<cfcatch>
		<cfset SqlEngine="MSSQL">
	</cfcatch>
</cftry>
<cfif SqlEngine is "MSSQL">
	<cftry>
  		<cfquery name="checkEngine" datasource="context">
		declare @x int
		</cfquery>
		<cfcatch>
			<cfset SqlEngine="Access">
		</cfcatch>
	</cftry>
</cfif>
<cfoutput>The DB engine in use is #SqlEngine#</cfoutput>

Try it; Integrate it

Browse SqlEngine.cfm. If you've named a valid datasource and are using one of these engines, you'll get a valid answer.

To integrate this with your code, have it check the engine at login and then store the result for later use. You might put the answer in a table you plan to read every time (such as in a table for client variables). (For a simple query, you don't need to know which engine is in use, so storing this information in a database isn't necessarily dumb.) You might store it in a memory variable and refresh it if the variable doesn't exist.

I store it in a session variable; because, I copy all session variables to request variables at the beginning of each page anyway. Strictly speaking, it would be more logical (if less handy for me) to store it in the application or server scope.

The important thing is to make the check, store the result, and use it when needed. =Marty=