Recordsets and db connections

[TimSki]

Hi,

I have an application written in calssic asp which connects to a sql server 2005 db.

I have 2 procs to open and close the db as follows

   
Dim strConn
Dim oConn
 
sub OpenDataConnection()
set oConn = CreateObject("ADODB.Connection")
	strConn = "Provider=SQLOLEDB; Data Source = xxx; Initial Catalog = xxx; User Id = xxx; Password=xxx"
	oConn.Open strConn
end sub
 
 
sub CloseDataConnection()
	oConn.Close 
	set oConn = nothing
end sub

I've just discovered (sorry if this is common knowledge) that doing this (case 1)...

 
OpenDataConnection()
set rsID = CreateObject("ADODB.recordset")
searchPhrase = "SELECT blah"
rsID.Open searchPhrase, strConn
rsID.close
CloseDataConnection()

is exactly the same as doing this (case 2)...

 OpenDataConnection() 
CloseDataConnection()
set rsID = CreateObject("ADODB.recordset")
searchPhrase = "SELECT blah"
rsID.Open searchPhrase, strConn
rsID.close

So have 3 questions...

1. Am i right in thinking that since strConn is a global variable, so as long as the connection has been opened at least once, strConn is available for use in the recordset open command.

2. In case 1, am i actually opening the connection twice (or at least making the call twice) i.e once via the sub and then a second time via the recordset open method.

3. Am i right in thinking that case 2 is not good practice since the connection has not been expicitly closed after the query.

Or maybe what i am pointing out demonstrates inefficiencies in my coding. fyi, i diligently code according to case 1 but maybe i can improve this further.

Thanks in advance for any help.

[DrBunchman]

Hi TimSki,

Are you actually returning a record set whilst using the code in Case 2? I'd be surprised if you were because you cannot open a recordset without referring to an open database connection. I think part of the problem is the code you're using to open the recordset.

It should be

rsID.Open searchPhrase, oConn

but you have used

rsID.Open searchPhrase, strConn

.

To answer your questions:

1. strConn is a global variable but it represents your connection string and not the database connection. Once you have closed your connection object (oConn) it cannot be used until it is re-opened.
2. No you are opening the database connection once and then opening the record set.

Can you change the bit of code as I've written it above so you're using your connection object (oConn) instead of your connection string (strConn) to open your record set. Your record set should open correctly and you should find that Case 2 will throw an error.

Let me know how it goes

Dr B

[TimSki]

Great, thanks for the clarification

[DrBunchman]

No problem.

Dr B

...

[TimSki]

Hi Dr B,

One more question if i may...

What is the difference between doing this...

 OpenDataConnection() 
set rsID = CreateObject("ADODB.recordset")
searchPhrase = "SELECT blah"
rsID.Open searchPhrase, strConn
rsID.close
CloseDataConnection()

and this...

 OpenDataConnection() 
set rsID = CreateObject("ADODB.recordset")
searchPhrase = "SELECT blah"
rsID.Open searchPhrase, oConn
rsID.close
CloseDataConnection()

i.e. using oConn instead of strConn. I've just changed everything to oConn from strConn but site performance seems to have dropped off considerably. fyi, i'm also trying to make the best use of connection pooling but this may not be relevant here.

[DrBunchman]

Hi TimSki,

I didn't think it was possible to open a record set in the way you describe above (I've never seen it done in that way) but have come to the conclusion that I was wrong!

To answer your original post correctly:

strConn is a global variable because it's outside the scope of the two subs.

Yes you ARE opening the database connection twice. You don't need to use OpenDataConnect ion & CloseDataConnec tion because an implied connection object will be created when you use your connection string to open your recordset directly. I don't know if there are any disadvantages to doing it this way - anybody got any thoughts on that? Because you are not explicitily closing the connection object the overheads may be higher but I don't know.

To streamline your code you can remove all references to these two subs as long as you define strConn somewhere.

Apologies for giving you incorrect advice in my first reply.

Dr B

[TimSki]

no problem, I certainly appreciate you coming back to me on this.

This whole problem relates to connection pooling where i've learnt that i should only create the connection just before i need it and then destroy it as soon as i'm done. so now it seems i have three options which presumably have different overheads...

Option 1
Declare oConn and strConn as global variables
Do OpenDataConnect ion(), CloseDataConnec tion at the top of the page to set oConn and strConn.
As i go down through the code simply open the recordests thus

set rsID = CreateObject("A DODB.recordset" )
searchPhrase = "SELECT blah"
rsID.Open searchPhrase, strConn
rsID.close

Option 2

Declare oConn and strConn as global variables
As i go down through the code simply open the recordests thus

OpenDataConnect ion()
set rsID = CreateObject("A DODB.recordset" )
searchPhrase = "SELECT blah"
rsID.Open searchPhrase, strConn
rsID.close
CloseDataConnec tion()

Option 3

Declare oConn and strConn as global variables
As i go down through the code simply open the recordests thus

OpenDataConnect ion()
set rsID = CreateObject("A DODB.recordset" )
searchPhrase = "SELECT blah"
rsID.Open searchPhrase, oConn (i.e. same as option 2 but not strConn)
rsID.close
CloseDataConnec tion()

Option 1 would seem to go against conventional wisdom but otherwise i have no clue which is best. I would assume the answer is pretty straightforward since this represents the most basic of asp/db processes ! Any help would be much appreciated.

[DrBunchman]

Option 1
Declare oConn and strConn as global variables
Do OpenDataConnect ion(), CloseDataConnec tion at the top of the page to set oConn and strConn.
As i go down through the code simply open the recordests thus

set rsID = CreateObject("A DODB.recordset" )
searchPhrase = "SELECT blah"
rsID.Open searchPhrase, strConn
rsID.close

If you use this method then you don't need to create the oConn object or use OpenDataConnect ion or CloseDataConnec tion. All you need to do is set the value of strConn at the top of your page.

Option 2

Declare oConn and strConn as global variables
As i go down through the code simply open the recordests thus

OpenDataConnect ion()
set rsID = CreateObject("A DODB.recordset" )
searchPhrase = "SELECT blah"
rsID.Open searchPhrase, strConn
rsID.close
CloseDataConnec tion()

This is certainly increasing the overhead because you are opening and closing a connection through the object oConn but then not using it.

Option 3

Declare oConn and strConn as global variables
As i go down through the code simply open the recordests thus

OpenDataConnect ion()
set rsID = CreateObject("A DODB.recordset" )
searchPhrase = "SELECT blah"
rsID.Open searchPhrase, oConn (i.e. same as option 2 but not strConn)
rsID.close
CloseDataConnec tion()

This is the method I have always employed where you explicitly create and destroy your connection object each time.

I don't know what the difference is between 1 and 3 (option 2 is a definite no) but I'm gonna look in to it and in the meantime I would pick one of these methods and go with it. If you use Option 3 then you are definitely destroying the connection object whereas I'm not sure that's the case with Option 1.

I'll have to get back to you on this unless any of the other forum experts have a view?

Dr B

[TimSki]

Ok thanks, i'll go with option 3 for the time being. It would be great to get any extra feedback that you or anyone else may have.

Thanks again.