Hi,
Is there an opensource library that checks/validates the syntax of an .sql file at runtime?
Thanks in advance!
Connie
+1
A:
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.
Matthew Vines
2010-09-07 19:20:09
+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.
OMG Ponies
2010-09-07 19:21:47
There are plenty of statements that are not subject to transactions, though. `DROP TABLE`, for example...
bobince
2010-09-07 19:47:43
What if the statement was so lousy that it contained a `COMMIT` statement somewhere.
aularon
2010-09-07 19:49:55
@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.
Matthew Vines
2010-09-07 19:56:05
+1
A:
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.
quanticle
2010-09-07 19:21:06