It’s the second type I’m using with simple single-quote characters. If I put a backslash character in there then it works fine in management studio but the schema modeller won’t validate it and throws an error.
Basically I’m joining two tables together and then need to concatenate their descriptions thus:
SELECT Table.Description + ‘’ + Table2.Description AS FullDescription
FROM Table1 INNER JOIN Table2 ON…
(again using basic single quotation characters)
The schema modeller won’t valid this and throws an error.
The error is actually claimed to be later in the query, which suggests to me that it’s interpreting the backslash and closing quote as an escape sequence and thus doesn’t realise the string literal has ended.
Ok I think it may be something to do with CTEs in MSSQL.
Say I have a table Contacts with fields Id, Forename & Surname.
This validates fine:
SELECT Id,Forename + '\' + Surname FROM Contacts WHERE Id = :Id
But this doesn’t:
WITH X(Id,FullName) AS (SELECT Id,Forename + '\' + Surname FROM Contacts) SELECT * FROM X WHERE Id = :Id
This throws an error saying "Incorrect syntax near ‘:’
This only happens when there’s a backslash character in the string literal, a space or forward slash is fine.
Seems the parser is getting confused when CTEs are in use.
I believe the \ acts as an escape char for the second quote, much like in say C or C#, think
Name = 'o\'Connel'
Not sure if thats valid for SQL or if that’s a bug/oversight in our SQL parser (Anton will know). As a workaround fr now, c an you try doubling the \ to escape it?, eg:
SELECT Id,Forename + '\\' + Surname FROM Contacts WHERE Id = :id:
It’s strange that it works outside of a CTE, as if the additional bracketing might be causing issues with the parser.
If I double the character, as if it was escaped, then it would probably satisfy the parser but the actual executed SQL would presumably have two backslash characters in the result.
Not sure, yeah… I’m not sure exactly what happens for validation vs actual execution that would make a difference there, Anton will know more when he’s back tomorrow.
SQL statement validation process by itself is rather simple: Schema Modeler tries to retrieve metadata for the provided SQL statement and checks if there were any errors. Schema Modeler by itself doesn’t parse the SQL statement to validate it (however it does parse the statement to extract parameters from it)
What’s curious is that a statement like
WITH X(Id,FullName) AS (SELECT Id,Name + '\' + Name FROM Genres)
SELECT * FROM X WHERE Id = :id:
successfully validates for all .NET drivers, SDAC and UniDAC drivers, but fails for FireDAC and AnyDAC drivers.
I’ll log an issue to check what’s causes failures for these drivers