Lucee Query Object Examples

query_add_column.cfm

<cfscript>
myQuery = queryNew("col1,col2,col3");
myQuery.addColumn("col4", ["foo","bar","baz"]);
dump(myQuery);
</cfscript>

query_add_column.png

query_add_row.cfm

<cfscript>
myQuery = queryNew("col1,col2,col3");
myQuery.addRow(["foo","bar","baz"]);
dump(myQuery);
</cfscript>

query_add_row.png

query_column_data.cfm

<cfscript>
myQuery = queryNew(columns="col1,col2,col3", 
				   data=[
				   		["one","two","three"], 
				   		["foo","bar","baz"], 
				   		["i","am","query"]
				   ]);
dump(myQuery.columnData("col1"));
</cfscript>

query_column_data.png

query_create.cfm

<cfscript>
myQuery = queryNew(columns="col1,col2,col3", 
				   data=[
				   		["one","two","three"], 
				   		["foo","bar","baz"], 
				   		["i","am","query"]
				   ]);
dump(myQuery);
</cfscript>

query_create.png

query_create_empty.cfm

<cfscript>
myQuery = queryNew("col1,col2,col3");
dump(myQuery);
</cfscript>

query_create_empty.png

query_for_in.cfm

<cfscript>
myQuery = queryNew(columns="col1,col2,col3", 
				   data=[
				   		["one","two","three"], 
				   		["foo","bar","baz"], 
				   		["i","am","query"]
				   ]);
for(row in myQuery){
	writeDump(row); //Dumps a structure representing the row
	echo(row.col1); //output the col1 value of this row
	echo(row.col2); //output the col2 value of this row
	echo(row.col3)	//output the col3 value of this row
}
</cfscript>

query_for_in.png

query_for_recordcount.cfm

<cfscript>
myQuery = queryNew(columns="col1,col2,col3", 
				   data=[
				   		["one","two","three"], 
				   		["foo","bar","baz"], 
				   		["i","am","query"]
				   ]);
for(i = 1; i <= myQuery.recordCount(); i++){
	
	echo(myQuery.col1[i]); //output the col1 value of this row
	echo(myQuery.col2[i]); //output the col2 value of this row
	echo(myQuery.col3[i])	//output the col3 value of this row
}
</cfscript>

query_loop.cfm

<cfscript>
myQuery = queryNew(columns="col1,col2,col3", 
				   data=[
				   		["one","two","three"], 
				   		["foo","bar","baz"], 
				   		["i","am","query"]
				   ]);
loop query="myQuery" index="i"{	
	echo(col1); //output the col1 value of this row
	echo(col2); //output the col2 value of this row
	echo(col3)	//output the col3 value of this row
}
</cfscript>

query_loop_attributes.cfm

<cfscript>
myQuery = queryNew(columns="col1,col2,col3", 
				   data=[
				   		["one","two","three"], 
				   		["foo","bar","baz"], 
				   		["ford","chevy","kia"],
				   		["hyundai","vw","toyota"], 
				   		["banana","berry","apple"], 
				   		["coffee","water","soda"]
				   ]);
loop query="myQuery" startrow="2" maxrows="2"{	
	echo(col1 & " | "); //output the col1 value of this row
	echo(col2 & " | "); //output the col2 value of this row
	echo(col3 & " | ");	//output the col3 value of this row
	echo("<br />");
}
</cfscript>

query_of_queries.cfm

<cfscript>
myQuery = queryNew(columns="col1,col2,col3", 
				   data=[
				   		["one","two","three"], 
				   		["foo","bar","baz"], 
				   		["i","am","query"]
				   ]);

query name="filterQuery" dbtype="query" {
	echo("Select col2 from myQuery");
}
writeDump(filterQuery);

query name="filterQuery" dbtype="query" {
	echo("Select * from myQuery where col2 = 'am'");
}
writeDump(filterQuery);
</cfscript>

query_of_queries.png

query_of_queries_row.png

query_referencing.cfm

<cfscript>
myQuery = queryNew(columns="col1,col2,col3", 
				   data=[
				   		["one","two","three"], 
				   		["foo","bar","baz"], 
				   		["i","am","query"]
				   ]);
echo(myQuery.col2[3]); //outputs col2 row 3, "am"
echo(myQuery.col1[2]); //outputs col1 row 2, "foo"
</cfscript>

query_row_data.cfm

<cfscript>
myQuery = queryNew(columns="col1,col2,col3", 
				   data=[
				   		["one","two","three"], 
				   		["foo","bar","baz"], 
				   		["i","am","query"]
				   ]);
dump(myQuery.rowData(2));
</cfscript>

query_row_data.png

query_set_cell.cfm

<cfscript>
myQuery = queryNew(columns="col1,col2,col3", 
				   data=[
				   		["one","two","three"], 
				   		["foo","bar","baz"], 
				   		["i","am","query"]
				   ]);
myQuery.setCell("col2", "hello"); //sets the last row of this column
myQuery.setCell("col2", "there", 2); //Sets the specific row of this column
dump(myQuery);
</cfscript>

query_set_cell.png

queryTests.cfc

/**
* My xUnit Test
*/
component extends="testbox.system.BaseSpec"{
	
/*********************************** LIFE CYCLE Methods ***********************************/

	// executes before all test cases
	function beforeTests(){
	}

	// executes after all test cases
	function afterTests(){
	}

	// executes before every test case
	function setup( currentMethod ){
	}

	// executes after every test case
	function teardown( currentMethod ){
	}

/*********************************** TEST CASES BELOW ***********************************/
	
	// Remember that test cases MUST start or end with the keyword 'test'
	function checkAllSyntaxTest(){
		 var files = directoryList("");
		 
		 for(file IN files){
		 	if(file CONTAINS ".cfm"){
		 		include template="#getFileFromPath(file)#";		 		
		 	}
		 }
	}

	function queryLoopTest(){
		include template="query_loop.cfm";
	}

	function queryLoopAttributesTest(){
		include template="query_loop_attributes.cfm";
	}

	function queryOfQueriesTest(){
		include template="query_of_queries.cfm";
	}
	
}