Forum > Databases
Best practice to store long SQL files with you project
Okoba:
Hello,
I want to ask your opinion about what is the best way to store multi line SQL in your code? I dont like converting the code to Pascal string as it will be as ugly as this sample:
--- Code: Pascal [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---'SELECT' + #13#10 +'A,' + #13#10 +'B,' + #13#10 +'C,' + #13#10 +'FROM' + #13#10 +'Test' + #13#10 +'WHERE' + #13#10 +'A > C' + #13#10 +'And' + #13#10 +'C + B = 2'
I thought about resources but they are a little complicated.
Is there other ways like including the text at compile time like .inc files?
I appericate any ideas.
rvk:
--- Quote from: Okoba on September 22, 2023, 02:46:39 pm ---Is there other ways like including the text at compile time like .inc files?
--- End quote ---
.inc files are stil code so you would still need to use the quotes etc.
You could just add the SQL in the TSQLQuery itself (via the object inspector).
But that way you can't easily change things in code.
Is that a requirement?
You could also put it in a .txt file and link that in as resource and then create a function which reads the resource and extract the correct SQL.
But then again... you can't change it easily in code.
So what exactly is the requirement because I would put the code you showed directly in the TSQLQuery.
And for doing it in code I would merge some lines together.
--- Code: Pascal [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---SQLQuery1.SQL.Text := 'SELECT A, B, C,' + #13#10 + 'FROM Test' + #13#10 + 'WHERE A > C And C + B = 2';
Also note that you would probably work with parameters:
--- Code: Pascal [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---SQLQuery1.SQL.Text := 'SELECT A, B, C,' + #13#10 + 'FROM Test' + #13#10 + 'WHERE A > C And C + B = :wanted_addition';SQLQuery1.ParamByName('wanted_addition').AsInteger := 2;
paweld:
--- Quote from: rvk ---You could also put it in a .txt file and link that in as resource and then create a function which reads the resource and extract the correct SQL.
--- End quote ---
I am just using a similar solution.
I have a small unit where I create a list of strings from defined resources.
a single string looks like this:
--- Code: ------<? string-name
sample text
with
few
lines
!!!
111
one one one
--?>
--- End code ---
This is very convenient if I use very long queries, texts, etc.
Sample in attachment.
Okoba:
@rvk Your solution in case of using TSQLQuery is very good. Thank you.
@paweld, great way to organize. Thank you very much.
I wished Lazarus had a better resource support and not the current simple OS resource management, or FPC had a better multi line string handling, or better of all, a way to include content like
--- Code: Pascal [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---{$Import test.sql}
rvk:
The multiline option in the source is discussed at length here (in a 11 page topic somewhere) and on the mailinglist.
I don't think it got implemented (topic is from 2019).
For my commercial program I actually use a text file on my webserver for a Report Generator.
The file looks something like this:
--- Code: Text [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---===================================================Menu title #1>Report title #1----------select id,name,field1, field2, etcfrom table1where name=:name and docdate>=:docdate ===================================================Menu title #1>Report title #2----------select id,name,field1, field2, etcfrom table2where name=:name and docdate>=:docdate ===================================================Menu title #2>Report title #3----------select id,name,field1, field2, etcfrom table3where name=:name and docdate>=:docdate etc. etc.
The Report Generator retrieves this file and builds a pulldown-menu the user can choose from (besides entering their own SQL).
There are a lot of ready made SQL statements ranging from simple to very complex (with joins/unions etc).
The program build a dialog for the possible parameters when there are any.
The user only has to choose one and press execute (and fill out the form if there is any).
I have that file on my own webserver so I can adjust it easily and all users can access it directly (without the need to update the program).
You could also have this text file in a resource of course.
Navigation
[0] Message Index
[#] Next page