19

Are there any SQL Validators that can check syntax against multiple database servers?

For example, I might want to check whether a particular query will work against Oracle, MySQL and SQL Server.

I am more concerned about the SQL syntax than the actual schema being queried, so a tool that can catch major syntax errors, like detecting that the limit clause is not supported in SQL Server and Oracle would be good enough.

EDIT:

Several answers have suggested checking syntax for a particular DBMS or for ANSI Standard.

What I am looking for a possibility of targeting a sort of union of features between two or more databases (say SQL Server and Oracle). I would like to be able to use any SQL feature that is supported by all of the DBMS I am targeting in a particular application.

I am not sure if it is worthwhile. I think it depends upon the number of non-ANSI features shared by several databases. If there are very few, then perhaps it will be better to target the ANSI standard.

Tahir Akhtar
  • 11,187
  • 7
  • 41
  • 68
  • Maybe the SQLite shell could help. However LEFT and FULL joins are not implemented, and unlike Oracle, you can SELECT from nothing whereas in Oracle you must use DUAL. – Benoit Oct 13 '11 at 11:20
  • how many databases are you writing for? Or is this an attempt at writing some db agnostic middleware? Maybe try dbms_sql.parse for Oracle, something similar(?) for other dbms. – tbone Oct 13 '11 at 11:58
  • Which versions of each database should it validate too. – Kevin Burton Oct 13 '11 at 12:07
  • @tbone and everyone else, please see updated question for some clarification. – Tahir Akhtar Oct 13 '11 at 13:26
  • @KevinBurton, I am currently looking if there are tools that do thing like this, specific versions of DBMS are not a huge issue at this stage. – Tahir Akhtar Oct 13 '11 at 13:27
  • 1
    Similar question: http://stackoverflow.com/questions/5015613/online-sql-syntax-checker-conforming-to-multiple-databases – Randall Cook Dec 14 '12 at 07:54
  • There are some capabilities in the SQL standard that are not supported by any of the popular databases. – Marichyasana Jul 11 '13 at 08:18

5 Answers5

7

I'm not aware of any that are that specific, these will check that the statements are valid ansi 92/99/2003...

http://developer.mimer.com/validator/index.htm

That will get you 99% of the way there (especially if are only doing CRUD operations)

maybe if you know which reserved words are used by which database you could roll your own simple checker.. see : How to find if a column name is a reserved keyword across various databases

(as already mentioned) If your goal is to create a database agnostic system think about using a third party tool e.g: entityspaces

Community
  • 1
  • 1
Kevin Burton
  • 11,178
  • 2
  • 21
  • 35
  • 1
    I lost bunch of time on one simple syntax error, I didn't close parenthesis (round brackets) in declaration of stored procedure. This link helped me out... [http://developer.mimer.com/validator/parser200x/](http://developer.mimer.com/validator/parser200x/) – Matija Dec 15 '14 at 10:14
2

From SQL Server point of view, instead of checking sql syntax against multiple database server you could check syntax "for compliance with the FIPS 127-2 standard. This is based on the ISO standard.".

Example:

SET FIPS_FLAGGER 'INTERMEDIATE'
Bogdan Sahlean
  • 19,000
  • 3
  • 39
  • 56
2

For the MySQL: try automatic SQL syntax check feature in dbForge Studio for MySQL.

For the Oracle: PL/SQL Editor in dbForge Studio for Oracle

Devart
  • 115,199
  • 22
  • 161
  • 180
0

This is an online mysql syntax checker, but this is mysql only http://www.piliapp.com/mysql-syntax-check/

Girvan
  • 41
  • 4
0

You can use https://www.db-fiddle.com/. It supports the following DBs

  • MySQL 8.0
  • MySQL 5.7
  • MySQL 5.6
  • MySQL 5.5
  • PostgreSQL 13
  • PostgreSQL 12
  • PostgreSQL 11
  • PostgreSQL 10.0
  • PostgreSQL 9.6
  • PostgreSQL 9.5
  • PostgreSQL 9.4
  • SQLite 3.30
  • SQLite 3.26
Sarangan
  • 853
  • 1
  • 8
  • 23