Sidebar

What is the most common SQL comment syntax?

0 votes
151 views
asked Apr 27, 2021 by rich-c-2789 (15,250 points)

I have several database connections in QIE.  I just noticed an H2 query with a comment that uses '//'.  I tried using a comment with '//' for a postgresql database but it didn't work.  I noticed that both H2 and postgresql support comments using '--'.  Then I tried this query on MySQL and it failed:

--Test comment on MySql 
Select 1

Is there a common syntax for comments?

1 Answer

0 votes

Yes.  Sort of.  The two most common forms across database vendors are:
            -- for single line comments
            /* for multi line
            comments */

However, there are a few things to know to avoid running into problems due to variations in implementation.

For single line comment '--' some database vendors like MySql require a space after the -- as the next valid character in the comment.

        --Bad without a space
        -- Good because it has a space

For multi line comment '/*...*/' some database vendors do not allow comments to be nested.

        Bad example with nested comments:
        /* outer /* inner nested comment */ comment */

        Good example without nesting comments:
        /* one comment */ /* another comment */

Another thing to check for is variations in JDBC drivers if you expect the comments to be sent to the database.  The jdbc drivers I have used do not strip out the comments but some might.

Also see this question for an example of using comments in SQL queries within a QIE mapping function:

Can I include some sort of debug info in an SQL query?

 

Resources:

H2 http://www.h2database.com/html/grammar.html#comments
MySql https://dev.mysql.com/doc/refman/8.0/en/comments.html
MariaDb https://mariadb.com/kb/en/comment-syntax/
MSSQL

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/comment-transact-sql?view=sql-server-ver15

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/slash-star-comment-transact-sql?view=sql-server-ver15

Oracle https://docs.oracle.com/cd/B12037_01/server.101/b10759/sql_elements006.htm
IBM Informix https://www.ibm.com/docs/en/informix-servers/14.10/14.10?topic=syntax-how-enter-sql-comments
PostgreSql https://www.postgresql.org/docs/8.0/sql-syntax.html#SQL-SYNTAX-COMMENTS
answered Apr 27, 2021 by rich-c-2789 (15,250 points)
edited Jul 16, 2021 by rich-c-2789
...