I needed a way to build my database quickly on a project I was working on and I was constantly making changes to the database that supplied content to my application. So I came up with an easy batch script that would help facilitate the changes in my database environment.
Here is a mocked version of the batch script that I wrote to build the database:
@echo off REM: Command File Created To Create MySQL Experiments Database REM: Date Generated: 4/5/2008 REM: Usage: CommandFilename [Host] [Username] [Password]if '%1' == '' goto usage if '%2' == '' goto usage if '%3' == '' goto usage if '%1' == '/?' goto usage if '%1' == '-?' goto usage if '%1' == '?' goto usage if '%1' == '/help' goto usage echo experiments.build.database START mysql -h %1 -u %2 -p%3 > "experiments.build.database.sql" if %ERRORLEVEL% NEQ 0 goto errors echo experiments.build.database END echo experiments.build.tables START mysql -h %1 -u %2 -p%3 "experiments" > "experiments.build.tables.sql" if %ERRORLEVEL% NEQ 0 goto errors echo experiments.build.tables END echo building stored procedures START mysql -h %1 -u %2 -p%3 "experiments" > "sprocs\uspAddUser.sql" if %ERRORLEVEL% NEQ 0 goto errors echo uspAddUser echo building stored procedures END echo experiments.insertdata.messenger START mysql -h %1 -u %2 -p%3 "experiments" > "experiments.insertdata.sql" if %ERRORLEVEL% NEQ 0 goto errors echo experiments.insertdata.messenger END echo Finished Building Database goto finish REM: How to use screen :usage echo. echo Usage: MyScript Server Database echo Host: MySQL Server echo Database: the name of the target database echo Username: username to be used on target database echo Password: password to be used on target database echo. echo Example: BuildDatabase.cmd "localhost" "root" "password" echo. echo. goto done REM: error handler :errors echo. echo WARNING! Error(s) were detected! echo -------------------------------- echo Please evaluate the situation and, if needed, echo restart this command file. You may need to echo supply command parameters when executing echo this command file. echo. pause goto done REM: finished execution :finish echo. echo Script execution is complete! :done @echo on |
This script will call the sql files that contain the commands to build the database, tables, stored procedures, and even create some test data. To call this command file all you have to do is supply the host, username, and password parameters. Like this:
c:\BuildDatabase.cmd "localhost" "root" "password" |
This script example will call your database: experiments. But if you wanted to dynamically name your database you can just add another parameter onto your batch script and replace all the mysql calls above with this code:
mysql -h %1 -u %2 -p%3 %4 > "sql_code.sql" |
Then your call to the command file will be like this:
c:\BuildDatabase.cmd "localhost" "root" "password" "database_name" |
If you wish to try out the code in action please download the zip file below. You will need MySql Version 5 or above to test this. Please let me know if you have any questions.
2 responses so far ↓
1 JR // Apr 15, 2008 at 3:02 pm
Nice man! I was looking for something like this awhile back, but now I’m using this code in my database creation scripts with vs2005. Thanks!
2 Lacy // Oct 28, 2008 at 6:21 pm
Good for people to know.
Leave a Comment