CF Ehcache and Saving Object

Hi,


I came across a situation where Session had too many variables which was getting through a Query. Logging was taking a long time and most of the Session Variables had Limited used only to show Charts on user activity just after Login.

I looked into 2 options for Limited Use Query.
1. Ehcache.
2. Saving as binary data file by using ObjectSave and ObjectLoad.

Ehcache

You can enable Ehcache through CF Admin-> Server Settings->Caching. 

Number of queries cached is 100 by default. You can increase it. If it increases maximum it will delete from the older one and save the new one in Ehcache region.
In my version CF 10 I have Ecache 2.6.6 JARS. Can have max 10000 Queries in Memory

 <cfquery datasource="dsn" name="VARIABLES.getClient"  cachedwithin="#createTimespan(1,0,0,0)#" >  
      SELECT TOP 10 cCompanyName FROM CLIENTS WHERE cCompanyName = 'company'  
 </cfquery>  
 <cfdump var="#VARIABLES.getClient#" >  

Cached will be true. In Normal Queries it will be false




It will save in 'Ehcache' cache region with id '1234' for 1 day

Working
If Same query is ran within 1 day Database layer hitting is surpassed and will get a query object from Ehcahe region. Parameter used should be same & query plan should be same, then only it will get from Cache.

Putting in Different cache region
We can save in different cache region too.

 <cfset cacheRegionNew('clients')>  
 <cfquery datasource="dsn" name="VARIABLES.getClient" cacheregion="clients" cachedwithin="#createTimespan(1,0,0,0)#" >  
      SELECT TOP 10 cCompanyName FROM CLIENTS WHERE cCompanyName = 'company'  
 </cfquery>  
 <cfdump var="#VARIABLES.getClient#" >  

We can't get the above Cached query  by using 'cacheGet' function. It will show 'undefined'.

We can put in CacheRegion with CacheId using 

 <cfset cachePut(1234,getClient,createTimespan(1,1,1,0),'','clients')>  

To retrieve Cached Query object 

We can verify whether the CacheRegion or CacheID exists before getting Query object.

 <cfif cacheRegionExists('Ehcache')>  
      <cfif cacheIdExists('1234','Ehcache')>  
           <cfdump var="#cacheGet(1234,'Ehcache')#" >  
      </cfif>  
 </cfif>  

Saving Query as Binary In Data File

We can save Query object in Data File as Binary Format.We can relative path for saving it in file.

 <cfquery datasource="rsa" name="VARIABLES.getClient">  
      SELECT TOP 10 cCompanyName FROM CLIENTS WHERE cCompanyName = 'company'  
 </cfquery>  
 <cfset objectSave(getClient,'../parent/client.txt')>  

To retrieve it 

 <cfdump var="#objectload('../parent/client.txt')#" >  

In 'Ehcahe' Cache region will be in Server Space. If the parameter is changing the Cached query will be used less. The Object Saved will be on Physical file as the Query runs it will be updated if we are saving it in Same page.
I went for 'Ehcache' option as it was very helpful in this particular case.

Updating Query Column value in CF

Hi,


I faced a  problem while updating a Date value in in Query Object.

I had one task to change date of one column by checking Date value of other column of same query object.

For updating Query Column value most of people use javacast(type,variable) function.

Accepting types in javaCast are
bigdecimal,boolean,byte,char,double,byte,char,double,float,int,long,null,short and string

These are all from java.lang class. But there is no Type for date or datetime

I found there are three date classes used in CF (that can be more).

In This is code snippet , By making Query object adding cell and values and updating values.

 <cfset VARIABLES.user = queryNew('SENDDATE,AGE','date,Integer')>  
 <cfset VARIABLES.userDate1 = createDate(year(now()),month(now()),day(now())-2)>  
 <cfset VARIABLES.userDate2 = createDate(year(now()),month(now()),day(now())-3)>  
 <cfset VARIABLES.userDate3 = createDate(year(now()),month(now()),day(now())-4)>  
 <cfset VARIABLES.t = queryAddRow(VARIABLES.user,1)>  
 <cfset QuerySetCell(VARIABLES.user,'SENDDATE',VARIABLES.userDate1)>  
 <cfset QuerySetCell(VARIABLES.user,'AGE',21)>  
 <cfset VARIABLES.t = queryAddRow(VARIABLES.user,1)>  
 <cfset QuerySetCell(VARIABLES.user,'SENDDATE',VARIABLES.userDate3)>  
 <cfset QuerySetCell(VARIABLES.user,'AGE',22)>  
 <cfdump var="#VARIABLES.user#" >  
 <cfloop query="VARIABLES.user">  
      <!---Sample of updating a column value --->  
      <cfset VARIABLES.user['age'][VARIABLES.user.currentRow] = VARIABLES.user.age +1>  
      <!---We can do javacast also for this --->  
      <cfset VARIABLES.user['age'][VARIABLES.user.currentRow] = javacast("int",VARIABLES.user.age +1)>  
      <!---We can update date by normal assigning --->  
      <cfset VARIABLES.user [ 'SENDDATE' ][ VARIABLES.user.currentRow ] = createDate(year(now()),month(now()),day(now())-10) >  
 </cfloop>  
 <cfoutput >  
           #getMetadata(VARIABLES.user.SENDDATE).getName()#</br>  
           #getMetadata(VARIABLES.user.AGE).getName()#</br>  
 </cfoutput>  
 <cfdump var="#VARIABLES.user#" >  

Here is the second one where getting result by Executing Query.

 <cfquery name="VARIABLES.activity" datasource="rsa">  
 SELECT top 1 DATEADD,LISTIDS,EMAIL_ID FROM emaillist WHERE DATEADD IS NOT NULL       
 </cfquery>  
 <cfdump var="#VARIABLES.activity#" >  
 <!---Sample of updating a column value of Query Object we can use javaCast(type,variable) only for Email_ID or ListIDS here  --->  
 <cfset VARIABLES.activity['EMAIL_ID'][VARIABLES.activity.currentRow] = VARIABLES.activity.EMAIL_ID +5>  
 <cfset VARIABLES.activity['DATEADD'][VARIABLES.activity.currentRow] = createDate(year(now()),month(now()),day(now())-10)>  
 <cfdump var="#VARIABLES.activity#" >  

The normal CF date

 <cfset VARIABLES.now = now()>  
 <cfoutput >  
 In Normal CF:<br/>   
      #getMetadata(VARIABLES.now).getName()#<br/>  
 </cfoutput>  

As you can see there are 3 Date class in above.

1) The Query Object Created and added Date from CF is java.sql.Date.
2) The Query Object got from SQL server is  java.sql.Timestamp
3) The normal Date in CF is coldfusion.runtime.OleDateTime

We can update using just by assigning value. If we use javaCast it will make us confuse.

Note: 
QOQ-Query of Query(DBTYPE='QUERY') can't be used on the updated Query object