How to run multiple .sql files in Eclipse DTP

Question

I've a list of .sql script files to create Stored Procedures which I'm using the Eclipse DTP to develop. Currently to create/update all these Stored Procedures, I've to open & run one by one from the Data Perspective.

Is there a way to create a batch file that run the scripts along the lines of

    run createSP1.sql
    run createSP2.sql
    ...
    run createSPn.sql

and run it in the Eclipse DTP to avail of the DB connection defined there?

Answer 1

why not just create a batch file that merges all of your .sql files together into a single procs.sql file as part of the build process. I don't know what platform you're running on but in Windows you could have a .bat file that does something like this:

type *.sql > proc.sql

then to apply it to the database, why not do it outside Eclipse and connect to the database via the command line. You could bundle this all up as a single batch file that gets the latest version of your stored procedures from source control, merges them into a single file and then applies it to the database.

Answer 2

Part I

As far as I know the developers of Eclipse DTP have not yet implemented a command line SQL execution interface through the Eclipse console view.

See the following URL on the eclipse DTP developer forum http://dev.eclipse.org/newslists/news.eclipse.dtp/msg00304.html

Part II

While the Eclipse DTP people are working on it, you can use a database specific tool to load a master SQL file (all SQL proc files appended together)

There are database specific console tools that will load your master SQL file command line. (ie. SQL*Plus for Oracle, ij for Apache Derby)

Part III

An improvement over DOS batch is using Cygwin bash or python or perl to merge all of your sql files together into a master file.

I found that the text processing tools available in UNIX (awk,sed,cat...) are great for this sort of thing.