Delphi-即使释放后，TSQLQuery也会在MySQL上留下过程 [英] Delphi - TSQLQuery leaving a proccess on MySQL even after been freed

问题描述

我正在Delphi 2007中使用DBExpress连接到网上的MySQL5数据库服务器.

I am using DBExpress in Delphi 2007 to connect to a MySQL5 database server on the net.

一切正常，直到我尝试上传大量数据为止.我试图一次将8000多个记录插入数据库，一次循环一次，在循环中，我将TSQLConection对象与要插入的数据一起传递给一个函数.

It all works OK, until I try and upload a large amount of data. I am trying to insert 8000+ records into the database, one at a time in a loop, in the loop I pass the TSQLConection object to a function along with the data to be inserted.

该函数在释放TSQLQuery之前创建一个TSQLQuery对象并运行插入查询.当我在大量数据上运行它时，会收到一条消息，指出MySQL服务器必须建立许多连接.在MySQL服务器的进程列表中，我看到了这一点.

The function creates a TSQLQuery object and runs the insert query, before freeing the TSQLQuery. when I run it on large sets of data, I get a messages saying there the MySQL server has to many connections. Looking in the process list for the MySQL server I see this.

+---------+------+-------------------------------+--------+---------+------+--------+-----------------------+
| Id      | User | Host                          | db     | Command | Time | State  | Info                  |
+---------+------+-------------------------------+--------+---------+------+--------+-----------------------+
| 2962500 | name | myispdomain.co.uk:27812       | data   | Sleep   |    3 |        | [NULL]                |
+---------+------+-------------------------------+--------+---------+------+--------+-----------------------+

我创建的每个TSQLQuery对象都有一个吸引人的地方，如果我单步执行代码，我可以在运行ExecSQL()时看到一个新的对象.我在TSQLQuery上调用FreeAndNil，并尝试在释放它之前调用Close.

There is one entriy for every TSQLQuery object I have created, and if I step through the code I can see a new one go in when I run ExecSQL(). I am calling FreeAndNil on the TSQLQuery and have tried calling Close before freeing it.

我的MySQL连接设置如下

My MySQL connection settings are as follows

   ConnectionName := 'MySQLConnection';
   DriverName     := 'MySQL';
   GetDriverFunc  := 'getSQLDriverMYSQL';
   KeepConnection := TRUE;
   LibraryName    := 'dbxmys30.dll';
   LoadParamsOnConnect := False ;
   LoginPrompt := FALSE;
   Name := 'mySQLConnection';
   VendorLib := 'LIBMYSQL.DLL';
   TableScope := [tsTable,tsView];

    Params.Add('DriverName=MySQL');
    Params.Add('HostName=www.sample.com');
    Params.Add('Database=data'); 
    Params.Add('User_Name=myuser'); 
    Params.Add('Password=mypassword'); 
    Params.Add('BlobSize=-1');
    Params.Add('ErrorResourceFile=');
    Params.Add('LocaleCode=0000');
    Params.Add('Compressed=False');
    Params.Add('Encrypted=True');

如果我将KeepConnection设置为False，问题就消失了，但是运行查询的时间却增加了.

If I set KeepConnection to False the problem goes away, but the time to run the queries goes up.

有没有办法解决这个问题?

Is there a way to get around this?

推荐答案

不要一遍又一遍地重新创建连接，也不要查询.使用参数进行查询；一次打开连接，填充查询的参数，执行它，关闭查询(但不关闭连接)，再次填充查询参数，然后再次执行.

Don't re-create your connection over and over again, nor your query. Use parameters for the query; open the connection once, populate the params of the query, execute it, close the query (but not the connection), populate the query params again, and execute it again.

类似的事情(使用Advantage Database Server，但概念相同):

Something like this (using Advantage Database Server, but the concept is the same):

// Both Create() calls should be followed by try..finally to ensure they're 
// cleaned up after. Omitted for brevity.
Conn := TAdsConnection.Create(nil);
// Configure connection parameters here
Conn.Open;

Qry := TAdsQuery.Create(nil);
Qry.AdsConnection := Conn;
Qry.SQL.Add('INSERT INTO SOMETABLE (COL1, COL2, COL3)');
Qry.SQL.Add('VALUES (:COL1, :COL2, :COL3)');
while not OtherTable.Eof do
begin
  Qry.ParamByName('COL1').AsInteger := OtherTable.FieldByName('COL1').AsInteger;
  Qry.ParamByName('COL2').AsString := OtherTable.FieldByName('COL2').AsString;
  Qry.ParamByName('COL3').AsDateTime := OtherTable.FieldByName('COL3').AsDateTime;
  Qry.ExecSQL;
  Qry.Close;
  OtherTable.Next;
end;
// Free query
Conn.Close;
// Free connection.

这篇关于Delphi-即使释放后，TSQLQuery也会在MySQL上留下过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！