Quick Transfer Tip

This post is more than 2 years old.

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;cfreturn val(foo.total)&gt;


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.

Raymond Camden's Picture

About Raymond Camden

Raymond is a senior developer evangelist for Adobe. He focuses on document services, JavaScript, and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support. You can even buy me a coffee!

Lafayette, LA https://www.raymondcamden.com