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:
1 <cffunction name="getFoo" access="public" returnType="numeric" output="false">
2 <cfset var ds = getDatasource()>
3 <cfset var foo = "">
4
5 <cfquery name="foo" datasource="#ds.getName()#">
6 select sum(rabbits) as total
7 from huntinglog
8 where club_no = <cfqueryparam cfsqltype="cf_sql_varchar" value="#getId()#">
9 </cfquery>
10 <cfreturn val(foo.total)>
11
12 </cffunction>
Notice I pass ds.getName() to load the datasource name. My datasource.xml looks like so:
2 <cfset var ds = getDatasource()>
3 <cfset var foo = "">
4
5 <cfquery name="foo" datasource="#ds.getName()#">
6 select sum(rabbits) as total
7 from huntinglog
8 where club_no = <cfqueryparam cfsqltype="cf_sql_varchar" value="#getId()#">
9 </cfquery>
10 <cfreturn val(foo.total)>
11
12 </cffunction>
1 <?xml version="1.0" encoding="UTF-8"?>
2 <datasource>
3 <name>romulanale</name>
4 <username></username>
5 <password></password>
6 </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:
2 <datasource>
3 <name>romulanale</name>
4 <username></username>
5 <password></password>
6 </datasource>
1 <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.
Comment 1 written by Justin Johnson on 19 March 2009, at 9:03 AM
Comment 2 written by Will B. on 19 March 2009, at 9:42 AM
@Ray: Sometimes it's not really necessary to var out the extra variable. Just put datasource="#getDatasource().getName()#" user="#getDatasource().getUser()#" etc. Then you can create a snippet in Eclipse to quickstart your queries, and not have to have that extra command since you'll likely only use it for the query itself 99% of the time.
Comment 3 written by Phillip Senn on 19 March 2009, at 5:34 PM
If so, and if they could guess your server, then they could use management studio to connect to your database.
Comment 4 written by Will B. on 19 March 2009, at 5:37 PM
The trick is to make your config.xml be config.xml.cfm. You read it like a normal xml file, it's just text. But you put <cfabort /> in an application.cfm in the same folder...voila..untouchable.
Comment 5 written by Phillip Senn on 19 March 2009, at 5:45 PM
I've seen config.xml.cfm before with a cfabort at the top.
I seen a peanut stand, heard a rubber band.
But I ain't never seen a config.xml.cfm with an application.cfm! That's kinda of clever.
http://lyricsplayground.com/alpha/songs/w/whenisee...