?
Solved

Need to clear components from memory

Posted on 2006-11-01
10
Medium Priority
?
297 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
10 Comments
 
LVL 36

Assisted Solution

by:SidFishes
SidFishes earned 800 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
Simple, centralized multimedia control

Watch and learn to see how ATEN provided an easy and effective way for three jointly-owned pubs to control the 60 televisions located across their three venues utilizing the ATEN Control System, Modular Matrix Switch and HDBaseT extenders.

 
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
 
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 1200 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses
Course of the Month8 days, 5 hours left to enroll

765 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