Question Re: Validate sql statements in Technology Q&A

Validate sql statements

Former Member — Mon, 23 Feb 2015 05:24:26 GMT

I was thinking that I could use:

SELECT PLAN('statement goes here');

If the result is some text, then I know the syntax of the sql statement is valid.

Is there a better way of doing such validations?

I'm interested solely in validating the syntax correctness of an sql script.

Re: Validate sql statements

Breck_Carter — Mon, 23 Feb 2015 06:22:41 GMT

Also consider SQLDIALECT(), WATCOMSQL(), TRANSACTSQL(), REWRITE() and SQLFLAGGER().

Re: Validate sql statements

VolkerBarth — Mon, 23 Feb 2015 06:45:08 GMT

"DBISQL -x" might also help...

Re: Validate sql statements

VolkerBarth — Tue, 24 Feb 2015 03:49:51 GMT

It might be added that "select plan()" will only work for SQL statements that generate a plan, i.e. for single queries and DML statements, not for procedure calls or DDL statements. However, it seems to check whether the schema is valid for the statement, so it will notify about misspelled identifiers or non-existing tables/columns. That makes it only partially useful for "syntax-only" validation.

REWRITE() and DBISQL -x in contrast can parse all statements, however, they will only check for syntax errors (as you have asked for). E.g. the following will raise a syntax error:

GRANT SELECTX ON products for MyUser;


whereas they will not check whether "products" or "MyUser" actually do exist.