to answer the other questions, you don't have to worry about this in CF code, CF handles the closings for you. Also note that unless you have connection pooling off for some reason, CF handles that too. Your direct approach is bypassing any sort of connection pooling.
DK On Jan 24, 2008 8:12 AM, Dean H. Saxe <[EMAIL PROTECTED]> wrote: > I'd actually wrap this whole thing in a very simple helper object which > can handle creating the DB connection and destroying it. Also, make sure > you use parameterized queries to prevent any SQL injection. > -dhs > > > > Dean H. Saxe, CISSP, CEH > > [EMAIL PROTECTED] > > "If liberty means anything at all, it means the right to tell people what > they do not want to hear." > > -- George Orwell, 1945 > > > > On Jan 24, 2008, at 7:45 AM, Dusty Hale wrote: > > That was an excellent thought. I've never used cffinally but just looked > up what it does. > > thx > > -Dusty > > ------------------------------ > *From:* [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] <[EMAIL PROTECTED]>] *On > Behalf Of *Dean H. Saxe > *Sent:* 01/24/2008 6:56 AM > *To:* discussion@acfug.org > *Subject:* Re: [ACFUG Discuss] closing database connections > > Actually, you should wrap your code in a try/catch/finally block. The > closing of the connection should always happen in the finally block to > ensure that it closes if an exeption occurs. > That being said, once this object is out of scope, garbage collection > should close the connection for you, but its unclear when that happens due > to garbage collection rules. > > -dhs > > > > Dean H. Saxe, CISSP, CEH > > [EMAIL PROTECTED] > > "If liberty means anything at all, it means the right to tell people what > they do not want to hear." > > -- George Orwell, 1945 > > > > On Jan 23, 2008, at 11:51 PM, Darin Kohles wrote: > > Looking back at some of my old Java code, you can (should?) close both > your query (aka statement) and your connection; so in your code just add the > lines: > > st.close(); > con.close(); > > Put that somewhere at then end, in that order, after you've got your > return data set. Close the Statement (and/or PreparedStatement) then close > the Connection. As for in CF, unless your set your queries to cache, I think > they are freed when complete. John Mason did a presentation a while back > about memory management, but I'm not sure what your access would be to the > CF server to be able to change anything in that regard. > > Hope that does the trick. > > Darin Kohles > Web Application Developer > > On Jan 23, 2008 9:36 PM, Dusty Hale <[EMAIL PROTECTED]> wrote: > > > I've run into a problem with my VPS server at hostmysite. It seems my > > server gets really low on memory due to database connections that are left > > open (so I'm told by HMS) and HMS recommended this: > > > > ------------------------------------------ > > From HMS support: > > > > SQL memory problems like this usually happen because of looping queries, > > or hanging connections. The best solution is to update your code, adjusting > > the queries to close the connection to the database once complete. This will > > ensure that the memory used for the query is then freed. > > > > ------------------------------------------- > > > > OK so when I used to do ASP and VBScript I always closed and destroyed > > the database connections and objects. However, I didn't even know there was > > a way to do it with queries in CF. Is this right? Should I somehow be > > closing database connections? In most cases, these are just <cfquery> tags > > used with either <cfloop> or <cfoutput> to display the query results. > > However, there is one area of this site where I used Java inside of > > <cfscript> to open a database connection so I am guessing that may be where > > I should be closing the connection. The code reminds of the way I used to do > > it with ASP. See example: > > > > <cfscript> > > classLoader = createObject("java", "java.lang.Class"); > > classLoader.forName("sun.jdbc.odbc.JdbcOdbcDriver"); > > dm = createObject("java","java.sql.DriverManager"); > > con = dm.getConnection("jdbc:odbc:DRIVER={SQL Server};Database=" & > > application.db_name & ";Server=" & application.dbserver_name & ";", > > application.db_user, application.db_pword); > > qText = "Select * FROM SomeTable"; > > st = con.createStatement(); > > rs = st.ExecuteQuery(qText); > > q = createObject("java", "coldfusion.sql.QueryTable").init(rs); > > </cfscript> > > > > 1. Ok so I am guessing there is a way to close the connection and > > destory the objects. If so, could someone send me a snippet of code to do > > that? > > > > 2. I'm not sure if closing a database connection is needed when using > > <cfquery> tags. If so, I've been missing that part for well over 5 years of > > cf coding. Ooops!!! Hope that is not the case. > > > > > > > > -Dusty > > > > > > ------------------------------------------------------------- > > Annual Sponsor - Figleaf Software <http://www.figleaf.com> > > > > To unsubscribe from this list, manage your profile @ > > http://www.acfug.org?fa=login.edituserform > > > > For more info, see http://www.acfug.org/mailinglists > > Archive @ http://www.mail-archive.com/discussion%40acfug.org/ > > List hosted by FusionLink <http://www.fusionlink.com> > > ------------------------------------------------------------- > > > > ------------------------------------------------------------- > Annual Sponsor - Figleaf Software <http://www.figleaf.com> > > To unsubscribe from this list, manage your profile @ > http://www.acfug.org?fa=login.edituserform > > For more info, see http://www.acfug.org/mailinglists > Archive @ http://www.mail-archive.com/discussion%40acfug.org/ > List hosted by FusionLink <http://www.fusionlink.com> > ------------------------------------------------------------- > > > > ------------------------------------------------------------- > Annual Sponsor - Figleaf Software <http://www.figleaf.com> > > To unsubscribe from this list, manage your profile @ > http://www.acfug.org?fa=login.edituserform > > For more info, see http://www.acfug.org/mailinglists > Archive @ http://www.mail-archive.com/discussion%40acfug.org/ > List hosted by FusionLink <http://www.fusionlink.com> > ------------------------------------------------------------- > > > -- Douglas Knudsen http://www.cubicleman.com this is my signature, like it? ------------------------------------------------------------- Annual Sponsor FigLeaf Software - http://www.figleaf.com To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com -------------------------------------------------------------
