At AeonCube Networks and Host Media we have been  moving our CFML code from tag based to cfscript for more performance and to keep up to date with coding in CFML. In doing so we found that when we have our cfquery’s we couldn’t simply add <cfif>/if() within our SQL code.

After testing and reading what others in the ColdFusion community suggested we came up with a way to do this and keep performance high. Below is a sample code to select data from a table but with argument options:

query function *FUNCTIONAME*(arguments){
	var q = new query();
	q.setDatasource(*DATASOURCENAME*);
	savecontent variable="local.sql"{
	WriteOutput("
	     SELECT *
	     FROM *TABLENAME*
	     WHERE id = :id
	");
	if (arguments.id2 != '') {
	 WriteOutput("AND id2 = :id2 ");
	}
	if (arguments.id3 != '') {
	 WriteOutput("AND id3 = :id3");
	}
	}
	q.setSQL(local.sql);
	q.addParam(name="id",value="#arguments.id",CFSQLTYPE="CF_SQL_INT");
	q.addParam(name="id2",value="#arguments.id2#",CFSQLTYPE="CF_SQL_INT");
	q.addParam(name="id3",value="#arguments.id3#",CFSQLTYPE="CF_SQL_INT");
	return q.execute().getResult();
}

This worked fine on (Adobe) CF8-CF10 and on Railo 3+. If you have a way of doing this please do share in the comments section below.

If you are looking for great Railo Hosting or ColdFusion Hosting make sure to check out our website for details.

Leave a Reply

Your email address will not be published. Required fields are marked *

About Host Media

We have been hosting websites since 2002 and are always moving forward. All articles written under the Host Media author are created by the team who support our customers.

Category

ColdFusion/Railo & MySQL

Tags

, , , ,