1) huh? each connection to Oracle from CF creates a new session. If you happen to have connection pooling on in the ColdFusion DSN settings, then yes you would only see a fixed set of sessions as they are reused, having this set to one would get what you are seeing perhaps. If you have connection pooling off in CF for the DSN, then you could see only one session in the kill/trace view in Oracle IFF there is only one connection to Oracle from CF whilst you are looking. In fact, I have connection pooling off for several DSNs on a server. Say the DSN is named FOO. I can look in Oracle and see multiple sessions for FOO that die as soon as the ColdFusion request containing the CFQUERY/CFSTOREPROC call is done.
Now, what I have not figured out yet is, if you have connection pooling off for a DSN, call it GOO, and you have two CFQUERYs in a single CF page, does one connection/sessioon get used for both or are there two connections/sessions? If its two seperate requests to the same CF page, then yes, there are two seperate and distinct connections/sessions. I just don't know at the page level. 2) a comment from my DBA here 'Yup, that comment is dead wrong. Oracle never inherently auto-commits inside or after a SP, unless it contains a DDL operation (create index, analyze table, grant privs, etc).' I'm agreeing with tanguy below, we have seen this behaviour. CF automagically appends 'SET TRANSACTION ISOLATION LEVEL READ COMMITTED' to the end of each statement passed unless you use CFTRANSACTION. This includes calling SPs. Now, if you have a commit withing the body of your SP, this kind of bypasses what CF can be concerned about, eh? DK On 6/8/05, Adrocknaphobia <[EMAIL PROTECTED]> wrote: > > 1) No. Not true. I have a public website that has over 5k oracle > users. 1 user account is used for the public (ie. people who do not > log in). They all share the same session. When you look at active > sessions in oracle there is only 1 entry for the public account. > Unless that account is connecting from a different client toad/cf. CF > passes the same username/password to oracle for the public account, > oracle has no way to determine that different requests are different > sessions, its all based on the client used to connect (dataDirect > JDBC) and the username/password. > > 2) Well thats kinda what I'm wondering. I think the commit occurs > directly following the </cfstoredproc> tag, because Oracle > automatically commits when _any_ stored procedure finishes without > exception. Regardless if CF made the procedure call or an Oracle Forms > app did. I don't think the <cftransaction> tag can modify the > procedure call to tell oracle not to commit. Even if it could, would > it be able to chain that to all procedures that were internal to the > initial procedure call? > > -Adam > > On 6/8/05, RADEMAKERS Tanguy <[EMAIL PROTECTED]> wrote: > > Adam, > > > > 1) Even with the same username/password, oracle treats it as two > > sessions. If you have TOAD you can verify this with the session browser. > > > > 2) stored procs don't change the transaction handling of oracle (unless, > > or course, you manually commit within the body of the proc) - ie cf > > handles cfstoredproc just like cfquery: by default it will commit after > > each </cfstoredprocedure> unless you use <cftransaction>. > > > > /t > > > > >-----Original Message----- > > Subject: FW: Get New Record ID in ORACLE > > >From: Adrocknaphobia <[EMAIL PROTECTED]> > > >Date: Wed, 8 Jun 2005 10:52:48 -0400 > > >Thread: > > >http://www.houseoffusion.com/cf_lists/index.cfm/method=messages > > >&threadid=40594&forumid=4#208966 > > > > > >James, > > > > > >Interesting test. This strongly contradicts what our DBAs have been > > >telling me. I'm going to bring this up with them. (I love when they > > >are proved wrong!). The only thing is that you are using only a single > > >session. ie. same user to perfrom both actions since I'm assuming u/p > > >is hardcoded into your DSN. Although you get all sorts of errors with > > >cftransaction if they aren't hardcoded. > > > > > >What about with stored procedures? I think the second a procedure is > > >completed and returned the commit is automatically applied, regardless > > >of the cftransaction. So is it safe to say that cftransaction should > > >only be used with cfquery and not cfstoredproc? > > > > > >-Adam > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:209017 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
