[SOLVED] Function result the content of a query lazarus / 63-Database ---------------------------------------------------------------------- TheLastCayen · 2016-10-31 21:27 Hi, I am using: Lazarus 1.6 FPC 3.0.0 ZEOSDBO  7.1.4 I am building a db class to manage my connection with a sqllite database. I wonder if there is a way to return the result of a query trough a function? I tried to use without success Tfields type for my function but it keep crashing on me.  This is my code: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i constructor Tmydb.Create;var  dbexist : boolean; //Use to know if we build the databasebegin  inherited;  //DeleteFile(dbname);  dbexist:=FileExists(dbname);  DatabaseTZ:= TZConnection.Create(Nil);  with DatabaseTZ do    begin      Protocol  := 'sqlite-3';      Database  := dbname;      Connect;    end;end; destructor Tmydb.Destroy;begin  DatabaseTZ.Disconnect;  DatabaseTZ.free;  inherited;end; function Tmydb.query(sqlcommand: AnsiString):TFields;var  Querry: TZReadOnlyQuery;begin  Querry    := TZReadOnlyQuery.Create(nil);  with Querry do    begin      Connection := DatabaseTZ;      SQL.Clear;      SQL.Add(sqlcommand);      Open;    Result := Fields;      Close;      Free;    end;end;   And this how I'am trying to call for it: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i   var  database: tmydb;  tempresult: TFields;begin  database:= tmydb.Create;  tempresult := database.query('SELECT * FROM testtable;');  database.free;end;   Thank you ---------------------------------------------------------------------- molly · 2016-10-31 23:17 I'm not so much into databases but, from my understanding: Quote from: TheLastCayen on October 31, 2016, 10:27:43 pmCode: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i   Querry    := TZReadOnlyQuery.Create(nil);  with Querry do    begin...      Result := Fields;...      Free;    end; You free the querry and expect the fields property to stay alive ? ---------------------------------------------------------------------- totya · 2016-11-01 00:09 It seems to me it's true. The solution for example: http://stackoverflow.com/questions/16846686/memory-leaks-when- returning-tfields-from-a-function ---------------------------------------------------------------------- TheLastCayen · 2016-11-01 00:30 Quote from: totya on November 01, 2016, 01:09:24 amIt seems to me it's true. The solution for example: http://stackoverflow.com/questions/16846686/memory-leaks-when- returning-tfields-from-a-function Thank you titya. Work like a charme. For future reference this is my code now: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i function Tmydb.query(sqlcommand: AnsiString):TZReadOnlyQuery;begin  Result := TZReadOnlyQuery.Create(nil);  try    Result.Connection := DatabaseTZ;    Result.SQL.Add(sqlcommand);    Result.Open;  except    Result.Close;    Result.Free;    raise;  end; And the way I call it: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i var  database: tmydb;  tempresult: TFields;begin  database:= tmydb.Create;  tempresult:=database.query('SELECT * FROM testtable;').Fields;  showmessage('Items found in  testtable:  ' + inttostr(tempresult.Count));  database.free;end; ---------------------------------------------------------------------- molly · 2016-11-01 00:35 Quote from: TheLastCayen on November 01, 2016, 01:30:24 amAnd the way I call it: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i var  database: tmydb;  tempresult: TFields;begin  database:= tmydb.Create;  tempresult:=database.query('SELECT * FROM testtable;').Fields;  showmessage('Items found in  testtable:  ' + inttostr(tempresult.Count));  database.free;end;    afaik that still gives you memory leakage for the query ? Please double-check with heaptrc unit in debugging options. Edit: on top of that, the code is assuming the result to always be valid while doing exception handling returning an invalid result. Very hard to debug in such one-liners in case things go wrong. I understand you went for convenience there but, that is not always possible. ---------------------------------------------------------------------- TheLastCayen · 2016-11-01 01:24 Good point molly, I recompile using -gh and yup memory leak. Since I have more function in my database class, I was not sure about creating  the TZReadOnlyQuery in my constructor but if I have to choice between using unnecessary memory or a memory leak, I choice the unnecessary memory  This is my code for future reference: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i constructor Tmydb.Create;var  dbexist : boolean; //Use to know if we build the databasebegin  inherited;  dbexist := FileExists(dbname);  DatabaseTZ := TZConnection.Create(Nil);  QueryTZ := TZReadOnlyQuery.Create(Nil);  QueryTZ.Connection := DatabaseTZ; ;  with DatabaseTZ do    begin      Protocol  := 'sqlite-3';      Database  := dbname;      Connect;    end;  if not dbexist then    initializedb;end;   destructor Tmydb.Destroy;begin  QueryTZ.Close;  QueryTZ.Free;  DatabaseTZ.Disconnect;  DatabaseTZ.free;  inherited;end;   function Tmydb.query(sqlcommand: AnsiString):tfields;begin  with QueryTZ do    begin      Close;      SQL.Clear;      SQL.Add(sqlcommand);      Open;      Result := Fields;    end;end; This is how I use it: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i var  database: tmydb;  tempresult: tfields;begin  database:= tmydb.Create;  tempresult:=database.query('SELECT * FROM testtable;');  while not tempresult.Dataset.Eof do   begin      showmessage(tempresult.FieldByName('name').AsString);      tempresult.Dataset.Next;   end;  database.free;end;  Thank you. ps: Big thank you for informing me about  heaptrc. I now have to recompile bunch of my tools and I have the feeling few of them will make me cry ---------------------------------------------------------------------- molly · 2016-11-01 01:53 Quote from: TheLastCayen on November 01, 2016, 02:24:24 amGood point molly, I recompile using -gh and yup memory leak. Thanks for checking and reporting back. Quote... but if I have to choice between using unnecessary memory or a memory leak, I choice the unnecessary memory  Memory leaks are poison for apps, especially when they need to run 24/7. So, it is a wise decision  Quoteps: Big thank you for informing me about  heaptrc. I now have to recompile bunch of my tools and I have the feeling few of them will make me cry  You're most welcome. It of course depends a little on your coding skills how happy you're going to be with that  In case this is your introduction to heaptrace, then please use it wisely. It is one of the best tools/options available to make sure your code is not doing something stupid. While you're at it, make sure to set debug options range and overflow checking as well ---------------------------------------------------------------------- totya · 2016-11-01 08:27 Quote from: TheLastCayen on November 01, 2016, 01:30:24 amThank you titya. Work like a charme. Hi! Your last code is will be better, if you use exception handle, for example: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i var  database: tmydb;  tempresult: tfields;begin  database:= nil;  database:= tmydb.Create;   if Assigned(database) then  try    tempresult:= database.query('SELECT * FROM testtable;');     while not tempresult.Dataset.Eof do     begin        showmessage(tempresult.FieldByName('name').AsString);        tempresult.Dataset.Next;     end;  finally    database.free;  end;end; But try/except block recommended too. Otherwise, I think your code is slightly complicated, because if you want handle SQLite3, the TSqlite3Dataset is much easier to use. http://sqlite4fpc.yolasite.com/dataset-tutorial-1.php ---------------------------------------------------------------------- TheLastCayen · 2016-11-01 18:14 Hi totya. Thank you for the recommendation, I will implement the exception handler. My code can look complicate when you don't know what I am aiming for. 3 softwares will use my dbclass.pas. 1 GUI settings 1 CLI settings 1 core app running as deamon under linux or service under windows. So I was looking for an easy way to centralize my database management and also a quick way to move from sqllite to postgres if I need later on. I really appreciate how fast people jump in to help me solving my issue:)  Again Thank you everyone. ---------------------------------------------------------------------- totya · 2016-11-01 22:02 Quote from: TheLastCayen on November 01, 2016, 07:14:39 pmMy code can look complicate when you don't know what I am aiming for. 3 softwares will use my dbclass.pas. Hi! The TSqlite3Dataset was a simple tip, nothing more.