The correct way to close a database connection?

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.

Re: The correct way to close a database connection?

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.inetser ver.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.

Re: The correct way to close a database connection?

Yours is better.

Bob Lehmann

"Andyza" wrote in message
news:1171369882.308752.57300@v45g2000cwv.googlegroups.com...
> 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.
>

Re: The correct way to close a database connection?

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] & "')"



conn.Execute(SQLStmt)

Isn't this what you were saying I should do?

Re: The correct way to close a database connection?

"Andyza" wrote in message
news:1171369882.308752.57300@v45g2000cwv.googlegroups.com...

> 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.

Re: The correct way to close a database connection?

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.

Re: The correct way to close a database connection?

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.inetser ver.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&sel m=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl

>
> 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.

Re: The correct way to close a database connection?

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.

Re: The correct way to close a database connection?

"Dave Anderson" wrote in message
news:uPw7Sf4THHA.4632@TK2MSFTNGP04.phx.gbl...
> 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.

Re: The correct way to close a database connection?

"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:

"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.a spx



--
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.

Re: The correct way to close a database connection?

"Dave Anderson" wrote in message
news:12t65map0c0el36@corp.supernews.com...
> "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.a spx
>

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.

Re: The correct way to close a database connection?

"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"?



--
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.

Re: The correct way to close a database connection?

"Dave Anderson" wrote in message
news:12t8r6v3kke98a0@corp.supernews.com...
> "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.

Re: The correct way to close a database connection?

"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.



--
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.

Re: The correct way to close a database connection?

"Dave Anderson" wrote in message
news:12ta97n34f4v206@corp.supernews.com...
> "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.) ;)

Re: The correct way to close a database connection?

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...