I don’t need to write anything on the benefits of using CFQUERYPARAM, there have been many articles over the years doing just that. Instead I am going to cut to the chase and simply state -

‘If you are using CFQUERY, then you should always use CFQUERYPARAM when passing in typed data, end of story’.

OK, that was a bit short and dull, here are 3 very good reasons:

1. Reduces the risk of SQL injection attacks
2. Makes data more ‘SQL happy’, escapes problematic characters, eg: single quotes, forward slashes etc…
3. Forces you to think about the data being passed into your query, less mistakes made.

Not enough information for you? read this excellent article from Adobe – http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=queryDB_5.html#1142383

If there was anything that could be called a ‘champagne moment’ from the developers at Adobe it was when they came up with the idea of that excellent tag – CFDUMP.
I am sure when this piece of brilliance was brought to life there were many cheers and congratulatory back slapping amongst the developers not to mention the week long party and holiday bonus in the Bahamas (well maybe not that much celebration).

No other language has an easier, more elegant and quicker debugging utility that gives as much detail and information as CFDUMP. It is one of my favorite things about developing in Coldfusion, if only there was some way of getting the same sort of information in a similar manner in Javascript…. well it turns out that there is!

I was wandering around the web when I stumbled across John Bartletts Coldfusion tips n tricks. He has taken some code that essentially dumps a javascript array and given it Coldfusion like formatting. I have often wanted something just like this to save me the hours of developing ways to debug large arrays of javascript data and now here it is –

http://johnwbartlett.com/cf_tipsntricks/index.cfm?TopicID=85

Give it a shot and I am sure you will agree it is a beautiful thing.

I’ve recently started building my first html based Air application using jQuery and have came across this very bizarre image issue. So here’s the scenario, I’ve got a text link with a image background attached with an onClick function, so when a click event occurred, the image background changes to another image. Now on a normal browser that would work fine but when I ran that in an air environment, the second image just wouldn’t load. I’ve double checked the syntax and even tried using a CSS class that contains the background image and using jQuery to add the class to the link upon onClick event. As expected that didn’t work!

After hours of testing and googling, I have came to a conclusion that jQuery can’t dynamically load images in an Air environment. So what I had to do was to pre-load all div elements with all the required classes on the page for the text link and that will pre-load all the images. Therefore when an onClick event occurs, the background images are displayed. Problem fixed!

Though this is not the best solution but developers should be keep this in mind when developing your Air application, because it would seriously affect how you’ll build/structure your application and could potentially save you hours of head scratching and frustration.

I discovered this a bit by accident. I was wondering if it was possible to query 2 MYSQL databases in the one CFQUERY as I had 2 tables that I wanted to join in 2 separate databases and instead of moving the tables in question into the same database I thought I’d have a play… here are the results:

Lets make things nice and simple and call the databases database1 and database2… it’s easier. I should also mention that the 2 databases are located on the same MYSQL server and are accessed with the same user account. I have a Coldfusion datasource connected to database1 called database1connector. Here is the query and the syntax, it’s all about aliases people!

<cfquery name="q" datasource="database1connector">
	SELECT tb1.name,tb2.address
	FROM table1 tb1
	INNER JOIN database2.table2 tb2
		ON tb2.id  = tb1.id
</cfquery>

Note that I include the second database by name in the join clause, simply referring to it by ‘databasename.’ then the table name. Note that I don’t use the ‘databasename.’ notation for the database being accessed via the CF datasource (the first database, ie database1 in the above example) as it will error.

I haven’t tried it but I am guessing that it would be possible to essentially join multiple databases using this same setup, give it a go!

Ever have a lower case string and needed the initial letter of each word to be in capital? well I did and luckily found a nice regular expression via google.

rereplace(“this string is all lowercase”, “(\b\w)”, “\u\1″, “all”) would produce ‘This String Is All Lowercase’

problem solved.

The other day while I was walking to work I found myself day dreaming. As my thoughts wondered I dreamt of a perfect world where you could use regular expressions whenever, wherever you liked.

Once arriving at work I was back to reality not everything in life can be a regular expression!

As I worked away writing some boring queries, I figured I might liven up my day and try using regular expressions to improve a query. After some research, much to my surprise I found that MySQL could handle regular expressions… maybe my day dream was coming true.

In my case I was looping over a list and comparing with another list found within the database, previously I had been using numerous ‘LIKE’ statements to match the two lists (see below)

<cfquery name="qData" datasource="#application.dsn#">
 SELECT *
 FROM tableName
 WHERE status != 'Archive'
 AND
 <cfset iCount = 1 />
 <cfloop list="#arguments.groups#" index="i">
 <cfoutput>
 (availability LIKE '#i#,%' OR availability LIKE '%,#i#' OR availability LIKE '%,#i#,%' OR availability = #i#)
 </cfoutput>
 <cfif iCount LT listLen(arguments.groups)>
 <cfoutput> OR </cfoutput>
 </cfif>
 <cfset iCount = iCount + 1 />
 </cfloop> 

 ORDER BY #arguments.sort# #arguments.sortOrder#

 </cfquery>

After some messing about with regexp (and some help from my boss) I was able to reduce this to a simple regular expression so my query became.


<cfquery name="qData" datasource="#application.dsn#">
 SELECT *
 FROM tableName
  WHERE status != 'Archive'
 AND
 <cfset iCount = 1 />
 <cfloop list="#arguments.groups#" index="i">
 <cfoutput>
 availability REGEXP '[^0-9]*(#i#)[^0-9]*'
 </cfoutput>
 <cfif iCount LT listLen(arguments.groups)>
 <cfoutput> OR </cfoutput>
 </cfif>
 <cfset iCount = iCount + 1 />
 </cfloop> 

 ORDER BY #arguments.sort# #arguments.sortOrder#

 </cfquery>

Next time you are writing a query take a second to ask yourself is this a good time to try out regexp? and hopefully the answer will be yes.

It’s simple and makes a whole bunch of sense, if UUID creation in coldfusion was faster then farcry will be faster given that it seems to use it quite a bit. Unfortunately the native CF UUID creation is really quite slow which is very well documented and does not need anymore coverage, if you want to find out why then use google.

Fortunately the guys at Daemon have decided to make it nice and easy to use their UUID method for generating UUIDs if you are using farcry 5.1x and above by simply swapping any instance of createUUID() with application.fc.utils.createJavaUUID() , that’s it!

This new method checks for the existence of 2 separate java libraries (depending on your version of JRUN) that can be used to create UUIDs before falling back to the native CF UUID method. If one of the 2 java libraries exist then you should notice a significant performance gain where the createUUID() method has been replaced because the java versions are so much more efficient, eg an import script.

Ever wonder how great it would be if you can edit the content of an object that you have selected in the library? Well now you can! By adding a form tool attribute ‘ftAllowLibraryEdit’ into your CF property such as below, it allows you to individually edit each object.

<cfproperty ftSeq="15" ftwizardStep="Body" ftFieldset="Relationships" name="aRelatedIDs" type="array" required="no" default="" ftJoin="dmNavigation,dmHTML,dmNews,dmEvent,dmFacts,dmLink" ftLabel="Associated Content" ftAllowLibraryEdit="true" />

Once you have included the new attribute, update the application. You should see a new button named ‘Edit Item’ appearing next to the ‘Remove Item’ button.

Regular Expressions are your friend. Once you’ve learn the dark art, you’ll wonder how you ever got by without. It does take a little time and some mastering however. Here are a few links to get you started on your way.

Annoyingly, depending on the setup of the MYSQL server at your host (and more specifically if they host their MYSQL server on a Windows box) the case sensitivity setting of the database may be set to be ‘case insensitive’… which is a pain if you are moving from a Linux MYSQL server to Windows and back etc because Farcry will not think that any of the object tables have been deployed along with all sorts of other issues. So to get around this issue I wrote a script that produces a nice bunch of sql statements to plant into your MYSQL GUI (eg. sqlYog) and it will rename all the tables back ito the case expected by farcry. This beats the heck out of doing it manually…. zippedy doo!


<cfset lcoretables = "container_aRules,dmExternalGroupToPolicyGroup,dmGroup,dmPermission,dmPermissionBarnacle,dmPolicyGroup,dmUser,dmUserToGroup,fqAudit,refCategories,refContainers,refDSAS,reffriendlyURL,refObjects,statsCountries,statsDays,statsHours,statsSearch" />

<cfloop index="i" list="#lcoretables#">
	<cfoutput>ALTER table #lcase(i)# RENAME _#i#;</cfoutput>
</cfloop>

<cfloop index="i" list="#lcoretables#">
	<cfoutput>ALTER table _#i# RENAME #i#;</cfoutput>
</cfloop>

<cfset lTypes = "#structKeyList(application.types)#">

<cfloop list="#lTypes#" index="st">
	<cfoutput>ALTER table #lcase(st)# RENAME _#st#;</cfoutput>
</cfloop>

<cfloop list="#lTypes#" index="st">
	<cfoutput>ALTER table _#st# RENAME #st#;</cfoutput>
</cfloop>

</cfset><cfset lRules = "#structKeyList(application.rules)#">

<cfloop list="#lRules#" index="st">
	<cfoutput>ALTER table #lcase(st)# RENAME _#st#;</cfoutput>
</cfloop>

<cfloop list="#lRules#" index="st">
	<cfoutput>ALTER table _#st# RENAME #st#;</cfoutput>
</cfloop>

<cfloop index="i" list="#lTypes#"></cfloop><cfloop collection="#application.types[i].stProps#" item="st"><cfif application.types[i].stProps[st].metadata.type is 'array'>
	<cfoutput>ALTER table #lcase(i&"_"&st)# RENAME _#i&"_"&st#;</cfoutput>
</cfif></cfloop>

<cfloop index="i" list="#lTypes#"></cfloop><cfloop collection="#application.types[i].stProps#" item="st"><cfif application.types[i].stProps[st].metadata.type is 'array'>
	<cfoutput>ALTER table _#i&"_"&st# RENAME #i&"_"&st#;</cfoutput>
</cfif></cfloop>

<cfloop index="i" list="#lRules#"></cfloop><cfloop collection="#application.rules[i].stProps#" item="st"><cfif application.rules[i].stProps[st].metadata.type is 'array'>
	<cfoutput>ALTER table #lcase(i&"_"&st)# RENAME #i&"_"&st#;</cfoutput>
</cfif></cfloop>

<cfloop index="i" list="#lRules#"></cfloop><cfloop collection="#application.rules[i].stProps#" item="st"><cfif application.rules[i].stProps[st].metadata.type is 'array'>
	<cfoutput>ALTER table _#i&"_"&st# RENAME #i&"_"&st#;</cfoutput>
</cfif></cfloop>