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

Question

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.

Maybe the answers to this other question could help you: http://stackoverflow.com/questions/141499/any-java-libraries-out-there-that-validate-sql-syntax — Haroldo_OK, Jan 27 '16 at 13:05

score 12 · Answer 1 · answered Apr 20 '12 at 13:25

12

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

answered Apr 20 '12 at 13:25

Randy

16,480
1
37
55

3

explain plan is the way to go. It will give the same syntax errors as "running" the query – Apr 20 '12 at 15:33
2

How to deal with bind parameters? – MRalwasser Jan 21 '14 at 16:06
True, if you have even a single parameter in your query, this simply won't work. – Haroldo_OK Jan 27 '16 at 13:01
1

you might consider replacing the bind with a fake value - it does not matter if it will return rows, just if it will parse. – Randy Jan 29 '16 at 23:16

score 6 · Answer 2 · answered Apr 20 '12 at 15:36

6

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

answered Apr 20 '12 at 15:36

GriffeyDog

8,186
3
22
34

score 5 · Answer 3 · answered Apr 20 '12 at 14:17

5

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.

answered Apr 20 '12 at 14:17

ypercubeᵀᴹ

113,259
19
174
235

gordy's comment gets my vote because it works for other dbms than Oracle. – AlainD Dec 05 '15 at 18:42
@user1938185 Yes, the `FROM dual` only works in Oracle and MySQL (and SQLite I think). In other, liek SQL Server and Postgres, you can remove the `FROM dual` completely and it will work as well. – ypercubeᵀᴹ Dec 05 '15 at 19:33

WoMo · Answer 4 · 2012-04-20T15:28:53.637

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;
/

score 0 · Answer 5 · answered Aug 21 '13 at 12:04

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.

Create a file test.pc with this code:

EXEC SQL SELECT * FROM DUAL WERE 1=1;
Run this command after installing the precompiler tools:

proc INAME=test SQLCHECK=SYNTAX
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

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

5 Answers5