Close connections after sql queries.

[Ame555]

Dear community.

Recently I had to review a program made in C# connected to MariaDB that stopped recording data suddenly, when investigating the source code and find out on the Internet the error code that recorded the DB log I could determine that it was due to a blocking error, this because the application after each query did not close the connections properly and just for there goes my query.

I am using this code to save data on my DB.

Code: Pascal  [Select][+][-]

1. procedure TForm1.Button1Click(Sender: TObject);
2. begin
3.      ZQuery1.Open;  //    ZQuery.Active:=true it's the same
4.      ZQuery1.Insert;
5.      ZQuery1.FieldByName('numero').AsInteger:=StrToInt(Edit1.Text);
6.      ZQuery1.FieldByName('fecha').AsDateTime:=Now;                //now();
7.      ZQuery1.FieldByName('nombre').AsString:=Edit1.Name;  // Error
8.      ZQuery1.Post;
9. end;
10.  

When I use this code to insert data I notice that there is a part where the connection "ZQuery1.Open;" is opened, but after executing it it seems that it does not close the connection, this is fine or it is also necessary to close this connection?

Sorry if my question is too basic, I'm just starting in FreePascal and I don't want to have the problems of the application I mentioned.

I appreciate your kind attention.

[MarkMLl]

I'm not confident that I'm the best person to answer this, but my understanding is that you need to keep the query open whilst you are making use of the data in GUI-oriented controls, after which you close it.

This practice was inherited from the way Delphi did it, but it might be problematic if the path to your server includes connection pooling etc. There was recent discussion which said that you could transfer the results to- I think- a TBufDataset, but this is something that I've not yet had a chance to explore.

MarkMLl

[af0815]

You should close the query if you need no data anymore in the datasentensive components. If you use this query only for insert data, you can a Zquery1.close after the Post. If you need no data anymore, you can also close the connection.

A good place is the closeform to check if some of the connections are open and to close this.

[Ame555]

Hi, thanks for answering, actually I will use this query only to insert data, it will be about 100 data, in each of the inserts I should close the query?

Thank you for your time.

[MarkMLl]

Hi, thanks for answering, actually I will use this query only to insert data, it will be about 100 data, in each of the inserts I should close the query?

Bundle them up into a single transaction.

MarkMLl

[Ame555]

Thanks for answering, I have to make the data to be saved every 1 minute so I was thinking to use a timer for that, then to group everything in a single transaction I should do it as follows?

Code: Pascal  [Select][+][-]

1. procedure TForm1.MinuteTimer(Sender: TObject);
2. begin
3.   second:=second+1;
4.     lblSecond.Caption:=IntToStr(second);
5.       if second > 59 then
6.         begin
7.                          ZQuery1.Open;  //    ZQuery.Active:=true it's the same
8.                          ZQuery1.Insert;
9.                          ZQuery1.FieldByName('number1').AsInteger:=StrToInt(Edit1.Text);
10.                          ZQuery1.FieldByName('date1').AsDateTime:=Now;                //now();
11.                          ZQuery1.FieldByName('name1').AsString:=Edit1.Name;  // Error
12.                          ZQuery1.FieldByName('number2').AsInteger:=StrToInt(Edit1.Text);
13.                          ZQuery1.FieldByName('date2').AsDateTime:=Now;                //now();
14.                          ZQuery1.FieldByName('name2').AsString:=Edit1.Name;  // Error
15.                          ZQuery1.FieldByName('number3').AsInteger:=StrToInt(Edit1.Text);
16.                          ZQuery1.FieldByName('date3').AsDateTime:=Now;                //now();
17.                          ZQuery1.FieldByName('name3').AsString:=Edit1.Name;  // Error
18.                          ZQuery1.Post;
19.                          ZQuery1.Close;
20.                 end;     
21. end;
22.  

I appreciate your time.

[cdbc]

hi
have a look at this, it's made for SQLite, but maybe you can the idea and turn it towards MariaDB....
Regards Benny

[wp]

Code: Pascal  [Select][+][-]

1.       second := second + 1;
2.       if second > 59 then
3.  

Be warned: A second #60 does not exist, the second following #59 is #0 of the next minute --> your condition will never be true.

[rvk]

Code: Pascal  [Select][+][-]

1.       second := second + 1;
2.       if second > 59 then
3.  

Be warned: A second #60 does not exist, the second following #59 is #0 of the next minute --> your condition will never be true.

He's using a variable second and second := second + 1 so second #60 will happen.
(Although the name isn't really wisely chosen )

(And there should also be a reset to zero otherwise after this #60 it will save every second)

[egsuh]

Many beginners do not understand the relationship among database (and transaction), datasets and visual controls.  You'd better to think of a three layer.


                       (A)                                          (B)                                                    (C)
             Database file               TDataSet descendants    (TDatasource)     TDataControls
   (SQLite, Firebird, MariaDB)          (TSQLQuery, etc.)                                (TDBEdit, TDBGrid, etc.)


1.  When you open an SQL of TSQLQuery (or other query) , e.g.      "select * from table1",   records are selected within server (A), and a copy of them are sent to DataSet(B).  The content is visually shown in the data controls (C).

2.  TDataset operations,  insert, edit, post, etc. are done to this TDataSet (local copy, B).  But these are not reflected in the A in itself.

3.  The changes done to B should be updated to A, for them to be permanently stored. This is done by TDataSet.ApplyUpdates, or other SQL statements, like  insert into table1 (f1, f2) values (v1, v2);", or  "update table1 set f1=v1", etc. depending on the connection type.

4. Transactions are applied to the server side(A). 
    It defines a block of operations to stored or cancelled late. Followings are general order.

Code: Pascal  [Select][+][-]

1.               Transaction.StartTransaction
2.                  // do operations -- insert, update, or delete of records in the database  
3.                  Transaction.Commit or Rollback  
4.                          --> the changes since the start of transaction are stored if committed, and discarded if rolled back.

Transaction is not directly related with with local copy, i.e. TDataSet(B).  For example,

Code: Pascal  [Select][+][-]

1.              with ZQuery do begin
2.                    Open;
3.  
4.                    // Insert into (B)
5.                    insert;
6.                    FieldByName('numero').AsString := Edit1.Text;
7.                    FieldByName('fecha').AsDateTime:=Now;            
8.                    FieldByName('nombre').AsString:= Edit1.Name;  // =>  this is strange. Edit1.Name is always Edit1. Anyway,
9.                    post;
10.  
11.                    // Save to (A)
12.                    Transaction1.StartTransaction;
13.                    Yquery.SQL:= Format ('insert into table1 values (%d, ''%s'', ''%s'')',
14.                                      [FieldByName('numero').AsInteger, FieldByName('fecha').AsString, FieldByName('nomber').AsString]);
15.                    yquery.execsql;
16.                    Transaction1.CommitRetaining;    // Commit closes the dataset, ZQuery.
17.  
18.                    // Insert into (B)
19.                    insert;
20.                    FieldByName('numero').AsString := Edit2.Text;
21.                    FieldByName('fecha').AsDateTime:=Now;            
22.                    FieldByName('nombre').AsString:= Edit2.Name;
23.                    post;
24.  
25.                    // second insertion is not saved to DB (A).
26.  
27.                   showmessage('do you see edit2.content?');
28.              
29.                   Close;
30.                    // you many need starttransaction here.
31.                    Open;
32.                   showmessage('do you still see edit2.content?');
33.               end;
34.        
35.  

In this case, you will see contents of both Edit1 and Edit2 on (C), but your server has only Edit1's content. You can confirm this just by Closing and reopening ZQuery.
   
Memory-resident datasets, like TBufDataSet, do not have (A). There are only (B) and (C).  But the operations done to these are also to the copy within memory. To save them, you have to store it to a file or in other ways.

So, whatever stay in TDataSet descendants are stay just in memory, not on a permanent media.

I'm writing this because I spent many days to finally grasp this relationship (and no problem in writing programs afterwards. Hope other beginners do not waste time. If you already know this, and this is not point of your question, just forgive me .