Introduction
Professionally, I am a database and BI consultant. Mostly, I work on SQL Server and BI related technology, but traditionally I assume myself as a programmer. I used a different scripting language to solve a specific problem many times in my work profile. No matter what BI platform you are using, sometimes, you require to develop such script to make your job easy. It could be for cleansing dirty files, managing file system related task, handling any executable utility through command line, automation of any process, etc. All these require script writing skills. This post is based on learning programming skills for DOS batch script. Although DOS is nowhere used but its scripting and commands are powerful too. I will be explaining different tokens to learn DOS batch script through out this post.
Background
DOS batch script uses DOS commands with many programming stuff. It becomes extremely powerful when programming is applied over these commands and commands output. Users only require basic programming skills with excellent knowledge of DOS commands to learn batch programming. As mentioned above, I am a BI and database consultant, so my research and article contents are based on that experience only.
Using the Code
Launch command prompt and start DOS batch programming. you can create batch script in Notepad++.batch script has extension .bat.
Demo Operator in DOS Batch Programming
@echo off
rem demo all arithmetic operator
rem set /p is used for prompt reading on variable
set /p val1="Enter first value :"
set /p val2="Enter second value :"
echo ********* arithmetic operator demo ************
rem set /a is used for arithmetic calculation
set /a res=%val1%+%val2%
echo Sum of %val1% and %val2% is %res%
set /a res=%val1%-%val2%
echo Subtraction of %val1% and %val2% is %res%
set /a res=%val1%*%val2%
echo Multiplication of %val1% and %val2% is %res%
set /a res=%val1%/%val2%
echo Division of %val1% and %val2% is %res%
set /a res=%val1%%%val2%
echo Modulo division of %val1% and %val2% is %res%
echo ********* relational operator demo @@ geq,gtr,equ,lss,leq @@************
if %val1% gtr %val2% (
echo %val1% is greater than %val2%
) else (
if %val1% lss 10 (
echo %val1% is even less than 10
) else (
echo %val1% is less than %val2%
)
)
In the above operator demo batch script, I have described all arithmetical operator and relational operator used in batch programming. It will accept 2 values from user and perform arithmetical and relational calculation on it. I have used set /a command to perform arithmetical operation and set /p to accept input from user.
Demo if-else in DOS Batch Programming
@echo off
rem check for leap year using if else
set /p yval= "Enter year value :"
set /a res=%yval%%%4
if %res%==0 (
echo "%yval% is a leap year"
) else (
echo "%yval% is not a leap year"
)
rem check if file is available or not
set filename=savecustomer1.sql
if exist %filename% (
echo File exist
) else (
echo File does not exist
)
rem check exit status of last running command
DEL test.txt
if %ERRORLEVEL%==1 (
echo last operation was successful
) else (
echo problem in last operation
)
In if-else demo, I have explored how if-else works in DOS batch programming. I have used 3 examples to explain it. In my first example, I am accepting year value from user and displaying result whether given year is leap year or not. In my second example, I am checking the existence of a file using if-else. In my last example, I have used ERRORLEVEL system variable to get exit status of last executed command so that user can check whether last command execution was a success or a failure. For each success execution, it returns 0 and 1 for failure.
Demo for Loop in DOS Batch Programming
@echo off
echo *****Simple numeric for loop*****
FOR /L %%G IN (2,1,5) DO echo %%G
echo *****for loop working on collection*****
FOR %%G IN (Sun Mon Tue Wed Thur Fri Sat) DO echo %%G
echo *****for loop /R reading files of current directory recursively *****
For /R %%G in (*.*) do Echo "%%G"
echo ***** looping file contents @@@ Reading comma separated file @@@@******
FOR /F "tokens=1,2,3 delims=," %%G IN (persondata.txt) DO @echo %%G %%H %%I
In the above for loop demo, I have explained 4 examples of using for loop. In my first example, I have used a simple loop where iteration starts from 2 and reaches upto 5 incremented by 1 every time. In my second example, I have described for loop iterating through a string collection. It will display all lists available in for loop collection. In my third example, I have used for loop to recursively read all files of current directory. In my final example, I am reading a comma separated file persondata.txt and displaying all columns.
Demo SQL Query Execution Using DOS Batch Shell
@echo off
Rem execute SQL query from batch file
sqlcmd -S .\sql2012 -Q "insert into SQLGD.dbo.customer values(1,'Ajit Kumar Thakur')"
Rem execute SQL query from SQL file
sqlcmd -S .\sql2012 -i SaveCustomer.sql
In the above shell script demonstration, I have used SQLCMD command line utility to connect SQL Server database. I have used Windows authentication mode to connect with database server. One can use another different option with SQLCMD to perform database related operation from DOS batch script. In my first example, I have executed insert script, while in my other example I am executing SQL query available in SaveCustomer.sql file.
Demo Execution of Multiple DOS Batch Script from Another Batch Script
@echo off
FOR /L %%G IN (1,1,200) DO cmd /C demoexecsqlquery
In the above example, I am calling demoexecsqlquery.bat file 200 times. So 200 separate command sessions will generate and execute scripts inside it.
Demo of Incremental File Loading through DOS Batch Script
@echo off
rem processing incremental load using batch file
echo processing incremental load
For /R %%A in (demofileprocessing\curversion\*.txt) do (
set %dupflag%=0
For /R %%B in (demofileprocessing\preversion\*.txt) do (
rem compare current version file with previous version file repository
fc %%A %%B > 0
rem check for same data file repeating in new version
if %errorlevel%==1 (
set %dupflag%=1
)
)
if %dupflag%==1 (
rem copy incremental file in separate location
copy %%A demofileprocessing\incrementalfile
) else (
rem copy duplicate file in other location
copy %%A demofileprocessing\duplicatefile
)
)
In the above batch programming example, I am reading all files from curversion folder, checking if the same file was available in the previous version or not. If it is new file, then copy file into incrementalfile folder, otherwise copy it into duplicatefile folder.
Points of Interest
This post explains programming tokens of DOS batch programming. It also describes how we can use SQLCMD in batch programm and how programming flow of DOS batch script works. I hope my effort will help someone in learning of DOS batch script. Happy scripting...
History
- 31st March, 2015: Initial version
