Validate syntax of CREATE TABLE DDLs using EXPLAIN Command

5
votes

I have some create table scripts which I need to pre-validate in my application. I can think of two ways:

  1. CREATE table using that script and immediately DROP table.
  2. Use EXPLAIN command to find syntactical errors without creating it.

I found 2nd way more efficient. So, I validated CREATE TABLE DDLs using Explain command.

Working :

Teradata

Explain <CREATE TABLE DDL>

Oracle

EXPLAIN PLAN FOR <CREATE TABLE DDL>

Not working :

SQL SERVER

Could not find stored procedure 'explain'. SQLState: S00062 ErrorCode: 2812

Netezza

^ found "CREATE" (at char 18) expecting DELETE' orINSERT' or SELECT' orUPDATE' or `WITH'

DB2

Error: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=TABLE;EXPLAIN CREATE ;JOIN, DRIVER=4.14.111 SQLState: 42601 ErrorCode: -104

  • Is there any other better way to validate Create Table DDLs?

  • Is there any generic way to handle this across popular RDBMS?

  • If explain is the only available solution, how to perform explain for SQL Server, Netezza and DB2?

Edit:

Here validate means to check syntax (storage size, precision, scale range violations, reserved keywords as table or column names, etc.)

For example, query like - 

create table abc (c1 decimal(555,44))

I want to get precision overflow error beforehand.

4
sql-serveroracledb2teradatanetezza
And by "validate" you mean what exactly? – Nick Krasnov
@NicholasKrasnov validate means to find precision, scale overflows,etc. For example create table abc (c1 decimal(555,44)) – Dev
In Oracle, there is no native way to parse a DDL statement to check if it's syntactically and/or semantically correct without actually executing it. The explain plan (though it's not its purpose) is as close as you can get to parsing a DDL statement without actually executing it. DML parsing(without statement execution) can be done with dbms_sql.parse() - DDL statement will be executed. – Nick Krasnov
"Is there any generic way to handle this across popular RDBMS?" - no, absolutely not. – a_horse_with_no_name
@a_horse_with_no_name okay thanks...! – Dev

4 Answers

3
votes

There is no generic/standard method that will work in all DBMS.

I expect all popular DBMS to have something similar to EXPLAIN command. Something to return the execution plan instead of running the query itself. Each server will have its own way of doing it.

http://use-the-index-luke.com/sql/explain-plan shows how to do it for few DBMS. A search for <your DBMS name> explain plan command usually gives good results.

Another approach is to start a transaction, run your statement and roll back the transaction. Of course, you need to have proper error handling, which again differs between servers. In SQL Server there is TRY ... CATCH.

It is also worth checking if DDL statements in transactions are supported in the chosen DBMS. For example, in MySQL "Some statements cannot be rolled back. In general, these include data definition language (DDL) statements, such as those that create or drop databases, those that create, drop, or alter tables or stored routines."

1
votes

You should be able to evaluate the correctness of a query in SQL Server using the format only option SET FMTONLY ON. With this option set SQL Server will not actually attempt to create the tables. Using your example, the T-SQL will look as follow:

SET FMTONLY ON
create table abc (c1 decimal(555,44))
SET FMTONLY OFF

Executing the above T-SQL will return error message ‘Column or parameter #1: Specified column precision 555 is greater than the maximum precision of 38.’

You could also create a stored procedure that evaluates the query for you using the method that works the best on the database platform you are using. I am not familiar with Netezza, Teradata & DB2 but I am assuming that they can execute dynamic SQL. Using this method, you simply pass the query you wish to evaluate as a parameter to the stored procedure from your application layer. The following code snippet show how this can be done for SQL Server:

CREATE PROCEDURE ValidateQuerySyntax
(
    @query NVARCHAR(MAX)
)
AS
BEGIN

    SET NOCOUNT ON;

    DECLARE @validationQuery NVARCHAR(MAX) = 'SET FMTONLY ON; ' + CHAR(13) + @query + ';' +  CHAR(13) + 'SET FMTONLY OFF;';

    BEGIN TRY
        EXEC (@validationQuery);

        -- Return error code 0 if query validation was successful.
        SELECT  
             0 AS ErrorNumber  
            ,0 AS ErrorSeverity  
            ,0 AS ErrorState  
            ,0 AS ErrorLine  
            ,'Query evaluated successfully' AS ErrorMessage; 

    END TRY  
    BEGIN CATCH  

        -- Return error information if query validation failed.
        SELECT  
             ERROR_NUMBER() AS ErrorNumber  
            ,ERROR_SEVERITY() AS ErrorSeverity  
            ,ERROR_STATE() AS ErrorState  
            ,ERROR_LINE() AS ErrorLine  
            ,ERROR_MESSAGE() AS ErrorMessage; 
    END CATCH; 

END

Queries can be evaluated as follow:

DECLARE @query_1 NVARCHAR(MAX) = 
'CREATE TABLE A 
(   
     c1 INT
)';

DECLARE @query_2 NVARCHAR(MAX) = 
'CREATE TABLE B 
(   
     c1 INT
     c2 INT
)';

DECLARE @query_3 NVARCHAR(MAX) = 
'CREATE TABLE B 
(   
     c1 INT
     ,c2 DECIMAL(555,44)
)';

EXEC dbo.ValidateQuerySyntax @query = @query_1;

EXEC dbo.ValidateQuerySyntax @query = @query_2;

EXEC dbo.ValidateQuerySyntax @query = @query_3;

The output of the above validation calls is as follow:

-------------------------------------------------------------------------------------------------------------------------------------------------------------
ErrorNumber | ErrorSeverity | ErrorState    | ErrorLine | ErrorMessage
-------------------------------------------------------------------------------------------------------------------------------------------------------------
0           | 0             | 0             | 0         | Query evaluated successfully
-------------------------------------------------------------------------------------------------------------------------------------------------------------
102         | 15            | 1             | 4         | Incorrect syntax near 'c2'.
-------------------------------------------------------------------------------------------------------------------------------------------------------------
2750        | 16            | 1             | 1         | Column or parameter #2: Specified column precision 555 is greater than the maximum precision of 38.
-------------------------------------------------------------------------------------------------------------------------------------------------------------

Of course this does mean creating the stored procedure that evaluates the query for you first, but it should simplify the validation of your query across the different database platforms.

1
votes

My suggestion to (at least) your first two concerns:

  • Is there any other better way to validate Create Table DDLs?
  • Is there any generic way to handle this across popular RDBMS?

would be to use Perl's parsing and database interface capabilities/features (i.e. Perl DBI module) and write a script that validates the SQL via a prepared statement call against your choice(s) of database(s).

The high-level code flow would be:

  1. Connect to your database of choice
  2. Run your SQL through Perl's prepare() call (e.g. $dbh->prepare('CREATE TABLE emp (emp_name VARCHAR2(30)')
  3. Check the output status of the prepare() call

From the A Short Guide to DBI

The prepare call prepares a query to be executed by the database. The argument is any SQL at all. On high-end databases, prepare will send the SQL to the database server, which will compile it. If prepare is successful, it returns a statement handle object which represents the statement; otherwise it returns an undefined value and we abort the program. $dbh->errstr will return the reason for failure, which might be ``Syntax error in SQL''. It gets this reason from the actual database, if possible.

Perl has some other modules that are worth a look at and may be of use, namely:

SQL::Translator

SQL::Statement

0
votes

Perhaps the SQL PREPARE statement, issued against the string that is the DDL statement; should be revealing of the SQLCODE and SQLSTATE for when the DDL CREATE TABLE statement is not valid. Something like the following REXX-like pseudo-code:

sCrtTable="create table abc (c1 decimal(555,44))" ;
prepare DDL_stmt from :sCrtTable ;
say sqlCode ":" sqlState ; /* e.g.: "-604 : 42611"  per invalid length attribute */