[RESOLVED] Test if a query will work

dougal85

Is it possible to test a query to see if it will work or not?

I know you can do this with a SELECT easily... as you just need to execute it. However, what about with a DELETE, UPDATE etc.?

I want to be able to return if a query will be successful without making the changes.

I guess in a way, checking the syntax is valid and so on.

WDPEjoe

Yes, put test data into your database and run the delete and update queries on that.

dougal85

Not a bad idea...

However, the idea is not for me to use it. its for people to learn using SQL with. and they must be able to do more than SELECTS. Think of it as something that will be used in say... phpMyAdmin... under the query section an AJAX response will give a green or red light in the corner depending on success. Not how i plan to use it, but just an idea.

Any other suggestions are greatly appreciated.

malariajoe

If you use MySQL's InnoDB instead of MyISAM you can use transactions:

After every "user generated" query you use the command ROLLBACK to undo all changes.

Just an idea :-)

dougal85

Sounds like an interesting idea, the only thing that scares me is it seems like quite a dangerous idea.

I was thinking I could maybe use 'EXPLAIN' somehow? http://dev.mysql.com/doc/refman/4.1/en/mysql-explain-log.html

dougal85

Anybody else have any thoughts about this?

bradgrafelman

Really, I don't see what's wrong with the transaction method of using START TRANSACTION, executing query, see if error resulted, ROLLBACK table(s) to original state. Just make sure the AUTOCOMMIT is set to 0, and you might want to strip out the keyword "AUTOCOMMIT" from their queries just in case they try to harm the tables.

WDPEjoe

I agree, this is probably the most simple way to go about it.

dougal85

cool thanks, well, i'll see how it goes. 🙂