Delphi OO

Hi, I am new to delphi and would like to understand more about OO techniques. The application I have developed uses ADO technology. I would like to know some basics of how to define a class that uses ADOQUery and ADODatabase objects to connect to the database and perform updates. As I come from VB background I would like to convert my query methods to classes. For example, I have a pas file which has set of query statements and I would like to convert this file to a class. There is a set of functions which takes in a parameter and returns the query string as output.

Function buildtestquery(paramname:string; paramname1:string):string;

How to build something which is more generic as I dont want to keep increasing the number of inputs to the function as the number of parameters increases. Do I create a class which uses TParam and define the parameters from my form when I need to execute this query? Any explanation and example code will be useful for me to understand this better.

Thanks.

Hello:

Your question is a good one and you are wise to seek advise on coding OO when you don't know - heck, I still ask others, after 20 years of coding, what their thoughts are as they may think of things I have not have thought of yet.

Anyway, on to the answer.

I do not see a way to attach a file here in the post, maybe I am just too tired - however, if you email me, I will be happy to share with you some code I already wrote (see below.)

This is one way to generically call two different types of DB using a singleton TDataset for all objects.

Again, if you want more input, drop me a line - I realize this is very brief, but unfortunately, I have to get going and cannot post more right now.

function TPOSDB.AddSQLToQuery(p_sSQL: string;
                              p_sSQLToAddLocallyForServer: string = ''): boolean;
var
  sError:      string;

  function DoADOQuery(p_sSQL: string): boolean;
  begin
// for threading    CoInitialize(nil); // <[;_--] manually call CoInitialize()
    if (not objDBServer.F_conDB.Connected) then
    begin
      try
        objDBServer.F_conDB.Open;
      except
        on E: EOLEException do
        begin
          sError       := E.Message;
          if (    (    (pos('10061',sError) > 0)  // cannot connect to DB
                    or (pos('10060',sError) > 0)  // cannot connect to DB
                    or (pos('gone away',sError) > 0) // lost connection
                    or (pos('10065',sError) > 0)  // connect to MySQL server
                   )
              and (     (pos('select fn',lowercase(p_sSQL)) = 0) // do not "fail over" to elevate if using functions
                    // except for scanning of bands, fail those over
                    or (pos('fn_scan_wristband',lowercase(p_sSQL)) > 0)
                  )
             ) then
          begin
            // only save locally if told to do so
            if (F_objDBWork.F_bAllowAddToSQLToAddTable) then
            begin
              { Send in the "main" objDBServer.F_objLocalDB to the rescue using its local object }
//test              F_objDBWork := objDBServer.F_objLocalDB;
              Result := objDBServer.F_objLocalDB.AddSQLToQuery(p_sSQL, p_sSQLToAddLocallyForServer);
              exit;
            end;
          end
          else begin
            if (     (pos('select fn',lowercase(p_sSQL)) > 0) // do not "fail over" to elevate if using functions
                 // except for scanning of bands, fail those over
                 and (pos('fn_scan_wristband',lowercase(p_sSQL)) = 0)
               ) then
            begin
              if (    (pos('10061',sError) > 0)  // cannot connect to DB
                   or (pos('10060',sError) > 0)  // cannot connect to DB
                   or (pos('gone away',sError) > 0) // lost connection
                   or (pos('10065',sError) > 0)  // connect to MySQL server
                 ) then
              begin
//                ShowMessage('Cannot connect to the DB. Cannot process this type of transaction');
                Result := False;
                exit;
              end;
            end
            else begin
              // some other error
              ShowMessage(E.ClassName+' error raised, with message : '+sError
                         + CRLF
                         + 'SQL statment:'
                         + CRLF
                         + p_sSQL
                         );
              Result := False;
              exit;
            end;
            Raise;

          end;

        end;

      end;

    end;

    F_objDBWork.qryWork := TADOQuery(F_objDBWork.qryWork);
    with (TADOQuery(F_objDBWork.qryWork)) do
    begin
      if (Active) then
      begin
        Active := False;
      end;
      Sql.Clear;
      {
        MySQL: If sending in a "\" as part of the SQL, say like with a filename
        in a string field, MySQL, treats it as an escape character and throws
        it out, so just double each of the "\"
      }
      if (pos('\',p_sSQL) > 0) then
      begin
        p_sSQL := StringReplace(p_sSQL, '\', '\\', [rfReplaceAll, rfIgnoreCase]);
      end;

      // add any tracing of SQL here
      SQL.Add(p_sSQL);
      if (lowercase(copy(p_sSQL,1,6)) = 'select') then
        Active := True
      else
        ExecSQL;
    end;
    Result := True;
// for threading    CoUnInitialize();
  end;

  function DoEDBQuery(p_sSQL: string; p_sSQLToAddLocallyForServer: string = ''): boolean;
  begin
    F_objDBWork.qryWork := TEDBQuery(F_objDBWork.qryWork);
    with (TEDBQuery(F_objDBWork.qryWork)) do
    begin
      // need to change the FIELD_SEPERATOR_MYSQL to FIELD_SEPERATOR_ELEVATE
      p_sSQL := StringReplace(p_sSQL, FIELD_SEPERATOR_MYSQL, FIELD_SEPERATOR_ELEVATE, [rfReplaceAll, rfIgnoreCase]);
      if (Active) then
      begin
        Active := False;
      end;
      {
        Elevate needs the current session name and database
        If an elevate is acting as a "server" and this server is down, the
        intital session name and database pointing to the "server" is no
        longer valid as UseLocalConnection has now created new session names
        and database
      }
      if (F_objDBWork.F_EDBDatabase <> nil) then
      begin
        TEDBQuery(F_objDBWork.qryWork).DatabaseName := F_objDBWork.F_EDBDatabase.Database;
        TEDBQuery(F_objDBWork.qryWork).SessionName  := F_objDBWork.F_EDBDatabase.SessionName;
      end;

      {
        Okay to add fn_scan_wristband to the DB at this point, but nothing else
        do not execute or make active as it will fail
        // JUST check the first 30 characters (the insert into sqltoadd will
        also have this call
      }
      if (pos('fn_scan_wristband',lowercase(copy(p_sSQL,1,30))) = 0) then
      begin
        Sql.Clear;
        // add any tracing of SQL here
        SQL.Add(p_sSQL);
        if (lowercase(copy(p_sSQL,1,6)) = 'select') then
          Active := True
        else begin
          ExecSQL;
          {
            Do we need to save the query for later replication to the server?
            First, the MySQL will not be a "client/local" DB, so the only time
            we will write an SQL for later replication will be in an Elevate.
            Now, there can be an Elevate server, so, we have to see if though
            Elevate, did we try to write to the Elevate server first and fail.
            Also, the only time we want to save SQL statements is when they
            are NOT SELECT statements, hence why this code is in this portion
            of the IF statement immediately above.
            We will need to save for later replication
            if F_bUsingLocalDB is True
            and
            DatabaseConnections.UseLocalOnly is False
            F_bUsingLocalDB is turned on when a server write
            fails and when using ONLY a local DB, this is why we also need to
            check UsingLocalOnly. If UsingLocalOnly is also true, then we
            already wrote to the local DB.
            When actually adding the records to the tblSQLToAdd table,
            we do not want to add more records F_bAllowAddToSQLToAddTable
          }
          if (     (DatabaseConnections.UseLocalOnly = False)
               and (F_objDBWork.F_bUsingLocalDB = True)
               and (objdbserver.F_bAllowAddToSQLToAddTable = True)
             ) then
          begin
            // do the writing to the tblSQLtoAdd table right away
            if (p_sSQLToAddLocallyForServer = '') then
              WriteSQLLocally(p_sSQL)
            else
              WriteSQLLocally(p_sSQLToAddLocallyForServer);
          end;
        end;
      end
      else begin
        if (     (DatabaseConnections.UseLocalOnly = False)
             and (F_objDBWork.F_bUsingLocalDB = True)
             and (objdbserver.F_bAllowAddToSQLToAddTable = True)
           ) then
        begin
          // do the writing to the tblSQLtoAdd table right away
          if (p_sSQLToAddLocallyForServer = '') then
            WriteSQLLocally(p_sSQL)
          else
            WriteSQLLocally(p_sSQLToAddLocallyForServer);
        end;
      end;
    end;
    Result := True;
  end;

begin
  Result       := False;

  // trace me
  if (POSSettings.TraceSQL = '1') then
  begin
    TPOSUtils.TraceSQL(p_sSQL);
    if (p_sSQLToAddLocallyForServer <> '') then
    begin
      TPOSUtils.TraceSQL(p_sSQLToAddLocallyForServer);
    end;
  end;

  if (F_objDBWork.qryWork is TADOQuery) then
  begin
    try
      Result := DoADOQuery(p_sSQL);
    except
      on E: EDatabaseError do
      begin
        sError       := E.Message;
        // cannot drop table because does not exist or no permission
        if (pos('3701',sError) > 0) then
        begin
          // do nothing, this is okay
        end
        else begin
          ShowMessage(E.ClassName+' error raised, with message : '+sError
                     + CRLF
                     + 'SQL statment:'
                     + CRLF
                     + p_sSQL
                     );
          // some other error
          Raise;
        end;
      end;
      on E: EOLEException  do
      begin
        sError       := E.Message;
        if (     (   (pos('10061',sError) > 0)  // cannot connect to DB
                  or (pos('10060',sError) > 0)  // cannot connect to DB
                  or (pos('gone away',sError) > 0) // lost connection
                  or (pos('10065',sError) > 0)  // connect to MySQL server
                 )
             and (    (pos('select fn',lowercase(p_sSQL)) = 0) // do not "fail over" to elevate if using functions
                    // except for scanning of bands, fail those over
                   or (pos('fn_scan_wristband',lowercase(p_sSQL)) > 0)
                 )
           ) then
        begin
          // try to re-establish a connection
          try
            if (pos('gone away',sError) > 0) then // lost connection
            begin
              objDBServer.qryWork.Active := False;
              objDBServer.F_conDB.Close;
              objDBServer.F_conDB.Open;
              Result := DoADOQuery(p_sSQL);
            end;
          except
            on E: EOLEException do
            begin
              sError       := E.Message;
              if (    (pos('10061',sError) > 0)  // cannot connect to DB
                   or (pos('10060',sError) > 0)  // cannot connect to DB
                   or (pos('gone away',sError) > 0) // lost connection
                   or (pos('10065',sError) > 0)  // connect to MySQL server
                 ) then
              begin
                // only save locally if told to do so
                if (F_objDBWork.F_bAllowAddToSQLToAddTable) then
                begin
                  { Send in the "main" objDBServer.F_objLocalDB to the rescue using its local object }
//test                  F_objDBWork := objDBServer.F_objLocalDB;
                  Result := objDBServer.F_objLocalDB.AddSQLToQuery(p_sSQL, p_sSQLToAddLocallyForServer);
                end;
              end
              else begin
                // some other error
                ShowMessage(E.ClassName+' error raised, with message : '+sError
                           + CRLF
                           + 'SQL statment:'
                           + CRLF
                           + p_sSQL
                           );
                  Result := False;
                  exit;
                Raise;

              end;

            end;

          end;

        end
        else begin
          if (       (pos('select fn',lowercase(p_sSQL)) > 0)  // do not "fail over" to elevate if using functions
               // except for scanning of bands, fail those over
               and (pos('fn_scan_wristband',lowercase(p_sSQL)) = 0)
             ) then
          begin
            if (    (pos('10061',sError) > 0)  // cannot connect to DB
                 or (pos('10060',sError) > 0)  // cannot connect to DB
                 or (pos('gone away',sError) > 0) // lost connection
                 or (pos('10065',sError) > 0)  // connect to MySQL server
               ) then
            begin
//              ShowMessage('Cannot connect to the DB. Cannot process this type of transaction');
              Result := False;
              exit;
            end;
          end
          else begin
            // some other error
            ShowMessage(E.ClassName+' error raised, with message : '+sError
                       + CRLF
                       + 'SQL statment:'
                       + CRLF
                       + p_sSQL
                       );
            Result := False;
            exit;
          end;
        end;
      end;
    end;
  end
  else if (F_objDBWork.qryWork is TEDBQuery) then
  begin
    try
      Result := DoEDBQuery(p_sSQL, p_sSQLToAddLocallyForServer);
    except
      on E: EDatabaseError do
      begin
        sError := E.Message;
        // cannot drop table because does not exist or no permission
        if (pos('3701',sError) > 0) then
        begin
          // do nothing, this is okay
        end
        else if (    (pos('10061',sError) > 0)  // cannot connect to DB
                  or (pos('10060',sError) > 0)  // cannot connect to DB
                  or (pos('1101',sError) > 0)   // lost connection
           ) then
        begin
          // try to re-establish a connection
          try
            objDBServer.F_EDBSession.Close;
            objDBServer.F_EDBSession.Open;
          except
            on E: EDatabaseError do
            begin
              sError := E.Message;
              // cannot drop table because does not exist or no permission
              if (pos('3701',sError) > 0) then
              begin
                // do nothing, this is okay
              end
              else if (    (pos('10061',sError) > 0)  // cannot connect to DB
                        or (pos('10060',sError) > 0)  // cannot connect to DB
                        or (pos('1101',sError) > 0)   // lost connection
                 ) then
              begin
                // only save locally if told to do so
                if (F_objDBWork.F_bAllowAddToSQLToAddTable) then
                begin
                  { Send in the "main" objDBServer.F_objLocalDB to the rescue using its local object }
//test                  F_objDBWork := objDBServer.F_objLocalDB;
                  Result := objDBServer.F_objLocalDB.AddSQLToQuery(p_sSQL, p_sSQLToAddLocallyForServer);
                end;
              end;
            end
            else begin
              // some other error
              ShowMessage(E.ClassName+' error raised, with message : '+sError
                         + CRLF
                         + 'SQL statment:'
                         + CRLF
                         + p_sSQL
                         );
              Raise;
            end;
          end;
        end
        else begin
          // some other error
          ShowMessage(E.ClassName+' error raised, with message : '+sError
                     + CRLF
                     + 'SQL statment:'
                     + CRLF
                     + p_sSQL
                     );
          Raise;
        end;
      end;
    end;
  end;
  if (     (F_objDBWork.qryWork.Eof)
       and (not objDBServer.F_objLocalDB.qryWork.Eof)
     ) then
    // try the local
    qryTemp := objDBServer.F_objLocalDB.qryWork
  else
    qryTemp := F_objDBWork.qryWork;
end;

Okay. Sounds good. Sorry I could not write more, but I did not know how to wrap it up nicely inthe time I had at the moment.

Hello,

Thank you so much for the detailed post and sorry for the delayed response. I have been away on holiday for few weeks. I will go through the detail and come back to you if I have further queries. Thanks once again.