Sw33tCode

Smart code for developers

Sw33tCode header image 2

Building Mysql Database contents from batch script in Windows

April 5th, 2008 · 2 Comments


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.

Sample Mysql Batch Script

Tags: Mysql · Windows Batch Script

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

*