Best practice for storing many long SQL queries in Delphi

Question

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?

Answer 1

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:

  

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

  

Answer 2

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.

Answer 3

I'd go for a single (text) resource file for each query. That makes it much easier to trace changes in your source code management system.

Add a subfolder in your project's directory for your SQL files and add them to a rc- file like:

SQL1               TEXT  .\SQL\SQL1.sql
SQL2               TEXT  .\SQL\SQL1.sql

Add that rc-file to the project and you can load the SQL text directly into the SQL member of a TQuery, just like

program Project2;

{$APPTYPE CONSOLE}

{$R *.res}
{$R 'SQLQueries.res' 'SQLQueries.rc'}

uses
  Windows,
  Data.SqlExpr,
  Classes;

var
  ResourceStream: TResourceStream;
  Query: TSQLQuery;
begin
  ResourceStream := TResourceStream.Create(HInstance, 'SQL1', 'TEXT');
  Query:= TSQLQuery.Create(nil);
  try
    Query.SQL.LoadFromStream(ResourceStream);
    Writeln(Query.SQL.Text);
    ReadLn;
  finally
    Query.Free;
    ResourceStream.free;
  end;
end.