Solved

Need to clear components from memory

Posted on 2006-11-01
10
232 Views
Last Modified: 2013-12-24
First, thanks to ASFUSION for portions of the code I am using!

I am working with their REAL ESTATE application they developed and am running into a problem.  This is the first time that I have used Coldfusion CFCs (components) and when I make a change to one of them it is not executing.  When I run my application I get an error message due to an error that I had in a query in one of my components.  I fixed the component and ran the application again but it appears that the component is being run from memory.

The interesting thing is that the code that is shown with the error message is the correct (changed) code;

     16 :       <cfquery name="q_BidFiles" datasource="#variables.dsn#" cachedWithin="#CreateTimeSpan(0,2,0,0)#">
     17 :             SELECT            BnP_BidBuild.*
     18 :             FROM            BnP_BidBuild
     19:            ORDER BY            BnP_BidBuild.BidFile
     20:      </cfquery>

However, the error message displays the old SQL statement that was executed;

     SQL    SELECT DISTINCT BnP_BidBuild.BidQty1 FROM BnP_BidBuild ORDER BY BidFile  
     DATASOURCE   BnP
     VENDORERRORCODE   145
     SQLSTATE   HY000

I have cleared the template cache on the server using the button in SERVER SETTINGS -> CACHING but with no luck.


=================================================================================


Here is the error message I received;


The web site you are accessing has experienced an unexpected error.
Please contact the website administrator.

The following information is meant for the website developer for debugging purposes.  

Error Occurred While Processing Request  
Error Executing Database Query.  
[Macromedia][SQLServer JDBC Driver][SQLServer]ORDER BY items must appear in the select list if SELECT DISTINCT is specified.  
 
The error occurred in D:\Inetpub\wwwroot\drs_lt_intranet\bnp\components\BidFilesGateway.cfc: line 16
Called from D:\Inetpub\wwwroot\drs_lt_intranet\bnp\index.cfm: line 39
Called from D:\Inetpub\wwwroot\drs_lt_intranet\bnp\index.cfm: line 1
Called from D:\Inetpub\wwwroot\drs_lt_intranet\bnp\components\BidFilesGateway.cfc: line 16
Called from D:\Inetpub\wwwroot\drs_lt_intranet\bnp\index.cfm: line 39
Called from D:\Inetpub\wwwroot\drs_lt_intranet\bnp\index.cfm: line 1
 
14 :       <cfset var q_BidFiles = "" />
15 :       
16 :       <cfquery name="q_BidFiles" datasource="#variables.dsn#" cachedWithin="#CreateTimeSpan(0,2,0,0)#">
17 :             SELECT                  BnP_BidBuild.*
18 :             FROM                  BnP_BidBuild
19:            ORDER BY            BnP_BidBuild.BidFile
20:      </cfquery>

 

--------------------------------------------------------------------------------
 
SQL    SELECT DISTINCT BnP_BidBuild.BidQty1 FROM BnP_BidBuild ORDER BY BidFile  
DATASOURCE   BnP
VENDORERRORCODE   145
SQLSTATE   HY000
 
Resources:  

 
=================================================================================


Here are the CFC files;

APPLICATION.CFC

<cfcomponent displayname="Application">
      <cfscript>
            this.name = "BnP_part2";
            this.setclientcookies="false";
            
            // data source name as entered in the CF Administrator
            variables.dns = "BnP";

            // folder path to this file (replace / by dots .)
            variables.componentPath = "bnp";
      </cfscript>

      <!--- ::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::: --->
      <cffunction name="OnApplicationStart">
             <cfset application.listingGateway = createObject("component",variables.componentPath & ".components.ListingGateway").init(variables.dns) />
            <cfset application.bidfilesGateway = createObject("component",variables.componentPath & ".components.BidFilesGateway").init(variables.dns) />
            <cfset application.bidfilenamesGateway = createObject("component",variables.componentPath & ".components.BidFilenamesGateway").init(variables.dns) />
            <cfset application.listingManager = createObject("component",variables.componentPath & ".components.ListingDAO").init(variables.dns) />
      </cffunction>
            
      
      <!--- ::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::: --->
       <cffunction name="onRequestStart" returnType="boolean">
            <cfargument type="String" name="targetPage" required="true" />
                  
                  <!--- for convenience, put the BidFilesGateway in the request scope. In a bigger application, you may not want to do this --->
                  <cfset request.bidfilesGateway = application.bidfilesGateway />
                  <cfset request.componentPath = variables.componentPath />

            <cfreturn true>
      </cffunction>
      
</cfcomponent>


BIDFILESGATEWAY.CFC

<cfcomponent name="BidFilesGateway" access="public" hint="Gateway for BidFiles">

 <!--- :::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::: --->
<cffunction name="init" output="false" returntype="any" hint="instantiates an object of this class" access="public">
      <cfargument name="dns" required="true" type="any">
      
            <cfset variables.dsn = arguments.dns>
            <cfreturn this />
</cffunction>

<!--- :::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::: --->
<cffunction name="getAll" output="true" hint="Gets all the records" access="public" returntype="query">

      <cfset var q_BidFiles = "" />
      
      <cfquery name="q_BidFiles" datasource="#variables.dsn#" cachedWithin="#CreateTimeSpan(0,2,0,0)#">
            SELECT                  BnP_BidBuild.*
            FROM                  BnP_BidBuild
            ORDER BY            BnP_BidBuild.BidFile
      </cfquery>

      <cfreturn q_BidFiles />
</cffunction>

<cffunction name="getAllBF" output="true" hint="Gets all the records" access="public" returntype="query">

      <cfset var q_AllBidFiles = "" />
      
      <cfquery name="q_AllBidFiles" datasource="#variables.dsn#" cachedWithin="#CreateTimeSpan(0,2,0,0)#">
            SELECT DISTINCT      BnP_BidBuild.BidFile
            FROM                  BnP_BidBuild
            ORDER BY            BnP_BidBuild.BidFile
      </cfquery>

      <cfreturn q_AllBidFiles />
</cffunction>

</cfcomponent>
0
Comment
Question by:DRSLT
  • 4
  • 4
  • 2
10 Comments
 
LVL 36

Assisted Solution

by:SidFishes
SidFishes earned 200 total points
ID: 17852075

"When using the cachedWithin parameter, there is no easy way to clear the cached query. Clearing a query when you have used this method requires that you execute the query with a time span of zero (for example, createTimeSpan(0,0,0,0)). Additionally, you cannot clear the cached query without executing it."

http://www.adobe.com/devnet/coldfusion/articles/query_cache_02.html

0
 
LVL 1

Author Comment

by:DRSLT
ID: 17852139
UPDATE
=======================================================

If I let the server sit for a while it does clear the 'saved' cfc from cache or memory and the newly revised one is used.  I don't know the time limit on this and really don't have a way to check it.
0
 
LVL 36

Expert Comment

by:SidFishes
ID: 17852184
while you are developing your may want to remove cachedWithin="#CreateTimeSpan(0,2,0,0)#" to avoid this problem
0
 
LVL 1

Author Comment

by:DRSLT
ID: 17852439
I will try that...I did remark it out but I will have to let it expire once before running the application and see if that works.
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 17853576

 force the timeout by changing the query name or adding a clause to the where clause ( something like WHERE 1 = 1 could be enough)
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 39

Expert Comment

by:gdemaria
ID: 17853586

 let me clarify my last comment, the cache will not be use if you change the query name, the cached query will still exist (and not be timed out as I said).   Also, the cached query will not be used if the statement in the query changes (thus adding a where clause or another column in the select).

Just trying to save you from sitting around.. :)
0
 
LVL 1

Author Comment

by:DRSLT
ID: 17857565
gdemaria:

Changing the query name or query will not have the desired results.  That is what I have been doing all along...changing the query, adding a new query, etc. and those changes have not caused the query to be re-executed.  The time out is the only thing that fixed it.

Having said that...

SidFishes:

When I take the cachedWithin="#CreateTimeSpan(0,2,0,0)#" out of the query it times out faster but not immediately.  I have also tried changing part of the statement to CreateTimeSpan(0,0,0,0) and that seems to have the same effect as removing it completely.  I also changed settings in the administrator under SERVER SETTINGS > MEMORY VARIABLES so that application and session variables timeout at 1 minute.  I haven't researched to impact of this but no users have notified me of problems on other pages.  I don't know if changing these settings or changing cachedWithin="#CreateTimeSpan(0,2,0,0)#" has made the query time out faster.

Your thoughts?
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 17858128

 I just re-read your original post, this has nothing to do with the query cache.  You are storing the cfc into the application variable  scope.  The cfc itself is being remembered, when your application times out, you re-invoke it.



<cffunction name="OnApplicationStart">
           <cfset application.listingGateway = createObject("component",variables.componentPath & ".components.ListingGateway").init(variables.dns) />



 To force it to reinvoke, rename your application name in <cfapplication name=""

 Or restart Coldfusion Service




0
 
LVL 1

Author Comment

by:DRSLT
ID: 17858480
That last post started making sense to me.  I reviewed the code and I see what is going on.  I can workaround the problem by renaming it like you said.  However, is there a way to clear the object in or before the OnApplicationStart in the Application.CFC file so that it is recreated on each run during the coding phase?
0
 
LVL 39

Accepted Solution

by:
gdemaria earned 300 total points
ID: 17858860


 you could move the invoke command out of the OnApplicationStart function or perhaps just rename the OnApplicationStart start function to OnSessionStart (if you don't already have one).  



 You could also change the timeout of your application on Development only to something pretty short like 1 or 5 minutes...
   <cfapplication name="myApp"  applicationTimeOut="#timespan(0,0,5,0)#" ....



 It's not uncommon to have variations in your dev and your live application.cfc / .cfm files.   You just have to be more diligent tracking changes to application.cfc and testing again with the "final" application.cfc version before going live with it.

0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now