Using an ORM means never having to write SQL. Well, ok, maybe not. As much as I love Transfer (and am beginning to love Hibernate), there are still times when you have to resort to writing SQL. Here is a quick tip.

Using getDatasource() to get access to the datasource configuration? If so, you may have code that looks like this:

<cffunction name="getFoo" access="public" returnType="numeric" output="false"> <cfset var ds = getDatasource()> <cfset var foo = "">

<cfquery name="foo" datasource="#ds.getName()#"> select sum(rabbits) as total from huntinglog where club_no = <cfqueryparam cfsqltype="cf_sql_varchar" value="#getId()#"> </cfquery> <cfreturn val(foo.total)>

</cffunction>

Notice I pass ds.getName() to load the datasource name. My datasource.xml looks like so:

<?xml version="1.0" encoding="UTF-8"?> <datasource> <name>romulanale</name> <username></username> <password></password> </datasource>

Notice that I did not specify a username/password. But what happens if the production system needs this? It is trivial to supply it in the XML. Transfer will use it. But my query above will fail. Luckily I can just switch to:

<cfquery name="foo" datasource="#ds.getName()#" username="#ds.getUsername()#" password="#ds.getPassword()#">

What's nice is that this works just fine when the username/password values are blank. Now I'm set no matter what.