public void SelectStatement_SelectLiteral()
{
List <TSQLStatement> statements = TSQLStatementReader.ParseStatements(
"select 1;",
includeWhitespace: true);
TSQLSelectStatement select = statements[0] as TSQLSelectStatement;
Assert.IsNotNull(statements);
Assert.AreEqual(1, statements.Count);
Assert.AreEqual(TSQLStatementType.Select, statements[0].Type);
Assert.AreEqual(3, select.Tokens.Count);
Assert.AreEqual(TSQLKeywords.SELECT, select.Tokens[0].AsKeyword.Keyword);
Assert.AreEqual(" ", select.Tokens[1].AsWhitespace.Text);
Assert.AreEqual("1", select.Tokens[2].AsNumericLiteral.Text);
}
public void SelectStatement_MultipleSelectsWithoutSemicolon()
{
List <TSQLStatement> statements = TSQLStatementReader.ParseStatements(
"select 1 select 1",
includeWhitespace: true);
TSQLSelectStatement select1 = statements[0] as TSQLSelectStatement;
TSQLSelectStatement select2 = statements[1] as TSQLSelectStatement;
Assert.IsNotNull(statements);
Assert.AreEqual(2, statements.Count);
Assert.AreEqual(TSQLStatementType.Select, select1.Type);
Assert.AreEqual(TSQLStatementType.Select, select2.Type);
}
public void SelectStatement_CountAliasWithWhitespace()
{
List <TSQLStatement> statements = TSQLStatementReader.ParseStatements(
@"SELECT COUNT ( * ) as count FROM sqlite_master",
includeWhitespace: true);
TSQLSelectColumn count = statements
.Single()
.AsSelect
.Select
.Columns
.Single();
Assert.AreEqual("COUNT", count.Expression.AsFunction.Function.Name);
Assert.AreEqual("count", count.ColumnAlias.Name);
}
public void SelectStatement_CaseInJoin()
{
string query = @"SELECT a.*
FROM SomeTable a JOIN SomeTable b
ON CASE WHEN a.Value = 1 THEN 'One' ELSE 'Other' END = 'One'" ;
var statements = TSQLStatementReader.ParseStatements(query, includeWhitespace: false);
Assert.AreEqual(1, statements.Count);
TSQLSelectStatement select = statements[0] as TSQLSelectStatement;
Assert.AreEqual(4, select.Select.Tokens.Count);
Assert.AreEqual(21, select.From.Tokens.Count);
Assert.IsNull(select.Where);
}
public void MergeStatement_StandardMerge_WhenNotMatched()
{
List <TSQLStatement> statements = TSQLStatementReader.ParseStatements(
@"
MERGE INTO [t].[a] AS [Target]
USING (VALUES
(1,N'v1',NULL)
,(2,N'v2',NULL)
) AS [Source] ([ID],[Val])
ON ([Target].[ID] = [Source].[ID])
WHEN MATCHED AND (
NULLIF([Source].[Val], [Target].[Val]) IS NOT NULL OR NULLIF([Target].[Val], [Source].[Val]) IS NOT NULL) THEN
UPDATE SET
[Target].[Val] = [Source].[Val],
WHEN NOT MATCHED THEN
INSERT([ID],[Val])
VALUES([Source].[ID],[Source].[Val])
WHEN NOT MATCHED BY SOURCE THEN
DELETE
OUTPUT
$ACTION AS [Action]
,DELETED.[ID]
,DELETED.[Val]
,INSERTED.[ID]
,INSERTED.[Val];" ,
includeWhitespace: true);
TSQLMergeStatement merge = statements[0] as TSQLMergeStatement;
Assert.IsNotNull(statements);
Assert.AreEqual(1, statements.Count);
Assert.AreEqual(TSQLStatementType.Merge, statements[0].Type);
Assert.AreEqual(191, merge.Tokens.Count);
Assert.AreEqual(TSQLKeywords.MERGE, merge.Tokens[0].AsKeyword.Keyword);
Assert.AreEqual(" ", merge.Tokens[1].AsWhitespace.Text);
Assert.AreEqual(TSQLKeywords.INTO, merge.Tokens[2].AsKeyword.Keyword);
Assert.AreEqual(" ", merge.Tokens[3].AsWhitespace.Text);
Assert.AreEqual("t", merge.Tokens[4].AsIdentifier.Name);
Assert.AreEqual(2, merge.Merge.Tokens.Count);
Assert.AreEqual(10, merge.Into.Tokens.Count);
Assert.AreEqual(34, merge.Using.Tokens.Count);
Assert.AreEqual(14, merge.On.Tokens.Count);
Assert.AreEqual(3, merge.When.Count);
Assert.AreEqual(64, merge.When[0].Tokens.Count);
Assert.AreEqual(26, merge.When[1].Tokens.Count);
Assert.AreEqual(14, merge.When[2].Tokens.Count);
Assert.AreEqual(27, merge.Output.Tokens.Count);
}
public void SelectStatement_MultiLevelParens()
{
string query = "SELECT ((A/B)-1) FROM SomeTable";
List <TSQLStatement> statements = TSQLStatementReader.ParseStatements(query);
Assert.AreEqual(1, statements.Count);
Assert.AreEqual(TSQLStatementType.Select, statements[0].Type);
TSQLSelectStatement selectStatement = statements[0].AsSelect;
Assert.AreEqual(12, selectStatement.Tokens.Count);
TSQLSelectClause selectClause = selectStatement.Select;
Assert.AreEqual(1, selectClause.Columns.Count);
// outer parens
TSQLExpression lvl1Expression = selectClause.Columns[0].Expression;
Assert.AreEqual(TSQLExpressionType.Grouped, lvl1Expression.Type);
// contents of outer parens
TSQLExpression lvl2Expression = lvl1Expression.AsGrouped.InnerExpression;
Assert.AreEqual(TSQLExpressionType.Operator, lvl2Expression.Type);
Assert.AreEqual("-", lvl2Expression.AsOperator.Operator.Text);
// (A/B)
TSQLExpression lvl2aExpression = lvl2Expression.AsOperator.LeftSide;
// 1
TSQLExpression lvl2bExpression = lvl2Expression.AsOperator.RightSide;
Assert.AreEqual(TSQLExpressionType.Grouped, lvl2aExpression.Type);
Assert.AreEqual(TSQLExpressionType.Constant, lvl2bExpression.Type);
Assert.AreEqual(1, lvl2bExpression.AsConstant.Literal.AsNumericLiteral.Value);
// A/B
TSQLExpression lvl3Expression = lvl2aExpression.AsGrouped.InnerExpression;
Assert.AreEqual(TSQLExpressionType.Operator, lvl3Expression.Type);
Assert.AreEqual("/", lvl3Expression.AsOperator.Operator.Text);
// A
TSQLExpression lvl3aExpression = lvl3Expression.AsOperator.LeftSide;
// B
TSQLExpression lvl3bExpression = lvl3Expression.AsOperator.RightSide;
Assert.AreEqual(TSQLExpressionType.Column, lvl3aExpression.Type);
Assert.AreEqual("A", lvl3aExpression.AsColumn.Column.Name);
Assert.IsNull(lvl3aExpression.AsColumn.TableReference);
Assert.AreEqual(TSQLExpressionType.Column, lvl3bExpression.Type);
Assert.AreEqual("B", lvl3bExpression.AsColumn.Column.Name);
Assert.IsNull(lvl3bExpression.AsColumn.TableReference);
}
public void AdvancedRecordset_SelectStatementWithAllias_Join_ReturnOutputs()
{
//------------Setup for test--------------------------
string query = "SELECT * FROM person JOIN address on person.address_id = address.id";
var worker = CreatePersonAddressWorkers();
var statements = TSQLStatementReader.ParseStatements(query);
var updatedQuery = worker.UpdateSqlWithHashCodes(statements[0]);
var results = worker.ExecuteQuery(updatedQuery);
//------------Assert Results-------------------------
Assert.AreEqual("bob", Encoding.UTF8.GetString(results.Tables[0].Rows[0]["Name"] as byte[]));
Assert.AreEqual(21, int.Parse(Encoding.UTF8.GetString(results.Tables[0].Rows[0]["Age"] as byte[])));
Assert.AreEqual(1, int.Parse(Encoding.UTF8.GetString(results.Tables[0].Rows[0]["address_id"] as byte[])));
Assert.AreEqual("11 test lane", Encoding.UTF8.GetString(results.Tables[0].Rows[0]["Addr"] as byte[]));
Assert.AreEqual(3421, int.Parse(Encoding.UTF8.GetString(results.Tables[0].Rows[0]["Postcode"] as byte[])));
}
public void AdvancedRecordset_Converter_CanRunWhereQuery_ExpectFilteredResults()
{
string query = "select * from person p join address a on p.address_id=a.id where a.addr=\"11 test lane\" order by Name";
var worker = CreatePersonAddressWorkers();
var statements = TSQLStatementReader.ParseStatements(query);
var updatedQuery = worker.UpdateSqlWithHashCodes(statements[0]);
var results = worker.ExecuteQuery(updatedQuery);
Assert.AreEqual(Encoding.UTF8.GetString(results.Tables[0].Rows[0]["name"] as byte[]), "bob");
Assert.AreEqual(int.Parse(Encoding.UTF8.GetString(results.Tables[0].Rows[0]["age"] as byte[])), (Int32)21);
Assert.AreEqual(int.Parse(Encoding.UTF8.GetString(results.Tables[0].Rows[0]["address_id"] as byte[])), (Int32)1);
Assert.AreEqual(Encoding.UTF8.GetString(results.Tables[0].Rows[0]["addr"] as byte[]), "11 test lane");
Assert.AreEqual(int.Parse(Encoding.UTF8.GetString(results.Tables[0].Rows[0]["postcode"] as byte[])), (Int32)3421);
Assert.AreEqual(Encoding.UTF8.GetString(results.Tables[0].Rows[1]["name"] as byte[]), "jef");
Assert.AreEqual(int.Parse(Encoding.UTF8.GetString(results.Tables[0].Rows[1]["age"] as byte[])), (Int32)24);
}
public void AdvancedRecordset_Converter_ExpectUpdateAffectedRows()
{
var worker = CreatePersonAddressWorkers();
string query = "update person set Age=65 where Name=\"zak\";";
var statements = TSQLStatementReader.ParseStatements(query);
var updatedQuery = worker.UpdateSqlWithHashCodes(statements[0]);
var results = worker.ExecuteNonQuery(updatedQuery);
Assert.AreEqual(1, results);
query = "select * from person where Name=\"zak\";";
statements = TSQLStatementReader.ParseStatements(query);
updatedQuery = worker.UpdateSqlWithHashCodes(statements[0]);
var result = worker.ExecuteQuery(updatedQuery);
Assert.AreEqual("zak", Encoding.UTF8.GetString(result.Tables[0].Rows[0]["Name"] as byte[]));
Assert.AreEqual(65, int.Parse(Encoding.UTF8.GetString(result.Tables[0].Rows[0]["Age"] as byte[])));
}
public void SelectStatement_DontEatFinalDescAsKeyword()
{
List <TSQLStatement> statements = TSQLStatementReader.ParseStatements(
@"select 1 as blah order by 1 desc select 1",
includeWhitespace: false);
Assert.AreEqual(2, statements.Count);
Assert.AreEqual(TSQLStatementType.Select, statements[0].Type);
Assert.AreEqual(TSQLStatementType.Select, statements[1].Type);
TSQLSelectStatement select1 = statements[0] as TSQLSelectStatement;
TSQLSelectStatement select2 = statements[1] as TSQLSelectStatement;
Assert.AreEqual(8, select1.Tokens.Count);
Assert.AreEqual(2, select2.Tokens.Count);
}
public void SelectStatement_CaseInSelect()
{
string query = "SELECT Value, CASE WHEN Value = 1 THEN 'One' ELSE 'Other' END AS Cased FROM SomeTable";
var statements = TSQLStatementReader.ParseStatements(query, includeWhitespace: false);
Assert.AreEqual(1, statements.Count);
TSQLSelectStatement select = statements[0] as TSQLSelectStatement;
Assert.AreEqual(15, select.Select.Tokens.Count);
Assert.AreEqual(2, select.From.Tokens.Count);
Assert.AreEqual("FROM SomeTable",
query.Substring(
select.From.BeginPosition,
select.From.Length));
}
/// <summary>
/// This method attempts to parse column names from any sql statement.
/// E.g. SELECT 1 AS 'Test', Col2, t2.Col3 FROM table1 t1 INNER JOIN t2 ON t1.Id = t2.Id
/// will return Test, Col2 and Col3 als column names.
/// </summary>
/// <param name="sql">The sql code from which the column names should be parsed</param>
/// <returns>The names of the columns in the sql</returns>
public static List <string> ParseColumnNames(string sql)
{
try
{
var statement = TSQLStatementReader.ParseStatements(sql).FirstOrDefault() as TSQLSelectStatement;
List <string> result = new List <string>();
int functionStartCount = 0;
string prevToken = string.Empty;
foreach (var token in statement.Select.Tokens)
{
if (token.Type == TSQL.Tokens.TSQLTokenType.Character &&
token.Text == "(")
{
functionStartCount++;
}
else if (token.Type == TSQL.Tokens.TSQLTokenType.Character &&
token.Text == ")")
{
functionStartCount--;
}
if (token.Type == TSQL.Tokens.TSQLTokenType.Identifier)
{
prevToken = token.Text;
}
if (token.Type == TSQL.Tokens.TSQLTokenType.Character &&
functionStartCount <= 0 &&
token.Text == ","
)
{
result.Add(prevToken);
}
}
if (prevToken != string.Empty)
{
result.Add(prevToken);
}
return(result);
}
catch (Exception e)
{
throw new ETLBoxException("The attempt to read the column names from the given sql statement failed. " +
"Please provide a TableDefinition with at least column name and preferably the data type. ");
}
}
public void SelectStatement_Option()
{
List <TSQLStatement> statements = TSQLStatementReader.ParseStatements(
@"SELECT *
FROM
Sales.SalesOrderHeader oh
OPTION (FAST 10)" ,
includeWhitespace: false);
Assert.AreEqual(1, statements.Count);
Assert.AreEqual(TSQLStatementType.Select, statements[0].Type);
TSQLSelectStatement select = statements[0] as TSQLSelectStatement;
Assert.AreEqual(12, select.Tokens.Count);
Assert.IsNotNull(select.Option);
Assert.AreEqual(5, select.Option.Tokens.Count);
}
public void OrderByClause_OffsetFetch()
{
// regression test for https://github.com/bruce-dunwiddie/tsql-parser/issues/75
List <TSQLStatement> statements = TSQLStatementReader.ParseStatements(
@"
SELECT *
FROM Product.Product P
ORDER BY P.ProductId
OFFSET (@page -1) * @RowPerPage ROWS
FETCH NEXT @RowPerPage ROWS ONLY" ,
includeWhitespace: false);
TSQLSelectStatement select = statements[0].AsSelect;
Assert.AreEqual(1, statements.Count);
Assert.AreEqual(26, select.Tokens.Count);
}
public void SelectStatement_TableHint()
{
List <TSQLStatement> statements = TSQLStatementReader.ParseStatements(
@"SELECT *
FROM
Sales.SalesOrderHeader oh WITH (NOLOCK)
ORDER BY
oh.OrderDate;" ,
includeWhitespace: false);
Assert.AreEqual(1, statements.Count);
Assert.AreEqual(TSQLStatementType.Select, statements[0].Type);
TSQLSelectStatement select = statements[0] as TSQLSelectStatement;
Assert.AreEqual(16, select.Tokens.Count);
Assert.AreEqual("OrderDate", select.Tokens[15].Text);
}
public void SelectStatement_DistinctAndTop()
{
List <TSQLStatement> statements = TSQLStatementReader.ParseStatements(
@"SELECT DISTINCT TOP(5) FirstName
FROM
Person.Person p;" ,
includeWhitespace: false);
Assert.AreEqual("FirstName", statements
.Single()
.AsSelect
.Select
.Columns
.Single()
.Expression
.AsColumn
.Column
.Name);
}
public void InsertStatement_DefaultValues()
{
string sql = @"
INSERT INTO T1 DEFAULT VALUES;" ;
List <TSQLStatement> statements = TSQLStatementReader.ParseStatements(
sql,
includeWhitespace: false);
TSQLInsertStatement insert = statements[0].AsInsert;
Assert.AreEqual(5, insert.Tokens.Count);
Assert.IsNull(insert.With);
Assert.IsNull(insert.Output);
Assert.IsNull(insert.Select);
Assert.IsNull(insert.Values);
Assert.IsNull(insert.Execute);
Assert.AreEqual(3, insert.Insert.Tokens.Count);
Assert.AreEqual(2, insert.Default.Tokens.Count);
}
public void SelectStatement_CorrelatedSelect()
{
List <TSQLStatement> statements = TSQLStatementReader.ParseStatements(
"select (select 1);",
includeWhitespace: true);
TSQLSelectStatement select = statements[0] as TSQLSelectStatement;
Assert.IsNotNull(statements);
Assert.AreEqual(1, statements.Count);
Assert.AreEqual(TSQLStatementType.Select, statements[0].Type);
Assert.AreEqual(7, statements[0].Tokens.Count);
Assert.AreEqual(TSQLKeywords.SELECT, select.Tokens[0].AsKeyword.Keyword);
Assert.AreEqual(" ", select.Tokens[1].AsWhitespace.Text);
Assert.AreEqual("(", select.Tokens[2].AsCharacter.Text);
Assert.AreEqual("select", select.Tokens[3].AsKeyword.Text);
Assert.AreEqual(" ", select.Tokens[4].AsWhitespace.Text);
Assert.AreEqual("1", select.Tokens[5].AsNumericLiteral.Text);
Assert.AreEqual(")", select.Tokens[6].AsCharacter.Text);
}
public void AdvancedRecordset_AddRecordsetAsTable_ExecuteStatement_Insert()
{
var l = new List <string>();
l.Add("name");
l.Add("age");
l.Add("address_id");
var advancedRecordset = new AdvancedRecordset();
advancedRecordset.AddRecordsetAsTable(("person", l));
const string query = "INSERT OR REPLACE INTO person VALUES (1,'testName', 10, 1);";
var statements = TSQLStatementReader.ParseStatements(query);
var results = advancedRecordset.ExecuteStatement(statements[0], query);
Assert.AreEqual(1, results.Tables[0].Rows[0]["records_affected"]);
}
public void AdvancedRecordset_AddRecordsetAsTable_ExecuteStatement_Select()
{
var l = new List <string>();
l.Add("name");
l.Add("age");
l.Add("address_id");
var advancedRecordset = new AdvancedRecordset();
advancedRecordset.AddRecordsetAsTable(("person", l));
var statements = TSQLStatementReader.ParseStatements("Select * from person");
var results = advancedRecordset.ExecuteStatement(statements[0], "Select * from person");
Assert.AreEqual("name", results.Tables[0].Columns[1].ColumnName);
Assert.AreEqual("age", results.Tables[0].Columns[2].ColumnName);
Assert.AreEqual("address_id", results.Tables[0].Columns[3].ColumnName);
}
public void InsertStatement_Exec()
{
string sql = @"
INSERT INTO dbo.EmployeeSales
EXECUTE dbo.uspGetEmployeeSales;" ;
List <TSQLStatement> statements = TSQLStatementReader.ParseStatements(
sql,
includeWhitespace: false);
TSQLInsertStatement insert = statements[0].AsInsert;
Assert.AreEqual(9, insert.Tokens.Count);
Assert.IsNull(insert.With);
Assert.IsNull(insert.Output);
Assert.IsNull(insert.Select);
Assert.IsNull(insert.Default);
Assert.IsNull(insert.Values);
Assert.AreEqual(5, insert.Insert.Tokens.Count);
Assert.AreEqual(4, insert.Execute.Tokens.Count);
}
public void SelectStatement_CaseInWhere()
{
string query = "SELECT Value FROM SomeTable WHERE (CASE WHEN Value = 1 THEN 'One' ELSE 'Other' END) = 'One'";
var statements = TSQLStatementReader.ParseStatements(query, includeWhitespace: false);
Assert.AreEqual(1, statements.Count);
TSQLSelectStatement select = statements[0] as TSQLSelectStatement;
Assert.AreEqual(2, select.Select.Tokens.Count);
Assert.AreEqual("Value", select.Select.Columns[0].Expression.AsColumn.Column.Name);
Assert.AreEqual(2, select.From.Tokens.Count);
Assert.AreEqual("FROM SomeTable",
query.Substring(
select.From.BeginPosition,
select.From.Length));
Assert.AreEqual(15, select.Where.Tokens.Count);
Assert.AreEqual(1, select.Select.Columns.Count);
}
public void InsertStatement_SimpleValues()
{
string sql = @"
INSERT INTO Production.UnitMeasure
VALUES (N'FT', N'Feet', '20080414');" ;
List <TSQLStatement> statements = TSQLStatementReader.ParseStatements(
sql,
includeWhitespace: false);
TSQLInsertStatement insert = statements[0].AsInsert;
Assert.AreEqual(13, insert.Tokens.Count);
Assert.IsNull(insert.With);
Assert.IsNull(insert.Output);
Assert.IsNull(insert.Select);
Assert.IsNull(insert.Default);
Assert.IsNull(insert.Execute);
Assert.AreEqual(5, insert.Insert.Tokens.Count);
Assert.AreEqual(8, insert.Values.Tokens.Count);
}
public void SetOperatorClause_UnionDontOverrun()
{
List <TSQLStatement> statements = TSQLStatementReader.ParseStatements(
@"
SELECT TOP 1 L1.ID
FROM
(SELECT 2 AS ID) L1
WHERE
L1.ID = 2
GROUP BY
L1.ID
HAVING COUNT(*) > 0
UNION ALL
SELECT TOP 1 L2.ID
FROM
(SELECT 1 AS ID) L2
WHERE
L2.ID = 1
GROUP BY
L2.ID
HAVING COUNT(*) > 0
-- the only table alias in scope is the first one
-- but all rows from result of UNION are referenced
ORDER BY L1.ID
OPTION (FAST 2)
FOR XML PATH;" ,
includeWhitespace: false);
Assert.AreEqual(1, statements.Count);
Assert.IsNotNull(statements[0].AsSelect.SetOperator);
Assert.AreEqual(36, statements[0].AsSelect.SetOperator.Tokens.Count);
Assert.IsNotNull(statements[0].AsSelect.SetOperator.Select);
Assert.AreEqual(34, statements[0].AsSelect.SetOperator.Select.Tokens.Count);
Assert.IsNull(statements[0].AsSelect.SetOperator.Select.OrderBy);
Assert.IsNotNull(statements[0].AsSelect.OrderBy);
}
public void DeleteStatement_FullFunctionality()
{
List <TSQLStatement> statements = TSQLStatementReader.ParseStatements(
@"
DELETE t
OUTPUT deleted.*
INTO #holder (ID, Column1, Column2)
FROM dbo.SomeTable t
WHERE
t.ID = 1
OPTION (RECOMPILE);" ,
includeWhitespace: false);
TSQLDeleteStatement delete = statements[0].AsDelete;
Assert.AreEqual(30, delete.Tokens.Count);
Assert.AreEqual(13, delete.Output.Tokens.Count);
Assert.AreEqual(9, delete.Output.Into.Tokens.Count);
Assert.AreEqual(5, delete.From.Tokens.Count);
Assert.AreEqual(6, delete.Where.Tokens.Count);
Assert.AreEqual(4, delete.Option.Tokens.Count);
}
public void SelectStatement_Dont_Overrun()
{
string sql = @"
BEGIN
SELECT 1
END" ;
List <TSQLStatement> statements = TSQLStatementReader.ParseStatements(
sql,
includeWhitespace: false);
Assert.AreEqual(3, statements.Count);
Assert.AreEqual(1, statements[0].Tokens.Count);
Assert.AreEqual(2, statements[1].Tokens.Count);
Assert.AreEqual("SELECT 1",
sql.Substring(
statements[1].BeginPosition,
statements[1].Length));
Assert.AreEqual(1, statements[2].Tokens.Count);
Assert.IsTrue(statements[2].Tokens[0].IsKeyword(TSQLKeywords.END));
}
public void InsertStatement_ValuesWithColumns()
{
string sql = @"
INSERT INTO Production.UnitMeasure
(Name, UnitMeasureCode, ModifiedDate)
VALUES (N'Square Yards', N'Y2', GETDATE());" ;
List <TSQLStatement> statements = TSQLStatementReader.ParseStatements(
sql,
includeWhitespace: false);
TSQLInsertStatement insert = statements[0].AsInsert;
Assert.AreEqual(22, insert.Tokens.Count);
Assert.IsNull(insert.With);
Assert.IsNull(insert.Output);
Assert.IsNull(insert.Select);
Assert.IsNull(insert.Default);
Assert.IsNull(insert.Execute);
Assert.AreEqual(12, insert.Insert.Tokens.Count);
Assert.AreEqual(10, insert.Values.Tokens.Count);
}
public void SqliteServer_ParseTSQL_UnknownStatement_MissingFirstToken()
{
//------------Setup for test--------------------------
try
{
//------------Execute Test---------------------------
List <TSQLStatement> statements = TSQLStatementReader.ParseStatements(
@"DECLARE @Location VARCHAR(4)
DECLARE @ReportDate DATETIME
SET @Location = '1010'
SET @ReportDate = '01/09/18'" ,
includeWhitespace: false);
Assert.IsTrue(statements[0].Tokens[0].IsKeyword(TSQLKeywords.DECLARE));
//------------Assert Results-------------------------
}
finally
{
}
}
public void WithStatement_SelectInParensWithWhitespace()
{
List <TSQLStatement> statements = TSQLStatementReader.ParseStatements(
@"WITH test AS
(
SELECT 1 AS value
),
test2 AS
(
SELECT 2 AS value
)
(
SELECT *
FROM
test
)" ,
includeWhitespace: true);
Assert.AreEqual(1, statements.Count);
Assert.IsInstanceOf(typeof(TSQLSelectStatement), statements[0]);
Assert.AreEqual(46, statements[0].AsSelect.Tokens.Count);
}
public void AdvancedRecordset_ApplyScalarResultToEnvironment_updatedTrue()
{
var worker = CreatePersonAddressWorkers();
var query = "select * from person where Name=\"zak\";";
var statements = TSQLStatementReader.ParseStatements(query);
var updatedQuery = worker.UpdateSqlWithHashCodes(statements[0]);
var result = worker.ExecuteScalar(updatedQuery);
worker.ApplyScalarResultToEnvironment("[[Table1Copy().records_affected]]", result);
var internalResult = worker.Environment.EvalAsList("[[Table1Copy().records_affected]]", 0);
var e = internalResult.GetEnumerator();
if (e.MoveNext())
{
Assert.AreEqual("3", e.Current.ToString());
}
else
{
Assert.Fail();
}
}