Conditional DDL in MySQL 5.1

[SimonSellick]

Hi,

The web host I am using (not my choice) controls users and script execution options; in particular, the whole script fails if I try to drop a non-existent object (instead of just warning), and I cannot grant permissions on objects that I have created, they are just assumed. I could rewrite the scripts and alter my local user model, but I would prefer to simply make the problem statements conditional.

In MS SQL I could script something like

if not exists [some object]
  [some DDL]

IF works only in a proc in MySQL, is there some other way to accomplish the above without developing a stored proc?

Any help appreciated.

Simon.

 

[r937]

instead of dropping something before creating it (and risk the error that it doesn't exist), try using the IF NOT EXISTS option of the CREATE statement, which will create it ~only~ if it doesn't already exist...

... at which point you can then drop it with assurance that it will not cause an error, and then just create it again

simple, innit ;-)

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[SimonSellick]

Yes, it sort-of is easy (and thanks for the reply) but I can't think of a way of making the GRANT conditional. Perhaps I should just use the same user for DDL and DML, then the problem disappears.
Thanks again.

 

[ingresman]

Or you could read the information_schema to see what is going on and generate dynamic grants from there.

 

[SimonSellick]

I can't see how to do that - could you give an example or point me towards the appropriate part of the manual?