More queryExecute Examples

By |2018-06-24T14:38:34+00:00May 11th, 2014|Code|0 Comments

I am very new to this scripting paradigm, so if others want to critique me, please feel free.  I would venture to say that 99.9% of my queries live in CFCs so these examples will all be in the form of functions.  These are the primary functions that feed my demo site.  So far, the biggest issue was understanding how to pass parameters.

Basic Query

 

query function getData(){
 var dl = queryExecute(" Select column names from table/view");
 return dl;
 }

Query with Argument

 

public query function getDeviceSpecialties(required string dc){
 
 var ds = queryExecute("Select specialtyname From devicespecialty_tbl Where deviceCode = :dcp",
 {dcp={value="#arguments.dc#",cfsqltype="CF_SQL_VARCHAR", maxlength=5 }});
 return ds;
 
 }

Query with Subquery

public query function getEqMfg(required string dc){
 
 var ml = queryExecute("Select name, mfgCode, country
 From mfgdetails_tbl
 Where mfgcode in (
   Select mfgCode
   From mfg2devices_tbl
   Where deviceCode = :dcp
 )
 Order By country, name",
 {dcp={value="#dc#",cfsqltype="CF_SQL_VARCHAR", maxlength=5 }});
 return ml;
 
 }

Returning a Structure

public struct function getCompanyDetails(required string cc){
 
 var cd = queryExecute("Select mfgCode,name,nameAlt,street,address2,city,state,county,postalCode,
 country,phone,phoneTollFree,fax,url,corpEmail,manufacturer,distributor,calibrates_test_eq,specialist,
 reconditioner,last_verified,servicer,exporter,importer,leases
 	From mfgDetails_tbl	
 	Where mfgCode = :ccp",{
 		ccp={value="#cc#", cfsqltype="CF_SQL_VARCHAR", maxlength=6}
 		}
 		);
 	
 	objCompany = structNew();
 	objCompany.cc = cd.mfgCode ;
 	objCompany.name = cd.name ;
 	objCompany.name_alt = cd.nameAlt ;
 	objCompany.street = cd.street;
 	objCompany.address_alt = cd.address2 ;
 	objCompany.city = cd.city ;
 	objCompany.state = cd.state ;
 	objCompany.county = cd.county ;
 	objCompany.postalCode= cd.postalCode;
 	objCompany.country = cd.country ;
 	objCompany.phone = cd.phone ;
 	objCompany.tollfree = cd.phoneTollFree ;
 	objCompany.fax = cd.fax ;
 	objCompany.url = cd.url ;
 	objCompany.email = cd.corpEmail ;
 	objCompany.isMfg = cd.manufacturer ;
 	objCompany.isDist = cd.distributor;
 	objCompany.isImp = cd.importer ;
 	objCompany.isExp = cd.exporter ;
 	objCompany.isSrv = cd.servicer ;
 	objCompany.isRecon = cd.reconditioner ;
 	objCompany.isSpec = cd.specialist ;
 	objCompany.isLease = cd.leases ;
 	objCompany.isCal = cd.calibrates_test_eq ;
 	
 	return objCompany;
 	
 }

Setting Dynamic MaxRows

This one was interesting because it required an empty array for the second argument.  Since this is where the parameters usually go, I am assuming (always dangerous) that because I stated a required value was needed, you must inform ColdFusion that there are no parameters.  This is the feed for the Specialty Codes chart on the homepage of the demo area.  I set this to have a dynamic row count so that I could easily create different chart types that may need smaller or larger data sets.

query function getSpecialtyCodeChartData(required numeric rc=99){
 
 	var sc = queryExecute("Select specialtyname, qty From specialtycategorycount_view
 		Order by qty DESC, specialtyname",{},{maxrows = "#rc#"});
 	return sc;	
 }

 

Returning a Number

public numeric function getSpecialtyCount(){
 	var dsc = queryExecute("Select Count(*) as qty From specialtycategorycount_view");
 	return dsc.qty;
 }
 
 //on the homepage
 
 #NumberFormat(application.counters.getSpecialtyCount(),",")#

Leave a Reply