The correct way to close a database connection?

Discussion in 'ASP General' started by Andyza, Feb 13, 2007.

  1. Andyza

    Andyza Guest

    I'm looking at another developers code and I'm now confused about the
    correct way to close a database connection and destroy the object.

    I would normally do it this way:

    Set conn = Server.CreateObject("adodb.connection")
    conn.Execute(UPDATE TABLE Blah Set Something = 'Somevalue' WHERE
    whatever = 'blah')
    conn.Close
    Set conn = nothing

    This other developer has closed the connection this way:

    conn = Server.CreateObject("adodb.connection")
    conn.Execute(UPDATE TABLE Blah Set Something = 'Somevalue' WHERE
    whatever whatever = 'blah')
    conn.close()
    conn = null

    1. Which is correct (or better) way of coding this sql update?
    2. Is the "conn = null" method of destroying the 'conn' object valid?




    An example of the code that I've inherited is:

    db="DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" +
    Server.Mappath("thedb.mdb")
    conn=Server.CreateObject("adodb.connection")
    conn.Open(db)

    SQLStmt = "INSERT INTO tblBlah (Name,Surname,Occupation) "
    SQLStmt = SQLStmt + "VALUES ('" + frmTest[0] + "'" + ",'" + frmTest[1]
    + "'" + ",'" + frmTest[2] + "'"

    RS = conn.Execute(SQLStmt)
    RS = null
    conn.close()
    conn = null


    My way of coding it would be like this:

    Dim SQLStmt, db, conn
    SQLStmt = "INSERT INTO tblBlah (Name,Surname,Occupation) " & _
    "VALUES ('" & frmTest[0] & "', '" & frmTest[1] & "', '" &
    frmTest[2] & "')"

    Set conn = Server.CreateObject("adodb.connection")
    conn.Open("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" +
    Server.Mappath("thedb.mdb"))
    conn.Execute(SQLStmt)
    conn.Close
    Set conn = nothing


    3. Assigning the conn.Execute(SQLStmt) to the RS variable in the
    original code is not necessary. Am I correct?
    4. Is the "conn = null" method of destroying the 'conn' object valid?

    Yes, I know, putting the sql code into a stored procedure would be the
    best solution, but I'd like to know which of the above methods is
    better.

    Thanks.
     
    Andyza, Feb 13, 2007
    #1
    1. Advertising

  2. Andyza wrote:
    > I'm looking at another developers code and I'm now confused about the
    > correct way to close a database connection and destroy the object.
    >
    > I would normally do it this way:
    >
    > Set conn = Server.CreateObject("adodb.connection")
    > conn.Execute(UPDATE TABLE Blah Set Something = 'Somevalue' WHERE
    > whatever = 'blah')
    > conn.Close
    > Set conn = nothing
    >
    > This other developer has closed the connection this way:
    >
    > conn = Server.CreateObject("adodb.connection")
    > conn.Execute(UPDATE TABLE Blah Set Something = 'Somevalue' WHERE
    > whatever whatever = 'blah')
    > conn.close()
    > conn = null
    >
    > 1. Which is correct (or better) way of coding this sql update?

    Neither :)
    As written, the above statements will not run - try it. :)
    OK, I accept that the quotes around the sql statements were left out
    inadvertantly. However, there are still issues:
    1. You should assign your sql statement to a variable and pass the
    variable to the Execute method. It makes debugging easier.
    2. You should always specify the commandtype argument instead of making
    ADO guess. Usually it will guess correctly that the command type is
    Text, but in rare cases, it can guess wrong, leading hours of debugging:
    const adCmdText = 1
    dim sql
    sql="UPDATE TABLE Blah Set Something = 'Somevalue' " & _
    "WHERE whatever = 'blah'"
    conn.Execute sql,,adCmdText
    conn.close: set conn=nothing

    Better yet, you should use parameters instead of dynamic sql:
    http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e


    > 2. Is the "conn = null" method of destroying the 'conn' object valid?
    >

    Not in vbscript. In jscript, yes.
    >
    >
    > An example of the code that I've inherited is:
    >
    > db="DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" +
    > Server.Mappath("thedb.mdb")
    > conn=Server.CreateObject("adodb.connection")
    > conn.Open(db)
    >
    > SQLStmt = "INSERT INTO tblBlah (Name,Surname,Occupation) "
    > SQLStmt = SQLStmt + "VALUES ('" + frmTest[0] + "'" + ",'" + frmTest[1]
    > + "'" + ",'" + frmTest[2] + "'"
    >
    > RS = conn.Execute(SQLStmt)
    > RS = null
    > conn.close()
    > conn = null
    >
    >
    > My way of coding it would be like this:
    >
    > Dim SQLStmt, db, conn
    > SQLStmt = "INSERT INTO tblBlah (Name,Surname,Occupation) " & _
    > "VALUES ('" & frmTest[0] & "', '" & frmTest[1] & "', '" &
    > frmTest[2] & "')"
    >
    > Set conn = Server.CreateObject("adodb.connection")
    > conn.Open("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" +
    > Server.Mappath("thedb.mdb"))
    > conn.Execute(SQLStmt)
    > conn.Close
    > Set conn = nothing
    >
    >
    > 3. Assigning the conn.Execute(SQLStmt) to the RS variable in the
    > original code is not necessary. Am I correct?
    > 4. Is the "conn = null" method of destroying the 'conn' object valid?
    >
    > Yes, I know, putting the sql code into a stored procedure would be the
    > best solution, but I'd like to know which of the above methods is
    > better.
    >
    > Thanks.


    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows [MVP], Feb 13, 2007
    #2
    1. Advertising

  3. Andyza

    Bob Lehmann Guest

    Yours is better.

    Bob Lehmann

    "Andyza" <> wrote in message
    news:...
    > I'm looking at another developers code and I'm now confused about the
    > correct way to close a database connection and destroy the object.
    >
    > I would normally do it this way:
    >
    > Set conn = Server.CreateObject("adodb.connection")
    > conn.Execute(UPDATE TABLE Blah Set Something = 'Somevalue' WHERE
    > whatever = 'blah')
    > conn.Close
    > Set conn = nothing
    >
    > This other developer has closed the connection this way:
    >
    > conn = Server.CreateObject("adodb.connection")
    > conn.Execute(UPDATE TABLE Blah Set Something = 'Somevalue' WHERE
    > whatever whatever = 'blah')
    > conn.close()
    > conn = null
    >
    > 1. Which is correct (or better) way of coding this sql update?
    > 2. Is the "conn = null" method of destroying the 'conn' object valid?
    >
    >
    >
    >
    > An example of the code that I've inherited is:
    >
    > db="DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" +
    > Server.Mappath("thedb.mdb")
    > conn=Server.CreateObject("adodb.connection")
    > conn.Open(db)
    >
    > SQLStmt = "INSERT INTO tblBlah (Name,Surname,Occupation) "
    > SQLStmt = SQLStmt + "VALUES ('" + frmTest[0] + "'" + ",'" + frmTest[1]
    > + "'" + ",'" + frmTest[2] + "'"
    >
    > RS = conn.Execute(SQLStmt)
    > RS = null
    > conn.close()
    > conn = null
    >
    >
    > My way of coding it would be like this:
    >
    > Dim SQLStmt, db, conn
    > SQLStmt = "INSERT INTO tblBlah (Name,Surname,Occupation) " & _
    > "VALUES ('" & frmTest[0] & "', '" & frmTest[1] & "', '" &
    > frmTest[2] & "')"
    >
    > Set conn = Server.CreateObject("adodb.connection")
    > conn.Open("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" +
    > Server.Mappath("thedb.mdb"))
    > conn.Execute(SQLStmt)
    > conn.Close
    > Set conn = nothing
    >
    >
    > 3. Assigning the conn.Execute(SQLStmt) to the RS variable in the
    > original code is not necessary. Am I correct?
    > 4. Is the "conn = null" method of destroying the 'conn' object valid?
    >
    > Yes, I know, putting the sql code into a stored procedure would be the
    > best solution, but I'd like to know which of the above methods is
    > better.
    >
    > Thanks.
    >
     
    Bob Lehmann, Feb 13, 2007
    #3
  4. Andyza

    Andyza Guest

    On Feb 13, 3:37 pm, "Bob Barrows [MVP]" <>
    wrote:

    > 1. You should assign your sql statement to a variable and pass the
    > variable to the Execute method. It makes debugging easier.


    Isn't that what I've already done with the conn.Execute(SQLStmt)
    line?
    The sql statement is assigned to the 'SQLStmt' variable and then
    conn.Execute(SQLStmt) executes the value in the SQLStmt variable, ie:

    SQLStmt = "INSERT INTO tblBlah (Name,Surname,Occupation) " & _
    "VALUES ('" & frmTest[0] & "', '" & frmTest[1] & "', '" &
    frmTest[2] & "')"

    <snip>

    conn.Execute(SQLStmt)

    Isn't this what you were saying I should do?
     
    Andyza, Feb 13, 2007
    #4
  5. "Andyza" <> wrote in message
    news:...

    > 4. Is the "conn = null" method of destroying the 'conn' object valid?


    It looks like JScript since that is how it's done in JScript. However it
    will work in VBScript as well but it's a bit e wierd. Set conn = nothing is
    more explicit. Personally I wouldn't bother with either but that's just me
    the critical thing is the .Close since that releases the resources that
    really need releasing ASAP.
     
    Anthony Jones, Feb 13, 2007
    #5
  6. Andyza wrote:
    > On Feb 13, 3:37 pm, "Bob Barrows [MVP]" <>
    > wrote:
    >
    >> 1. You should assign your sql statement to a variable and pass the
    >> variable to the Execute method. It makes debugging easier.

    >
    > Isn't that what I've already done with the conn.Execute(SQLStmt)
    > line?


    I didn't read that far. Let me read the rest of your message and reply
    to it later.
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows [MVP], Feb 13, 2007
    #6
  7. Andyza wrote:
    > db="DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" +
    > Server.Mappath("thedb.mdb")


    http://www.aspfaq.com/show.asp?id=2126

    > conn=Server.CreateObject("adodb.connection")
    > conn.Open(db)
    >
    > SQLStmt = "INSERT INTO tblBlah (Name,Surname,Occupation) "
    > SQLStmt = SQLStmt + "VALUES ('" + frmTest[0] + "'" + ",'" + frmTest[1]
    > + "'" + ",'" + frmTest[2] + "'"


    I guess this is jscript, not vbscript ...?


    Again, this is extremely vulnerable to sql injection. See:
    http://mvp.unixwiz.net/techtips/sql-injection.html
    http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

    See here for a better, more secure way to execute your queries by using
    parameter markers:
    http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e

    Personally, I prefer using stored procedures, or saved parameter queries
    as
    they are known in Access:

    Access:
    http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=

    http://groups.google.com/groups?hl=...=1&selm=

    >
    > RS = conn.Execute(SQLStmt)


    THIS IS HORRIBLE!!!!
    Do not open an expensive recordset when your sql statement does not
    return records!!

    > RS = null
    > conn.close()
    > conn = null


    Given that this is jscript, null is the only possibility. "Nothing" does
    not exist in jscript.
    >
    >
    > My way of coding it would be like this:
    >
    > Dim SQLStmt, db, conn


    The other developer is using jscript. you are using vbscript.

    > SQLStmt = "INSERT INTO tblBlah (Name,Surname,Occupation) " & _
    > "VALUES ('" & frmTest[0] & "', '" & frmTest[1] & "', '" &
    > frmTest[2] & "')"


    See above. You do not need to use a stored procedure to use parameters.

    >
    > Set conn = Server.CreateObject("adodb.connection")
    > conn.Open("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" +
    > Server.Mappath("thedb.mdb"))
    > conn.Execute(SQLStmt)


    Again. Specify the CommandType - see my initial reply.

    > conn.Close
    > Set conn = nothing
    >
    >
    > 3. Assigning the conn.Execute(SQLStmt) to the RS variable in the
    > original code is not necessary. Am I correct?
    > 4. Is the "conn = null" method of destroying the 'conn' object valid?


    Again, it will likely do the job, but Nothing is more correct.

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows [MVP], Feb 13, 2007
    #7
  8. Anthony Jones wrote:
    >> 4. Is the "conn = null" method of destroying the 'conn' object
    >> valid?

    >
    > It looks like JScript since that is how it's done in JScript.


    That might be how SOME PEOPLE do it in JScript, but it is not equivalent to
    the VBScript [Nothing] assignment. There is no practical difference in
    JScript between these two statements:

    conn = null
    conn = 0

    Neither triggers GC. On the other hand, there is an actual difference
    between these statements in VBScript:

    Set conn = Nothing
    conn = Null

    The first explicitly marks the object for GC. The second does not.



    --
    Dave Anderson

    Unsolicited commercial email will be read at a cost of $500 per message. Use
    of this email address implies consent to these terms.
     
    Dave Anderson, Feb 13, 2007
    #8
  9. "Dave Anderson" <> wrote in message
    news:...
    > Anthony Jones wrote:
    > >> 4. Is the "conn = null" method of destroying the 'conn' object
    > >> valid?

    > >
    > > It looks like JScript since that is how it's done in JScript.

    >
    > That might be how SOME PEOPLE do it in JScript, but it is not equivalent

    to
    > the VBScript [Nothing] assignment. There is no practical difference in
    > JScript between these two statements:
    >
    > conn = null
    > conn = 0
    >
    > Neither triggers GC. On the other hand, there is an actual difference
    > between these statements in VBScript:
    >
    > Set conn = Nothing
    > conn = Null
    >
    > The first explicitly marks the object for GC. The second does not.
    >


    Umm, actual no. There is no GC in VBScript. All objects in VBScript are
    COM objects. These objects track how often they are referenced and when
    they detect the reference count has dropped to 0 they will destroy
    themselves.

    When VBScript assigns a new value to a variable it first has to release the
    existing content. Eg.

    s = "Hello"
    s = "World"

    the second assignment to s requires VBScript to deallocate the currently
    held string "Hello".

    In the case where the variable holds a reference to an Interface VBScript is
    required to call the Release method of the interface before assigning the
    new value. This happens regardless of the type of data being assigned.
    E.g.

    Set s = New Class1
    s = "Pink Elephants"

    The second assignment causes VBScript to call Release on the object
    reference currently residing in the variable s. Since this is the only
    outstandin reference the ref count will drop to zero and Class1 code will
    destroy the object.

    Set o = Nothing

    Is identical to the above. It only varies after the release has ocurred. in
    that the Set operation doesn't attempt to call AddRef on the incoming object
    since it is a null object pointer.

    Set o = Nothing is in use in this way by the convention set by VB5/6 coders.

    Since VB is a typed language you can only assign an object reference to an
    object variable.

    Anthony.
     
    Anthony Jones, Feb 13, 2007
    #9
  10. "Dave Anderson" <> wrote in message
    news:...
    > "Anthony Jones" wrote:
    > > There is no GC in VBScript. All objects in VBScript are COM
    > > objects. These objects track how often they are referenced and
    > > when they detect the reference count has dropped to 0 they will
    > > destroy themselves.

    >
    > In that case, someone should tell Eric Lippert he has no idea what he is
    > talking about:
    >


    On the contrary Eric does know what he is talking about.

    > "VBScript on the other hand, has a much simpler stack-based
    > garbage collector. Scavengers are added to a stack when they
    > come into scope, removed when they go out of scope, and any
    > time an object is discarded it is immediately freed."
    >
    > http://blogs.msdn.com/ericlippert/archive/2003/09/17/53038.aspx
    >


    Never-the-less I would hardly call how VB/VBScript handles freeing variables
    on the stack a 'garbage collector'. I think he only refers to it as such
    so that VBScripts deallocation of resources can be more easily compared by
    the reader with JScript and other languages which do have an actual garbage
    collector.
     
    Anthony Jones, Feb 14, 2007
    #10
  11. "Dave Anderson" <> wrote in message
    news:...
    > "Anthony Jones" wrote:
    > > I think he only refers to it as such so that VBScripts deallocation
    > > of resources can be more easily compared by the reader with JScript
    > > and other languages which do have an actual garbage collector.

    >
    > I'll buy that.
    >
    > Now, will you agree that assigning null to a variable that previously held
    > an object is not "how it is done in JScript"?


    I see your point.

    In JScript an instance of ActiveXObject holds a reference to the COM object.
    Assigning the value of the resulting variable to other variables will not
    add further references to the COM object since all that is being copied is a
    reference to an ActiveXObject.

    When the code does something like this:-

    o = null

    all that happens is the variable now holds null. No action is performed on
    the ActiveXObject instance which may or may not be referenced elsewhere.

    At some point in the future the GC discovers this instance of an
    ActiveXObject is no longer referenced anywhere and destories it. Its only
    at this point that the COM object and an resources it is using are released.
     
    Anthony Jones, Feb 15, 2007
    #11
  12. "Dave Anderson" <> wrote in message
    news:...
    > "Anthony Jones" wrote:
    > > ...At some point in the future the GC discovers this instance of
    > > an ActiveXObject is no longer referenced anywhere and destories
    > > it. Its only at this point that the COM object and an resources
    > > it is using are released.

    >
    > This has always been my understanding of it.
    >
    > I do believe that the code offered by the OP was an example of JScript
    > copied from a VBScript example. I am usually saddened to see such a
    > perversion of an otherwise beautiful language.
    >


    I totally agree. Javascript becomes even better when the hosts own objects
    also conform such as in Firefox. IE's COM based DOM to Javascript interface
    works but is ultimately a kludge compared to the simplicity and
    extensibility of found in Mozilla.

    (shh don't tell the MS guys I said that.) ;)
     
    Anthony Jones, Feb 16, 2007
    #12
  13. Andyza

    Andyza Guest

    On Feb 13, 5:05 pm, "Bob Barrows [MVP]" <>
    wrote:
    >
    > I guess this is jscript, not vbscript ...?
    >

    Yes. She insisted on coding it in "Javascript".

    > Personally, I prefer using stored procedures, or saved parameter queries
    > as they are known in Access:


    Me too!

    > > RS = conn.Execute(SQLStmt)

    >
    > THIS IS HORRIBLE!!!!
    > Do not open an expensive recordset when your sql statement does not
    > return records!!


    I agree... and I've asked her to fix it...
     
    Andyza, Feb 16, 2007
    #13
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Paul van Rossem
    Replies:
    0
    Views:
    611
    Paul van Rossem
    Apr 7, 2005
  2. tshad
    Replies:
    1
    Views:
    470
    Scott M.
    Sep 6, 2006
  3. S_K
    Replies:
    6
    Views:
    693
    Russell
    Dec 7, 2007
  4. Iñaki Baz Castillo
    Replies:
    7
    Views:
    896
    Iñaki Baz Castillo
    Jan 12, 2010
  5. hyejin
    Replies:
    8
    Views:
    363
    Martin Honnen
    Aug 29, 2006
Loading...

Share This Page