The correct way to close a database connection?

[Andyza]

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 Barrows [MVP]]

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.

 

[Bob Lehmann]

Yours is better.

Bob Lehmann

 

[Andyza]

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?

 

[Anthony Jones]

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.

 

[Bob Barrows [MVP]]

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.

 

[Bob Barrows [MVP]]

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&[email protected]

http://groups.google.com/groups?hl=...=1&[email protected]

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.

 

[Dave Anderson]

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.

 

[Anthony Jones]

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]

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]

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]

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

 

[Andyza]

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!

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