Is there an opensource library that checks/validates the syntax of an .sql file at runtime?
Thanks in advance!
Connie
2
-
Looks like a duplicate of stackoverflow.com/questions/1189659/validate-mysql-queryDagg Nabbit– Dagg Nabbit2010-09-07 19:23:49 +00:00Commented Sep 7, 2010 at 19:23
-
Check stackoverflow.com/questions/1189659/validate-mysql-query/…georgepsarakis– georgepsarakis2013-02-19 10:43:24 +00:00Commented Feb 19, 2013 at 10:43
Add a comment
|
2 Answers
As a simple solution you could execute the statement inside of a transaction, and roll it back whether successful or not.
Catch any sql exceptions that arise and pass those on to the user as syntax errors.
4 Comments
OMG Ponies
+1 I was thinking dummy database, but a transaction is better. The idea of syntax validation outside of the database itself to me is very suspect.
bobince
There are plenty of statements that are not subject to transactions, though.
DROP TABLE, for example...
aularon
What if the statement was so lousy that it contained a
COMMIT statement somewhere.
Matthew Vines
@bobince and @aularon Good call, I wasn't considering DDL statements in with the problem definition. You would certainly want to consider such things in your implementation though. The easiest way, if such statements are not allowed, would just be to execute as a user that is not allowed to run any DDL scripts. The COMMIT issue is a little more involved. Without going too deep, I think keeping track of your transaction count could be the answer.
As per this question, Mimer SQL Validator can validate your SQL syntax. Note that it only checks your syntax, though - it'll tell you if you've forgotten to quote the data you're inserting, but it can't check if the table you're inserting into exists.
