RE: [ACFUG Discuss] closing database connections
Many thanks Darin. I added it to my cfc and the spikes in memory seem to be
under control now.
-Dusty
_
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Darin Kohles
Sent: 01/23/2008 11:51 PM
To: discussion@acfug.org
Subject: Re: [ACFUG Discuss] closing database connections
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 http://www.figleaf.com 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 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 http://www.fusionlink.com
-
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
-
RE: [ACFUG Discuss] closing database connections
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 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 http://www.fusionlink.com
-
Re: [ACFUG Discuss] closing database connections
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
Re: [ACFUG Discuss] closing database connections
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
Re: [ACFUG Discuss] closing database connections
Good point, Doug. So if your connections are limited by the DB
server, the connection pool + these connections outside the pool may
be maxing out your possible DB connections...
Is there a good reason to avoid using the built in connection pools?
-dhs
Dean H. Saxe, CISSP, CEH
[EMAIL PROTECTED]
Dissent is the purest form of patriotism.
--Thomas Jefferson
On Jan 24, 2008, at 9:52 AM, Douglas Knudsen wrote:
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] 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
RE: [ACFUG Discuss] closing database connections
That pretty much covered it. It's also one of the benefits to CF in my opinion. As far as a case where you would want to turn off connection pooling. I haven't run across a situation that would call for that. Here's an interesting article by Steven Erat about conection pooling.. http://www.talkingtree.com/blog/index.cfm/2005/3/14/ConnPooling1 Now going back to the memory stuff and your db connections, here's a few basic things to consider as well. -avoid select *.. calls they may work when your developing and when the database table has few records, but in time this can easily bite you in the butt as the data in the table grows. You also want to see if there's a way to eliminate old data over time. -cache your cfquery calls, if your running CF8 you can now use cfqueryparam with cachedwithin and cachedafter. So you really have no excuse not to cache several of your more common db calls. Also bare in mind, if it's a high traffic site - caching a db call for just 10 seconds can make a big improvement and reduce the number of times you need to open/close db connections. -remember that sql server (and the other db servers) have several functions, stored procedures and just basic verbiage that can do some very powerful stuff. For example, I see people all the time doing a select * and then a cfloop to find duplicates in a table. This can be handle more efficiently in SQL with something like.. select email, count(email) as ecount from users group by email having (count(email) 1) John [EMAIL PROTECTED] _ From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Douglas Knudsen Sent: Thursday, January 24, 2008 9:53 AM To: discussion@acfug.org Subject: Re: [ACFUG Discuss] closing database connections 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] 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] mailto:[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
