CODEWITHSUNDEEP
sqloracle-databasejdbcsyntaxverification
brabster
brabster

Reputation: 43560

How can I check SQL syntax for a JDBC statement without running the actual query?

I have an SQL statement (for an Oracle database) that takes a long time to run if it is valid. If it's not valid, it returns immediately with an error.

I'd like to check that the syntax is valid without running the statement (via JDBC), for example behind a 'check statement' button. Is there a vendor-independent way of doing that? My first thought of simply defining the query as a PreparedStatement does not seem to cause any kind of compilation or error checking.

Upvotes: 14

Views: 11504

Answers (5)

Joeri Sebrechts
Joeri Sebrechts

Reputation: 11146

You can use Oracle's Pro*C precompiler to perform a syntax check (download here).

This is a tool meant to precompile C code containing raw Oracle SQL statements, but you can "abuse" it to perform SQL syntax checks.

  1. Create a file test.pc with this code:

    EXEC SQL SELECT * FROM DUAL WERE 1=1;

  2. Run this command after installing the precompiler tools:

    proc INAME=test SQLCHECK=SYNTAX

  3. You will see this output:

    Syntax error at line 1, column 34, file test.pc: Error at line 1, column 34 in file test.pc
    EXEC SQL SELECT * FROM DUAL WERE 1=1;
    .................................1
    PCC-S-02201, Encountered the symbol "1" when expecting one of the following:
    ; , for, union, connect, group, having, intersect, minus, order, start, where, with,
    The symbol "having," was substituted for "1" to continue.

It should be straightforward to integrate that into your solution.

Note that it can also perform online semantics checks, verifying that all the used procedures and tables are valid in a specific schema. For that you pass in SQLCHECK=SEMANTICS USERID=youruser

Upvotes: 0

GriffeyDog
GriffeyDog

Reputation: 8376

If you're dealing with SELECT queries, perhaps JDBC PreparedStatement#getMetaData will work?

Upvotes: 6

WoMo
WoMo

Reputation: 7256

You could use DBMS_SQL.PARSE to check your statement. Warning: It will just parse DML statements, but it will execute and commit DDL statements such as create table etc. You could create a stored procedure to return a value or boolean and wrap a block like this:

set serveroutput on
-- Example of good SQL 
declare
  c integer;
  s varchar2(50) := 'select * from dual';
begin
  c := dbms_sql.open_cursor;
  dbms_sql.parse(c,s,1);
  dbms_sql.close_cursor(c);
  dbms_output.put_line('SQL Ok');
exception
  when others then
    dbms_sql.close_cursor(c);
    dbms_output.put_line('SQL Not Ok');
end;
/

-- Example of bad SQL
declare
  c integer;
  s varchar2(50) := 'select splat from dual';
begin
  c := dbms_sql.open_cursor;
  dbms_sql.parse(c,s,1);
  dbms_sql.close_cursor(c);
  dbms_output.put_line('SQL Ok');
exception
  when others then
    dbms_sql.close_cursor(c);
    dbms_output.put_line('SQL Not Ok');
end;
/

Upvotes: 2

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115550

This is more of a hack than a real answer but you could run a query that will always return one row and one column:

SELECT ( EXISTS (SELECT 1 FROM dual)
         OR
         EXISTS (your Query here)
       ) AS result
FROM dual

This should return TRUE if your query is valid and raise error if it is invalid.

Upvotes: 5

Randy
Randy

Reputation: 16677

possibly issuing an explain plan for the statement would give you useful results.

another thought - but maybe harder is to edit the query to add (and rownum < 1) or something to make it run fast

Upvotes: 12

Related Questions