Question about good practice for opening/terminating connections, etc

[kessa]

Hi Guys,

I've recently had a problem with my site displaying a "system resources exceeded" error message ... and whilst searching this site for a solution (which I think I've now found) I came across mention of closing database connections and so had a "best practice" question which I hoped someone might be able to help me with?

I'm a .asp newbie and much of what I have learnt has been done by looking at various snippets of code on the web and "hacking and slashing" code to get it to do what I need..... so needless to say, I've probably (inadvertently) picked up some bad habits.

However, I want to improve my coding and so I thought I'd start with connections sooo.....

Question 1: Opening Connections
Is there a best way to open a database connection? I've seen some people use a DNS.....

set objconn = Server.CreateOb ject("ADODB.Con nection")
objconn.open "DSN=mydatabase ;","addon","add ons69%"

....and others use an absolute path.

Dim conn
'create a database connection
Set conn = Server.CreateOb ject("ADODB.Con nection")
conn.Open "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=c:\patht omy_database.md b;" & _
"Jet OLEDB:Database"

Also, how do you reference a usename and password with each approach?

Is either better or more secure than the other, or are there situations where one should be used over the other, or is it just a question of personal preference?

Question 2: Closing connections.
I've seen mention that you should close connections with something like:

obj.close
obj = nothing

... but what I wondered is whether you should also close:

a) recordsets?
b) variables?

If you should close these, then can you do it just by closing the connection (i.e. does closing the connection mean that all child dependents collapse by default?)

Finally, do the following each need to be closed?

• Set domDoc = server.CreateOb ject("MSXML.DOM Document")
• Filename = server.MapPath( "filename.x ml")

Thanks
Kessa

[idsanjeev]

Hi kessa
you Haven't any problems becouse i think answer already posted in your post
Thats is you can open connection with your personal preference but i think the best idea is to short the code so i prefer like this
[code=asp]
<%
Set conn = Server.CreateOb ject("ADODB.Con nection")
conn.Provider = "Microsoft.Jet. OLEDB.4.0"
conn.Open(Serve r.Mappath("data base.mdb"))
Set R = Server.CreateOb ject("ADODB.Rec ordset")
R.Open "Select * From vUSER", conn
...........
...........
R.close
conn.close

%>
[/code]
if you close the connection then all recordset should be closed

[jhardman]

As far as best practices go, I find it is generally a better idea to use the DNS connection, it is easier to change especially if more than one page or more than one app connect to the same db. I would never use a file path unless possibly I was connecting to an Access db, and I just had to throw something up fast (making a DSN might take some troubleshooting ). Connection string looks like this:[code=asp] objConn.open "DSN=myDB;UID=m yUserID;PWD=myP assword"[/code]For closing objects, best practice is to close it as soon as you are done with it, but remember that it is never absolutely necessary to close objects, since all vbscript objects are closed automaticaly when the script finishes. However, if you are working on a high-traffic site and need to save resources, then you should definitely close every object you can. I'd be surprised if closing run-of-the-mill variables had any advantage, but definitely things like FileSystemObjec ts and any ADODB object could drain resources.

Jared

[markrawlingson]

since all vbscript objects are closed automaticaly when the script finishes.

Jared is correct in the above statement except for a single word.. "are". In reality, the ASP engine SHOULD clean up the objects after the page is finished executing - however it doesn't always. The standard garbage collection is incomplete and unreliable. If you don't explicity clean up your objects you are effectively placing all of your faith in the ASP engine to do the dirty work for you. But what happens when the ASP garbage collection fails to take those objects out of memory? They simply stay there, doing nothing, and are totally inaccessible to any program on your server (even ASP) until you reboot the server. This is called a memory leak.

Here are some tips for you:

1) Always destroy objects immediately after you're finished with them.
2) Know the difference between an object and other variables. Other variables don't need to be cleaned up. Most often this is anything that is created using Server.CreateOb ject Or CreateObject
3) Keep object creation to a minimum by having an intelligently designed database which makes efficient use of RDBMS concepts and make efficient use of SQL joins. This way you won't need any more than 1 database connection on your page, and maybe 3 recordsets at MAX. I personally don't like opening any more than 1 or 2 recordsets on a page, no matter what it is.
4) Never open and/or close an object inside of a loop. If you do, this will effectively create an object for each iteration through your loop. Say you loop 300 times - you just created 300 objects.
5) Pay attention, listen to your server - when your page is loading slowly - there's a reason for it, and chances are it's because of your objects.
6) Be especially careful with the Response.End Method. I've seen way too many a programmer open a recordset, check to see if a record is returned, and then kill the page using response.end without cleaning up the object first.

EG:

[code=asp]
ors.open sSQL, connection, adoConst, adoConst
If ors.EOF Then
Response.Write "No record found."
Response.End '<--- page will end here, so clean up of objects never gets done.
End If
ors.close
Set ors = nothing
[/code]

In a nutshell, sure.. the asp garbage collector will probably do your dirty work and kill the objects you've left open most of the time. But do you really want to rely on it? The benefits of putting the extra effort into cleaning up your objects and being smart about how you go about programming things always outweighs not doing so.

An ounce of prevention truly is worth a pound of cure :)

Sincerely,
Mark

[jhardman]

Jared is correct in the above statement except for a single word.. "are". In reality, the ASP engine SHOULD clean up the objects after the page is finished executing - however it doesn't always. The standard garbage collection is incomplete and unreliable. If you don't explicity clean up your objects you are effectively placing all of your faith in the ASP engine to do the dirty work for you. But what happens when the ASP garbage collection fails to take those objects out of memory? They simply stay there, doing nothing, and are totally inaccessible to any program on your server (even ASP) until you reboot the server. This is called a memory leak.

Point taken.

Jared