ExecSQL and Pooling

[VladimirG]

Hello

I am testing UniDAC for importing data from text files into a DB table (currently MySQL). For this I parse the file line by line, build an INSERT statement and execute it with TUniConnection.ExecSQL('INSERT ...');

Depending on the file it is sometimes done in a large INSERT statement that contains a few hundred records/lines, or a separate INSERT statement for each record.

After a few thousand executions of ExecSQL I get this error:
"Lost connection to MySQL server during query
Error on data reading from the connection:
A request to send or receive data was disallowed because the socket is not connected and (when sending on a datagram socket using a sendto call) no address was supplied.
Socket Error Code: 10057($2749)"

I noticed that my app seems to open/close a new connection for each ExecSQL statement, so after a minute or so in the TCP monitoring tool I see several thousand connections to MySQL in the TIME_WAIT state.

Could this opening and closing ports be the reason for the error?

I connect to the DB explicitly with TUniConnection.Connected := true;
I also tried setting Pooling to true and limiting the pool size, but this seems to have no effect.

Is there anything specific I have to set to make TUniConnection keep the connection to the DB?

Forgot to mention that this is with UniDAC 5.3.8, Delphi XE4, MySQL 5.5.32 on Win7, Direct connection to the DB.

Thanks
Vladimir

[VladimirG]

Doesn't really anyone know how to solve that?

I mean, is ExecSQL designed to open a new connection every time, or I have missed something in the settings?

It seems to slow the app down if it opens new connections every time, even if there was no error.

[PavloP]

If you have executed Connect explicitly for the TUniConnection component, calling the ExecSQL method doesn't lead to creating a new connection to the MySQL server. Make sure you don't call Disconnect explicitly.

[VladimirG]

This is that I read in the docs and what I do in my code. I explicitly set Connected and it still seems to be opening new connections.

Here is what the code looks like:

Code: Select all

  Conn  := TUniConnection.Create(nil);
  SDS   := TUniQuery.Create(Conn);

  Conn.LoginPrompt  := false;
  Conn.ProviderName := 'MySQL';
  Conn.Server       := DBServer;
  Conn.Port         := DBPort;
  Conn.Database     := DBName;
  Conn.Username     := DBUser;
  Conn.Password     := DBPass;
  Conn.Connected    := true;  // Explicitly connect

  SDS.Connection := Conn;
  SDS.UniDirectional := true;

  ......
  // Pseudo code
  while not EndOfMyTextFile do
  begin
    // Build some TextSQL insert statement
    SDS.SQL.Text := TextSQL;
    SDS.ExecSQL;
  end;

Does using a TUniQuery make a difference? I could use the TUniConnection.ExecSQL() if it behaves differently.
So is there anything wrong with the above code? I don't call Disconnect anywhere.

I use the Sysinternals TcpView tool, which shows that my app opens and closes a few thousand ports in a very short time, which I suppose is the cause for the error.

[PavloP]

Don't use TUniQuery in UniDirectional mode. For your sample, use:

Code: Select all

 SDS.UniDirectional := False; 

In this case, new TCP-connections won't be created for each call of the ExecSQL method of the TUniQuery component, but already open TCP-connections will be used.

[VladimirG]

Thanks.

I already solved it by using TUniConnection.ExecSQL(...) instead of TUniQuery.ExecSQL. This way it also doesn't open new connections.

I used UniDirectional=true to avoid loading large databases in memory when I used a SELECT query. This seemed to happen for bidirectional regardless of the Fetch... properties.
But the Unidirectional=false is also a good option as it can be changed whenever the SQL is updated.

Out of curiosity, can you tell me why the TUniQuery opens a new connection with UniDirectional=true?

[PavloP]

When FetchAll is set to False (it is by default when UniDirectional is True), TUniQuery reads out data from the server on user request. Therefore, if a client doesn't read all the data, and tries to perform any next call to the server, the server will return an error that not all data is read. To avoid such a situation, TUniConnection creates an additional connection for queries with FetchAll set to False.

To modify this behavior, you can use the CreateConnection option, which allows to block creation of additional connections in the FetchAll=False mode. More details about the CreateConnection option can be found out in the TUniQuery, TUniTable, TUniStoredProc sections at http://www.devart.com/unidac/docs/index ... rticle.htm