Delphi - DataSet creating too many connections in MySQL

669 Views Asked by At

I am having a problem using the TFDDataSet component in my application.

I have a function that fetch many times if a customer has new orders. If it returns empty the function ends.

...
 fdm_XMLREsumo.Close;
       fdm_XMLREsumo.Active := false;
       _DataSetJSON := SM.GetXMLResumoChave( pEnt_ID, pChave); //See Edit 1
       _DataSet.Close;
       _DataSet := TFDJSONDataSetsReader.GetListValueByName( _DataSetJSON, sXMLResumo );
       _DataSet.Open; <-- here's the problem 
       if not _DataSet.IsEmpty then begin
        exit;
       end;
       fdm_XMLREsumo.AppendData( _DataSet );
      ...

The problem is every time it executes _DataSet.Open; it creates a new connection in my DB.
Because of that I'm having a too many connections exception.

I've checked in my Server Properties and it is like this:

Many threads in Sleep state

I have tried Connection.Close,_DataSet.Close, _DataSet.Free and _DataSet.Destroy but nothing worked.
I read this, and it explains that even if you do _DataSet.Close the connection still exists, because DataSets work in memory.
There is also this guy having a similar issue, but using Query.
Does anyone know how can I manage to solve this?
I am using MySQL

EDIT 1
As @CraigYoung helped me saying my example needs MCVE

SM.GetXMLResumoChave method:


Here it uses a connection to the database that is closed at the end of the function. Already debugged, and here it does not leave an open connection in MySQL Proccess List

function TDAOXMLResumo.GetXMLResumoChave(xEnt_id: Integer; xChave: String): TFDJSONDataSets;
begin
  if not oSM.FDConn.Connected then
    oSM.FDConn.Connected := true;
  QueryPesquisa.SQL.Clear;
  QueryPesquisa.SQL.Text :=
     ' select * from table' +
     ' where ent_id = :ent_id ' +
     '       and xre_chNFe = :xre_chNFe ';
  QueryPesquisa.ParamByName('ent_id').Asinteger   := xEnt_id;
  QueryPesquisa.ParamByName('xre_chNFe').Asstring := xChave;
  Result := TFDJSONDataSets.Create;
  //TFDJSONDataSetsWriter.ListAdd Opens the Query that is passed as parameter and store the data as JSON in the TFDJSONDataSets (Result) object
  TFDJSONDataSetsWriter.ListAdd(Result, sXMLResumo, QueryPesquisa);  
  //Closing the Query
  QueryPesquisa.Close;
  //Closing the Connection
  oSM.FDConn.Close;
end;`

Basically, the _DataSet is only receiving a JSON List here: _DataSet := TFDJSONDataSetsReader.GetListValueByName( _DataSetJSON, sXMLResumo );, and then open it to access the data in it.

0

There are 0 best solutions below