[cfaussie] Re: DataLocking & CFTRANSACTION..

[Scott Barnes]

*cough*
Come on, Answer me someone... don't ignore it, its not that hard is it :)
Carn Sean, use that power you have at MM to bug the CFMX engineers or 
something eheheheheheheh

I have money and i'll use it if need be :D
Scott
Scott Barnes wrote:

Heyas I asked a few days ago about DataLocking and I kind of need a an 
answer of some kind  :)

1. How does CFTRANSACTION work.
To me, CFTRANSACTION will keep a DB Connection open, in that:
<CFTRANSACTION> (Open DB Connection)
    <CFQUERY 1>
    </CFQUERY>
    
    <INSERT YOUR SUBLOGIC ON QUERY1>

    <CFQUERY 2>
    </CFQUERY>
</CFTRANSACTION> (Close DB Connection)
So basically when you open a CFTRANSACTION tag, its kind of like a 
"hardcoded" please keep connection open, execute logic within the tag, 
and then when it cloes, it actually then closes that DB connection? I 
ask this, as we have placed some SQL datalocks (via an Informix DB) 
within these two parts, and the key to its success is that CFTRANSACTION 
 keeps the same connection for both CFQUERIES?

2. DataLocking. We are very mindful of the fact that CFMX needs to keep 
as much data integrity in tact as possible, and because of the nature of 
CFMX we need to compare datasets against the DB before we allow new data 
to be insterted. In that, when we first open up a page, which 
effectively take a snapshot in time of the entire rows of data we are 
about to manipulate (even if we only touch 10% of a rows property total, 
we still get the entire row). Store that in Session Scope.

When the person then hits SAVE on the form, we then take that row of 
data and compare it against the live data, if the live data has changed 
since we took the snapshot, throw an exception and ask them to retry 
(for now).

If the data compares ok, then proceed to to commit the data to the db, 
and to do this we follow this kind of procedure.

<CFTRANSACTION>
    <CFQUERY>
        SELECT and lock the rows   
    </CFQUERY>
    
    <CFSET convert QuerySet into string>

    <CFIF NOT session.oldQuerySet.toString() IS     newQuerySet.toString()
        <cfthrow>
    </cfif>
    <CFQUERY>
        UPDATE/INSERT/DELTE NOW
        THEN UNLOCK ROWS
    </CFQUERY>
    
</CFTRANSACTION>
<cfreturn OMGITWORKED!>

This is in simplistic format, but you get the picture. As you can 
imagine the DB has to first do a lock on the row, then grab that data 
convert it to basic string for simpler comparison against old data, if 
its disco, continue on and then unlock. Sadly it all has to be done in 
the one connection, as if you lock the rows you intend to manipulate, 
and the connection is lost before it unlocks.....bad things happen.

Q.3 How do you all handle DataLocking in your applications if infact you 
actually do this...

Regards
Scott Barnes

    


---
You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
To unsubscribe send a blank email to [EMAIL PROTECTED]
Aussie Macromedia Developers: http://lists.daemon.com.au/