Keep connection

[mstocker]

hi there,

I'm having a problem with dbexpmda40 (version 4.20.0.4), connecting to MySQL 5.0.22 with Delphi 2007 (update 3). Driver doesn't keep connection. (TSQLConnection.keepconnection is set to true) Instead it opens new connection for every query. So, for application with 1 TSQLConnection I end up with million open TCP/IP connections. how can I manage multiple queries with one connection?

[Antaeus]

By default DbxMda creates one connection to the server. Additional connections to MySQL are created for each query if you work in FetchAll=False mode, but these connections are closed just the command is executed.

What state have connections remained from your application? Do they automatically get closed after several minutes pass?

There is nothing to do with the KeepConnection option. It actually means that the connection will be automatically opened on the program startup if it was active at design time.

[mstocker]

thanks for your explanation. but, unfortunately I couldn't solve my problem. The following simple code produces 53 connections to mysql:

unit Unit1;

interface

uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, StdCtrls, SQLExpr;

type
TForm1 = class(TForm)
procedure FormCreate(Sender: TObject);
private
SQLConnection1 : TSQLConnection;
UpdateQuery : TSQLQuery;
SelectQuery : TSQLQuery;
end;

var
Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.FormCreate(Sender: TObject);
var d : double;
i : integer;
begin
SQLConnection1 := TSQLConnection.Create(self);
SQLConnection1.DriverName := 'MySQL (Core Lab)';
SQLConnection1.LibraryName := 'dbexpmda40.dll';
SQLConnection1.VendorLib := 'libmysql.dll';
SQLConnection1.GetDriverFunc := 'getSQLDriverMySQL';
SQLConnection1.Params.Clear;
SQLConnection1.Params.Add('DriverName=MySQL');
SQLConnection1.Params.Add('HostName=localhost');
SQLConnection1.Params.Add('Database=testdb');
SQLConnection1.Params.Add('User_Name=testuser');
SQLConnection1.Params.Add('Password=testpw');
SQLConnection1.Params.Add('BlobSize=1000');
SQLConnection1.Params.Add('LocaleCode=0000');
SQLConnection1.Params.Add('Compressed=False');
SQLConnection1.Params.Add('Encrypted=False');
SQLConnection1.Params.Values['FetchAll'] := 'True';
SQLConnection1.Open;

UpdateQuery := TSQLQuery.Create(self);
UpdateQuery.SQLConnection := SQLConnection1;
UpdateQuery.SQL.Text := 'Update CONFIGTABLE SET FVALUE= :value WHERE VARNAME="DUMMY"';

SelectQuery := TSQLQuery.Create(self);
SelectQuery.SQLConnection := SQLConnection1;
SelectQuery.SQL.Text := 'SELECT FVALUE FROM CONFIGTABLE WHERE VARNAME="DUMMY"';

for i := 0 to 50 do begin
SelectQuery.Open;
UpdateQuery.ParamByName('value').AsFloat := SelectQuery.FieldValues['FValue'];
UpdateQuery.ExecSQL();
UpdateQuery.Close;
SelectQuery.Close;
end;
end;
end.

I'm wondering that the property SQLConnection.MaxStmtsPerConn is set to 1. does this property depend on the mysql configuration? And why is the autoclone property set to true?

An identical program for oracle (with dbexpoda40) works fine.

[Antaeus]

Thank you for your example. We have reproduced the problem. The investigation of the problem is in progress. As soon as we solve it or we need additional information we will let you know.

[Antaeus]

We have investigate the problem. Its reason is in dbExpress code. dbExpress initiates a new connection to update data and them losses this connection. Unfortunately there is no way to avoid this problem in DbxMda.