Home > Mobile >  Best practice for storing many long SQL queries in Delphi
Best practice for storing many long SQL queries in Delphi

Time:05-05

I am developing applications with Delphi XE. I have many long SQL queries and I don't want to store them in code like below, because this method reduces code readability:

CONST_SQL1 = 'SELECT ... FROM ..'#13#10   'WHERE ...';

Maybe creating a DataModule and storing the SQL queries in the Memo components can be an alternative option. This way the SQL queries will have more readability rather than the method above but in this way, I have to add many Memo components in the DataModule like Memo_SQL1, Memo_SQL2, Memo_SQL3, ...

Also I don't want to store these SQL queries in separate files or in a database.

Is there a single component that I can store all these SQL queries inside or what is your best practice for this case?

CodePudding user response:

Thank you very much for all of your comments. On my project I prefer only one file and that is the program's EXE. Since I don't want any other separate files, I prefer the SQL queries to be stored in an EXE resource. Since SQL queries are changing so often and storing the SQL queries in the database needs additional insert efforts, I don't prefer to store them in the database.

I tried to develop the most basic and helpful method for my needs. In this method, all SQL queries are being stored in an EXE resource as a single XML file format. I am sharing below if this method can help any other people.

  • SQLQueries.rc:

    SQLQueries RCDATA SQLQueries.xml
    
  • SQLQueries.xml (in UTF-8):

    <?xml version="1.0" encoding="utf-8"?>
    <SQLQueries>
        <SQLQuery SQLQueryName="SQL1">
            SELECT
                *
            FROM
                TABLE1
            WHERE
                1 = 1
        </SQLQuery>
        <SQLQuery SQLQueryName="SQL2">
            SELECT
                *
            FROM
                TABLE2
            WHERE
                2 = 2
        </SQLQuery>
    </SQLQueries>
    
  • Project1.dpr:

    program Project1;
    
    {$APPTYPE CONSOLE}
    
    {$R Project1.res} // program's default res file storing the icon, version info, etc..
    
    {$R 'SQLQueries.res' 'SQLQueries.rc'} // add this line. if not SQLQueries.res automatically created after compile, try dragging and dropping SQLQueries.rc file to the project.
    
    uses
      Windows, Classes, XMLDoc, XMLIntf, Generics.Collections, ActiveX;
    
    var
      SQLDictionary: TDictionary<string,string>;
    
    procedure LoadGetSQLQueries;
    var
      ResourceStream: TResourceStream;
      XMLDocument: IXMLDocument;
      XMLNode: IXMLNode;
      i: Integer;
    begin
      ResourceStream := TResourceStream.Create(HInstance, 'SQLQUERIES', RT_RCDATA);
      try
        XMLDocument := TXMLDocument.Create(nil);
        try
          XMLDocument.LoadFromStream(ResourceStream);
          for i := 0 to XMLDocument.ChildNodes['SQLQueries'].ChildNodes.Count - 1 do
          begin
            XMLNode := XMLDocument.ChildNodes['SQLQueries'].ChildNodes[i];
            SQLDictionary.AddOrSetValue(XMLNode.Attributes['SQLQueryName'], XMLNode.Text);
          end;
        finally
          XMLDocument := nil;
        end;
      finally
        ResourceStream.Free;
      end;
    end;
    
    begin
      CoInitialize(nil);
      try
        SQLDictionary := TDictionary<string,string>.Create;
        try
          LoadGetSQLQueries;
          Writeln(SQLDictionary['SQL1']);
          Readln;
        finally
          SQLDictionary.Free;
        end;
      finally
        CoUninitialize;
      end;
    end.
    
  • Compiled EXE with my resource named SQLQueries and its content:

    EXE resources

  • The program's output of Writeln(SQLDictionary['SQL1']) verifies that the SQL query has been read successfully by XMLDocument.LoadFromStream(ResourceStream):

    program's console output

CodePudding user response:

A tip, you can use views on the database server, on your code in delphi you use SELECT * FROM VW_CUSTOMER, you always can modify the queries there, the plus side is that all changes in the logic are automatically reflected in all your instances of our EXE, the cons side is if your remove o add a column, if you add there must be an uprade of your exe to show the new column, if your remove or change the name of a column, you will have an exception where the query on that modified view is used.

  • Related