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.
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......
No updates available