Question Re: How to parse one SQL script differently for different database versions? in Technology Q&A

How to parse one SQL script differently for different database versions?

VolkerBarth — Tue, 19 Oct 2010 08:42:20 GMT

I would like to use one SQL script with DDL statements like CREATE TABLE for different SA versions and use (slightly) different syntax for these different versions.

E.g. SA 10 has introduced the VALIDATE authority (and REMOTE DBA authority is no more sufficient to VALIDATE), so for SA10 and newer, the script would contain a statement like

grant validate to <Remote DBA User>;

Naturally, a pre-V10 engine won't accept this syntax and will raise an error.

My simple tests with if statements like

if @@version like '12.0%' then grant validate to <Remote DBA User>; end if;

do fail, as obviously the complete statement gets parsed even if the statements in the if-block won't get executed for a pre-V12 engine.

So, basically, I would like to use something like C's #ifdef/#ifdef directives for conditional compilation, i.e. a means to suppress the parsing of a script portion when a certain condition is false.

Is this possible with SQL Anyhwere and DBISQL?

Just to add: It will not be sufficient to skip such statements completely via on_error = continue. This might be sufficient in the above example, but in general the statements will have to be executed by all versions, and only particular clauses may be different.

Re: How to parse one SQL script differently for different database versions?

Breck_Carter — Tue, 19 Oct 2010 09:05:35 GMT

EXECUTE IMMEDIATE won't throw a syntax error if it isn't executed.

( now, welcome to The World Of ''''''''Quotation Marks'''''''' 🙂

Re: How to parse one SQL script differently for different database versions?

VolkerBarth — Tue, 19 Oct 2010 09:19:29 GMT

Thanks for the pointer and good to know, but I guess that would make the script much more incomprehensible in my particular requirement...

Re: How to parse one SQL script differently for different database versions?

VolkerBarth — Tue, 19 Oct 2010 09:21:48 GMT

Yes, I'm aware that a possible enhancement for a current SA version won't make a pre-V10 engine change its behaviour. Kind of a very "post-mortem feature request":)

Re: How to parse one SQL script differently for different database versions?

Breck_Carter — Tue, 19 Oct 2010 18:00:45 GMT

I was sure I'd posted a suggestion about "more directives" in ISQL but I can't find it... it's something I would support as long as it doesn't go CRAZY like C.