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:The program's output of
Writeln(SQLDictionary['SQL1'])
verifies that the SQL query has been read successfully byXMLDocument.LoadFromStream(ResourceStream)
:
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.