Active Query Builder support area

Allowing custom SQL to pass through the parser

Avatar
  • updated
  • Completed
We use a few custom SQL codes in our application which basically help with reports by doing a string-substitution when the SQL is executed.

Example:-
We have a number of tokens like @CURRENTUSER and @LOGONDT
When our DB engine executes queries it simply replaces such tokens with the appropriate value.

Please can you tell me how to allow custom syntax like this through your SQL parser so that they don't get flagged as errors?

Thanks.
Avatar
Andrey Zavyalov, PM
Of course, string literals will be parsed fine and you'll be able to substitute them among with quotes.
Avatar
Hedley Muscroft
It's not that I wanted some special keywords to be defined. It was just a question as to whether we could do some sort of SQL substitution so that the parser didn't throw an error when it parses the SQL.

I guess I could modify all my own keywords to put them inside single quotes (') for example :-

select fld1, fld2, '@@function:param' from some_table

I believe that will work as the parser will treat the function as a string literal - is that correct?
Avatar
Andrey Zavyalov, PM
Hello,

The "@identifier" parameter syntax is supported now for all SQL syntaxes except Oracle. You can use this notiation freely. The "@@name:type" notation is not supported in by Active Query Builder in any SQL syntax.
Avatar
Hedley Muscroft
I should also add that we have more complex tokens too like :-
@@startdate:datetime

...which instructs our application to display a dialog box requesting user input on a date/time value which then gets substituted in. In the same way you could stipulate :-

@@description:string
@@num_occurrences:int

...and so on.

In all such cases we need to either pre-process the SQL (and preserve it) before the SQL parser reports errors or we need to tell the SQL parser what to allow and what to dis-allow.

I look forward to your help - thanks!