473,320 Members | 2,006 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Question about good practice for opening/terminating connections, etc

2
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.CreateObject("ADODB.Connection")
objconn.open "DSN=mydatabase;","addon","addons69%"
....and others use an absolute path.
Dim conn
'create a database connection
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\pathtomy_database.mdb;" & _
"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.CreateObject("MSXML.DOMDocument")
  • Filename = server.MapPath("filename.xml")

Thanks
Kessa
Feb 26 '08 #1
4 2445
idsanjeev
241 100+
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
Expand|Select|Wrap|Line Numbers
  1. <%
  2. Set conn = Server.CreateObject("ADODB.Connection")
  3. conn.Provider = "Microsoft.Jet.OLEDB.4.0"
  4. conn.Open(Server.Mappath("database.mdb"))
  5. Set R = Server.CreateObject("ADODB.Recordset")
  6. R.Open "Select * From vUSER", conn
  7. ...........
  8. ...........
  9. R.close
  10. conn.close
  11.  
  12. %>
  13.  
if you close the connection then all recordset should be closed
Feb 27 '08 #2
jhardman
3,406 Expert 2GB
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:
Expand|Select|Wrap|Line Numbers
  1.    objConn.open "DSN=myDB;UID=myUserID;PWD=myPassword"
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 FileSystemObjects and any ADODB object could drain resources.

Jared
Feb 27 '08 #3
markrawlingson
346 Expert 100+
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.CreateObject 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:

Expand|Select|Wrap|Line Numbers
  1. ors.open sSQL, connection, adoConst, adoConst
  2. If ors.EOF Then
  3. Response.Write "No record found."
  4. Response.End '<--- page will end here, so clean up of objects never gets done.
  5. End If
  6. ors.close
  7. Set ors = nothing
  8.  
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
Feb 28 '08 #4
jhardman
3,406 Expert 2GB
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
Feb 29 '08 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Mudge | last post by:
Hi, My hosting provider only allows me to use 50 connections to my MySQL database that my Web site will use. I don't know what this 50 connections means exactly. Does this mean that only 50...
11
by: Mark Yudkin | last post by:
The documentation is unclear (at least to me) on the permissibility of accessing DB2 (8.1.5) concurrently on and from Windows 2000 / XP / 2003, with separate transactions scope, from separate...
55
by: Steve Jorgensen | last post by:
In a recent thread, RKC (correctly, I believe), took issue with my use of multiple parameters in a Property Let procedure to pass dimensional arguments on the basis that, although it works, it's...
3
by: timtos | last post by:
Hi. I have to use a database in my c# program. For that I created a MySqlManager class. If it is being instanciated a connection will be established like this: odbcConnection = new...
3
by: Amadelle | last post by:
Hi All and thanks in advance, I wanted to know when is a good idea to use a static class (with static constructor) and when to use instance classes? I have read couple of articles on line and...
3
by: Wing | last post by:
Hi all, I am writing the C# code function that take 2 parameters and access the data in the MS SQL database and return a SqlDataReader object. In my funtion, a SQL connection is established and...
4
by: | last post by:
I was just wondering what other people's opinions and experiences were in regards to using Database Connections throughout a website. Supposing that a single webpage accesses a database anywhere...
51
by: bigHairy | last post by:
Hello. I have been teaching myself .NET over the last few months and have had some success. I would like to ask a question though... A number of examples I have followed have the following in...
20
by: fniles | last post by:
I am using VS2003 and connecting to MS Access database. When using a connection pooling (every time I open the OLEDBCONNECTION I use the exact matching connection string), 1. how can I know how...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.