SQL not validating backslash characters correctly


(tobygroves) #1

Similar to the issue described here:

It seems the SQL parser in the schema modeler doesn’t like backslash characters.

If I use this type of construct:

SELECT Column1 + ‘’ + Column2 FROM…

This fails to validate despite being perfectly valid T-SQL.

I’m using DA 9.4.109.1375 on Delphi 10.3.2 Enterprise using FireDAC against an MSSQL database.


(antonk) #2

Hello

Could you provide some more details?

This SQL fails with Incorrect syntax near '‘'. in SQL Server Management Studio:

 SELECT [Id], [Name] + ‘’ FROM [dbo].[Genres] 

This one works

 SELECT [Id], [Name] + '' FROM [dbo].[Genres] 

Also this SQL works in DASM:

 SELECT [Id], [Name] + '' AS FieldName FROM [dbo].[Genres] 

The only difference here is that a name for the second column is set explicitly. DASM requires this to perform field mapping.


(tobygroves) #3

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.


(antonk) #4

Have you explicitly specified the new column name (see the 3rd SQL snippet)?


(tobygroves) #5

Just tried that, makes no diference

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.


(antonk) #6

Could you send to support@ your .daSchema file (with only this Schema Table defined) and DDL scripts for tables used in the SQL statement?

Thanks in advance


(tobygroves) #7

It’s probably easier to rig up a simple test case from scratch, I’ll do so when I get a chance


(marc hoffman) #8

Or just paste the code here, but inside a triple pair of back ticks (`) to avoid reformatting:

SELECT X FROM Y

(tobygroves) #9

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.


(marc hoffman) #10

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:

(tobygroves) #11

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.


(marc hoffman) #12

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.


(antonk) #13

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


(RemObjects) #15

Thanks, logged as bugs://81501


(EvgenyK) #16

It can be pure FireDAC issue.
can you test the same SQL with TFDQuery w/o DataAbstract?