Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    Regular Coder
    Join Date
    Feb 2003
    Location
    South Africa
    Posts
    251
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question How do i know when to close a connection and re-open it?

    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?

  • #2
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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)

  • #3
    Regular Coder
    Join Date
    Feb 2003
    Location
    South Africa
    Posts
    251
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?

  • #4
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    else
    blablabla
    end if
    close recordset


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •