CODEWITHSUNDEEP
coldfusioncoldfusion-9
beena
beena

Reputation: 1

Unable to display the data returned by a sql function in ColdFusion

We are trying to display a record set in a webform using the cffunction in ColdFusion. But we are unable to display the ouptut..We are just getting a single value(last value in the record set).The sql function is returning ref cursor.Please help us resolve this issue! thanks!

<cfset bare_drive_result = functionname('x','y')> 

<cffunction name="functionname" hint="Gets all customer from the database" returntype="query"> 
    <cfargument name="sPcwQua" type=any>
    <cfargument name="sPcwAcc" type=any>

    <cfquery name="getRkBareDrive" datasource= "#PCW_dsn#"> 
       select pacakagename.functionname('#sPcwQua#','#sPcwAcc#')  bare_drive_result
       from dual
    </cfquery>

    <cfreturn getRkBareDrive> 
</cffunction> 

<cfoutput>#getRkBareDrive.bare_drive_result#</cfoutput>

Upvotes: 0

Views: 1659

Answers (4)

mujimu
mujimu

Reputation: 1619

Everything @Scott Stroz said in his answer is true - 1) you are confusing variable names between the code and the inline function, 2) use a var scope to define the getRKBareDrive variable inside your function (not directly part of the issue being an inline function, but good practice), and 3) try to CFDUMP the result instead of CFOUTPUT it. However I don't believe the core issue is addressed, which lies with this part of your question:

The sql function is returning ref cursor

So one glaring issue is that you are returning a REFCURSOR not a simple value, so you cannot just CFOUTPUT it but instead need to take certain steps so ColdFusion knows it is a query set. REFCURSOR can be returned in calls from Stored Procedures via CFPROCRESULT (or via CFPROCPARAM type="OUT" CFSQLType="CF_SQL_REFCURSOR" if it's a returned parameter not a result).

So... try coverting the CFQUERY call to a CFSTOREDPROC. Here's some sample code that assumes you can call your package/function directly as a stored proc vs through a query. I removed the inline function as it's adding too much complexity here (again see Scott's answer) -- just try bare code as a way to get the call working.

<cfstoredproc procedure="pacakagename.functionname" datasource= "#PCW_dsn#">
    <cfprocparam type="IN" CFSQLType="CF_SQL_VARCHAR", value="x">    
    <cfprocparam type="IN" CFSQLType="CF_SQL_VARCHAR", value="y">    
    <cfprocresult name="bare_drive_result" >
</cfstoredproc> 
<cfdump var="#bare_drive_result#">

If you see results in the dump, you should be able replace the dump and output the fields within the ref cursor just like a normal CFQUERY result within a CFOUTPUT query="bare_drive_result" call.

From CF8 documentation on CFPROCRESULT:

CFML supports Oracle 8 and 9 Reference Cursor type, which passes a parameter by reference. Parameters that are passed this way can be allocated and deallocated from memory within the execution of one application. To use reference cursors in packages or stored procedures, use the cfprocresult tag. This causes the ColdFusion JDBC database driver to put Oracle reference cursors into a result set. (You cannot use this method with Oracle's ThinClient JDBC drivers.)

Upvotes: 2

Sean Kimball
Sean Kimball

Reputation: 4494

I suspect Scott's solution will help you out, though if you are still having issues you can add a result parameter to your query to help troubleshoot & see exactly what is being passed.

<cfquery name="getRkBareDrive" datasource= "#PCW_dsn#" result="qryname">

then dump that:

<cfdump var="#qryname#" />

The returned structure should show you the query passed in, number of results etc [i suspect it is actually returning only one], see the full description here: http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=Tags_p-q_17.html

Upvotes: 0

Scott Stroz
Scott Stroz

Reputation: 7519

You have a scoping issue. The name of the query inside your method is getRKBareDrive, however, you are setting the result of that function call to another variable name - bare_drive_result.

The code would actually be:

<cfoutput query="bare_drive_result">#bare_drive_result.bare_drive_result</cfoutput>

And inside your function, you need to add this line just after the cfargument tags:

<cfset var getRkBareDrive = "" />

However, that does not solve your immediate problem. Have you tried doing:

<cfdump var="#bare_drive_result#" />

to see what is actually returned to ColdFusion

Upvotes: 4

Dale Fraser
Dale Fraser

Reputation: 4758

Try this

<cfoutput query="getRkBareDrive">#getRkBareDrive.bare_drive_result#</cfoutput>

Upvotes: 0

Related Questions