[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 235
  • Last Modified:

calling stored proc from CFC

I have a ColdFusion component that i call from one of my pages that runs a stored procedure to update a table. I can't seem to get this thing to work. Its not a problem with the stored procedure, it runs fine in SQL Query analyzer and if I run the stored proc from my page without using the CFC it works. The stored proc is fed values from the form on my page. Any ideas or suggestions on how to troubleshoot this problem?
0
stacey14
Asked:
stacey14
  • 5
  • 4
  • 4
  • +1
1 Solution
 
rob_lorentzCommented:

can you post the cfc code?
0
 
mrichmonCommented:
need to see code
0
 
stacey14Author Commented:
here it is:

<CFCOMPONENT  displayname="UpdateEducatorDemographics"
  hint ="Provides an interface for accessing insert and update stored procedures for an Educators Demographic data.">
            
 <!--- UpdateDemographicData() method --->
 <CFFUNCTION name="UpdateDemographicData"
      returntype="string"
      displayname="UpdateDemographicData"
      hint="Updates Educator demographic data in Main table">
        
      <CFARGUMENT name = "Akey"
            type="numeric"
            required="true"  
            displayname="Educator Akey"
            hint="Educator Akey">
                  
      <CFARGUMENT name = "Title"
            type="string"
            required="true" default=""
            displayname="Title"
            hint="Title of Educator">
                                                                  
      <CFARGUMENT name = "Fname"
            type = "string"
            required = "true"
            displayname="Educator First Name " 
            hint = "Educator First Name "> 
                                                      
      <CFARGUMENT name = "Mname"
            type="string"
            required="true"  default=""
            displayname="Educator Mid Name"
            hint="Educator Mid Name">
                                    
      <CFARGUMENT name = "Lname"
            type="string"
            required="true"  default=""
            displayname="Educator Last Name"
            hint="Educator Last Name">


      <CFTRY>
         <CFSTOREDPROC procedure="updateEducatorDemographics" datasource="#CertData#" returncode="no">
                              
          <cfprocresult name="updateDemo">
                                                      
          <CFPROCPARAM type = "IN"
            CFSQLType = CF_SQL_INTEGER
            value = #ARGUMENTS.akey#  dbVarName = @intPersonID>
                                    
          <CFPROCPARAM type = "IN"
            CFSQLType = CF_SQL_VARCHAR
            value = "#ARGUMENTS.title#"  dbVarName = @vctitle>
                                    
          <CFPROCPARAM type = "IN"
            CFSQLType = CF_SQL_VARCHAR
            value = "#ARGUMENTS.fname#"  dbVarName = @vcfname null="yes">
                                    
          <CFPROCPARAM type = "IN"
            CFSQLType = CF_SQL_VARCHAR
            value = "#ARGUMENTS.mname#"  dbVarName = @vcmname null="yes">
                  
          <CFPROCPARAM type = "IN"
            CFSQLType = CF_SQL_VARCHAR
            value = "#ARGUMENTS.lname#"  dbVarName = @vclname null="yes"></CFSTOREDPROC>
         </CFSTOREDPROC>            
      
         <CFCATCH TYPE = "any">
            <CFSET StatusCode = "99">
         </CFCATCH>
                              
      </CFTRY>

      <CFIF isdefined("cfstoredproc.statusCode")>
          <CFSET StatusCode =  storedproc.statusCode>
      </CFIF>
                  
      <CFRETURN StatusCode>
</CFFUNCTION>      

</CFCOMPONENT>
0
How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

 
rob_lorentzCommented:
and the code that calls the cfc please
0
 
stacey14Author Commented:
<cfinvoke
                  component="#objEducatorDemographics#"
                  method="UpdateDemographicData"
                  returnvariable="StatusCode">
                              
                  <cfinvokeargument
                        name="akey"
                        value="#session.akey#">
                              
                  <cfinvokeargument
                        name="title"
                        value="#form.title#">

                  <cfinvokeargument
                        name="fname"
                        value="#form.fname#">      
             
                  <cfinvokeargument
                        name="mname"
                        value="#form.mname#">
                        
                  <cfinvokeargument
                        name="lname"
                        value="#form.lname#">
</cfinvoke>

I instantiate the cfc in the application.cfm
0
 
mrichmonCommented:
where is the certdata variable defined?  Usually you need to pass the datasource itself into the CFC if it is stored in a variable....
0
 
stacey14Author Commented:
it is set from an include.
0
 
rob_lorentzCommented:
you either need to set certData in the cfc or include the file where it is defined in the cfc. The only variables visable to the cfc are those passed in or defined by the cfc.

what is the specific error message you get?

0
 
stacey14Author Commented:
I don't get an error message, the page reloads so the changes are visible but the data in the form doesn't change so I know the stored proc didn't run.

I added an extra argument to the cfc for datasource. From the calling page in the Invoke section I added:

<cfinvokeargument name="CertData" value="#CertData#">

In the cfc I added:

<CFARGUMENT name = "CertData"
     type="string" required="true"  default=""
     displayname="Data source" hint="Data source">

and in the stored proc header I refer to the datasource as

datasource="#Arguments.CertData#"

0
 
mrichmonCommented:
ANd did that change anything?
0
 
Tacobell777Commented:
Remember named arguments do not work!
Its a pain, but its a fact.

  <CFPROCPARAM type = "IN"
          CFSQLType = CF_SQL_VARCHAR
          value = "#ARGUMENTS.lname#"  dbVarName = @vclname null="yes"></CFSTOREDPROC>

You can remove dbVarName = @vclname or leave it, either way they do not work.
You need to pass in the variables in the order declared int he stored proc or use cfquery and
EXEC sp_name @vclname = 'whatever'
0
 
Tacobell777Commented:
Does anyone know if this is this fixed in CF7 BTW?
0
 
stacey14Author Commented:
Dumb question, but you can use <CFQUERY> to call a stored proc? If so, whats the syntax (with parameters being passed in)
0
 
mrichmonCommented:
yes if you have enabled certain permissions, but I understand that it is better to use cfstoredproc
0
 
Tacobell777Commented:
stacey look at my comment
0
 
Tacobell777Commented:
yes storedproc is better, but if you want flexibility or work with named parameters you want to do it with cfquery
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 5
  • 4
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now