* * *

Author Topic: [SOLVED] Function result the content of a query  (Read 2076 times)

TheLastCayen

  • New member
  • *
  • Posts: 10
[SOLVED] Function result the content of a query
« on: October 31, 2016, 10:27:43 pm »
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]
  1. constructor Tmydb.Create;
  2. var
  3.   dbexist : boolean; //Use to know if we build the database
  4. begin
  5.   inherited;
  6.   //DeleteFile(dbname);
  7.   dbexist:=FileExists(dbname);
  8.   DatabaseTZ:= TZConnection.Create(Nil);
  9.   with DatabaseTZ do
  10.     begin
  11.       Protocol  := 'sqlite-3';
  12.       Database  := dbname;
  13.       Connect;
  14.     end;
  15. end;
  16.  
  17. destructor Tmydb.Destroy;
  18. begin
  19.   DatabaseTZ.Disconnect;
  20.   DatabaseTZ.free;
  21.   inherited;
  22. end;
  23.  
  24. function Tmydb.query(sqlcommand: AnsiString):TFields;
  25. var
  26.   Querry: TZReadOnlyQuery;
  27. begin
  28.   Querry    := TZReadOnlyQuery.Create(nil);
  29.   with Querry do
  30.     begin
  31.       Connection := DatabaseTZ;
  32.       SQL.Clear;
  33.       SQL.Add(sqlcommand);
  34.       Open;
  35.       Result := Fields;
  36.       Close;
  37.       Free;
  38.     end;
  39. end;
  40.  
  41.  

And this how I'am trying to call for it:

Code: Pascal  [Select]
  1.  
  2.  var
  3.   database: tmydb;
  4.   tempresult: TFields;
  5. begin
  6.   database:= tmydb.Create;
  7.   tempresult := database.query('SELECT * FROM testtable;');
  8.   database.free;
  9. end;
  10.  
  11.  

Thank you
« Last Edit: November 01, 2016, 09:19:50 pm by TheLastCayen »

molly

  • Hero Member
  • *****
  • Posts: 1922
Re: Function result the content of a query
« Reply #1 on: November 01, 2016, 12:17:15 am »
I'm not so much into databases but, from my understanding:

Code: Pascal  [Select]
  1.   Querry    := TZReadOnlyQuery.Create(nil);
  2.   with Querry do
  3.     begin
  4. ...
  5.       Result := Fields;
  6. ...
  7.       Free;
  8.     end;
  9.  
You free the querry and expect the fields property to stay alive ?

totya

  • Sr. Member
  • ****
  • Posts: 352
Re: Function result the content of a query
« Reply #2 on: November 01, 2016, 01:09:24 am »

TheLastCayen

  • New member
  • *
  • Posts: 10
Re: Function result the content of a query
« Reply #3 on: November 01, 2016, 01:30:24 am »
It 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]
  1. function Tmydb.query(sqlcommand: AnsiString):TZReadOnlyQuery;
  2. begin
  3.   Result := TZReadOnlyQuery.Create(nil);
  4.   try
  5.     Result.Connection := DatabaseTZ;
  6.     Result.SQL.Add(sqlcommand);
  7.     Result.Open;
  8.   except
  9.     Result.Close;
  10.     Result.Free;
  11.     raise;
  12.   end;  
  13.  

And the way I call it:
Code: Pascal  [Select]
  1. var
  2.   database: tmydb;
  3.   tempresult: TFields;
  4. begin
  5.   database:= tmydb.Create;
  6.   tempresult:=database.query('SELECT * FROM testtable;').Fields;
  7.   showmessage('Items found in  testtable:  ' + inttostr(tempresult.Count));
  8.   database.free;
  9. end;  
  10.  
« Last Edit: November 01, 2016, 01:33:20 am by TheLastCayen »

molly

  • Hero Member
  • *****
  • Posts: 1922
Re: Function result the content of a query
« Reply #4 on: November 01, 2016, 01:35:14 am »
And the way I call it:
Code: Pascal  [Select]
  1. var
  2.   database: tmydb;
  3.   tempresult: TFields;
  4. begin
  5.   database:= tmydb.Create;
  6.   tempresult:=database.query('SELECT * FROM testtable;').Fields;
  7.   showmessage('Items found in  testtable:  ' + inttostr(tempresult.Count));
  8.   database.free;
  9. end;  
  10.  
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.
« Last Edit: November 01, 2016, 01:40:45 am by molly »

TheLastCayen

  • New member
  • *
  • Posts: 10
Re: Function result the content of a query
« Reply #5 on: November 01, 2016, 02:24:24 am »
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]
  1. constructor Tmydb.Create;
  2. var
  3.   dbexist : boolean; //Use to know if we build the database
  4. begin
  5.   inherited;
  6.   dbexist := FileExists(dbname);
  7.   DatabaseTZ := TZConnection.Create(Nil);
  8.   QueryTZ := TZReadOnlyQuery.Create(Nil);
  9.   QueryTZ.Connection := DatabaseTZ; ;
  10.   with DatabaseTZ do
  11.     begin
  12.       Protocol  := 'sqlite-3';
  13.       Database  := dbname;
  14.       Connect;
  15.     end;
  16.   if not dbexist then
  17.     initializedb;
  18. end;  
  19. destructor Tmydb.Destroy;
  20. begin
  21.   QueryTZ.Close;
  22.   QueryTZ.Free;
  23.   DatabaseTZ.Disconnect;
  24.   DatabaseTZ.free;
  25.   inherited;
  26. end;  
  27. function Tmydb.query(sqlcommand: AnsiString):tfields;
  28. begin
  29.   with QueryTZ do
  30.     begin
  31.       Close;
  32.       SQL.Clear;
  33.       SQL.Add(sqlcommand);
  34.       Open;
  35.       Result := Fields;
  36.     end;
  37. end;  
  38.  

This is how I use it:
Code: Pascal  [Select]
  1. var
  2.   database: tmydb;
  3.   tempresult: tfields;
  4. begin
  5.   database:= tmydb.Create;
  6.   tempresult:=database.query('SELECT * FROM testtable;');
  7.   while not tempresult.Dataset.Eof do
  8.    begin
  9.       showmessage(tempresult.FieldByName('name').AsString);
  10.       tempresult.Dataset.Next;
  11.    end;
  12.   database.free;
  13. end;
  14.  

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  ;)
« Last Edit: November 01, 2016, 02:26:37 am by TheLastCayen »

molly

  • Hero Member
  • *****
  • Posts: 1922
Re: Function result the content of a query
« Reply #6 on: November 01, 2016, 02:53:10 am »
Good 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  ;D

Quote
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  ;)
You're most welcome. It of course depends a little on your coding skills how happy you're going to be with that  :P

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

  • Sr. Member
  • ****
  • Posts: 352
Re: Function result the content of a query
« Reply #7 on: November 01, 2016, 09:27:53 am »
Thank you titya. Work like a charme.

Hi!

Your last code is will be better, if you use exception handle, for example:

Code: Pascal  [Select]
  1. var
  2.   database: tmydb;
  3.   tempresult: tfields;
  4. begin
  5.   database:= nil;
  6.   database:= tmydb.Create;
  7.  
  8.   if Assigned(database) then
  9.   try
  10.     tempresult:= database.query('SELECT * FROM testtable;');
  11.  
  12.     while not tempresult.Dataset.Eof do
  13.      begin
  14.         showmessage(tempresult.FieldByName('name').AsString);
  15.         tempresult.Dataset.Next;
  16.      end;
  17.   finally
  18.     database.free;
  19.   end;
  20. 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

  • New member
  • *
  • Posts: 10
Re: Function result the content of a query
« Reply #8 on: November 01, 2016, 07:14:39 pm »
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

  • Sr. Member
  • ****
  • Posts: 352
Re: Function result the content of a query
« Reply #9 on: November 01, 2016, 11:02:33 pm »
My 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. :)

 

Recent

Get Lazarus at SourceForge.net. Fast, secure and Free Open Source software downloads Open Hub project report for Lazarus