Wednesday, February 1, 2012

More on using the commandline vs. Visual editors

Shlomi Noach has made a couple of posts (here and here) on Visual Editors vs. commandline for MySQL working with SQL statements. All in all, I tend to agree with him in many respects, but I am surprised that he recommands MySQL Query Browser, as this is the editor that breaks one of the things that seems to annoy him (and me) most: Every SQL statement that is issed uses it's own connection. Which is not to say others may feel differently about it.

As for myself, I started developing my own MyQuery tool because there are things you just cannot do from the commandline, and there are things that are really complex and non-inituitive to do from the commandline, and you need a better tool than the commandline. Again: This is not to say that there is something wrong with the MySQL commandline, it really should not be full featured, but it should run all SQL commands with ease.

So, what was I missing in the commandline MySQL that led to the development of MyQuery, and project that I am still working on, on and off? Mainly, using the commandline makes it REAL hard to develop and debug scripts. That was the main reason! At the time that I released MyQueru 1.0, I was involved in a project that required A LOT of SQL scripting. And if you have a big, big SQL script, then you know what happens: The scripts runs for a few 100 lines, and then there is an error or a typy, and the commandline just keeps processing until we get an error, and then it stops (alternatively, you use --force, in which case you usually get truckloads of errors, and even if youjust get one error,it gets lost among all the other errors).

OK, so the script failed with an error, then you just fix the error and run it again? Right? Well, that approach assumes that your script is idempotent, which isn't always possible and is at least difficult in many cases. Wouldn't it be neat to have the script running stop when there is an error, allow you to fix the error, and then keep running from where the error occured? That is exactly what you can do with MyQuery.

Alternatively, here is another script running scenario:
You have a script that takes forever to run. You might have the odd error in there, but you can fix them afterwards, just run the script please! But also tell me about any errors in there, so I can fix them! OK, MyQuery does this too. You can run with force in MyQuery also, but the errors are should in a special errors tab. After the script has finished running, you can see the errors in the error output tab, and that is not intermixed with any status printouts or anything. And yes, click on an error text and you get to that position in the script.

Using SQL to generate SQL? Yeah, you can do that with the MySQL commandline, but MyQuery makes it far easier.

MyQuery has a bunch of other neat features, like the ability to save and store blob data to disk (doing that from the commandline is somewhat possible, but only with many limitations. INTO DUMPFILE only works for single row for example), extend the interface with custom tools, using SQL statements, or the MyQuery API and many other things (One supplied tool will nicely break up a long SQL statement into several indented lines, simple but useful when working with big SQL statements). That MyQuery has a colour-coded editor with a very flexible setup is also a bonus that makes it easier to work with.

Actually, the reason I started developing MyQuery is probably still the best reason to use it: To run scriripts.


1 comment:

shlominoach said...

Just a single comment about using MySQL Workbench (formerly QueryBrowser) rather than any other editor: