Learning DOS batch shell script

Introduction

Professionally I am Database and BI consultant. mostly I work on SQL Server and BI related technology. but traditionally I assume myself as a programmer.I used different scripting language to solve specific problem many times in my work profile. No matter what BI platform you are using, sometime 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 skill. This article is based on learning programming skills for DOS batch script. although DOS is no where is used but its scripting and commands are powerful too.I will be explaining different tokens to learn DOS batch script through out this article.

Background

DOS batch script uses DOS commands with many programming stuffs. it becomes extremly 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 I explained above ,I am a BI and Database consultant so my research and article contents is based on my 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 Multipication 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 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 example 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 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 success or 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 above for loop demo, I have explain 4 example of using for loop.In my first example I have used simple loop where iteration starts from 2 and reached upto 5 incremented by 1 everytime.In my second example , I have described for loop iterating through a string collection. it will display all list available in for loop collection. In my third example, I have used for loop to recursive 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 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 other 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 above example, I am calling demoexecsqlquery.bat file 200 times. so 200 separate command session 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 repositiry
  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 above batch programming example , I am reading all files from curversion folder. checking if same file were available in 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 article explain programming tokens of DOS batch programming.it also describe how we can use SQLCMD in batch programm and how programming flow of DOS batch script works. I hope my effort will help to someone in learning of DOS batch script. Happy scripting......

History

No updates available

Comments

• Anonymous
  May 13, 2015
    Recent Releases and Announcements   ·        Microsoft Disaster