Best practice to store long SQL files with you project lazarus / 16-Databases ---------------------------------------------------------------------- Okoba · 2023-09-22 12:46 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i '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 · 2023-09-22 13:10 Quote from: Okoba on September 22, 2023, 02:46:39 pmIs 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i 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  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i 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 · 2023-09-22 14:39 Quote from: rvkYou 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]---      This is very convenient if I use very long queries, texts, etc.      Sample in attachment. ---------------------------------------------------------------------- Okoba · 2023-09-22 14:51 @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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i {$Import test.sql} ---------------------------------------------------------------------- rvk · 2023-09-22 15:10 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i ===================================================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 docd ate>=: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. ---------------------------------------------------------------------- Okoba · 2023-09-22 15:31 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 · 2023-09-25 07:01 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i program Project1;Uses SysUtils; Var  f:Array[0..2] Of String;  w:Array[0..1] Of String;  s:String; begin  f[0]:='A';  f[1]:='B';  f[2]:='C';  w[0]:='A > C';  w[1]:='C + B = 2';  s:='SELECT '+String.Join(', ',f)+ ' FROM Test WHERE '+String.Join(' AND ',w);  Writeln(s);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 · 2023-09-25 07:14 Quote from: Zvoni on September 25, 2023, 09:01:33 amDon't store SQL- Statements in plain Textfiles, especially not as multiline (like your example). 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 · 2023-09-25 07:23 Quote from: rvk on September 25, 2023, 09:14:47 amQuote from: Zvoni on September 25, 2023, 09:01:33 amDon't store SQL-Statements in plain Textfiles, especially not as multiline (like your example). 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 · 2023-09-25 08:26 Quote from: Zvoni on September 25, 2023, 09:23:17 amIt'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 forHa, 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 · 2023-09-25 08:34 Quote from: rvk on September 25, 2023, 10:26:53 amSomeone 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 · 2023-09-25 08:44 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. ---------------------------------------------------------------------- Чебурашка · 2023-09-25 09:08 IIRC Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SQLQuery1.SQL  is a TStrings, and you can just make Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SQLQuery1.SQL.Add('select *');SQLQuery1.SQL.Add('from Table');SQLQuery1.SQL.Add('where');SQLQuery1.SQL.Add('field = :param_field');SQLQuery1.SQL.Add('and other_field = :param_other_field'); ---------------------------------------------------------------------- Zvoni · 2023-09-25 09:22 Quote from: Чебурашка on September 25, 2023, 11:08:09 amIIRC Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i SQLQuery1.SQL  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 ---------------------------------------------------------------------- Чебурашка · 2023-09-25 09:44 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!). ---------------------------------------------------------------------- Zvoni · 2023-09-25 10:30 Quote from: Чебурашка on September 25, 2023, 11:44:02 amI 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.With the downside, that if you HAVE to change your SQL-Statement (for whatever reason), you have to recompile/redistribute Quote 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!). Yep, for me too: Once a Statement is written, it ends up in one single line, because that's what it is in the end ---------------------------------------------------------------------- Чебурашка · 2023-09-25 11:48 Quote from: Zvoni on September 25, 2023, 12:30:28 pmIf you HAVE to change your SQL-Statement (for whatever reason), you have to recompile/redistribute True 100%, although seldomly sql changes and code not. ---------------------------------------------------------------------- Okoba · 2023-09-25 15:32 @Hansvb I am sure you and many other needed to have SQL queries and especially big ones in your code. And having an ugly SQL-To-Pascal output like my first post, is not very cool. ---------------------------------------------------------------------- incendio · 2023-10-25 03:31 For me, use a stored procedure for long sql queries.