1

When I have to run database migration scripts, I tend to exercise a large degree of paranoia and not run the script all at once like dbcli < migration.sql. Instead, I prefer to run the commands one-at-a-time. So far, I've just been using copy/paste which is miserable.

There has to be a tool that can do this kind of thing, but I'm having a hard time finding one using Google, Wikpedia, or StackOverflow (close but no cigar).

This is definitely something I could write myself, but it just has to exist already, doesn't it?

This really needs to be something that can be run from a command-line with a tiny bit of interactivity (like display the statement that will be executed, let you press e.g. ENTER to execute it, then show you the output if there is any) since servers usually don't have any GUI available.

My specific db target is MySQL but there's no need for such a tool to be db-specific.

Update

Meanwhile, I'm writing a utility in Java that will do what I want.

Community
  • 1
  • 1
Christopher Schultz
  • 20,221
  • 9
  • 60
  • 77
  • Are the separate statements reliably delimited with a `;`? – mellamokb Jan 09 '13 at 22:07
  • Sql Server Management Studio has debugging if you are sa, if you google "mysql debug" there are quite a few programs out there – msmucker0527 Jan 09 '13 at 22:07
  • @mellamokb Honestly I can write my scripts to conform to any tool that has such requirements. If I were to write it myself, I'd just look for lines ending with ";", present the statement and one of the options might be "look for the rest of the statement" which would continue collecting input for the statement at hand. – Christopher Schultz Jan 09 '13 at 22:18
  • http://stackoverflow.com/questions/8940230/how-to-run-a-sql-text-file-on-mysql – Sathish D Feb 06 '13 at 08:04
  • @SathishD That doesn't actually help: I know how to execute a whole script. I want to execute it one-line-at-a-time, interactively. I have written a suitable utility that will do this for me and will be posting it somewhere soon. – Christopher Schultz Feb 08 '13 at 19:15
  • 2
    "*.. there's no need for such a tool to be db-specific*" Actually there really is such a need. At the script level, SQL is *very* impelmentation-specific to the extent that just figuring out where one statement ends and another begins can be problematic. – RBarryYoung Sep 29 '13 at 17:19
  • @RBarryYoung Yes, the script must be very specific but the tool itself does not: it's just sending the (possible db-specific) queries to the server one statement at a time. As long as you can read "one statement at a time" all should be well. Since most SQL interfaces use ";" as a statement separator, and most scripts have only a single statement on any given line (or, more likely, a single statement than spans multiple lines), scanning for a ";\n" statement terminator is a reasonable source of action. – Christopher Schultz Sep 30 '13 at 19:23
  • @ChristopherSchultz, unless anyone puts a comment after the ";" on the line, or even a space. Or if ";\n" appears inside a quoted string or inside a comment. Or unless the SQL script changes the statement terminator, because ";\n" also appears inside the body of CREATE PROCEDURE or CREATE TRIGGER statements. If you want to support *any* valid SQL script, this task is a bit harder than it may first seem. It's a lot easier if you decide to support only a subset of SQL script features. – Bill Karwin Oct 01 '13 at 13:38
  • @BillKarwin Yes, I know it's hard to write a SQL parser but I don't really care. The tool I wrote meets my needs and doesn't choke when ";\n" appears inside of a statement or has trailing whitespace. It will not catch the case of "; -- This statement does X" but it does print the statement before executing it and you can always stop to avoid disaster. That's the whole point of one-at-a-time statement execution. Just because you can find a case that would break such a utility doesn't mean its useless. (I'm surprised this question is getting such attention 9 months after it was written...). – Christopher Schultz Oct 01 '13 at 14:36

5 Answers5

1

Oracle SQL Developer will run one line at a time as long as it's ended with a semi-colon.

You can connect to certain 3rd party databases with additional drivers (http://www.oracle.com/technetwork/products/migration/omwb-getstarted-093461.html)

Greg
  • 16,540
  • 9
  • 51
  • 97
  • Will it connect to non-Oracle databases? I was looking for something that was relatively self-contained... installing a development environment just to run a SQL script seems a little heavy-handed. – Christopher Schultz Sep 30 '13 at 19:25
  • I just used it to connect to a SQL Server database using the link above. The application is about 260 MB, so yeah, it's pretty heavy. – Greg Oct 01 '13 at 13:35
  • Do you mean Microsoft SQL Server? Good to know... if OQD can connect to a MS product, it will probably connect to anything. Just FYI, I created a program long ago to do exactly what I wanted. It works well, but requires Java. I'm working on a re-implementation in C but it's now pretty much bound to MySQL :( – Christopher Schultz Oct 01 '13 at 14:33
0

You could try dbForge. There is a free Express Edition, but I can't quite tell from the feature comparison list if it allows you to step through arbitrary SQL scripts.

mellamokb
  • 56,094
  • 12
  • 110
  • 136
  • Might have a winner: dbForge says it supports "Execution of scripts, selected SQL fragments, and statements". It's in the free version so I'll download it and take a look. – Christopher Schultz Jan 09 '13 at 22:20
  • @ChristopherSchultz: I have a feeling that just means you can run scripts with it, i.e., it's like a MySQL WorkBench replacement. Debugging seems to come with Standard+ editions. – mellamokb Jan 09 '13 at 22:23
  • Sadly, it's Microsoft Windows-only and we don't run any Windows servers. :( – Christopher Schultz Jan 09 '13 at 22:25
0

Emacs' SQL mode supports sending line by line, region by region and file by file.

Of course, you have to learn emacs, but it does do what you want.

Haakon Løtveit
  • 1,009
  • 10
  • 18
  • Interesting. I was an Emacs user once.... I should have suspected that, since Emacs can do anything, SQL script execution would be something it could also do... – Christopher Schultz Sep 30 '13 at 19:29
0

Long ago, I wrote my own tool for this purpose, and have been using and perfecting it over time. Feel free to use it and offer suggestions for features, etc.

Christopher Schultz
  • 20,221
  • 9
  • 60
  • 77
0

Flyway Teams Edition (commercial license) also supports executing statements one by one under Stream parameter. This is not an exact answer to your question, but it can at least give you information about existing tools.

Graham Asher
  • 1,648
  • 1
  • 24
  • 34
Zajo
  • 31
  • 7