cfstoredproc inside a cfc

[DomTrix]

I have a cfc which I create a session instance of. The cfc object represents a shopping basket and I create one instance of it in application.cfm:

<cfif not isDefined('session.Basket')>
	<cfobject name="session.Basket" component="myApp.components.basket">
</cfif>

The object has functions to add items, delete items and update items and each of these functions contains a call to a stored procedure using cfstoredproc. After adding, deleting or updating the basket's products another function is called that clears the product list in the basket object and calls another stored proc to refill the product list from the database.

It all works very smoothly when I either add or delete items. I run the function, refresh the product list and voila, the product has been added or subtracted from the database.

THE PROBLEM:

When I run the update function however, the values in the database update instantly and yet these changed values are not changed in my Basket object, even tho I am querying the database for those values.

However (#2), when the session expires and a new session.basket object is created the edited values appear in the basket items correctly.

My theory:
The results of the cfstoredproc query are being cached by Coldfusion, and will only change if the number of items the query is producing changes.

Does this sound familiar or correct? If so, is there a way I can not have it use this behaviour?

Many thanks in advance

DT

 

[TruthInSatire]

are the values being returned in the stored proc or are you running a new cfquery to get the results? what DB are you using?

We've heard that a million monkeys at a million keyboards could produce the complete works of Shakespeare; now, thanks to the Internet, we know that is not true.

 

[DomTrix]

Yes the storedproc in question selects data (on a SQL Server 2000 server). I am not using a <cfquery>, just using the <cfstoredprocresult name="blah"> tag.

The storedproc is being run and returning data, and the data it should be pulling off has definately been updated, my mind boggles.

I thought that SQL might be caching the storedproc, but this wouldn't make sense seeing that it produces the updated data when my basket object is deleted and recreated would it?

many thanks,

DT

 

[TruthInSatire]

Without seeing your code I'd have to suggest you look at how the values update in the cart. possibly create a new cart to see if the new values are set, that will remove any question of a failed attempt to update the existing cart.



We've heard that a million monkeys at a million keyboards could produce the complete works of Shakespeare; now, thanks to the Internet, we know that is not true.

 

[DomTrix]

Thanks again,

The update function first does the following:

...
<cfstoredproc datasource="#variables.DSN#" procedure="updBasketItemQty">
   <cfprocparam dbvarname="@productID" value="#arguments.productID#" cfsqltype="cf_sql_integer">
   <cfprocparam dbvarname="@newQty" value="#arguments.qty#" cfsqltype="cf_sql_integer">
   <cfprocparam dbvarname="@basketID" value="#variables.basketID#" cfsqltype="cf_sql_integer">
</cfstoredproc>
...

Once this has been done, the values on the database are updated correctly. It then calls the getBasketItems which does the following:

<cfset structClear(variables.product)>
<cfset variables.nProducts = 0>

<cfstoredproc datasource="#variables.DSN#" procedure="selBasketItemsByBasketID">
	<cfprocparam dbvarname="@basketID" value="#variables.basketID#" cfsqltype="cf_sql_integer">
	<cfprocresult name="bsk_products">
</cfstoredproc>
		
<cfset variables.nProducts = Bsk_products.RecordCount>
<cfloop query="Bsk_products">
	<cfset variables.product.ID[currentRow] = productID>
	<cfset variables.product.qty[currentRow] = qty>
	<cfset variables.product.title[currentRow] = title>
	<cfset variables.product.price[currentRow] = price>
	<cfset variables.product.offerID[currentRow] = offerID>			
</cfloop>

The variables.product structure is declared in the 'contsructor' of the cfc like so:

<cfparam name="variables.product" default="#structNew()#">
<cfparam name="variables.nProducts" default="0">
<cfparam name="variables.product.ID" default="#arrayNew(1)#">
<cfparam name="variables.product.qty" default="#arrayNew(1)#">
<cfparam name="variables.product.title" default="#arrayNew(1)#">
<cfparam name="variables.product.price" default="#arrayNew(1)#">
<cfparam name="variables.product.offerID" default="#arrayNew(1)#">

Hope this gives a clue, (sorry for dumping code)

 

[DomTrix]

Some more possibly helpful information.

When I add a product and then run the select products query (to fill my basket product structure), the edited values appear correctly. Likewise when I delete items.

Also, when I create a new basket object the values are shown correctly.

These facts all lead me to believe that something must be being cached, but I know nothing about caching so Im at a loss (especially as I cant find a thing about it with stored procs)

 

[TruthInSatire]

once you do the update, have you tried doing a cfdump of bsk_products to see if the correct values are in the result?



We've heard that a million monkeys at a million keyboards could produce the complete works of Shakespeare; now, thanks to the Internet, we know that is not true.

 

[DomTrix]

Yes and they're not.

However, I just found the answer while typing this. oh brother! The update function is the only one called remotely (from a form action) and so I wasnt updating the local session object as I was calling the cfc directly and not the object instance!

Phew.

Ok, so is there a way of using an instance of an object for the action on a form or do I just create some extra code?

Many thanks for your help

DT

 

[ecobb]

I've never seen CF cache a Stored Procedure. I'm not even sure that it's possible since the actual statement is compiled and run on the SQL Server.

Could it possibly be that the values you had set prior to running the procedure (variables.product.qty[currentRow], etc...) are not getting overwritten with the new values after the procedure runs?

You may want to try setting the values, running the procedure, deleting all values, then recreate all values from the updated info in the procedure.



Hope This Helps!

ECAR
ECAR Technologies, LLC

http://www.ecartech.com

"My work is a game, a very serious game." - M.C. Escher

 

[ecobb]

Oops...I guess you got it!



Hope This Helps!

ECAR
ECAR Technologies, LLC

http://www.ecartech.com

"My work is a game, a very serious game." - M.C. Escher

 

[DomTrix]

ty