How to use ConnectionPool

[DataCool]

Hi everbody,

can anyone give me a short example how to work with the "ConnectionPool" of UniConnection ?
Of course, i found the "Pooling" and "PoolingOptions" Property, but i didn't understand how to get several connections from the pool, like AcquireConnection/ReleaseConnection, or is the complete connection pool handling completely inside the uniconnection class invisible for outside ?

I'm asking because i'm working on a server application which will have more than 1000 clients connections and i didn't want to give each client his "own uniconnection",
so I'm searching for a thread safe way to manage a connection-pool of uniconnections (maybe 10-25) used by all >= 1000 clients.

My first idea was to realize a thread safe connection pool on my own, but then i found the "Pooling stuff" and i didn't want to invest time in stuff which is already existing ...

So, please can anybody push me into the right direction ?!

Many thanks in advance,

Data

[azyk]

In our online documentation, there is an article describing using of Connection Pooling in UniDAC: http://www.devart.com/unidac/docs/index ... ooling.htm .

If this article doesn't help, and you have any questions, please ask us - and we will answer.

[DataCool]

Hi azyk,

i already found and read that link.
My Problem or missunderstanding is:
- If there is just one TUniconnection, where could I request a connection from the connection pool ?
Or should I create more than one TUniconnection on my own, all with the same "pooling properties" ?

- I just need a simple Example or just lines of code, which explain the case when i create a new Query and i want to have for this new query a connection of the connection pool, WHERE CAN I GET OR REQUEST IT ?
Like myUniConnection.GetConnectionFromPool / myUniConnection.ReleaseConnectionToPool

Many thanks in advance,

Kind regards,

Data

[azyk]

To use pooling, you should set the Pooling property of TUniConnection to True before opening connection. When you open connection, firstly the pool will be checked for connections with the same connection parameters. If such connections are detected, they will be returned from the pool, otherwise, a new connection instance will be created.

When you call the TUniConnection.Close method for an open connection, the connection won't close at this, but put to the pool for a time (ms) specified in the ConnectionLifetime property. After that time, the connection will be closed and deleted from the pool if no other TUniConnection uses it.

Such actions with the pool as getting a connection from pool, putting a connection to the pool are performed by UniDAC automatically.

[DataCool]

Hi azyk,

if i got you right, the only thing I have to do, to get an exlusive connection from the pool for a Query, is:

Code: Select all

Var Qry : TUniQuery;
begin
  Qry := TUniQuery.Create(nil);
  try
    Qry.Connection := myGlobalUniConnectionOnTheDataModuleWithActivePooling; 
    Qry.Connection.Connect;  // <--- get a connection from the Pool
    // ^^ This sounds really weird for me, but ok I didn't know what's going on inside TUniConnection
    // ......
    // .... All the stuff from here is exclusive for this connection, until releasing the connection back to the pool ?
   Qry.Open;
   // ....
   Qry.Close;
   Qry.Connection.close;  // <--- release connection to the Pool, Connection not longer in exclusive Access

  finally
    Qry.free;
  end;
end;

If I'm not right, just give me a few lines of code , how to get a connection from the pool for a dynamic created Query like above.

Many thanks in advance,

Data

[azyk]

In the your sample, the connection instance will be used exclusively after execution of the Qry.Connection.Connect; method till the Qry.Connection.close method;.

Qry.Connection.Connect; // <--- get a connection from the Pool

The connection instance will be taken from the pool if there is a connection instance with the same connection parameters in the pool. If such a connection instance is not detected in the pool, a new connection instance will be created.

[monzer_yazigi]

Dear Azyk
Thanks for the information. My case as follows:
I am a customer of Devart developing an ISAPI web application using Delphi 2009 and Devart 6.0.2. The web application is supposed to access the same data object placed on the WebModule and do either insert, or retrieve or update. In order to do this is proper manner, all data access operation should be executed in Safe-Thread manner other wise wrong output or exceptions will happen.

My Question: When creating a new connection such as :
Qry.Connection.Connect; // <--- get a connection from the Pool

Does this mean, that the query will run on its own thread, and therefore it wont cause problem with other connections from the same pool executing the same query in other connections?

Please advise,

Many thanks
Monzer George Yazigi

[azyk]

When using pooling, each connection instance of TUniConnection is not executed in a separate thread. If you are using a multi-threaded application, then a separate connection per thread must be used.

[therat]

It took me a while but I did figure out how to use pooling successfully. I was also expecting some way to get a connection out of an existing TUniConnection but it doesn't actually work that way.

In your thread that needs a TUniConnection, create an instance of TUniConnection (I used a helper class like the one below to accomplish this). Substitute the relevant values for your project.

Code: Select all

class function TDb.NewDataConnection: TUniConnection;
begin
  Result := TUniConnection.Create(nil);
  Result.Server := SETTINGS.DataHost;
  Result.Port := SETTINGS.DataPort;
  Result.Username := SETTINGS.DataUserName;
  Result.Password := SETTINGS.DataPassword;
  Result.ProviderName := 'PostgreSQL';
  Result.Database := SETTINGS.DataDatabase;
  Result.PoolingOptions.MaxPoolSize := SETTINGS.PoolSize;
  Result.PoolingOptions.MinPoolSize := 0;
  Result.PoolingOptions.ConnectionLifetime := SETTINGS.PoolConnectionLifeTime;
  Result.PoolingOptions.Validate := True;
  Result.Pooling := True;
  Result.Connect;
  Result.Macros.Clear;
end;

Use this connection in your query, being mindful not to use the TUniConnection in any other threads or you will get errors.

When done, free the TUniConnection.

The opened connections remain open even though the TUniConnection is destroyed. That isn't mentioned anywhere in the documentation or any of the answers I could find here. They will be closed after the ConnectionLifeTime has been reached (in milliseconds).


It is very important that you free the TUniConnection or you will run out of connections as they will be seen as "in use" until they are freed. Wrap it in a try finally like this:

Code: Select all

Connection := TDb.NewDataConnection;
try
finally
  Connection.Free;
end;

[azyk]

When you use connection pooling, then on calling the TUniConnection.Close method, connection won't be closed, but placed to the pool for the number of milliseconds specified in ConnectionLifetime. When this time pasts, connection will be closed and deleted from the pool if no other TUniConnection uses it. Therefore the Close method should be called intead of Free in your code sample.

When opening a new connection (TUniConnection.Connect), the pool will be checked first. If a connection with similar parameters is found in the pool, it will be returned from the pool. Otherwise, a new connection instance will be created.

[Mahmood_M]

Therefore the Close method should be called intead of Free in your code sample

In a multi-thread Server application, any Client request has it`s own thread and each thread should create it`s own TUniConnection. Every created objects must be freed otherwise there will be some memory-leaks.
In this situation if new created TUniConnection object get freed, will Connection Instance be available in pooling list ?

[azyk]

In this case, when calling the Close method, UniDAC will first return the server connection instance to the pool and then destroy the TUniConnection instance. Thus, calling TUniConnection.Close will not cause memory leaks.

[zvasku]

This thread makes me more questions than answers. :-)

We use pooling simply by activation pooling on connection like this:

Code: Select all

begin
 C:=TUniConnection.Create(nil);
.. setup connection
 C.Pooling:=true;
 C.Open;
.. do something
 C.Free;
end;

All work I think.

Next - we use TUniConnection in multiple threads because TUniConnection is thread safe - with Oracle Provider. Is that still right with pooling?

Code: Select all

begin
 C:=TUniConnection.Create(nil);
.. setup connection
 C.Pooling:=true;
 C.Open;
.. do something in multiple threads
 C.Free;
end;

[azyk]

Please compose a small test project demonstrating how exactly you are using pooling in a multithreaded application. In the project, also include the scripts to create and fill test tables. You can send us the project using the contact form at our site: http://www.devart.com/company/contactform.html .

[abak]

Hello Devart,

Ibdac is excellent, without any doubt.
But, For the use of pooling in multhread environment, we have a:
- lack of clarity,
- lack of official documentation,
- lack of simple and concrete example since 2014 !

I would have liked to see something like: http://docwiki.embarcadero.com/RADStudi ... _(FireDAC)

Many of us have tried, groping (connetion with pooling multi thread unidac & Ibdac) and I say them BRAVO.

My request is simple:

1. Can you please, explain step by step how to properly code a connection with a pool in multi-thread environment?

2. Provide a very simple example.

many thx
and sorry for any annoyance.

Zerrouki