Implicit connections

[Mark J. McGinty]

[Bob Barrows, this is more to you than anyone else, but I didn't want to get
stuck in your spam trap]

As I was working on a project, I noticed something interesting about use of
statements such as:

dim cn, rs ' variants
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
'cn.Open[...]
rs.ActiveConnection = cn ' note the lack of the Set keyword.

This code actually assigns a Variant/Object/Connection to the
ActiveConnection property, and when you close the connection referenced by
ActiveConnection, cn will show as closed too.

If you instead used:

rs.ActiveConnection = cn.ConnectionString

or any other variable that is of type string (or variant subtype String)
*then* an implicit connection is created, however, it appears, from limited
testing, using ADO 2.8, this morning, here in my scenic office with a window
view, and with full realization that I could be entirely wrong, I might
add... where was I? Oh yeah, it looks like ADO extracts the object from the
variant in which the object is stored, even on a simple assignment.

It's significant to note that these observations were made from within VB6's
debugger, examining a recordset object that was created by/passed-in to a
COM object from script, therefore, all objects are wrapped in variants. It
looks like ActiveConnection is a Variant/Object/Connection unless it's
explicitly set to a connection object. (I'm babbling?)

Anyway, the key factor is whether the connection is wrapped in a variant.
If it is not, then the default property is it's connection string, and a
simple assignment creates an implicit connection. If it is wrapped in a
variant, then the simple assignment assigns one Variant/Object/Connection to
another, and no implicit connection is created and opened.

Here's some script to confuse the issue:

'''''''''''''''''''
Dim cn, rs
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.ConnectionString =
"Provider=SQLOLEDB;Trusted_Connection=Yes;Server=(local);"
cn.Open
rs.ActiveConnection = cn
rs.Open "select * from sysobjects"
Response.Write "Reality check, rs.State = " & rs.State & "<br>"
cn.Close

Response.Write "rs.State = " & rs.State

if rs.State = 0 then
Response.Write ", recordset forced closed when underlying connection was
closed"
else
Response.Write ", someone tell McGinty to STFU"
end if
'''''''''''''''''''

And now that I've blown entirely too much time on Usenet again, I think I'll
get back to work.


-Mark

 

[Bob Barrows [MVP]]

[Bob Barrows, this is more to you than anyone else, but I didn't want
to get stuck in your spam trap]

As I was working on a project, I noticed something interesting about
use of statements such as:

Hmm, that is interesting. I think I'll have to revise my spiel here. I could
have sworn the reverse was true in version 2.5. Maybe the ADO team has
addressed it since then ...

Bob Barrows

 

[Bob Barrows [MVP]]

[Bob Barrows, this is more to you than anyone else, but I didn't want
to get stuck in your spam trap]

As I was working on a project, I noticed something interesting about
use of statements such as:

Interesting. When you do something like:

msgbox cn

The connection string (the default property) is displayed. However, when you
do:

rs.ActiveConnection = cn

the object does seem to be used, despite the lack of the Set keyword. I've
verified this using SQL Profiler.

However, when you do this:

Dim v As Variant
v = cn
MsgBox TypeName(v)
Set v = cn
MsgBox TypeName(v)

"string" is displayed in the first message box and "connection" in the
second. Very interesting. The ADODB.Command object appears to be ignoring
the absence of of the "Set" keyword when setting the ActiveConnection
property. I'm thinking of sending this to Bill Vaughn to get his take on it.


Dave Anderson, if you're reading this thread, what would be the result of a
similar exercise in jscript?

Bob Barrows

 

[Mark J. McGinty]

[Bob Barrows, this is more to you than anyone else, but I didn't want
to get stuck in your spam trap]

As I was working on a project, I noticed something interesting about
use of statements such as:

Interesting. When you do something like:

msgbox cn

Because msgbox accepts a string as its first parameter.

The connection string (the default property) is displayed. However, when
you
do:

rs.ActiveConnection = cn

Because its property put method accepts a variant, and any encapsulated
object goes along for the ride. If cn is a Connection object, VB apparently
implicitly wraps the object in a variant -- I assume that happens any time
the lvalue of an assignment is a variant, and the rvalue is not?

the object does seem to be used, despite the lack of the Set keyword. I've
verified this using SQL Profiler.

However, when you do this:

Dim v As Variant
v = cn
MsgBox TypeName(v)
Set v = cn
MsgBox TypeName(v)

"string" is displayed in the first message box and "connection" in the
second. Very interesting. The ADODB.Command object appears to be ignoring
the absence of of the "Set" keyword when setting the ActiveConnection
property. I'm thinking of sending this to Bill Vaughn to get his take on
it.

That is interesting. It must be a facet of the ActiveConnection property
put code, actually taking a reference to the embedded object, rather than
simply accepting its default property?

I'll bet this would create/open an implicit connection:

Dim v As Variant
v = cn
rs.ActiveConnection = v

And by the same theory, this would throw an object required error:

Set rs.ActiveConnection = v


-Mark

 

[Chris Hohmann]

[Bob Barrows, this is more to you than anyone else, but I didn't want
to get stuck in your spam trap]

As I was working on a project, I noticed something interesting about
use of statements such as:

Interesting. When you do something like:

msgbox cn

The connection string (the default property) is displayed. However, when
you
do:

rs.ActiveConnection = cn

the object does seem to be used, despite the lack of the Set keyword. I've
verified this using SQL Profiler.

However, when you do this:

Dim v As Variant
v = cn
MsgBox TypeName(v)
Set v = cn
MsgBox TypeName(v)

"string" is displayed in the first message box and "connection" in the
second. Very interesting. The ADODB.Command object appears to be ignoring
the absence of of the "Set" keyword when setting the ActiveConnection
property. I'm thinking of sending this to Bill Vaughn to get his take on
it.

I believe that what you're observing is in fact in line with the documented
behavior of the assignment operator (=) in both VB6 and VBScript. I think
you just have to read the fine print. The VB6 documentation indicates that
the assignment operator can be used to assign a value to a variable or a
PROPERTY. It goes on to say that in the case of variables they must be
simple scalars or array elements. For properties, they must be writeable at
runtime. Digging a little deeper, the documentation indicates that the value
to be assigned can be a numeric or string literal, a constant or an
EXPRESSION. What's an expression you ask? The documentation defines an
expression to be "[a] combination of keywords, operators, variables, and
constants that yields a string, number, or OBJECT." So in this particular
context, we are setting the Recordset.ActiveConnection property to an
expression which yields a Connection object. No need for the Set statement.
Pretty sneaky.

On the recordset side of things, the constructor for the ActiveConnection
property will accept either a connection string or a connection object. When
a connection string is a passed, a new implicit connection object is
created. The data provider also has the option to set the ActiveConnection
property to this new connection object. When a connection object is passed
the ActiveConnection property is set to the connection object. In both
cases, when you close the ActiveConnection, any recordsets still associated
with the connection are closed as well. The way to sidestep this, as you
already know, is to disassociate the recordset from the connection object
before you close it, by using a clientside cursor and setting the
ActiveConnection to Nothing.

So, I guess what it all come down to is that I believe both the assignment
operator and the ActiveConnection property are behaving as expected. Here's
a list of the references I used to come to this conclusion:

Assignment (=) Operator:
http://msdn.microsoft.com/library/en-us/vbenlr98/html/vaoprassign.asp

Expression Definition:
http://msdn.microsoft.com/library/en-us/veendf98/html/defexpression.asp

ActiveConnection Property:
http://msdn.microsoft.com/library/en-us/ado270/htm/mdproactivecon.asp

Close Method (ADO):
http://msdn.microsoft.com/library/en-us/ado270/htm/mdmthclose.asp

 

[Bob Barrows [MVP]]

I believe that what you're observing is in fact in line with the
documented behavior of the assignment operator (=) in both VB6 and
VBScript. I think you just have to read the fine print.

Yes, you're right. Thanks for the refresher. I still have this vague memory
that ActiveConnection did not always work this way, but I really have no way
to confirm it. Oh well.

Bob Barrows

 

[Mark J. McGinty]

Mark J. McGinty wrote:

[snip]

I believe that what you're observing is in fact in line with the
documented behavior of the assignment operator (=) in both VB6 and
VBScript. I think you just have to read the fine print. The VB6
documentation indicates that the assignment operator can be used to assign
a value to a variable or a PROPERTY. It goes on to say that in the case of
variables they must be simple scalars or array elements. For properties,
they must be writeable at runtime. Digging a little deeper, the
documentation indicates that the value to be assigned can be a numeric or
string literal, a constant or an EXPRESSION. What's an expression you ask?
The documentation defines an expression to be "[a] combination of
keywords, operators, variables, and constants that yields a string,
number, or OBJECT." So in this particular context, we are setting the
Recordset.ActiveConnection property to an expression which yields a
Connection object. No need for the Set statement. Pretty sneaky.

Generically, the definition of expression and op(=) do not negate the
necessity of using the Set keyword when assigning a reference to an object
type, I think you're side-stepping the key point...

On the recordset side of things, the constructor for the ActiveConnection
property will accept either a connection string or a connection object.
When a connection string is a passed, a new implicit connection object is
created.

The key point is that the data type of Recordset.ActiveConnection is
Variant, not connection object. Its sub-type is Object/Connection, either
after it's assigned using a connection, or after a connection object is
implicitly created. It accepts a simple assignment without using Set,
because when assigning a variant, if whatever is on the right is not a
variant, it gets wrapped into the variant on the left. However, if you
assign that resulting variant to another variant, the object reference is
not transferred by default mechanisms -- you must use Set for that. (Bob's
experiments prove this out.)

So the ActiveConnection property put code must examine the variant's
subtype, and branch based on that subtype. If subtype is Object/Connection,
it uses the encapsulated connection. If subtype is a string, it constructs
a connection object and opens it implicitly.

[snip]

So, I guess what it all come down to is that I believe both the assignment
operator and the ActiveConnection property are behaving as expected.
Here's a list of the references I used to come to this conclusion:

I agree, but the specific reason for that behavior is that ActiveConnection
is a variant, not an object; it's the behavior of the Variant type that's
responsible, imho.


-Mark

 

[Chris Hohmann]

Mark J. McGinty wrote:

[snip]

I believe that what you're observing is in fact in line with the
documented behavior of the assignment operator (=) in both VB6 and
VBScript. I think you just have to read the fine print. The VB6
documentation indicates that the assignment operator can be used to
assign a value to a variable or a PROPERTY. It goes on to say that in the
case of variables they must be simple scalars or array elements. For
properties, they must be writeable at runtime. Digging a little deeper,
the documentation indicates that the value to be assigned can be a
numeric or string literal, a constant or an EXPRESSION. What's an
expression you ask? The documentation defines an expression to be "[a]
combination of keywords, operators, variables, and constants that yields
a string, number, or OBJECT." So in this particular context, we are
setting the Recordset.ActiveConnection property to an expression which
yields a Connection object. No need for the Set statement. Pretty sneaky.

Generically, the definition of expression and op(=) do not negate the
necessity of using the Set keyword when assigning a reference to an object
type, I think you're side-stepping the key point...

I believe it does negate the necessity of using the Set keyword, but I guess
we can agree to disagree. I'd be happy to revise my position if you could
provide either documentation or code to the contrary.

The key point is that the data type of Recordset.ActiveConnection is
Variant, not connection object. Its sub-type is Object/Connection, either
after it's assigned using a connection, or after a connection object is
implicitly created. It accepts a simple assignment without using Set,
because when assigning a variant, if whatever is on the right is not a
variant, it gets wrapped into the variant on the left. However, if you
assign that resulting variant to another variant, the object reference is
not transferred by default mechanisms -- you must use Set for that.
(Bob's experiments prove this out.)

According the above, shouldn't the following work?

Dim var As Variant
Dim cn As New ADODB.Connection
cn.Open ...
var = cn
Debug.Print var.ConnectionString

So the ActiveConnection property put code must examine the variant's
subtype, and branch based on that subtype. If subtype is
Object/Connection, it uses the encapsulated connection. If subtype is a
string, it constructs a connection object and opens it implicitly.

Agreed.

[snip]
So, I guess what it all come down to is that I believe both the
assignment operator and the ActiveConnection property are behaving as
expected. Here's a list of the references I used to come to this
conclusion:

I agree, but the specific reason for that behavior is that
ActiveConnection is a variant, not an object; it's the behavior of the
Variant type that's responsible, imho.

This is the part I don't agree with. If this were the case, the above code
sample I included should work, correct?

 

[Mark J. McGinty]

Mark J. McGinty wrote: [snip]

According the above, shouldn't the following work?

Dim var As Variant
Dim cn As New ADODB.Connection
cn.Open ...
var = cn
Debug.Print var.ConnectionString

Hmm, you've raised a valid point.

Agreed.

Yep this part still seems valid.

[snip]

So, I guess what it all come down to is that I believe both the
assignment operator and the ActiveConnection property are behaving as
expected. Here's a list of the references I used to come to this
conclusion:

I agree, but the specific reason for that behavior is that
ActiveConnection is a variant, not an object; it's the behavior of the
Variant type that's responsible, imho.

This is the part I don't agree with. If this were the case, the above code
sample I included should work, correct?

I seemed to have forgotten that VARIANT is just a struct/union, it has no
code, all it can do with non-intrinsic types is store a pointer.
COleVariant and variant_t implement extractors but that's off the subject a
ways.

So your point was that the Set keyword *could* be unnecessary, in that, if
ActiveConnection can do this, why couldn't everything else? That I'd have
to agree with, jscript gets along quite nicely without it. (But that wasn't
how I initially interpreted what you were saying.)

-Mark

 

[Bob Barrows [MVP]]

So your point was that the Set keyword *could* be unnecessary, in
that, if ActiveConnection can do this, why couldn't everything else? That
I'd have to agree with, jscript gets along quite nicely without
it. (But that wasn't how I initially interpreted what you were
saying.)

My new theory is that this is exactly the reason the ADO team did it this
way: they needed the Comand class to work regardless of the language that
was utilizing it. This should probably be c.p.'ed to an ADO group.

Bob Barrows

 

[Mark J. McGinty]

My new theory is that this is exactly the reason the ADO team did it this
way: they needed the Comand class to work regardless of the language that
was utilizing it. This should probably be c.p.'ed to an ADO group.

Not sure where language fits in, JScript doesn't need Set for any object
type (which is a good thing, since the Set keyword isn't implemented in
JScript!)

I was thinking that your perception that it wasn't always this way is right
on the money, and the ADO team did it this way to address what must've been
a widespread problem caused by connection returning a string if the user
forgot to use Set -- leaking an implicit connection with no warning, while
thinking you were using an explicit connection object.

Of course that's strictly a guess.


-Mark