[SOLVED] Function result the content of a query

[TheLastCayen]

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

[molly]

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]

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]

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.  

[molly]

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.

[TheLastCayen]

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 

[molly]

Good point molly, I recompile using -gh and yup memory leak.

Thanks for checking and reporting back.

... 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 

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 

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]

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]

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]

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.