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)>
Notice I pass ds.getName() to load the datasource name. My datasource.xml looks like so:
<?xml version="1.0" encoding="UTF-8"?>
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.