Hi Folks,
I got two procedures that are connected to MariaDB 5.6, My goal is if my application was launch for a long time, it must not lost the connection with my database and if the connection to the database was lost due to any reasons and restored (MariaDB running again), the procedure must still connect to the database without the application been restarted.

I have initialized the connection to the database using TSQLConnection to avoided the client library to trough away the connection, PLEASE see below.

procedure TdataModuleMySQL.createConnection;
begin
   SQLConnection1 := TMySQL55Connection.Create(nil);
   SQLConnection1.HostName:='127.0.0.1';
   SQLConnection1.DatabaseName:='first';
   SQLConnection1.UserName:='erickom';
   SQLConnection1.Password:='tux.';
end;
procedure TdataModuleMySQL.startDatabase;
begin
    //Start of connection
    createConnection;
    //Open the connection & transaction
    SQLConnection1.Connected:=True;
    SQLConnection1.Transaction:=SQLTransaction1;
    SQLTransaction1.DataBase:=SQLConnection1;
end;

I use the first procedure to INSERT data in the database via TLabeledEdit component and has been tested successfully , see below:

procedure TfrmMySQLTest.bitBtnInsertClick(Sender: TObject);
begin
try
   dataModuleMySQL.startDatabase;
   //Start of execution statment
   dataModuleMySQL.queryAtInsert;
   dataModuleMySQL.SQLQuery1.SQL.Text:='INSERT INTO student (names, dob)
VALUES (:getNAMES, :getDOB);';
dataModuleMySQL.SQLQuery1.Params.ParamByName('getNAMES').AsString:=lblEdtNames1.Text;
dataModuleMySQL.SQLQuery1.Params.ParamByName('getDOB').AsString:=lblEdtDOB1.Text;
   //This lets you know via the label if you are connected or not.
   if dataModuleMySQL.SQLConnection1.Connected then
   begin
     lblStatus1.Caption:='Connected';
     try
        //This starts the query which was created in the connection string
        dataModuleMySQL.SQLQuery1.ExecSQL;
        //Close the database
        dataModuleMySQL.stopDatabase;
     except
       showMessage('Execution of your query with error; APP can not
continued. ');
     end;
   end;
except
   lblStatus1.Caption:='Not Connected';
   showMessage('Database Connection Error. The database may be shutdown
or network issue.');
end;

The second procedure is used to SELECT and display in the TDBGrid (Successfully done) but lost the connect after the database been shutdown or when the application been running for long without activities, see below:

procedure TdataModuleMySQL.queryGrid;
begin
    SQLQueryGrid.DataBase:=SQLConnection1;
    SQLQueryGrid.Transaction:=SQLTransactionGrid;
    DataSource1.DataSet:=SQLQueryGrid;
    SQLQueryGrid.SQL.Text:='SELECT * FROM student;';
end;
procedure TfrmMySQLTest.btnGridClick(Sender: TObject);
begin
try
   dataModuleMySQL.startDatabase;
   //Start of execution statment
   dataModuleMySQL.queryGrid;
   DBGrid2.DataSource:=dataModuleMySQL.DataSource1;
   //This lets you know via the label if you are connected or not.
   if dataModuleMySQL.SQLConnection1.Connected then
   begin
           lblStatus3.Caption:='Connected';
                //This will also pass the inormation through to the GRID
                //This starts the query which was created in the
connection string
           try
              dataModuleMySQL.SQLQueryGrid.Open;
           except
              showMessage('Execution of your query with error; APP can
not continued.');
           end;
   end;
except
       lblStatus3.Caption:='Not Connected..';
       showMessage('Database Connection Error. The database may be
shutdown or network issue.');
       dataModuleMySQL.createConnection;
end;
end;

I don't know if the datasource can be the problem or not? or it is a normal behave?

Thank you in advance

I don't know much about disconnecting problem but I know you dont need to set transaction every time ,just this line work for others automatically :
SQLConnection1.Transaction:=SQLTransaction1;

About disconnecting you can make procedure that reconnect to DB when it disconnected or put a timer and check connection some times and if it was disconnected or other problem try to reconnect and put a counter so if MariaDB gone away for always,turn your auto connect system to off.

Regards,
Ara

You must always be prepared to handle such error conditions. The connection can go away for several reasons.

So, there is only one way to do what you want:

Catch errors, and analyse the error.
When the error indicates that the database connection was lost, re-initiate the connection.

There is a special connection.close(force) : if you set force to true, it will ignore errors that occur during close (the error can happen if the connection was lost).

Michael.

It will good to have something like this :

procedure TForm1.Connection1ConnectionLost(Sender: TObject;
ConnLostCause: TConnLostCause;var RetryMode: TRetryMode);

Regards,
Ara

Aradeonas wrote

It will good to have something like this : procedure TForm1.Connection1ConnectionLost(Sender: TObject; ConnLostCause: TConnLostCause;var RetryMode: TRetryMode);

I thought about this.
It should be possible, except that ConnLostCause will always be unknown.

Michael.

Aradeonas wrote

I don't know much about disconnecting problem but I know you dont need to set transaction every time ,just this line work for others automatically : SQLConnection1.Transaction:=SQLTransaction1;

So if I understand, for good practice I must not have many TSQLTransaction? Even if I got many TSQLQuery

If you need for special job yes otherwise no need and also no need to specify it many times.set connection's transaction one time and all query will follow connection.

Regards,
Ara

Yes. 1 transaction should be enough for most cases.

Michael.