sharing ADO connection across multiple VB EXEs

[Chris Hylton]

Has anybody found a way to share an ADO connection across multiple EXEs (all
apps and components done with VB6). I want to be able to have an exe or
activex exe/document create an ADO connection, then be able to get to that
connection from inside other EXEs that are started AFTER the first EXE establishes
the connection. The goal is to have a single connection to the database
per client machine for our application.

I know that you can't GetObject on a component created in VB.

I found the RotHook/RotClock example here on DEVX and I'm reading through
that now, but it's getting pretty deep and I'm looking for any other solutions
(other than building a C++ component) to solve this issue.

The final solution may end up being to leave this alone until we move to
.NET and then build this application the right way, but for now, we are stuck
w/ the existing design from 5+ years ago that we have to maintain for the
time being.

Thanks,
Chris

[michael]

Here is a link. I could not find a link to the pooling toolkit at microsoft.
However, I do have the toolkit burried in an email if you would like for
me to send it to your email. Let me know.

http://msdn.microsoft.com/library/de...l/pooling2.asp

Hope this may help.
Michael

"Chris Hylton" <cchylton@hotmail.com> wrote:
>
>Has anybody found a way to share an ADO connection across multiple EXEs
(all
>apps and components done with VB6). I want to be able to have an exe or
>activex exe/document create an ADO connection, then be able to get to that
>connection from inside other EXEs that are started AFTER the first EXE establishes
>the connection. The goal is to have a single connection to the database
>per client machine for our application.
>
>I know that you can't GetObject on a component created in VB.
>
>I found the RotHook/RotClock example here on DEVX and I'm reading through
>that now, but it's getting pretty deep and I'm looking for any other solutions
>(other than building a C++ component) to solve this issue.
>
>The final solution may end up being to leave this alone until we move to
>.NET and then build this application the right way, but for now, we are
stuck
>w/ the existing design from 5+ years ago that we have to maintain for the
>time being.
>
>Thanks,
>Chris

[Paul Clement]

On 6 Jun 2002 11:51:19 -0800, "Chris Hylton" <cchylton@hotmail.com> wrote:

¤
¤ Has anybody found a way to share an ADO connection across multiple EXEs (all
¤ apps and components done with VB6). I want to be able to have an exe or
¤ activex exe/document create an ADO connection, then be able to get to that
¤ connection from inside other EXEs that are started AFTER the first EXE establishes
¤ the connection. The goal is to have a single connection to the database
¤ per client machine for our application.
¤
¤ I know that you can't GetObject on a component created in VB.
¤
¤ I found the RotHook/RotClock example here on DEVX and I'm reading through
¤ that now, but it's getting pretty deep and I'm looking for any other solutions
¤ (other than building a C++ component) to solve this issue.
¤
¤ The final solution may end up being to leave this alone until we move to
¤ NET and then build this application the right way, but for now, we are stuck
¤ w/ the existing design from 5+ years ago that we have to maintain for the
¤ time being.

The ODBC and OLEDB managers already handle connection pooling if you're using a common connection
string. I wouldn't recommend sharing a single connection amongst your components.


Paul ~~~ pclement@ameritech.net
Microsoft MVP (Visual Basic)

[Chris Hylton]

Thanks Paul and Michael,

Still not sure I understand what's going on underneath the hood here...I've
read the article that Michael posted above as well as everything else I could
find in MSDN on ado connection pooling.

My problem is this (more specifically)...the way my current clients application
is structured it that there are multiple EXEs (standard vb programs) that
comprise the overall application in logical blocks of business functionality.
Each EXE is 'fired' from a common toolbar from a standard shell/shellexecute
statement. The design was implemented years ago by the powers that be at
the time. The primary reason was the relative size of the application, there
are TONS of little apps that comprise the overall app, if they were one MDI
interface, the EXE would be HUGE.

So...what happens is that when each EXE if opened by the toolbar (which itself
establishes a connection to the database, each EXE then establishes a connection
to the database. Every app uses the same user credentials, they are passed
via the command-line in the shell execute statement (again, not the greatest
design, but that's the way it is).

Anyway...once you have mulitple apps open, if you go to the Oracle session
list, that user is connected to the database multiple times, one for each
app that's open on their desttop.

That's what I'd like to get around...figure out a way to open a single connection
in ADO and have that connection used by all the applications...simple concept,
but difficult to implement in VB (the getobject issue) w/o using VC++ or
building a MTS solution.

Any ideas ?

Am I way off-base in my expectations here ?

Chris

Paul Clement <UseAdddressAtEndofMessage@swspectrum.com> wrote:
>On 6 Jun 2002 11:51:19 -0800, "Chris Hylton" <cchylton@hotmail.com> wrote:
>
>¤
>¤ Has anybody found a way to share an ADO connection across multiple EXEs
(all
>¤ apps and components done with VB6). I want to be able to have an exe
or
>¤ activex exe/document create an ADO connection, then be able to get to
that
>¤ connection from inside other EXEs that are started AFTER the first EXE
establishes
>¤ the connection. The goal is to have a single connection to the database
>¤ per client machine for our application.
>¤
>¤ I know that you can't GetObject on a component created in VB.
>¤
>¤ I found the RotHook/RotClock example here on DEVX and I'm reading through
>¤ that now, but it's getting pretty deep and I'm looking for any other solutions
>¤ (other than building a C++ component) to solve this issue.
>¤
>¤ The final solution may end up being to leave this alone until we move
to
>¤ NET and then build this application the right way, but for now, we are
stuck
>¤ w/ the existing design from 5+ years ago that we have to maintain for
the
>¤ time being.
>
>The ODBC and OLEDB managers already handle connection pooling if you're
using a common
>connection
>string. I wouldn't recommend sharing a single connection amongst your components.
>
>
>Paul ~~~ pclement@ameritech.net
>Microsoft MVP (Visual Basic)

[michael]

Does the connection to the database stay active the entire time the these
mini-exe's are loaded? If yes and they only do a few seconds of data access/manipulation,
then disconnect the connection with in the exe. You can alway reconnect it
if needed since all of the information is already contained with in the exe
to connect it the first time.
I have done a few 2 tier apps using disconnected recordsets with 30+ users.
And very rarely do I see more than 2 or 3 of users when doing a sp_who.

Michael

"Chris Hylton" <cchylton@hotmail.com> wrote:
>
>Thanks Paul and Michael,
>
>Still not sure I understand what's going on underneath the hood here...I've
>read the article that Michael posted above as well as everything else I
could
>find in MSDN on ado connection pooling.
>
>My problem is this (more specifically)...the way my current clients application
>is structured it that there are multiple EXEs (standard vb programs) that
>comprise the overall application in logical blocks of business functionality.
> Each EXE is 'fired' from a common toolbar from a standard shell/shellexecute
>statement. The design was implemented years ago by the powers that be at
>the time. The primary reason was the relative size of the application,
there
>are TONS of little apps that comprise the overall app, if they were one
MDI
>interface, the EXE would be HUGE.
>
>So...what happens is that when each EXE if opened by the toolbar (which
itself
>establishes a connection to the database, each EXE then establishes a connection
>to the database. Every app uses the same user credentials, they are passed
>via the command-line in the shell execute statement (again, not the greatest
>design, but that's the way it is).
>
>Anyway...once you have mulitple apps open, if you go to the Oracle session
>list, that user is connected to the database multiple times, one for each
>app that's open on their desttop.
>
>That's what I'd like to get around...figure out a way to open a single connection
>in ADO and have that connection used by all the applications...simple concept,
>but difficult to implement in VB (the getobject issue) w/o using VC++ or
>building a MTS solution.
>
>Any ideas ?
>
>Am I way off-base in my expectations here ?
>
>Chris
>
>Paul Clement <UseAdddressAtEndofMessage@swspectrum.com> wrote:
>>On 6 Jun 2002 11:51:19 -0800, "Chris Hylton" <cchylton@hotmail.com> wrote:
>>
>>¤
>>¤ Has anybody found a way to share an ADO connection across multiple EXEs
>(all
>>¤ apps and components done with VB6). I want to be able to have an exe
>or
>>¤ activex exe/document create an ADO connection, then be able to get to
>that
>>¤ connection from inside other EXEs that are started AFTER the first EXE
>establishes
>>¤ the connection. The goal is to have a single connection to the database
>>¤ per client machine for our application.
>>¤
>>¤ I know that you can't GetObject on a component created in VB.
>>¤
>>¤ I found the RotHook/RotClock example here on DEVX and I'm reading through
>>¤ that now, but it's getting pretty deep and I'm looking for any other
solutions
>>¤ (other than building a C++ component) to solve this issue.
>>¤
>>¤ The final solution may end up being to leave this alone until we move
>to
>>¤ NET and then build this application the right way, but for now, we are
>stuck
>>¤ w/ the existing design from 5+ years ago that we have to maintain for
>the
>>¤ time being.
>>
>>The ODBC and OLEDB managers already handle connection pooling if you're
>using a common
>>connection
>>string. I wouldn't recommend sharing a single connection amongst your components.
>>
>>
>>Paul ~~~ pclement@ameritech.net
>>Microsoft MVP (Visual Basic)
>

[Paul Clement]

On 7 Jun 2002 07:15:45 -0800, "Chris Hylton" <cchylton@hotmail.com> wrote:

¤
¤ Thanks Paul and Michael,
¤
¤ Still not sure I understand what's going on underneath the hood here...I've
¤ read the article that Michael posted above as well as everything else I could
¤ find in MSDN on ado connection pooling.
¤
¤ My problem is this (more specifically)...the way my current clients application
¤ is structured it that there are multiple EXEs (standard vb programs) that
¤ comprise the overall application in logical blocks of business functionality.
¤ Each EXE is 'fired' from a common toolbar from a standard shell/shellexecute
¤ statement. The design was implemented years ago by the powers that be at
¤ the time. The primary reason was the relative size of the application, there
¤ are TONS of little apps that comprise the overall app, if they were one MDI
¤ interface, the EXE would be HUGE.
¤
¤ So...what happens is that when each EXE if opened by the toolbar (which itself
¤ establishes a connection to the database, each EXE then establishes a connection
¤ to the database. Every app uses the same user credentials, they are passed
¤ via the command-line in the shell execute statement (again, not the greatest
¤ design, but that's the way it is).
¤
¤ Anyway...once you have mulitple apps open, if you go to the Oracle session
¤ list, that user is connected to the database multiple times, one for each
¤ app that's open on their desttop.
¤
¤ That's what I'd like to get around...figure out a way to open a single connection
¤ in ADO and have that connection used by all the applications...simple concept,
¤ but difficult to implement in VB (the getobject issue) w/o using VC++ or
¤ building a MTS solution.
¤
¤ Any ideas ?

Of course if you use a single connection object this will create a blocking situation where only one
application will be able to access the database at a time. Is this OK in your scenario?


Paul ~~~ pclement@ameritech.net
Microsoft MVP (Visual Basic)

[Chris Hylton]

Michael, already considered that option...to disconnect when not in use...the
pooling/resource sharing capabilities of OLEDB will keep a connection available
(at least for awhile) so a reconnect should be fairly fast...but, still not
an optimal solution...some apps are a quick in and out, some take awhile
to pull data down or execute a proc...

Paul, not sure I see what you mean...I might be confused though...if I had
a single connection object housed in some sort of an activex exe/document
app...every app would send requests for data through that object...the apps
are obviously only hit one at a time by the user...

So, let's say that the toolbar is opened, with buttons for 10 apps on it...the
toolbar spawns off my custom 'connect' object...then spawns off 2 apps (#1
and #2)...both in their own space (seperate EXEs)...no EXE would really be
sending a request through the connection object at the same time, but I guess
it's possible...for example, if app #1 sent a long running proc through and
then the user clicked on app #2 and tried to query data...then I guess that
might create the situation you are referring to (blocking)...where #2 can't
get to the connection because #1 has it tied up.

Is that what you are talking about ?

Makes sense I guess...maybe I do want a seperate ADO connection per app...might
have to explore the .Close method that Michael brought up...I had already
tested this a few days ago and it works fine...just requires reconnecting
w/ every call to a recordset or command object.

Chris

Paul Clement <UseAdddressAtEndofMessage@swspectrum.com> wrote:
>On 7 Jun 2002 07:15:45 -0800, "Chris Hylton" <cchylton@hotmail.com> wrote:
>
>¤
>¤ Thanks Paul and Michael,
>¤
>¤ Still not sure I understand what's going on underneath the hood here...I've
>¤ read the article that Michael posted above as well as everything else
I could
>¤ find in MSDN on ado connection pooling.
>¤
>¤ My problem is this (more specifically)...the way my current clients application
>¤ is structured it that there are multiple EXEs (standard vb programs) that
>¤ comprise the overall application in logical blocks of business functionality.
>¤ Each EXE is 'fired' from a common toolbar from a standard shell/shellexecute
>¤ statement. The design was implemented years ago by the powers that be
at
>¤ the time. The primary reason was the relative size of the application,
there
>¤ are TONS of little apps that comprise the overall app, if they were one
MDI
>¤ interface, the EXE would be HUGE.
>¤
>¤ So...what happens is that when each EXE if opened by the toolbar (which
itself
>¤ establishes a connection to the database, each EXE then establishes a
connection
>¤ to the database. Every app uses the same user credentials, they are passed
>¤ via the command-line in the shell execute statement (again, not the greatest
>¤ design, but that's the way it is).
>¤
>¤ Anyway...once you have mulitple apps open, if you go to the Oracle session
>¤ list, that user is connected to the database multiple times, one for each
>¤ app that's open on their desttop.
>¤
>¤ That's what I'd like to get around...figure out a way to open a single
connection
>¤ in ADO and have that connection used by all the applications...simple
concept,
>¤ but difficult to implement in VB (the getobject issue) w/o using VC++
or
>¤ building a MTS solution.
>¤
>¤ Any ideas ?
>
>Of course if you use a single connection object this will create a blocking
situation
>where only one
>application will be able to access the database at a time. Is this OK in
your scenario?
>
>
>Paul ~~~ pclement@ameritech.net
>Microsoft MVP (Visual Basic)

[Paul Clement]

On 7 Jun 2002 13:24:11 -0800, "Chris Hylton" <cchylton@hotmail.com> wrote:

¤
¤ Michael, already considered that option...to disconnect when not in use...the
¤ pooling/resource sharing capabilities of OLEDB will keep a connection available
¤ (at least for awhile) so a reconnect should be fairly fast...but, still not
¤ an optimal solution...some apps are a quick in and out, some take awhile
¤ to pull data down or execute a proc...

Once you release the connection through your app, it is returned to the pool. If the connection is
idle for one minute (OLEDB) it is destroyed.

¤
¤ Paul, not sure I see what you mean...I might be confused though...if I had
¤ a single connection object housed in some sort of an activex exe/document
¤ app...every app would send requests for data through that object...the apps
¤ are obviously only hit one at a time by the user...

Correct. If you are using a multi-use ActiveX EXE to share a connection, then blocking will occur at
the component level under concurrent access.

¤
¤ So, let's say that the toolbar is opened, with buttons for 10 apps on it...the
¤ toolbar spawns off my custom 'connect' object...then spawns off 2 apps (#1
¤ and #2)...both in their own space (seperate EXEs)...no EXE would really be
¤ sending a request through the connection object at the same time, but I guess
¤ it's possible...for example, if app #1 sent a long running proc through and
¤ then the user clicked on app #2 and tried to query data...then I guess that
¤ might create the situation you are referring to (blocking)...where #2 can't
¤ get to the connection because #1 has it tied up.
¤
¤ Is that what you are talking about ?

Yes.

¤
¤ Makes sense I guess...maybe I do want a seperate ADO connection per app...might
¤ have to explore the .Close method that Michael brought up...I had already
¤ tested this a few days ago and it works fine...just requires reconnecting
¤ w/ every call to a recordset or command object.

Probably why it's just better to stick with the connection pooling provided by the driver manager
services. If you want to reduce the number of connections for each desktop, just make certain each
application closes a connection after it has been used.


Paul ~~~ pclement@ameritech.net
Microsoft MVP (Visual Basic)