go-sqlcmd: Parsing error with database name

Hello,

Thank you for addressing the issue with the __ variable names yesterday. Our script generated from SQL Server Data Tools is now parsing further with go-sqlcmd, but not completely yet. There appears to be another gap (and one oddity that I need to think about before complaining about it).

The script contains a section similar to this fairly boiled-down repro:

GO
:setvar DatabaseName "MyTestDatabaseGitHub20230120"

GO

IF (DB_ID(N'$(DatabaseName)') IS NOT NULL)
BEGIN
    ALTER DATABASE [$(DatabaseName)]
    SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE [$(DatabaseName)];
END
GO

CREATE DATABASE [$(DatabaseName)]

GO
USE [$(DatabaseName)]
GO

When I save the above script to a .sql file and run it with go-sqlcmd, using ./sqlcmd -b -i testdb.sql -U SA, this script returns the following error:

Msg 105, Level 15, State 1, Server c6c5144e6044, Line 2
Unclosed quotation mark after the character string ') IS NOT NULL)
BEGIN
    ALTER DATABASE [MyTestDatabaseGitHub20230120]
    SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE [MyTestDatabaseGitHub20230120];
END'.

When run with legacy sqlcmd using the same options, the output looks like the below, which is the desired output, and the database is dropped and created. (note the very first time you run it, it won’t output the first four lines because it does not enter the if block):

2023-01-20 16:56:50.44 spid52      Setting database option SINGLE_USER to ON for database 'MyTestDatabaseGitHub20230120'.
2023-01-20 16:56:50.82 spid52      Starting up database 'MyTestDatabaseGitHub20230120'.
2023-01-20 16:56:50.87 spid52      Parallel redo is started for database 'MyTestDatabaseGitHub20230120' with worker pool size [1].
2023-01-20 16:56:50.90 spid52      Parallel redo is shutdown for database 'MyTestDatabaseGitHub20230120' with worker pool size [1].
Changed database context to 'MyTestDatabaseGitHub20230120'.

Please let me know if I can provide any additional information. I believe this is some sort of parsing/replacement bug but my Go skills are insufficient to create a failing unit test.

About this issue

  • Original URL
  • State: closed
  • Created a year ago
  • Comments: 18

Most upvoted comments

This issue is a symptom of several bugs in batch parsing, mostly related to handling of line feed insertions. The ODBC sqlcmd treats file input somewhat differently than console input and so should we. These are the rules as I understand them from experimenting with ODBC sqlcmd:

  1. If there’s no line feed on the last line of the file, don’t send one.
  2. Always send a line feed after each typed line in interactive mode.,
  3. Ignore blank lines even if they are inside a quote

eg:

E:\git>sqlcmd
1> select '200
2~
3~
4~
5~ '
6> go

-----------
200

Notice the result string has no carriage returns in it.

This input file:

select 'line1

line2
'

/* line 1 comment

line 2 comment

*/

Produces this batch on the server:

select 'line1
line2
'
/* line 1 comment
line 2 comment
*/

The buggy code impacts variables because it inserts a line feed in the buffer before doing the math to calculate the offsets of the variable in the buffer.