stored procedures and remoting

["kimby24" <webforumsuser@xxxxxxxxxxxxxx>]

Hi,
  Hoping someone can help with a stored procedure / Coldfusion MX / Flash form 
with remoting:  My application is based on the Real Estate application using 
services and DAO's.  For basic queries everything is working fine.  I'm now 
trying to put in more functionality and need to use Stored Procedures for all 
of the database interaction, so I thought I'd move everything into the stored 
procedure.  

 My remoting is set up and working. 

 The following code snippets are just dealing with a simple query that I'm 
trying to call from a Stored procedure.  When the query is in the DAO it works 
to populate the grid. The following code is what works.  The change that I made 
is in the camsUserDAO.cfc that now calls a stored procedure. 

 This is the function call to the service from the Coldfusion Flash Form.
   
 index.cfm:

 public function getUserid(userno:String):Void
 {
        var userArgs:Object = {};
        <cfoutput>
                userArgs.userno = userno;
        </cfoutput>
 _global.listingService.queryUserid(userArgs);
 mx.managers.CursorManager.removeBusyCursor();
 }

 This is the response handler:

 responseHandler.queryUserid_Result = function (results: Object ):Void
 {
        userIdGrid.dataProvider = results.items;
        mx.managers.CursorManager.removeBusyCursor();
 }


 This is the Listing Service:     userListingService.cfc

 <cfcomponent displayname="userListingService" access="remote" hint="Remote 
Facade for camsUserDao">

 <cffunction name="queryUserid" access="remote" returntype="query" 
output="false" hint="Returns a query of all userids for the selected user">
        <cfargument name="userno" required="true" type"string" hint= Primary 
Key 
-userno of selected user">
        <!--- Call a component sitting in memory ( applicaiton scope) --->
        <cfreturn 
application.userListingManager.queryUserid(argumentCollection=arguments) />
 </cffunction>

 </cfcomponent>

 This is the DAO:   camsUserDAO.cfc  - This is a simplified version of the 
query, not all columns were  included.

 <cfcomponent displayname="userListingManager"  hint="Add, update, delete 
users">

 <cffunction name="queryUserid" access="public" returntype="query" 
output="false" hint="Returns userids for a selected user">
        <cfargument name="userno" required = true type="string" hint= Primary 
Key 
-usernoof selected user">
        <cfquery name="useridQuery" datasource="#dsn#">
                select  user_id
                        ,user_no
                from user_accts
                         where user_no = <cfqueryparam value="#Trim(userno)#" 
cfsqltype="cf_sql_varchar" />
        </cfquery>
        <cfreturn useridQuery />
 </cffunction>

 </cfcomponent>

 Change to call Stored procedure:  It is an Oracle package with several stored 
procedures in it. The package has been compiled and the procedure works when 
tested in Toad.  I am using a ref Cursor to pass back the query record set. 

 <cfcomponent displayname="userListingManager"  hint="Add, update, delete 
users">

 <cffunction name="queryUserid" access="public" returntype="query" 
output="false" hint="Returns userids for a selected user">
        <cfargument name="userno" required = true type="string" hint= Primary 
Key 
-usernoof selected user">
        <cfstoredproc procedure="userid_pkg.getUserids" datasource="#dsn#">
        <cfprocresult name"qUserids">
        <cfreturn useridQuery />
 </cffunction>

 </cfcomponent>


 The call in the form is getting the correct user_no to pass into the DAO 
procedure call, but nothing is returned to the datagrid when using the stored 
procedure. 

 Do I need to put the storedprocedure information in the userListingService.cfc 
function or call the <cfstoredproc> somehow?   I've been searching for help for 
2 days and am at lose, I hope someone can help me to resolve this.

 Thanks in advance for any help.