HomeActive Query BuilderSQL escape sequences

SQL escape sequences


Is there any way to allow AQB to deal with SQL escape clauses? We have would like to be able to develop SQL statements that run against more than one database, and many of the drivers that we use already support SQL escape sequences but the AQB syntax providers do not seem to interpret them correctly.

For example, using the ADO provider for Microsoft Jet, this is a valid SQL statement:

SELECT {fn UCASE(Name)} FROM Employee

Which would apply the UCASE function against the Name field. Running the exact same statement using the ADO provider for SQLServer would actually apply the UPPER function against the Name field (that is how SQLServer implements the standard UCASE function).

The base SyntaxProvider would need to handle these common SQL escape sequences:

Function: {fn function-name}
Date literal: {d 'yyyy-mm-dd'}
Time literal: {t 'hh:mm:ss'}
Timestamp literal: {ts 'yyyy-mm-dd hh:mm:ss'}
Datetime interval: {interval 'value' interval-qualifier}

There's also a Like Wildcard escape sequence but that's probably harder to implement and I've never actually had to use it:
Like Wildcard: {escape 'escape-char'}

And I guess to be fully compliant you would need to support the outer join syntax:
{oj outer-join}

e.g.

SELECT *
FROM {oj Customers LEFT OUTER JOIN
Orders ON Customers.CustID=Orders.CustID}

...but again, I think that all of the major vendors already support the outer join syntax and that is probably harder to implement than the other functions.

The absolute best solution would be to allow each syntax provider to actually interpret the SQL escape clauses and actually implement their function (e.g. translate {fn UCASE(Name)} to UPPER(Name)) - that would give AQB the distinction of being the only query tool on the planet (as far as I can tell) that would actually allow a universal SQL syntax so that users could develop SQL statements against multiple databases.
aecspades | email
June 11 2009, 09:26 AM
I have ran into the same issue as well.
Is there a fix for this?
Can you please give us a reply at least?

Thanks.
Gazsi Robert | email
7 months, 3 weeks since original post
SQL escape sequences are not supported and we don't have plans to implement this support. Possly we'll get back to this problem in the future.
Sergey Smagin | email
7 months, 3 weeks since original post
This topic is closed