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] 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
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
-------------------------------------------------------------
-------------------------------------------------------------
Annual Sponsor - Figleaf Software
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
-------------------------------------------------------------
-------------------------------------------------------------
Annual Sponsor - Figleaf Software
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
-------------------------------------------------------------