Delphi - DataSet creating too many connections in MySQL

Question

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:

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.

Have you established (via the debugger) whether your server creates a new instance of the server module each time the client opens the dataset? Also, which Delphi version are you using? — MartynA, Feb 03 '17 at 20:07
This code appears wrong: `if not _DataSet.IsEmpty then begin exit; end;`. It looks like it should be `if _DataSet.IsEmpty then begin _DataSet.Close; Exit; end;` instead. — Ken White, Feb 03 '17 at 22:00
On a dataset `Close` and `Active := false` do the same thing. Doing both is redundant. — Disillusioned, Feb 04 '17 at 00:08
I don't think you've shown the relevant code. A dataset doesn't connect to a database; it uses a _connection_ object. Start by testing your connection with `Open; Close; Open; Close; Open;` If that manifests the problem, then show a [mcve] with all connection code only. If that doesn't manifest the problem, then perhaps your dataset isn't using your connection, and might be dynamically creating a new connection each time. But there may be other settings at play keeping the data set 'alive' or open on a background thread. You'll need a full MCVE with connection **and** dataset settings. — Disillusioned, Feb 04 '17 at 00:19
@MartynA yes, it only creates the connection when It executes `_DataSet.Open` — henrique romao, Feb 04 '17 at 20:39
@CraigYoung already tried closing the connection, with the connection object.. and verified If it's creating the `SM` object and It isn't. The thing is I don't know How to close a connection If my `_DataSet` isn't using any. It is only recieving the JSON data — henrique romao, Feb 04 '17 at 20:43
@henriqueromao Your comment doesn't change the fact that you need MCVE. (In fact I'd say it reinforces it.) — Disillusioned, Feb 05 '17 at 10:39
@henriqueromao Your example is still far from complete, and so is obviously **not** verifiable. What is `osm`? How are connection and dataset linked? You need to simplify your code so that a few lines have everything needed to reproduce the problem. *Remember we can't see anything you don't tell us about.* If you create a new project and use ***only*** the code presented in this question you'll be unable to get anything compiling let alone reproduce the problem. — Disillusioned, Feb 06 '17 at 21:03
That said, the code you've shown suggests you're leaking memory because you haven't destroyed the object created in `GetXMLResumoChave`. But that could also simply be part of the code you ***haven't shown***! Follow the link: [mcve]. Search online for MCVE. If you learn to provide a good MCVE, you'll usually get an answer very quickly. Your failure to provide a **Complete** and **Verifiable** example is just wasting your and everyone else's time. — Disillusioned, Feb 06 '17 at 21:11

Delphi - DataSet creating too many connections in MySQL

0 Answers0