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 = "">
&lt;cfquery name="foo" datasource="#ds.getName()#"&gt;
select	sum(rabbits) as total
from	huntinglog
where	club_no = &lt;cfqueryparam cfsqltype="cf_sql_varchar" value="#getId()#"&gt;
&lt;/cfquery&gt;
&lt;cfreturn val(foo.total)&gt;

</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.