Error on parsing T-SQL using TSql120Parser

Question

Hello stackoverflowers,

I've got a problem parsing T-SQL statements using TSql120Parser from Microsoft.SqlServer.TransactSql.ScriptDom. My goal is to simply parse a T-SQL select statement and get it's tokens.

This is the essential part of the problem:

using (var reader = new StringReader(query))
    {
        IList<ParseError> errors;
        var parser = new TSql120Parser(true);
        var fragment = parser.Parse(reader, out errors);

        parameters.AddRange(
            fragment.ScriptTokenStream
                .Where(token => token.TokenType == TSqlTokenType.Variable)
                .Select(token => token.Text)
                .ToList());
    }

The variable query contains this statement:

SELECT A.ColumnX, B.ColumnY FROM TableA AS A INNER JOIN TableB AS B ON A.Id = B.IdA

Now I always get the following error (antlr.MismatchedTokenException.TokenTypeEnum.TokenType):

expecting "Dot", found ',' at column 17

Since I guess the statment is correct (I tested several other statements that work in SQL-Server, but are not parsed by the TSql120Parser), I've no idea what the problem could be.

I'm not forced to use this parser, but I thought this would be the most simple and robust solution. I would appreciate any help to find a solution!

Best regards, Sören

EDIT

With the help of Alex K. I found that it works in a clean console application but not in my WPF project. It uses the same Nuget package and .Net 4.6. Strange enough, it parses something like

SELECT @column FROM @table

but not

SELECT Column FROM Table

SOLUTION

TSql100Parser.Parse didn't work in project but TSql100Parser.GetTokenStream does. I don't know the reason and I can't reproduce the error in another project.

Thanks to Alex K. and Matthew Vines for your help!

Primary

Aside from whatever parameters is it works for me: dotnetfiddle.net/4ZEDW8 make sure you have the latest nuget package. – Saddle

Ok, thanks! I tested your code in a fresh console application and it works fine. In my current (WPF) project it's not working. I checked for the latest package, fine. It's both .Net 4.6, fine. But the error is the same. Any idea what else to check in my project? – Primary

did you try re-adding it via the nuget console? – Saddle

Tried unistall+install and update -reinstall. Still the same error. Could there be a problem with having the code in a class library? – Primary

I don't know what to suggest, try the working console code in a new empty WPF project? If that works there is something up with the other WPF solution. – Saddle

Yes, that's the only possibility I see. I will try that tomorrow and report the result. Thanks for your help! – Primary

Let us continue this discussion in chat. – Primary

Answer 1

I just went through a similar problem myself. Try something more like this. but note that the query you provided has no variables. This query returns a list of '@0' and '@1'

SELECT product_id, AVG(sale_price)
FROM Sales
WHERE sale_date > @0
    AND ([sales].system_id = 450)
GROUP BY product_id
Having AVG(sale_price) > @1


public List<string> GetVariables(string sql)
    {
        List<string> parseErrors;
        List<TSqlParserToken> queryTokens = TokenizeSql(sql, out parseErrors);

        List<string> parameters = new List<string>();
        parameters.AddRange(queryTokens.Where(token => token.TokenType == TSqlTokenType.Variable)
                                        .Select(token => token.Text)
                                        .ToList());
        return parameters;
    }

    private List<TSqlParserToken> TokenizeSql(string sql, out List<string> parserErrors)
    {
        using (System.IO.TextReader tReader = new System.IO.StringReader(sql))
        {
            var parser = new TSql120Parser(true);

            IList<ParseError> errors;
            var queryTokens = parser.GetTokenStream(tReader, out errors);
            if (errors.Any())
            {
                parserErrors = errors.Select(e => $"Error: {e.Number}; Line: {e.Line}; Column: {e.Column}; Offset: {e.Offset};  Message: {e.Message};").ToList();
            }
            else
            {
                parserErrors = null;
            }
            return queryTokens.ToList();
        }
    }

Impuissant

Thanks for your answer. The problem isn't in connection with variables. I cannot even parse something like SELECT columnA FROM tableB. – Primary

I was able to run your original query through these methods, It just returned an empty list. – Impuissant

As I wrote, for me it also works in a clean application but not in my existing WPF project. – Primary

Now I noticed that you use parser.GetTokenStream instead of parser.Parse. Surprisingly this works in my project. Thanks very much! – Primary