is there a way to put comments in SnowSQL files that do not cause errors?

Question

I'm using files with snowsql to automate certain processes. For maintenance, I want to include comments in the file (using // at the start of the line) to explain key steps. However, when I do this snowsql reports an error: 000900 (42601): SQL compilation error: Empty SQL statement.

for example:

select 'hello world';
// and now exit the session
!exit

will cause the error:

$ snowsql --filename comments.sql
* SnowSQL * v1.2.5approval |                     
Type SQL statements or !help                     
+---------------+                                
| 'HELLO WORLD' |                                
|---------------|                                
| hello world   |                                
+---------------+                                
1 Row(s) produced. Time Elapsed: 0.209s          
000900 (42601): SQL compilation error:           
Empty SQL statement.                             
Goodbye!

If I remove the comments and leave empty lines instead:

select 'hello world';

!exit

Then it works with no errors reported

$ snowsql --filename no-comments.sql
* SnowSQL * v1.2.5approval |
Type SQL statements or !help
+---------------+
| 'HELLO WORLD' |
|---------------|
| hello world   |
+---------------+
1 Row(s) produced. Time Elapsed: 1.088s

Goodbye!

This occurs with snowsql version 1.2.5

Is there a way to include comments in sql file that does not cause errors in snowsql?

score 0 · Answer 1 · answered Mar 26 '20 at 17:24

It looks like the issue is that it's treating the comment text as a sql statement. Even though it's correctly finds that it's just a comment, it's trying to execute and doesn't find an actual script. Hence "Empty SQL statement."

As a workaround You should just be able to put the comment prior to the semicolon so that it realizes that there's only one script to run.

You could also put in a dummy select 1 or something similar.

select 'hello world'
// and now exit the session
;

!exit

Greg Pavlik · Accepted Answer · 2020-03-26T18:29:18.640

0

You can use the standard SQL comment markers, double dashes. I tested it and it works:

select 'hello world';
-- and now exit the session
!exit

I think that if it works in the web UI it should work the same way in SnowSQL, so I'll open a ticket to check on this.

edited Mar 26 '20 at 18:29

answered Mar 26 '20 at 18:22

Greg Pavlik

10,089
2
12
29

Any idea how to make this work? !define test_var='whatever'; -- my comment. I keep getting value doesn't end with a quote character error – Radagast Jun 17 '21 at 00:19

score 0 · Answer 3 · answered Apr 21 '20 at 15:38

Not sure there's a problem ... though the !exit might be a problem. First, another example:

My test with comments.sql:

select 'hello Grovers Corner';

-- this is a double dash comment

select 'hello Indiana'
-- dashes in middle of sql statement
from information_schema.tables
fetch 1 row only;

select 'hello United States'
// slashes in middle of sql statement
from information_schema.tables
fetch 1 row only;

// some final slashes on the last line

... note there is no !exit at the end of the script

... and the snowsql execution with results:

$ snowsql -f comments.sql -o echo=True
* SnowSQL * v1.1.86
Type SQL statements or !help
select 'hello Grovers Corner';
+------------------------+
| 'HELLO GROVERS CORNER' |
|------------------------|
| hello Grovers Corner   |
+------------------------+
1 Row(s) produced. Time Elapsed: 0.085s
-- this is a double dash comment

select 'hello Indiana'
-- dashes in middle of sql statement
from information_schema.tables
fetch 1 row only;
+-----------------+
| 'HELLO INDIANA' |
|-----------------|
| hello Indiana   |
+-----------------+
1 Row(s) produced. Time Elapsed: 1.803s
select 'hello United States'
// slashes in middle of sql statement
from information_schema.tables
fetch 1 row only;
+-----------------------+
| 'HELLO UNITED STATES' |
|-----------------------|
| hello United States   |
+-----------------------+
1 Row(s) produced. Time Elapsed: 1.748s
// some final slashes on the last line
000900 (42601): SQL compilation error:
Empty SQL statement.
Goodbye!
$

So, the double slashes did not impact the sql statement, but they are confusing the end of the script!

Finally, I did replace the last line double-slashes with double-dashes and there was no SQL compilation error. And same results without the echo=True option on the command line execution step.

I think this is related to my own question about the apparent inability to NOT exit a script when called via the snowsql command line. (See SO question login.sql and NOT exit)

is there a way to put comments in SnowSQL files that do not cause errors?

3 Answers3