Skip to main content
C
cfnew
Known Participant
January 20, 2011
Question

need to convert this to cfc component

  • January 20, 2011
  • 3 replies
  • 922 views

Hi,

I need help from you guys, I have a query like this which returns some columns and from loop we are getting some values from other queries depending up on first query values like joins.

I need to make this in to CFC and need to return results from first query as well as from the loop queries.

Is there is any way to do this to convert in component.

I need to return result which is shown in cfoutput below.

Thanks

<cfquery name="myquery" datasource="scott">
                    select empno,ename,deptno,job,mgr,sal from emp

</cfquery>
                               
        <cfloop  query="myquery">           
                <cfquery name="test1" datasource="scott">
                     select dname,loc from dept where deptno =#myquery.deptno#
                </cfquery>
               
                <cfquery name="test2" datasource="coldfusion">
                  select job,hiredate from emp where deptno=#myquery.deptno#               
                </cfquery>               
                   
                <cfoutput>
                    <table border="1" width="75%">
                        <tr >
                            <td >
                            <b>First Query</b>#myquery.empno#,#myquery.ename#,#myquery.deptno#  
                            <b>Second Query</b>#test1.dname#,#test1.loc#  
                            <b>Third Query</b>#test2.job#,#DateFormat(test2.hiredate,"mm/dd/yyyy")#
                            </td>
                        </tr>
                    </table>
                </cfoutput>       
        </cfloop>          

This topic has been closed for replies.

3 replies

BKBK
BKBK
Community Expert
January 23, 2011

General rules of thumb:

1) No HTML in component;

2) A function should specialize, returning one specific resultset upon request;

3) Use var to make function variables local to the function (especially relevant for garbage-collection of large queries!);

4) Use remote access for functions you intend to call with Flash.

<cfcomponent>
<cffunction name="getEmployee" access="remote" returntype="query">
     <cfset var employee = queryNew("","")>
     <cfquery name="employee" datasource="scott">
         select empno,ename,deptno,job,mgr,sal from emp
     </cfquery>
     <cfreturn employee>
</cffunction>

<cffunction name="getName_loc" access="remote" returntype="query">                             
<cfargument name="deptNo" required="yes" type="numeric">
     <cfset var name_loc = queryNew("","")>          
     <cfquery name="name_loc" datasource="scott">
          select dname,loc from dept where deptno =#arguments.deptno#
     </cfquery>
     <cfreturn name_loc>
</cffunction>   

<cffunction name="getJob_hiredate" access="remote" returntype="query">                            

<cfargument name="deptNo" required="yes" type="numeric">

    <cfset var job_hiredate = queryNew("","")>         

    <cfquery name="job_hiredate" datasource="coldfusion">

         select job,hiredate from emp where deptno = #arguments.deptno#

    </cfquery>

    <cfreturn job_hiredate>

</cffunction>

</cfcomponent>

C
cfnewAuthor
Known Participant
January 23, 2011

HI BKBK,

thanks for ur reply i made finally some structure that i want is to return from cfc and i made this remote for flash remoting.

i have no idea how we can refer this structure to display in flash for example in flash datagrid.

here i copy my code and dump please have a look, now i need to display this structure in flash as datagrid

thanks

<cfcomponent output="false">
    <cffunction name="myquery" access="remote"  returntype="any" output="false">
         <cfsetting requesttimeout="3600"> 
           <cfset mystr= structNew()>
                <cfquery name="myquery" datasource="scott">
                    select empno,ename,deptno,job,mgr,sal from emp
                </cfquery>               
                   
         <cfloop  query="myquery">   
             <cfset mystr[myquery.empno]=structNew()>                       
                <cfquery name="test1" datasource="scott">
                     select dname,loc from dept where deptno =#myquery.deptno#
                </cfquery>               
                <cfquery name="test2" datasource="coldfusion">
                  select hiredate from emp where deptno=#myquery.deptno#               
                </cfquery>                                   
                <cfset mystr[myquery.empno].empno=myquery.empno>
                <cfset mystr[myquery.empno].ename=myquery.ename>
                <cfset mystr[myquery.empno].deptno=myquery.deptno>
                <cfset mystr[myquery.empno].job=myquery.job>
                <cfset mystr[myquery.empno].mgr=myquery.mgr>
                <cfset mystr[myquery.empno].sal=myquery.sal>
                <cfset mystr[myquery.empno].dname=test1.dname>

                 <cfset mystr[myquery.empno].loc=test1.loc>

                <cfset mystr[myquery.empno].hiredate=test2.hiredate>                   

        </cfloop>       
         <cfreturn mystr>
     </cffunction>
</cfcomponent>

CFDUMP HERE:

STRUCTURE TEST - struct
7369
STRUCTURE TEST - struct
DEPTNO20
DNAMERESEARCH
EMPNO7369
ENAMESMITH
HIREDATE1980-12-17 00:00:00.0
JOBCLERK
LOCDALLAS
MGR7902
SAL800.00
7499
STRUCTURE TEST - struct
DEPTNO30
DNAMESALES
EMPNO7499
ENAMEALLEN
HIREDATE1981-02-20 00:00:00.0
JOBSALESMAN
LOCCHICAGO
MGR7698
SAL1600.00
7521
STRUCTURE TEST - struct
DEPTNO30
DNAMESALES
EMPNO7521
ENAMEWARD
HIREDATE1981-02-20 00:00:00.0
JOBSALESMAN
LOCCHICAGO
MGR7698
SAL1250.00
7566
STRUCTURE TEST - struct
DEPTNO20
DNAMERESEARCH
EMPNO7566
ENAMEJONES
HIREDATE1980-12-17 00:00:00.0
JOBMANAGER
LOCDALLAS
MGR7839
SAL2975.00
7654
STRUCTURE TEST - struct
DEPTNO30
DNAMESALES
EMPNO7654
ENAMEMARTIN
HIREDATE1981-02-20 00:00:00.0
JOBSALESMAN
LOCCHICAGO
MGR7698
SAL1250.00
7698
STRUCTURE TEST - struct
DEPTNO30
DNAMESALES
EMPNO7698
ENAMEBLAKE
HIREDATE1981-02-20 00:00:00.0
JOBMANAGER
LOCCHICAGO
MGR7839
SAL2850.00
7782
STRUCTURE TEST - struct
DEPTNO10
DNAMEACCOUNTING
EMPNO7782
ENAMECLARK
HIREDATE1981-06-09 00:00:00.0
JOBMANAGER
LOCNEW YORK
MGR7839
SAL2450.00
7788
STRUCTURE TEST - struct
DEPTNO20
DNAMERESEARCH
EMPNO7788
ENAMESCOTT
HIREDATE1980-12-17 00:00:00.0
JOBANALYST
LOCDALLAS
MGR7566
SAL3000.00
7839
STRUCTURE TEST - struct
DEPTNO10
DNAMEACCOUNTING
EMPNO7839
ENAMEKING
HIREDATE1981-06-09 00:00:00.0
JOBPRESIDENT
LOCNEW YORK
MGR[empty string]
SAL5000.00
7844
STRUCTURE TEST - struct
DEPTNO30
DNAMESALES
EMPNO7844
ENAMETURNER
HIREDATE1981-02-20 00:00:00.0
JOBSALESMAN
LOCCHICAGO
MGR7698
SAL1500.00
7876
STRUCTURE TEST - struct
DEPTNO20
DNAMERESEARCH
EMPNO7876
ENAMEADAMS
HIREDATE1980-12-17 00:00:00.0
JOBCLERK
LOCDALLAS
MGR7788
SAL1100.00
7900
STRUCTURE TEST - struct
DEPTNO30
DNAMESALES
EMPNO7900
ENAMEJAMES
HIREDATE1981-02-20 00:00:00.0
JOBCLERK
LOCCHICAGO
MGR7698
SAL950.00
7902
STRUCTURE TEST - struct
DEPTNO20
DNAMERESEARCH
EMPNO7902
ENAMEFORD
HIREDATE1980-12-17 00:00:00.0
JOBANALYST
LOCDALLAS
MGR7566
SAL3000.00
7934
STRUCTURE TEST - struct
DEPTNO10
DNAMEACCOUNTING
EMPNO7934
ENAMEMILLER
HIREDATE1981-06-09 00:00:00.0
JOBCLERK
LOCNEW YORK
MGR7782
SAL1300.00
BKBK
BKBK
Community Expert
January 25, 2011

To avoid ambiguity, rename the function to something like getEmployees. I shall also suppose that your component is saved as Employee.cfc and that gridPage.cfm is in the same directory as the component.

gridPage.cfm

<cfset employeeObject = createobject("component", "Employee")>

<cfset employeeStruct = employeeObject .getEmployees()>

<cfform name = "employeeForm">
    <cfgrid name="employeeGrid" format="flash">
        <cfgridcolumn name="deptno" header="Dept No">
        <cfgridcolumn name="dname" header="Dept name">
        <cfgridcolumn name="empno" header="Emp No">
        <cfgridcolumn name="ename" header="Ename">      
        <cfgridcolumn name="hiredate" header="Hire Date">
        <cfgridcolumn name="job" header="Job">
        <cfgridcolumn name="loc" header="Location">
        <cfgridcolumn name="mgr" header="Mgr">
        <cfgridcolumn name="sal" header="Salary">
      
        <!--- Loop through struct to populate the grid ---> 
        <cfloop list="#structKeyList(employeeStruct)#" index="empNo">
                <cfgridrow data ="#empNo#"><!--- the first column --->
                <cfloop list="#structKeyList(employeeStruct[empNo])#" index="empAttribute">
                       <cfgridrow data ="#employeeStruct[empNo][empAttribute]#">
                </cfloop>
        </cfloop>


    </cfgrid>
</cfform>

D
Dan_Bracuk
Inspiring
January 20, 2011

Since you are re-writing it anyway, you might want to make it more efficient.

You don't need query test1.  Since it has the same datasource as myquery, you can combine them.

For test2, you may be able to combine this, or you may not.  It depends on database stuff.  Assuming you can't, you can put this query outside the loop so that you only have to run it once.  Pass it a value list from the first query.  Then use Query of Queries to join everything together and output the result.

C
cfnewAuthor
Known Participant
January 20, 2011

Hi Dan,

Thanks for ur reply . My intension is that we have this code incoldfusoon forms that display reports from this code , but now we are moving the same code to flash front end to display reports on flash screen. So now I need to made this to component and return results from component to flash remoting. So I need this code to be converted to component and returns results from all like first query and the queries in loop as a single return like array or structure. Any help in this.

Thanks

Owainnorth
Owainnorth
Inspiring
January 20, 2011

To keep it simple I'd create a cfc with three functions:

1 - getEmployees() - returns the first queryset to loop on

2 - getDepartmentByNumber() - returns a queryset or struct when given a deptno

3 - getEmployeesByDepartmentNumber() - performs your last lookup and returns a queryset

Therefore your pseudocode would be:

<cfset qEmployees = mycfc.getEmployees() />

<cfloop query="qEmployees">

  <cfset qThisDept = mycfc.getDepartmentByNumber() />

  <cfset qTheseEmployees = mycfc.getEmployeesByDepartmentNumber() />

  Output stuff here.

</cfloop>

Hope that helps.

O.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices