View Full Version : How do i know when to close a connection and re-open it?

02-27-2003, 07:47 AM
I have developed an e-commerce site over the last couple of weeks... It works great - I just think that i might be handling the database connection badly... When should i open and close connections and result sets?

I also have this problem with opening pages - sometimes (not very often) i get errors due to lack of information from the database... I guess its because of my bad connection handling or a slow connection, but is there a way i can fix it?

02-27-2003, 01:45 PM
about connection:
- put the code to open the connection in a SSI (which makes is easier to switch databeses).
-insert the refference to the ssi just before you execute the sql statements (so afer your vallidations of the posted data)
-close it after the last sql statement

if you use if-then-else or other control of flows: be sure that the closing is put at the right place (the connection should only be closed if it was opened)
If you use a redirect: close the connection and recorsets first + set them to nothing !!

always set the connection and recordset to nothing!

close the recordset as soon aspossible (after the last value was read) + set to nothing.
keep them as small as possible ! (don't use select * from .... do your filtering and aeragion through sql and not in asp etc)

02-27-2003, 01:58 PM
Thanx for the advice, but what if i have a whole lot of database infomation on one page? At the moment i have it as:

open connection
open resultset
close resultset
open resultset
close resultset
open resultset
close resultset
close connection

is this ok, or should i not close the resultset every time? or anything else?

02-27-2003, 02:24 PM
you only need to open and close the recordset once.

the sequence is:
1. open connection
2. open recordset (+ execute sql statement)
3. display the info you need from that recordest
4. close recordset + set to nothing
5. close connection + set to nothing

now, between 1 and 5, you can have multiple recordsets open (all created by different sql statement) or you can run updatequerys and stuff.
or you can open multiple recordsets and display info from these sets or even compare values from these different recordsets.

just close the recordset after you used the last value you need from it (but be careful that the recordset was openen before you close it !)

this, for instance, wount work:
if blablabla then
open recordset
end if
close recordset