Board index » delphi » ODBC, MS SQL6, BDE and eating connections

ODBC, MS SQL6, BDE and eating connections

I'm using ODBC to creat aliases that connect to an MS SQL 6 server with
Delphi 1.

I have a form with one database, two dbgrids and two datasources. One is
connected via a TTable, the other via a TQuery. The database has
KeepConnections True.

If I open and close the TTable, the signon dialog appears and a connection
is made to the server. the connection is left open, so that doing it
again, re-uses the connection and I do not need to signon again.

If I open and close the TQuery, An additional connection is made and left
open. Doing it again opens another connection., doing it again, uses
*another* connection and leaves that one open as well.

In my real world application I open and close lots of virtual TQueries
which eats lots of connections until the Server either runs out of
licenses or memory. DropConnections has no effect on this.

Is there any way to persuade TQuery to drop it's connection when it's
finished with it, without killing the entire session in the same fashion
as TTable. This is a major brick wall in our development.

Can anyone help?
Julian Bond
--
=======================================================================
|  /\  | <Julian Bond> jul...@shockwav.demon.co.uk | Ducati 900SS '93 |
| /<>\ | uses Disclaim                PGPKey @ MIT | MZ ETZ250    '80 |
|/____\|        So many words, so little time      | DoD# 65535       |
=======================================================================

 

Re:ODBC, MS SQL6, BDE and eating connections


Quote
Julian Bond wrote:

> I'm using ODBC to creat aliases that connect to an MS SQL 6 server with
> Delphi 1.

> I have a form with one database, two dbgrids and two datasources. One is
> connected via a TTable, the other via a TQuery. The database has
> KeepConnections True.

> If I open and close the TTable, the signon dialog appears and a connection
> is made to the server. the connection is left open, so that doing it
> again, re-uses the connection and I do not need to signon again.

> If I open and close the TQuery, An additional connection is made and left
> open. Doing it again opens another connection., doing it again, uses
> *another* connection and leaves that one open as well.

> In my real world application I open and close lots of virtual TQueries
> which eats lots of connections until the Server either runs out of
> licenses or memory. DropConnections has no effect on this.

> Is there any way to persuade TQuery to drop it's connection when it's
> finished with it, without killing the entire session in the same fashion
> as TTable. This is a major brick wall in our development.

> Can anyone help?
> Julian Bond
> --
> =======================================================================> |  /\  | <Julian Bond> jul...@shockwav.demon.co.uk | Ducati 900SS '93 |
> | /<>\ | uses Disclaim                PGPKey @ MIT | MZ ETZ250    '80 |
> |/____\|        So many words, so little time      | DoD# 65535       |
> =======================================================================<Ade> nth...@globalnet.co.uk | Hi Julian, I had the same problem and

solved it by not using the 'Prepare'(Query1.Prepare) property of TQuery
to initialize the query before use i.e. Q1.Close;
Q1.ParamByName('.....'); Q1.Open;
and appears to solve the problem.

Re:ODBC, MS SQL6, BDE and eating connections


Quote
Julian Bond <jul...@shockwav.demon.co.uk> wrote:

>I'm using ODBC to creat aliases that connect to an MS SQL 6 server with
>Delphi 1.

>I have a form with one database, two dbgrids and two datasources. One is
>connected via a TTable, the other via a TQuery. The database has
>KeepConnections True.

>If I open and close the TTable, the signon dialog appears and a connection
>is made to the server. the connection is left open, so that doing it
>again, re-uses the connection and I do not need to signon again.

>If I open and close the TQuery, An additional connection is made and left
>open. Doing it again opens another connection., doing it again, uses
>*another* connection and leaves that one open as well.

>In my real world application I open and close lots of virtual TQueries
>which eats lots of connections until the Server either runs out of
>licenses or memory. DropConnections has no effect on this.

>Is there any way to persuade TQuery to drop it's connection when it's
>finished with it, without killing the entire session in the same fashion
>as TTable. This is a major brick wall in our development.

>Can anyone help?
>Julian Bond

You use the Database component for persistent connections through the
entire session. This will allow for automated login. To drop database
connections, you use the methods of the Session object.

Look up the documentation for the above two.

Good Luck
Peter Antypas
paa00...@bayou.uh.edu

Re:ODBC, MS SQL6, BDE and eating connections


Quote
>Julian Bond <jul...@shockwav.demon.co.uk> wrote:

>>I'm using ODBC to creat aliases that connect to an MS SQL 6 server with
>>Delphi 1.

>>I have a form with one database, two dbgrids and two datasources. One is
>>connected via a TTable, the other via a TQuery. The database has
>>KeepConnections True.

>>If I open and close the TTable, the signon dialog appears and a connection
>>is made to the server. the connection is left open, so that doing it
>>again, re-uses the connection and I do not need to signon again.

>>If I open and close the TQuery, An additional connection is made and left
>>open. Doing it again opens another connection., doing it again, uses
>>*another* connection and leaves that one open as well.

>>In my real world application I open and close lots of virtual TQueries
>>which eats lots of connections until the Server either runs out of
>>licenses or memory. DropConnections has no effect on this.

I've had various suggestions but none of them worked or are acceptable.:
- One TDatabase per TQuery //Unacceptable
- Use TDatabase.CloseDatasets //Doesn't work
- Use Session methods //Doesn't work
- use dbiclosecursor or TdbDataset.disconnect directly //doesn't work.

We have now done the analysis by debugging into the VCL Source. Both
TTable and TQuery .close route through the same close down code and end
up with a DBICloseCursor. IMO there is a bug in the V1 BDE that is
failing to close connections from TQuery on databases that only allow a
single statement per connection. This effectively makes TQuery unusable
with these databases.

Using BDE 2 with 32bit native or ODBC connections to MS SQL and with
Delphi 2, the connections are correctly closed when the TQuery closes.
It will be interesting to see if this and any other bugs in BDE 1 get
fixed once Delphi 2 ships...

In the end the curent apps we're developing could be done with just
TTable and future apps can be 32 bit only so I seem to have got away
with it. I was seriously thinking of bypassing BDE completely and coding
straight ODBC, which would be a shame as I would have to chuck out all
the Delphi DB code as well.

--
=======================================================================
| <Julian Bond> jul...@shockwav.demon.co.uk                PGPKey@MIT |
| Quetzal & Q-Support, Help Desk and Inventory Management for Windows |
|                 "So many words, So little time"                     |
=======================================================================

Other Threads