Devart Forums

Hi.
We're new to UniDac and trying to learn how to use it properly.

Connection pooling is very important to us, as we are implementing a multi-threaded server style application, which must open a new thread for each request, and handles many requests per second. We have previous confirmed (in another topic on this forum: http://forums.devart.com/viewtopic.php?f=28&t=28346) that the correct way to implement this is to use a different connection for each thread, and to use connection pooling to reduce the delay that making each new connection causes, thus keeping up the performance.

However while experimenting with this to see how it works, initially with a simple MS-Access database just for testing, we cannot seem to get the connection pooling to work. Every time we Free the connection, the database is immediately closed, with no connection being maintained in the pool.

Obviously we are doing something wrong, but we can't figure out what. So I have simplified the test code down to shortest reproducible steps (see below), and I hope someone with more knowledge about UniDac than us can help.

First the setup assumptions:

• There should be an unsecured MS-Access database file in the same folder as the test application Exe, with the same name as the test application Exe file, but with the extension '.mdb'.
• In that database there should be a table called 'tblTest'. It's columns or contents do not matter for this test.
• Put a button on the a form in the test application with the following OnClick event handler code...

  Code: Select all

  procedure TfrmUniDac_Dev_Main.btnConnectionPoolTestClick(Sender: TObject);
  const
    c_DbFileExt = '.mdb';
    c_ProviderName = 'Access';
    c_TestSql = 'SELECT * FROM tblTest';
  var
    dbFileSpec: string;
    dbConn: TUniConnection;
    dbQry: TUniQuery;
  begin
    dbFileSpec := ChangeFileExt(application.ExeName, c_DbFileExt);
  
    ShowMessage('Database should be closed here (no .ldb file).');
  
    dbConn := TUniConnection.Create(nil);
    try
      dbConn.ProviderName := c_ProviderName;
      dbConn.Database := dbFileSpec;
      dbConn.Pooling := True;
      dbConn.Open;
  
      dbQry := TUniQuery.Create(nil);
      try
        dbQry.Connection := dbConn;
        dbQry.SQL.Text := c_TestSql;
        dbQry.Open;
  
        ShowMessage('Database connection should now be made (.ldb file should exist).');
      finally
        dbQry.Free;
      end;
    finally
      ShowMessage('Database connection should still be active (.ldb file should still exist).');
      dbConn.Free;
    end;
  
    ShowMessage('Database connection should still be active in the pool for a while (.ldb file should still exist), but it has been closed.');
  end;
  

At each call to ShowMessage the application pauses so you can check if the database file is open or not, by the presence of the .ldb file.

As you will see, at the last call to ShowMessage, we are expecting the database file to still be open, with the connection pool maintaining a connection to it, but that does not happen.

We have probably made some incorrect assumptions about how this is supposed to work, or missed something in the documentation, but we haven't yet been able to determine what.

Please help.

Hello,

MSAccessUniProvider doesn't support Pooling

Thanks Alex. I guess we were wasting our time trying to make it work then.

Could you please let me know exactly which database providers DO support connection pooling?

In the documentation about connection pooling we didn't find any mention that it was only supported by some database providers and not by others. This is a critical component of our application design, as it would be for anyone building a multi-threaded server application, which by definition includes the business logic layer of almost any multi-tier application.

Is there somewhere with a list of which database providers do, or do not support all the major features mentioned in the documentation? Or even a partial list?

That feature support list is a very important piece of the puzzle for anyone trying to use UniDac to develop applications to work with different databases types. Without that list, it would be very easy to get stuck in a dead end, trying to use a database provider that does not support a critical feature that the application design relies upon.

Especially as we are trying to use UniDac specifically as a way to shield the application from needing to know about many of the differences between the various types of databases. It is important to know where that shield ends, and where the application must know about those differences.

Hello,

The following providers support Pooling

AdvantageUniProvider, ASEUniProvider, DB2UniProvider, InterBaseUniProvider, NexusDBUniProvider, MySQLUniProvider, OracleUniProvider, PostgreSQLUniProvider, SQLServerUniProvider