Never closing database connections - a problem?

[Peter Larsson]

Hi there,

I have been doing database connections through ASP and VB for a few years
now, but I've lately come to realize that I might not be working with the
databases in the "right" way. Readings I have done lately state that it's
important to close the connections to the database as soon as you don't need
the returned recordset anymore, but I'm wondering how to do this in an
efficient way.

I have a sub procedure that I use in ASP (and a similar one for VB) and that
I call to perform any SQL command:

Sub db_open(objectname,sp)
dim db, dbConn, dbCmd
Set dbConn = Server.CreateObject("ADODB.Connection")
dbConn.open "Provider=SQLOLEDB; Data Source=XXX; Initial Catalog=XXX;
User ID=XXX; Password=XXX; network=DBMSSOCN"
Set dbCmd = Server.CreateObject("ADODB.Command")
Set dbCmd.ActiveConnection = dbConn
dbCmd.CommandText = sp
dbCmd.CommandType = 1
Set db = Server.CreateObject("ADODB.Recordset")
Set db = dbCmd.Execute
Set objectname = db
End Sub

And I call it from my ASP page by something like:
Dim db
Call db_open(db,"SELECT * FROM SomeTable")

And when I'm done with the recordset, I simply close the object by:
db.Close

Now - does this mean that the connection is still open? Is there a way I can
close it, and if so, when should it be cone?Or does is not matter if I leave
it open?

I'm very thankful for any suggestions and comments that you can provide me
with. Maybe I need to rethink and not use a sub procedure like this?

Greetings // Peter Larsson

[Bob Barrows]

Peter Larsson wrote:

> Hi there,
>
> I have been doing database connections through ASP and VB for a few
> years now, but I've lately come to realize that I might not be
> working with the databases in the "right" way. Readings I have done
> lately state that it's important to close the connections to the
> database as soon as you don't need the returned recordset anymore,
> but I'm wondering how to do this in an efficient way.
>

> And I call it from my ASP page by something like:
> Dim db
> Call db_open(db,"SELECT * FROM SomeTable")
>
> And when I'm done with the recordset, I simply close the object by:
> db.Close
>
> Now - does this mean that the connection is still open?

Usually not. But, if a transaction is in progress, the connection might not
close, and therefore will not lose its reference when it goes out of scope.

So make sure you've closed _ and destroyed _ any recordset objects you may
have opened. Also destroy any Command objects. Once that is done, closing
and destroying the connection will work.

> Is there a
> way I can close it, and if so, when should it be cone?

rs.close: set rs=nothing
db.close: set db=nothing

> Or does is not
> matter if I leave it open?

Orphaned ADO objects (objects that have gone out of scope but were not
destroyed) can cause memory leaks that can eventually cause IIS to crash,
forcing a reboot.

Normally, the garbage cleanup process will cause the connection to close and
be destroyed, but every so often ...

It just takes a couple extra lines of code to be sure, right?

>
> I'm very thankful for any suggestions and comments that you can
> provide me with. Maybe I need to rethink and not use a sub procedure
> like this?

No, that is irrelevant. Don't call the sub until just before you need the
opened connection. Close and destroy the connection as soon as the database
operation is complete.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Paul Clement]

On Thu, 15 Jan 2004 00:58:11 -0800, "Peter Larsson" <nospam@thankyou.com> wrote:

¤ Hi there,
¤
¤ I have been doing database connections through ASP and VB for a few years
¤ now, but I've lately come to realize that I might not be working with the
¤ databases in the "right" way. Readings I have done lately state that it's
¤ important to close the connections to the database as soon as you don't need
¤ the returned recordset anymore, but I'm wondering how to do this in an
¤ efficient way.
¤
¤ I have a sub procedure that I use in ASP (and a similar one for VB) and that
¤ I call to perform any SQL command:
¤
¤ Sub db_open(objectname,sp)
¤ dim db, dbConn, dbCmd
¤ Set dbConn = Server.CreateObject("ADODB.Connection")
¤ dbConn.open "Provider=SQLOLEDB; Data Source=XXX; Initial Catalog=XXX;
¤ User ID=XXX; Password=XXX; network=DBMSSOCN"
¤ Set dbCmd = Server.CreateObject("ADODB.Command")
¤ Set dbCmd.ActiveConnection = dbConn
¤ dbCmd.CommandText = sp
¤ dbCmd.CommandType = 1
¤ Set db = Server.CreateObject("ADODB.Recordset")
¤ Set db = dbCmd.Execute
¤ Set objectname = db
¤ End Sub
¤
¤ And I call it from my ASP page by something like:
¤ Dim db
¤ Call db_open(db,"SELECT * FROM SomeTable")
¤
¤ And when I'm done with the recordset, I simply close the object by:
¤ db.Close
¤
¤ Now - does this mean that the connection is still open? Is there a way I can
¤ close it, and if so, when should it be cone?Or does is not matter if I leave
¤ it open?

Technically the object that references the connection is destroyed once it goes out of scope (after
the ASP code in the page has finished executing) but you should be closing it explicitly once you've
finished retrieving or updating data for the page. What you're doing looks to be just fine.

In addition, the actual connection does not actually terminate but is returned to a connection pool
(created by process and connection string) for reuse. If it remains unused (in the connection pool)
for a minute, it is then destroyed.


Paul ~~~ [email]pclement@ameritech.net[/email]
Microsoft MVP (Visual Basic)

[Casey]

I have a VB program where I leave the connection open the entire life-span
of the executable being in memory and then close it once the program is
terminated. My users pound that program for 8 to 10 hours daily and it has
never demonstrated any need for explicit statements setting objects to
nothing.

But I use the ADO Data Environment, and only write to the database via
explicit SQL.

Perhaps that makes the difference.

Casey

"Paul Clement" <UseAdddressAtEndofMessage@swspectrum.com> wrote in message
news:udad00pbevv95nkchlp14iufnvd9o9teu5@4ax.com...

> On Thu, 15 Jan 2004 00:58:11 -0800, "Peter Larsson" <nospam@thankyou.com>

wrote:

>
> ¤ Hi there,
> ¤
> ¤ I have been doing database connections through ASP and VB for a few

years

> ¤ now, but I've lately come to realize that I might not be working with

the

> ¤ databases in the "right" way. Readings I have done lately state that

it's

> ¤ important to close the connections to the database as soon as you don't

need

> ¤ the returned recordset anymore, but I'm wondering how to do this in an
> ¤ efficient way.
> ¤
> ¤ I have a sub procedure that I use in ASP (and a similar one for VB) and

that

> ¤ I call to perform any SQL command:
> ¤
> ¤ Sub db_open(objectname,sp)
> ¤ dim db, dbConn, dbCmd
> ¤ Set dbConn = Server.CreateObject("ADODB.Connection")
> ¤ dbConn.open "Provider=SQLOLEDB; Data Source=XXX; Initial

Catalog=XXX;

> ¤ User ID=XXX; Password=XXX; network=DBMSSOCN"
> ¤ Set dbCmd = Server.CreateObject("ADODB.Command")
> ¤ Set dbCmd.ActiveConnection = dbConn
> ¤ dbCmd.CommandText = sp
> ¤ dbCmd.CommandType = 1
> ¤ Set db = Server.CreateObject("ADODB.Recordset")
> ¤ Set db = dbCmd.Execute
> ¤ Set objectname = db
> ¤ End Sub
> ¤
> ¤ And I call it from my ASP page by something like:
> ¤ Dim db
> ¤ Call db_open(db,"SELECT * FROM SomeTable")
> ¤
> ¤ And when I'm done with the recordset, I simply close the object by:
> ¤ db.Close
> ¤
> ¤ Now - does this mean that the connection is still open? Is there a way I

can

> ¤ close it, and if so, when should it be cone?Or does is not matter if I

leave

> ¤ it open?
>
> Technically the object that references the connection is destroyed once it

goes out of scope (after

> the ASP code in the page has finished executing) but you should be closing

it explicitly once you've

> finished retrieving or updating data for the page. What you're doing looks

to be just fine.

>
> In addition, the actual connection does not actually terminate but is

returned to a connection pool

> (created by process and connection string) for reuse. If it remains unused

(in the connection pool)

> for a minute, it is then destroyed.
>
>
> Paul ~~~ [email]pclement@ameritech.net[/email]
> Microsoft MVP (Visual Basic)

[Mark Schupp]

When the VB program exist its windows process terminates which would clear
any allocated memory. It may or may not terminate an abandoned database
connection but I think most DBMSs will eventually clear the connection if
there is no process attached to it.

ASP scripts will be running in the context of a process that does not
terminate when the user exits (the web-server will not even know when the
user exits, it only knows about individual HTTP requests). So it is possible
for objects that are not deallocated properly to accumulate and eventually
crash the web-server process.

In theory, all local objects are deallocated when their procedure (or asp
page) terminates and any connections will automatically be closed (returned
to the connection pool). But if someone at MS makes a mistake or if you
encounter a situation with transactions described by Bob then YOU are the
one that pays the price.

So:
if you open it, close it
if you create it, destroy it

I never did like automatic garbage collection. My first experience with it
was in a LISP class in college and we used to get the following error
occasionally:

ERROR:Thrashing in garbage collector

--
Mark Schupp
Head of Development
Integrity eLearning
[url]www.ielearning.com[/url]


"Casey" <clengacher@smcky.com> wrote in message
news:OiFDw632DHA.3216@TK2MSFTNGP11.phx.gbl...

> I have a VB program where I leave the connection open the entire life-span
> of the executable being in memory and then close it once the program is
> terminated. My users pound that program for 8 to 10 hours daily and it has
> never demonstrated any need for explicit statements setting objects to
> nothing.
>
> But I use the ADO Data Environment, and only write to the database via
> explicit SQL.
>
> Perhaps that makes the difference.
>
> Casey
>
> "Paul Clement" <UseAdddressAtEndofMessage@swspectrum.com> wrote in message
> news:udad00pbevv95nkchlp14iufnvd9o9teu5@4ax.com...

> > On Thu, 15 Jan 2004 00:58:11 -0800, "Peter Larsson"

<nospam@thankyou.com>

> wrote:

> >
> > ¤ Hi there,
> > ¤
> > ¤ I have been doing database connections through ASP and VB for a few

> years

> > ¤ now, but I've lately come to realize that I might not be working with

> the

> > ¤ databases in the "right" way. Readings I have done lately state that

> it's

> > ¤ important to close the connections to the database as soon as you

don't

> need

> > ¤ the returned recordset anymore, but I'm wondering how to do this in an
> > ¤ efficient way.
> > ¤
> > ¤ I have a sub procedure that I use in ASP (and a similar one for VB)

and

> that

> > ¤ I call to perform any SQL command:
> > ¤
> > ¤ Sub db_open(objectname,sp)
> > ¤ dim db, dbConn, dbCmd
> > ¤ Set dbConn = Server.CreateObject("ADODB.Connection")
> > ¤ dbConn.open "Provider=SQLOLEDB; Data Source=XXX; Initial

> Catalog=XXX;

> > ¤ User ID=XXX; Password=XXX; network=DBMSSOCN"
> > ¤ Set dbCmd = Server.CreateObject("ADODB.Command")
> > ¤ Set dbCmd.ActiveConnection = dbConn
> > ¤ dbCmd.CommandText = sp
> > ¤ dbCmd.CommandType = 1
> > ¤ Set db = Server.CreateObject("ADODB.Recordset")
> > ¤ Set db = dbCmd.Execute
> > ¤ Set objectname = db
> > ¤ End Sub
> > ¤
> > ¤ And I call it from my ASP page by something like:
> > ¤ Dim db
> > ¤ Call db_open(db,"SELECT * FROM SomeTable")
> > ¤
> > ¤ And when I'm done with the recordset, I simply close the object by:
> > ¤ db.Close
> > ¤
> > ¤ Now - does this mean that the connection is still open? Is there a way

I

> can

> > ¤ close it, and if so, when should it be cone?Or does is not matter if I

> leave

> > ¤ it open?
> >
> > Technically the object that references the connection is destroyed once

it

> goes out of scope (after

> > the ASP code in the page has finished executing) but you should be

closing

> it explicitly once you've

> > finished retrieving or updating data for the page. What you're doing

looks

> to be just fine.

> >
> > In addition, the actual connection does not actually terminate but is

> returned to a connection pool

> > (created by process and connection string) for reuse. If it remains

unused

> (in the connection pool)

> > for a minute, it is then destroyed.
> >
> >
> > Paul ~~~ [email]pclement@ameritech.net[/email]
> > Microsoft MVP (Visual Basic)

>
>

[Paul Clement]

On Thu, 15 Jan 2004 10:46:09 -0500, "Casey" <clengacher@smcky.com> wrote:

¤ I have a VB program where I leave the connection open the entire life-span
¤ of the executable being in memory and then close it once the program is
¤ terminated. My users pound that program for 8 to 10 hours daily and it has
¤ never demonstrated any need for explicit statements setting objects to
¤ nothing.
¤
¤ But I use the ADO Data Environment, and only write to the database via
¤ explicit SQL.
¤
¤ Perhaps that makes the difference.

You're working in a different environment. ASP is stateless by nature so objects created within a
page are destroyed after the code in the page has finished executing.

VB apps are different in this respect where you can very easily maintain the state of objects and
the application. The DataEnvironment and DataControls maintain persistent connections by design.

I suppose that as long as your database administrator doesn't have an issue with persistent database
resource usage, or in the case of a Microsoft Access database, you aren't experiencing database
corruption, then the persistent use of connection resources will actually serve as a benefit by
eliminating the overhead associated with establishing those connections.


Paul ~~~ [email]pclement@ameritech.net[/email]
Microsoft MVP (Visual Basic)