JavaScript Editor Js editor     Website development 
↑


Main Page

Previous Page
Next Page

Using Batch Files

For the most part, throughout the course of this book I have run queries directly in the mysql client. The MySQL software also allows you to run queries without entering the mysql client itself. To do so is to use mysql in what is called batch mode. Instead of entering queries directly, you can store them in a text file and then run the text file through mysql. From a command prompt, you would do something like the following:

bin/mysql -u username -p < '/path/to/filename.txt'

Since you will presumably be running the queries on a particular database, you will often use the -D option to select it in advance:

bin/mysql -u username -p -D databasename < '/path/to/filename.txt'

As just one example of this technology, I will run a batch script that creates a table and populates it.

To use batch files:

1.
Download the SQL commands for this book from the book's corresponding Web site (www.dmcinsights.com/mysql2).

Every SQL command I use in the entire book is available in a single text file. You'll find this on the Extras page.

2.
Unzip the downloaded file.

I've archived the text file in Zip format, so you'll need to expand it.

3.
Open the text file in a text editor.

You can edit this in any text application, although Notepad (and perhaps some others) will not handle the line breaks properly.

4.
Copy any SQL commands you want to run into a new text document.

You absolutely, positively should not run the entire SQL file as is! Doing so would take forever and make all sorts of messes. Instead, copy just the SQL commands you need at this time.

In this specific example, I will use a batch file to re-create and populate the expense_categories table, so I will need to copy its CREATE and INSERT statements. You can find them under the "Chapter 4" and "Chapter 5" headings in the text file.

5.
Save the new file as ec.sql.

It doesn't really matter what name you use or where you save it, as long as you know where it is and what it's called.

6.
Access your server from a command-line interface and move into the MySQL bin directory.

These instructions are in the second chapter, if you do not know how to do this.

7.
If desired, create a new database (Figure 13.29).

./bin/mysqladmin -u root -p create accounting2

Since I already have an accounting database with this table (that I don't want to mess up), I'll create a new database.

Figure 13.29. In order to practice using batch files without altering my existing database, I'll create a new database using mysqladmin.


8.
Run the batch file (Figure 13.30).

./bin/mysql -u root -p -D accounting2 < '/path/to/ec.sql'

You'll need to change the /path/to/ec.sql part so that it corresponds to the name and location of the file on your server. This might be C:\Documents and Settings\My Name\Desktop\ec.sql on Windows or ~/Desktop/ec.sql on Mac OS X (where ~ is your home directory).

Figure 13.30. To re-create the expense_categories table without retyping my SQL, I can use the batch mode.


9.
Confirm that the SQL commands worked (Figure 13.31).

./bin/mysql -u root -p accounting2
SHOW TABLES;
SELECT * FROM expense_categories;

Figure 13.31. Verify the results using the mysql client.


Tips

  • If you want to save the result of a batch file as its own text file, add > '/path/to/output.txt to your code. The line

    bin/mysql -u username -p < '/path/to/input.txt' > '/path/to/output.txt'

    creates a file containing the results of the queries.

  • To have mysql continue executing a batch file even if it encounters errors, add --force to the execution line.

  • Batch files can also be run from within the mysql client, if you'd prefer. Just log in, select your database, and then type

    source/path/to/filename

  • You should not have any comments (lines that begin with #) as the first lines in your batch script or else it will not run.



Previous Page
Next Page


JavaScript Editor Js editor     Website development


©

→