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  [Select][+][-]

1. 'SELECT' + #13#10 +
2. 'A,' + #13#10 +
3. 'B,' + #13#10 +
4. 'C,' + #13#10 +
5. 'FROM' + #13#10 +
6. 'Test' + #13#10 +
7. 'WHERE' + #13#10 +
8. 'A > C' + #13#10 +
9. 'And' + #13#10 +
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]

Is there other ways like including the text at compile time like .inc files?

.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  [Select][+][-]

1. SQLQuery1.SQL.Text := 'SELECT A, B, C,' + #13#10 +
2.   'FROM Test' + #13#10 +
3.   'WHERE A > C And C + B = 2';

Also note that you would probably work with parameters:

Code: Pascal  [Select][+][-]

1. SQLQuery1.SQL.Text := 'SELECT A, B, C,' + #13#10 +
2.   'FROM Test' + #13#10 +
3.   'WHERE A > C And C + B = :wanted_addition';
4. SQLQuery1.ParamByName('wanted_addition').AsInteger := 2;

[paweld]

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.

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: [Select]

---<? string-name 
sample text   
with   
few 
lines 
!!!   
111   
one one one   
--?>   
   
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  [Select][+][-]

1. {$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  [Select][+][-]

1. ===================================================
2. Menu title #1>Report title #1
3. ----------
4. select id,name,field1, field2, etc
5. from table1
6. where name=:name and docdate>=:docdate
7.  
8. ===================================================
9. Menu title #1>Report title #2
10. ----------
11. select id,name,field1, field2, etc
12. from table2
13. where name=:name and docdate>=:docdate
14.  
15. ===================================================
16. Menu title #2>Report title #3
17. ----------
18. select id,name,field1, field2, etc
19. from table3
20. where name=:name and docdate>=:docdate
21.  
22. etc. etc.
23.  

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.

[Okoba]

That is a very good sample. Thank you.
Yes I saw dissuasions about it and anyway it will be needed, not for SQL only.

[Zvoni]

Don't store SQL-Statements in plain Textfiles, especially not as multiline (like your example).
For "complex" and dynamic statements i use something like this:

Code: Pascal  [Select][+][-]

1. program Project1;
2. Uses SysUtils;
3.  
4. Var
5.   f:Array[0..2] Of String;
6.   w:Array[0..1] Of String;
7.   s:String;
8.  
9. begin
10.   f[0]:='A';
11.   f[1]:='B';
12.   f[2]:='C';
13.   w[0]:='A > C';
14.   w[1]:='C + B = 2';
15.   s:='SELECT '+String.Join(', ',f)+ ' FROM Test WHERE '+String.Join(' AND ',w);
16.   Writeln(s);
17. end.

Returns:
SELECT A, B, C FROM Test WHERE A > C AND C + B = 2

If i have many SQL-Statements, i want to have access from outside, then i store them directly in the Database itself in its own Table

[rvk]

Don't store SQL-Statements in plain Textfiles, especially not as multiline (like your example).
<snip>
If i have many SQL-Statements, i want to have access from outside, then i store them directly in the Database itself in its own Table

What do you think is the problem with storing them in one textfiles on my own server (with backup on a webhost) and retrieving them on demand?

For me, storing them in a database on customer side is not convenient because I'm the one changing and adding those SQL commands when needed.
(BTW. The customer has also the option to add their own SQL, or adjust my retrieved SQL, which is stored in the database itself. This is for specific SQL which is only used by that customer.)

This has worked flawlessly for me for over 20 years (with over 100 customers and different installations).
Maintaining my own SQL statements (the shared ones by all customers) in the database of the customer itself would be a nightmare.
Now I can even change it while I have the customer on the telephone. Having them in a database remotely on customer side this would be much more problematic.

But I guess it depends on your use-case (which is why I have a combo of a textfile and database-stored).

[Zvoni]

Don't store SQL-Statements in plain Textfiles, especially not as multiline (like your example).
<snip>
If i have many SQL-Statements, i want to have access from outside, then i store them directly in the Database itself in its own Table

What do you think is the problem with storing them in one textfiles on my own server (with backup on a webhost) and retrieving them on demand?

For me, storing them in a database on customer side is not convenient because I'm the one changing and adding those SQL commands when needed.
(BTW. The customer has also the option to add their own SQL, or adjust my retrieved SQL, which is stored in the database itself. This is for specific SQL which is only used by that customer.)

This has worked flawlessly for me for over 20 years (with over 100 customers and different installations).
Maintaining my own SQL statements (the shared ones by all customers) in the database of the customer itself would be a nightmare.
Now I can even change it while I have the customer on the telephone. Having them in a database remotely on customer side this would be much more problematic.

But I guess it depends on your use-case (which is why I have a combo of a textfile and database-stored).

Each statement has its own unique numeric identifier in that table, so if i have to roll-out an update for statements themselves, it's a basic UPDATE SET WHERE.

I didn't say your approach is wrong, since it works for you.
It's more of a personal thing, that i just plainly dislike having such stuff locally in a plain textfile, where any noob can look inside and start playing with it.
Getting access to a Database-Server is a whole other thing, nevermind in such a case you can restrict read-access to the Statements-Table to an Application-User nobody except DBA's get the password for

[rvk]

It's more of a personal thing, that i just plainly dislike having such stuff locally in a plain textfile, where any noob can look inside and start playing with it.
Getting access to a Database-Server is a whole other thing, nevermind in such a case you can restrict read-access to the Statements-Table to an Application-User nobody except DBA's get the password for

Ha, Ok. For me the plain textfile is on my side, not on the customers side. It's something I can change easily and the program retrieves it over internet and uses it.
For the locally changed SQL the statements are of course stored in the database itself (otherwise you would have problems accessing it if the filesystem is restricted in a local network).
And the report generator is restricted per user and only SELECT is possible (with some additional checking for standard hacking practices ). There is even some table checking but normally you would want to restrict a report generator, the same way you would want to restrict direct database access via filesystem (unless you don't care about that and want an 'open' system).

But yes, it does shows that you need to think about more than just plumping some text in a file and executing it

Someone doing a DROP TABLE X could really mess up your system 

[Zvoni]

Someone doing a DROP TABLE X could really mess up your system 

Which is the worst case!
It's more like users fiddling with the statement, and simply forgeting a comma, having a typo or whatever

[Hansvb]

Hi,

I don't know why you want to include queries, but maybe this is an idea. I once wrote a tool that edits and executes queries. The queries are linked to a database connection, so when you make a connection, you only see the queries that belong to the opened connection. The queries are placed in a tree view and double-clicking on a node starts the query. You can check multiple queries (nodes) and all checked queries will be executed.
The management of the queries is also included in the tool. As you can see in the print screen, 1 query can actually contain multiple queries. The storage is a SQLite database file. The queries are stored encrypted so that no one can accidentally change them outside the tool.
I made this a few years ago with C#, but you can also make this with Pascal. I work a lot with Oracle spatial data. That won't work with the standard Lazarus components.
Maybe it's something you can use as an idea.

[Чебурашка]

IIRC

Code: Pascal  [Select][+][-]

1. SQLQuery1.SQL
2.  

is a TStrings, and you can just make

Code: Pascal  [Select][+][-]

1. SQLQuery1.SQL.Add('select *');
2. SQLQuery1.SQL.Add('from Table');
3. SQLQuery1.SQL.Add('where');
4. SQLQuery1.SQL.Add('field = :param_field');
5. SQLQuery1.SQL.Add('and other_field = :param_other_field');
6.  

[Zvoni]

IIRC

Code: Pascal  [Select][+][-]

1. SQLQuery1.SQL
2.  

is a TStrings,

in that case you could just use LoadFromFile.
OTOH, it would only work for a single statement per File.
My objections above to such not withstanding

[Чебурашка]

I was just thinking about OP's original request to manage the multiline sql into source code.

I normally keep sql in code, even if I understand that somebody prefers to store in external files, because in turn, sql in code means loaded in memory while program runs and this is kinda senseless. I like using json for that or xml, having query name as key and sql text as content. That said I definitely do not split sql into separate lines, but this is just my personal way of seeing the world. All in one line (sometimes is very long!).