473,325 Members | 2,792 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,325 software developers and data experts.

Implicit connections

[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
Jul 22 '05 #1
10 1489
Mark J. McGinty wrote:
[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
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Jul 22 '05 #2
Mark J. McGinty wrote:
[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

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Jul 22 '05 #3

"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...
Mark J. McGinty wrote:
[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

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

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Jul 22 '05 #4
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...
Mark J. McGinty wrote:
[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...aoprassign.asp

Expression Definition:
http://msdn.microsoft.com/library/en...expression.asp

ActiveConnection Property:
http://msdn.microsoft.com/library/en...oactivecon.asp

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

Jul 22 '05 #5
Chris Hohmann wrote:

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
--
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"
Jul 22 '05 #6

"Chris Hohmann" <no****@thankyou.com> wrote in message
news:%2****************@TK2MSFTNGP15.phx.gbl...
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...
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

Assignment (=) Operator:
http://msdn.microsoft.com/library/en...aoprassign.asp

Expression Definition:
http://msdn.microsoft.com/library/en...expression.asp

ActiveConnection Property:
http://msdn.microsoft.com/library/en...oactivecon.asp

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

Jul 22 '05 #7
"Mark J. McGinty" <mm******@spamfromyou.com> wrote in message
news:dHnge.9699$D91.4385@fed1read01...

"Chris Hohmann" <no****@thankyou.com> wrote in message
news:%2****************@TK2MSFTNGP15.phx.gbl...
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...
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.

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.)


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?
Jul 22 '05 #8

"Chris Hohmann" <no****@thankyou.com> wrote in message
news:O2*************@TK2MSFTNGP14.phx.gbl...
"Mark J. McGinty" <mm******@spamfromyou.com> wrote in message
news:dHnge.9699$D91.4385@fed1read01...

"Chris Hohmann" <no****@thankyou.com> wrote in message
news:%2****************@TK2MSFTNGP15.phx.gbl...
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...
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.

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.


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


Jul 22 '05 #9
Mark J. McGinty wrote:
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
--
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"
Jul 22 '05 #10

"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:e2**************@TK2MSFTNGP12.phx.gbl...
Mark J. McGinty wrote:
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.


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
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"

Jul 22 '05 #11

This thread has been closed and replies have been disabled. Please start a new discussion.

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...
3
by: Reneé | last post by:
I wanted to know the order of implicit conversions and which sort of values allow them. From searching around in books and the archive of this mailing list, it seems to be that only numbers are...
11
by: Steve Gough | last post by:
Could anyone please help me to understand what is happening here? The commented line produces an error, which is what I expected given that there is no conversion defined from type double to type...
9
by: Girish | last post by:
Im trying to understand implicit type conversions from object -> string and vice versa. I have two classes, one Driver and one called StringWrapper. These are just test classes that try and...
11
by: Aaron Queenan | last post by:
Given the classes: class Class { public static implicit operator int(Class c) { return 0; } } class Holder
36
by: Chad Z. Hower aka Kudzu | last post by:
I have an implicit conversion set up in an assembly from a Stream to something else. In C#, it works. In VB it does not. Does VB support implicit conversions? And if so any idea why it would work...
3
by: SharpCoderMP | last post by:
hi, i've found out here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=131195&SiteID=1 that FtpWebRequest does not support implicit connections. I have a serious problem with that,...
17
by: arindam.mukerjee | last post by:
I was running code like: #include <stdio.h> int main() { printf("%f\n", 9/5); return 0; }
5
by: rocketboy2000 | last post by:
i am maintaining a system that was developed using dream weaver. a lot of the asp ado code looks like this: set sp_yellow = Server.CreateObject("ADODB.Command") sp_yellow.ActiveConnection =...
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
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: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
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: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
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.