Multi-query scripts in Query Browser

from the Artful MySQL Tips List

The MySQL Query Browser is an unfortunate example of software that got too far with a mistaken model of user needs. What do we mean? Try executing a simple multi-query script in the QB Query Edit window, for example ...

use test;
drop table if exists t;
create table t(i int);
set @x=3;
insert into t values(@x);
select * from t;

Senselessly, QB reports a "No database selected" error. What's going on? By default the query window executes one query at a time, using a new connection each time, so after it executes use test; it closes that connection and opens a new connection which therefore uses no database and cannot parse drop table if exists t;. A new connection per query also defeats any attempt to carry user variable values from one query to another. Oy.

Well, how about the QB script editor (File | New Script Tab on the menu), which is advertised as capable of executing multi-query scripts? It executes multi-query scripts but displays no results from them. Yikes.

Is there a workaround? Sort of. Close Query Browser and re-open it. You are back in QB's Query Editor rather than its Script Editor. Click the Transaction button on the Advanced Toolbar to open a transaction. Now QB will execute one highlighted query at a time from your script, and show the result if any. By highlighting each query in turn and clicking the Execute button, you can see the results of each query in the script. When you are done, close the transaction. It's awkward but it works.

When will MySQL fix this? In early 2006 the MySQL Senior Software Engineer for Developer Tools said it would be fixed in the next major QB release.

Return to the Artful MySQL Tips page