Is it possible to recreate the raw SQL from the tokens?

[ghost]

Hi,

Is it possible to reconstruct the raw SQL from the tokens?

I want to take a SELECT query and replace the field list with a COUNT(*) field and then reconstruct the SQL so I can execute it against the DB, is this possible?

Regards,
Andy

[bruce-dunwiddie]

The intention would be yes.

Doing your replacement will be challenging on more complex SELECT statements using only TSQLToken's from TSQLTokenizer however, trying to keep track of if you're in the column list or not, especially considering CTE's.

The simplest way, if you still have the original SQL as a string, would be to use TSQLStatementReader to parse a TSQLSelectStatement. Then, you can look at the SELECT property, and its Tokens property, which would contain your column list, after a few keyword tokens like SELECT, TOP, DISTINCT. You might choose to drop some of those also. But once you find the first token that you want to start dropping, you can get it's BeginPosition , and replace through the SELECT property's EndPosition , with " COUNT(*) ".

There may be SELECT statements where the parsing is not yet perfect, which you can report back here as bugs, but the intention would be that it parses the column list correctly.

Piecing back SQL from a list of TSQLToken's is a different challenge, and doesn't seem like it would solve your problem.

Bruce Dunwiddie

[ghost]

Hi @bruce-dunwiddie

Thank you for your response, I came to the same conclusion after looking at the TSQLTokenizer output! I thought I would post the results of my challenge here so that any flaws in my approach can be identified by other devs and it also might help someone else trying to do something similar in the future.

My use case is that I'm creating a SQL statement to use as a data source for a JSON configured bulk import process. Everything was fine when my SQL was SELECT * FROM tblDataTable; however I then realized that on occasions I needed more complex SQL that contained specific columns and joins then I couldn't easily swap the SQL to SELECT COUNT(*) FROM tblDataTable;. The only reason I need this capability is so that I know how many records are to be processed so I can monitor the progress of the bulk import process.

Here's a real example of an input query

SELECT
	CD.CompanyId,
	CS.Id, 
	[EnableSchedule], 
	[ChecklistDefinitionId], 
	[Title], 
	[EntityConfigurationMode], 
	[EntityRelationshipTypeId], 
	[AllowAdhoc], 
	[Deleted], 
	[EntityTypeFilter], 
	[Enabled], 
	[AutEnabled], 
	[AutUseEntityHierarchy], 
	[AutEntityRelationshipTypeId], 
	[Reference]
FROM tblChecklistService AS CS
INNER JOIN tblChecklistDefinition AS CD ON Cs.ChecklistDefinitionId=CD.Id
ORDER BY
	CS.Id;

The above query needs to be transformed into

SELECT
    COUNT(*)
FROM tblChecklistService AS CS
INNER JOIN tblChecklistDefinition AS CD ON Cs.ChecklistDefinitionId=CD.Id
ORDER BY
	CS.Id;

The following code seems to be working in my specific use case: -

        private static string ConvertSqlToCount(string sql)
        {
            var statements = TSQLStatementReader.ParseStatements(sql);

            if (!statements.Any())
            {
                throw new Exception($"Invalid SQL statement {sql} when converting SQL to get row count!");
            }


            if (statements[0] is not TSQLSelectStatement select)
            {
                throw new Exception($"Invalid SQL statement {sql} when converting SQL to get row count!");
            }

            StringBuilder countSql = new();

            countSql.Append($"SELECT COUNT(*)");

            foreach (var token in select.From.Tokens)
            {
                var space = IsSpaceRequired(token);
                countSql.Append(space + token.Text + space);
            }

            if (select.GroupBy != null)
            {
                foreach (var token in select.GroupBy.Tokens)
                {
                    var space = IsSpaceRequired(token);
                    countSql.Append(space + token.Text);
                }
            }

            if (select.Having != null)
            {
                foreach (var token in select.Having.Tokens)
                {
                    var space = IsSpaceRequired(token);
                    countSql.Append(space + token.Text + space);
                }
            }

            return countSql.ToString().Replace("  ", " ");
        }

        private static string IsSpaceRequired(TSQL.Tokens.TSQLToken token)
        {
            if (token.Type == TSQLTokenType.Keyword)
            {
                return " ";
            }

            return string.Empty;
        }