View comments | RSS feed

Handling query results using UDFs

When you call a UDF in the body of a tag that has a query attribute, such as a cfloop query=... tag, any function argument that is a query column name passes a single element of the column, not the entire column. Therefore, the function must manipulate a single query element.

For example, the following code defines a function to combine a single first name and last name to make a full name. It queries the cfdocexamples database to get the first and last names of all employees, and then it uses a cfoutput tag to loop through the query and call the function on each row in the query.

<cfscript>
function FullName(aFirstName, aLastName)
 { return aFirstName & " " & aLastName; }
</cfscript>

<cfquery name="GetEmployees" datasource="cfdocexamples"> 
   SELECT FirstName, LastName
   FROM Employee
</cfquery>

<cfoutput query="GetEmployees">
#FullName(FirstName, LastName)#<br>
</cfoutput>

You generally use functions that manipulate many rows of a query outside tags that loop over queries. Pass the query to the function and loop over it inside the function. For example, the following function changes text in a query column to uppercase. It takes a query name as an argument.

function UCaseColumn(myquery, colName) {
   var currentRow = 1;
   for (; currentRow lte myquery.RecordCount; 
      currentRow = currentRow + 1)
   {
      myquery[colName][currentRow] =
         UCase(myquery[colName][currentRow]);
   }
   Return "";
}

The following code uses a script that calls the UCaseColumn function to convert all the last names in the GetEmployees query to uppercase. It then uses cfoutput to loop over the query and display the contents of the column.

<cfscript>
   UCaseColumn(GetEmployees, "LastName");
</cfscript>
<cfoutput query="GetEmployees">
   #LastName#<br>
</cfoutput>

ColdFusion 9 | ColdFusion 8 | ColdFusion MX 7 | ColdFusion MX 6.1 | ColdFusion MX | Forums | Developer Center | KnowledgeBase | Bug Reporting

Version 7

Comments


Ashish-Saxena said on Jul 23, 2007 at 4:38 AM :
While using queries in UDF's following comments (please see cfscript block) should be remember :
Function to combine firstName and lastName to give fullName:
<cfset myQry = QueryNew("FirstName,LastName","varchar,varchar")>
<cfset addRow = QueryAddRow(myQry,2)>

<cfset temp = QuerySetCell(myQry,"FirstName","Ashish",1)>
<cfset temp = QuerySetCell(myQry,"LastName","Saxena",1)>

<cfset temp = QuerySetCell(myQry,"FirstName","Gopa",2)>
<cfset temp = QuerySetCell(myQry,"LastName","Nair",2)>

<strong>myQry: Before function call.</strong><br />
<cfdump var="#myQry#" label="myQry: Before function call."><br />

<!--- 1.Query passed as function argument. --->
<cfset myReturnedQry = FullFirstName(myQry)>

<strong>myQry: After function call.</strong><br />
<cfdump var="#myQry#" label="myQry: After function call."><br />

<strong>myReturnedQry: Qry returned by function call.</strong><br />
<cfdump var="#myReturnedQry#" label="myReturnedQry: Qry returned by function call."><br />

<cfscript>
//1.Query passed as function argument.
function FullFirstName(myQryArg)
{
/*2.As query passed by referenc(dy default), we can use
either existing Query's name (myQry), ARGUMRNT scope identifer
or Argument's name, passed as query (myQryArg)
*/
nextRow = ARGUMENTS.myQryArg.RecordCount+1;// nextRow = 2 +1; = 3
addRow = QueryAddRow(myQryArg,1);// Adding only one, new row in existing query.

/*3.Making changes in query objects with in function,
and these changes will sustain after/outside function call also(as passed qry by
reference).
*/
QuerySetCell(myQry,"FirstName","ColdFusion",nextRow);//nextRow = 2 +1; = 3
QuerySetCell(myQry,"LastName","MX",nextRow);//nextRow = 2 +1; = 3




/*4.As it is not possible to use any tag even query loop tag in cfscript
we cannot use QryName.ColumnName(as in qry loop) in cfscript to get values for
all rows, if we use QryName.ColumnName in cfscript, it will relate always with
first row. So to populate all rows of query, we must use column number or index.
*/
myFullFirstNameArray = ArrayNew(1);
For(i=1;i LTE myQryArg.RecordCount; i = i+1)
{
myFullFirstNameArray[i] = myQry.FirstName[i] & " " &
myQryArg.LastName[i];
// Can be also use: myQry["FirstName"][i]
}

//Adding new column in existing query.
QueryAddColumn(myQryArg,"CompletName",myFullFirstNameArray);


qryReturn = Duplicate(myQryArg);

//5.Query returned by function.
return qryReturn;
}
</cfscript>

OUTPUT:
myQry: Before function call.
myQry: Before function call. - query - Top 2 of 2 Rows
FIRSTNAME LASTNAME
1 Ashish Saxena
2 Gopa Nair

myQry: After function call.
myQry: After function call. - query - Top 3 of 3 Rows
COMPLETNAME FIRSTNAME LASTNAME
1 Ashish Saxena Ashish Saxena
2 Gopa Nair Gopa Nair
3 ColdFusion MX ColdFusion MX

myReturnedQry: Qry returned by function call.
myReturnedQry: Qry returned by function call. - query - Top 3 of 3 Rows
COMPLETNAME FIRSTNAME LASTNAME
1 Ashish Saxena Ashish Saxena
2 Gopa Nair Gopa Nair
3 ColdFusion MX ColdFusion MX

 

RSS feed | Send me an e-mail when comments are added to this page | Comment Report

Current page: http://livedocs.adobe.com/coldfusion/7/htmldocs/00001018.htm