Simple but effective technique for working with SQL comment blocks

Developers of systems using databases have to write a lot in SQL. Everyone knows, but not everyone realizes that SQL has been experiencing for the fourth decade as one of the most successful and widely used technologies in the world of computers. Technology does not stand still, but even today, many creators of post-relational database systems specifically invest funds and resources to provide users with SQL-like search tools and data manipulation. Let's take a look at how modern requirements for database development products facilitate and speed up the creation of correct SQL code and get to know a curious little trick.

Recently I came across a simple and effective solution to one elementary, not even a problem, but the inconvenience I decided to share. The bottom line is this:

Like any practicing SQL developer or DBA, I save scripts to solve repetitive tasks so that in the future I already have a prepared tool for quick execution. Using DBArtisan, you can automatically record all SQL statements that I executed during the session and then use some of them to create and save such scripts. In the DBArtisan environment, I can put items on the main menu to invoke the most frequently used scripts or run the script on several servers simultaneously.
')
Naturally, many of these repetitive tasks require different specialized “pieces of code”, depending on the task or database being solved. It turned out that it is often quicker and easier to support not to create many similar "almost" identical SQL scripts or versions, but to use "block comments" to temporarily disable / enable the necessary SQL fragment and manually manage them in the ISQL editor.

Everyone knows that the SQL standard provides 2 types of comments in the source code:

• "Lower case", which starts with the characters '-', and turns everything to the end of this line into text that the SQL translator does not perceive. When moving to the next line, this comment is no longer valid.
• "Block", which can be converted into a comment large block of SQL code. They begin with the combination of the characters '/ *' and end as soon as the combination '* /' is encountered. These symbols indicate the beginning of the block and the end of the block.

The trick, by the example of which I will talk about the capabilities of modern ISQL editors, was suggested by Peter Zerk , one of the well-known MS SQL Server experts. Take the SQL text with the commented out chunk:

while (@j < @nstr) begin --     INSERT INTO PRR.TRANSITADVOLS ( VAL_ID,VAL_MASH ) VALUES /*        ,     , ... ...    ""       --*/ (@USERNM,@BDATE); 

It would seem that nothing has changed, but the placement of the “end of the block” in the “lower-level” comments gives us the opportunity to activate this whole block (when we decide that we need to execute it) not to search and delete both the beginning and the end of the block, and just put the characters '-' in front of the “block start” combination

 /*     --*/ --/*     --*/ 

If we did not put the “end of the block” inside the line comment, the script would be terminated, and we would get the error Incorrect syntax near '*'

One can only wonder why my colleagues and I, in due time, passed by this decision.

Now we can localize similar, but specific pieces of code in one place, manage our code.

 --       /* --SCRIPT 'A' -     .    mm/dd/yyyy ... ..         mm/dd/yyyy ... --*/ /* -- SCRIPT 'B' -     .    dd/mm/yyyy ... ..         dd/mm/yyyy ... --*/ 

Suppose I need to process input data with dates in the “American” format - 'mm / dd / yyyy'. I insert the symbols - before the first group “the beginning of the block” and the execution of the block SCRIPT A is turned on. At the same time - SCRIPT B remains off. If the data came in with the date in “our” format - dd / mm / yyyy - then I do the opposite, put it - before the second “start of the block”, and the SCRIPT B is turned on

Tracing

Using the same trick, T-SQL allows us to use another useful “side effect”.

 --    PRINT,     --/* --SCRIPT 'A' -       Print 'Script A active - integer data' --*/ Print 'Script A inactive /* -- SCRIPT 'B' -      Print 'Script B active - char data' --*/ Print 'Script B inactive' 

A trace will appear in the message window:



And all this is achieved by commenting on a single line of source text.

Debugging

Have you ever figured out why a query with a complex set of selection conditions does not work as expected?

Now it is easier to do this by alternately testing the performance of all or part of these conditions.

 SELECT * FROM #table /* --       WHERE ID in ( SELECT something FROM a-really-complex-condition WHERE we-are-not-sure-what-we-get ) --*/ WHERE ID = 1 --        

Or control the depth of data modification when executing the script.

 update #table set Comment = 'changed' where Comment = 'change me' /* -- use 'line comment' to enable UPDATE to proceed and 1 = 1 --*/ and 1 = 0 

This example allows me to debug the whole script without actually updating it until I check that everything works as it should.

And the last.

Dear author gave an example of using this trick to support nested blocks of one another.

 SELECT * FROM Mytable WHERE 1 = 1 -- always true /* -- first condition to check AND condition1 = TRUE --*/ /* -- some more conditions AND condition2 = ( SELECT condition3 FROM Myanothertable WHERE 1 = 1 /* -- Paying attention? This is a nested Comment Block AND NestedCondition = TRUE --*/ ) --*/ --/* the comment here 'activates' this condition. It gets checked. AND Comment = 'buggy value' --*/ 

You can see for yourself, it really works. In any ISQL editor, for example, run DBArtisan, and copy this text there.

SQL is a universal, if you can say so, standardized language, which, however, is transformed in each DBMS in accordance with the requirements imposed by the creators of this platform. In MS SQL, this is the T-SQL dialect, in Oracle we use PL / SQL, in other platforms we introduce our own unique features of syntax and behavior. Here is how it looks for MS SQL:



I decided to apply the trick I liked to work with Oracle. You can additionally run the tools that come with Oracle, but you can stay at DBArtisan, which supports simultaneous work with different DBMS platforms.

In DBArtisan, I opened another window of the SQL editor, indicated that this editor was already connected to the Oracle database and transferred the script from the example above to it:



I just inserted the text of the request. See the red mark on line 13?
This DBArtisan automatic syntax analyzer signals that SQL text does not conform to the rules of a given SQL dialect. A check on the Oracle site confirmed that, despite the graphical syntax schemes in the documentation, Oracle did not support nested comments and did not support them. By the way, the rest of the examples worked!

The automatic parser / validator is one of the three components that were included in the SQL editor DBArtisan, in accordance with modern requirements to accelerate the development of SQL programs and improve their quality. It also detects instances of using object names that are absent in the database, right as you type.

It also helps a lot when developing SQL programs, greatly speeds up and avoids the simplest mistakes in writing the Code Completion mode. If you had to write in modern programming languages ​​in rapid application development environments, for example, Delphi or C ++ Builder, Java Designer or Eclipse, you are familiar with it: as you type, the editor himself selects the appropriate context objects (not just syntax elements ) and substitutes them in the text for you or offers to make a choice from the list. DBArtisan selects objects from the current context based on the DBMS editor platform, from the list of actually existing schema objects of the used data source.

If, when writing complex SQL code, it suddenly turns out that you need to make some changes to other schema objects, for example, add an input parameter to the stored procedure, you can simply go to editing it (in a specialized dialog editor), selecting its name directly in text on SQL and calling the corresponding Hyperlink Object Action with the mouse.



Like any text, SQL program scripts can be created in any text editor. But if you are a professional, you work a lot and often with SQL, then syntax highlighting and automatic reformatting of the code will no longer be enough, especially if you have to switch between different DBMS platforms.

Moreover, more or less successful attempts are made to make server-side technologies for developing information systems more flexible, more adaptable for making changes and implementing new requirements, taking more advantage of the collective development and the repeated use of successful and proven solutions. For quite a long time, many DBMS and solutions for developers have the opportunity to apply the "versioning" of SQL texts. About this - in the following articles.