从cfc返回多个存储过程结果集 [英] returning multiple stored procedure result sets from a cfc
问题描述
我试图将我的应用程序中的一些页面转换为使用cfc，一个页面使用存储过程来检索几组数据。
I am trying to convert some pages from my app to use cfc's, and one page uses a stored procedure to retrieve a couple sets of data.
访问结果，他们的行为就像一个如果我使用< cfquery> 标签，以及所有的功能。所以现在我试图在我正在建设的cfc中使用这个相同的存储过程，我想能够以相同的方式访问结果，这里有我的问题。我不知道如何从函数返回多个查询，而不创建一个数组，我已经开始。顺便说一下，函数是不完整的。我只是想得到一些工作。在下面的设置中，我得到一个查询对象数组，但我觉得有更好的方法来做。
Now when I access the results, they act just like a if I used a <cfquery> tag, and all of the functionality that gives. So now I am trying to use this same stored procedure in a cfc that I am building, and I would like to be able access the results in the same manner, and there in lies my problem. I'm not sure how to return multiple queries from the function, without creating an array, which I have started. By the way, the function is incomplete. I was just trying to get something to work. In the below setup I get an array of query objects, but I feel there is a better way to do it.
这里是 cffuntion> ：
<cffunction name="getProfileData"
access="public"
output="false"
returntype="string">
<cfargument name="cusip" type="string" required="true">
<cfargument name="report_date" type="date" required="true">
<cfset var errorMessage = "everything is good">
<cftry>
<cfstoredproc datasource="#dsn#" procedure="prc_asset_profile_retrieve">
<cfprocparam type="in" cfsqltype="cf_sql_varchar" value="#cusip#" dbvarname="@cusip">
<cfprocparam type="in" cfsqltype="cf_sql_varchar" value="#report_date#" dbvarname="@reportDate">
<cfprocresult name="profile_head" resultset="1">
<cfprocresult name="attribution" resultset="2">
<cfprocresult name="characteristics" resultset="3">
<cfprocresult name="exposure" resultset="4">
<cfprocresult name="weights" resultset="5">
<cfprocresult name="holdings" resultset="6">
</cfstoredproc>
<cfset var profileArray = []>
<cfset #ArrayAppend(profileArray,profile_head)#>
<cfcatch type="any">
<cfset errorMessage = "something happened">
</cfcatch>
</cftry>
<cfreturn profileArray>
</cffunction>
当我输出一些测试数据时，它匹配
When I output some test data, it matches up
<cfset count = fund_profile.getProfileData("#cusip#","#report_date#")>
<cfdump var="#count[1]#">
<cfoutput>
From cfc (##count[1].recordCount##): #count[1].recordCount#<br>
From stored proc (##profile_head.recordCount##): #profile_head.recordCount#
</cfoutput>
我得到：
从cfc（#count [1] .recordCount＃）：1
从存储过程（＃profile_head.recordCount＃）：1
但第二种方式看起来更干净。
But the second way looks so much cleaner.
-----------------------------WORKING SOLUTION------------------------------
所以在处理了@leigh的答案后，我想出了这一点。
So after working with the answer from @leigh, I came up with this.
这里是完整的cfc ：
Here is the full cfc:
<cfcomponent displayname="Fund Profile" hint="This is the cfc that will do the processing of all fund profile information" output="false">
<cfproperty name = "result1"> <!--- PROFILE HEAD --->
<cfproperty name = "result2"> <!--- ATTRIBUTION --->
<cfproperty name = "result3"> <!--- CHARACTERISTICS --->
<cfproperty name = "result4"> <!--- EXPOSURE --->
<cfproperty name = "result5"> <!--- WEIGHTS --->
<cfproperty name = "result6"> <!--- HOLDINGS --->
<cffunction name="init"
displayname="init"
hint="This will initialize the object"
access="public"
output="false"
returnType="Any">
<cfargument name="dsn" type="string" required="true" />
<cfargument name="cusip" type="string" required="true" />
<cfargument name="report_date" type="date" required="true" />
<cfset variables.dsn = #arguments.dsn#>
<cfset variables.cusip = #arguments.cusip#>
<cfset variables.report_date = #arguments.report_date#>
<cfscript>
getProfiledata(cusip,report_date);
</cfscript>
<cfreturn this>
</cffunction>
<cffunction name="getProfileData"
access="private"
output="false"
returntype="void">
<cfargument name="cusip" type="string" required="true">
<cfargument name="report_date" type="date" required="true">
<cfstoredproc datasource="#dsn#" procedure="prc_asset_profile_retrieve">
<!--- STORED PROCEDURE HASN'T CHANGED. SEE ABOVE FOR CODE --->
</cfstoredproc>
<cfscript>
setProfilehead(profile_head);
setAttribution(attribution);
setCharacteristics(characteristics);
setExposure(exposure);
setWeights(weights);
setHoldings(holdings);
</cfscript>
<cfreturn>
</cffunction>
<!--- NOT GOING TO INCLUDE ALL SETTERS AND GETTERS, --->
<!--- BECAUSE THEY ARE ALL THE SAME OTHER THAN THE NAMES --->
<cffunction name="setProfileHead" access="private">
<cfargument name="ProfileHead">
<cfset variables.result1 = arguments.ProfileHead>
</cffunction>
<cffunction name="getProfileHead" access="public" returntype="query">
<cfreturn variables.result1>
</cffunction>
</cfcomponent>
这是来自呼叫页面的代码：
Here is the code from the calling page:
<cfset fund_profile = CreateObject("component", "CFCs.fund_profile").init("#dsn#","#cusip#","#report_date#")>
<cfset profile_head = fund_profile.getProfileHead()>
对不起，所有的代码，但我想让代码可用。因此，有没有人看到任何问题，我想出了什么？
Sorry for all the code, but I wanted to make the code available. So does anyone see any problems with what I came up with?
推荐答案
我会在CFC中创建其他方法，负责从存储的proc返回结果。在main方法中，调用setters
setProfileHead（profilehead：profileHead）
I would create other methods in the CFC that would each be responsible for returning a result from the stored proc. In the main method , call setters setProfileHead(profilehead:profileHead)
<cffunction name=ProfileHead>
<cfarguments name=ProfileHead />
<cfset variables.profilehead = arguments.profilehead>
</cffunction>
然后...
<cffunction name=GetProfileHead>
<cfreturn variables.profileHead />
</cffuction>
这篇关于从cfc返回多个存储过程结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
