Close connections after sql queries.
lazarus / 16-Databases

----------------------------------------------------------------------
Ame555 · 2020-12-15 13:40

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TForm1.Button1Click(Sender: TObject);begin     ZQuery1.Open;  //  
 ZQuery.Active:=true it's the same     ZQuery1.Insert;   
 ZQuery1.FieldByName('numero').AsInteger:=StrToInt(Edit1.Text);   
 ZQuery1.FieldByName('fecha').AsDateTime:=Now;              
 //now();     ZQuery1.FieldByName('nombre').AsString:=Edit1.Name;  //
Error     ZQuery1.Post;end; 
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 · 2020-12-15 15:00

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 · 2020-12-15 15:47

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 · 2020-12-15 16:18

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 · 2020-12-15 17:17

Quote from: Ame555 on December 15, 2020, 05:18:46 pmHi, 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 · 2020-12-15 18:56

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TForm1.MinuteTimer(Sender: TObject);begin  second:=second+1;   
lblSecond.Caption:=IntToStr(second);      if second > 59 then       
begin                         ZQuery1.Open;  //    ZQuery.Active:=true
it's the same                         ZQuery1.Insert;                
     
 ZQuery1.FieldByName('number1').AsInteger:=StrToInt(Edit1.Text);     
                  ZQuery1.FieldByName('date1').AsDateTime:=Now;      
        //now();                       
 ZQuery1.FieldByName('name1').AsString:=Edit1.Name;  // Error        
             
 ZQuery1.FieldByName('number2').AsInteger:=StrToInt(Edit1.Text);     
                  ZQuery1.FieldByName('date2').AsDateTime:=Now;      
        //now();                       
 ZQuery1.FieldByName('name2').AsString:=Edit1.Name;  // Error        
             
 ZQuery1.FieldByName('number3').AsInteger:=StrToInt(Edit1.Text);     
                  ZQuery1.FieldByName('date3').AsDateTime:=Now;      
        //now();                       
 ZQuery1.FieldByName('name3').AsString:=Edit1.Name;  // Error        
               ZQuery1.Post;                         ZQuery1.Close;  
            end;     end; 
I appreciate your time.

----------------------------------------------------------------------
cdbc · 2020-12-15 21:26

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 · 2020-12-15 21:34

Quote from: Ame555 on December 15, 2020, 07:56:58 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      
second := second + 1;      if second > 59 then 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 · 2020-12-15 22:02

Quote from: wp on December 15, 2020, 10:34:03 pmQuote from: Ame555 on
December 15, 2020, 07:56:58 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      
second := second + 1;      if second > 59 then 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 · 2020-12-15 23:10

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i           
  Transaction.StartTransaction                 // do operations --
insert, update, or delete of records in the database                 
 Transaction.Commit or Rollback                            --> 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i           
 with ZQuery do begin                    Open;                    //
Insert into (B)                   insert;                  
 FieldByName('numero').AsString := Edit1.Text;                 
 FieldByName('fecha').AsDateTime:=Now;                             
 FieldByName('nombre').AsString:= Edit1.Name;  // =>  this is strange.
Edit1.Name is always Edit1. Anyway,                    post;         
          // Save to (A)                 
 Transaction1.StartTransaction;                    Yquery.SQL:= Format
('insert into table1 values (%d, ''%s'', ''%s'')',                   
                 [FieldByName('numero').AsInteger,
FieldByName('fecha').AsString, FieldByName('nomber').AsString]);     
             yquery.execsql;                  
 Transaction1.CommitRetaining;    // Commit closes the dataset,
ZQuery.                     // Insert into (B)                 
 insert;                    FieldByName('numero').AsString :=
Edit2.Text;                   FieldByName('fecha').AsDateTime:=Now;  
                           FieldByName('nombre').AsString:=
Edit2.Name;                    post;                     // second
insertion is not saved to DB (A).                    showmessage('do
you see edit2.content?');                                 Close;     
            // you many need starttransaction here.                  
 Open;                   showmessage('do you still see
edit2.content?');               end;       
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 .