By: Daniel Farina | Comments (1) | Related: > SQL Server on Linux
Problem
If you are going to be using SQL Server on Linux then it would be helpful to understand how to write bash scripts. In this tip I will cover the basics of iterative statements which is part of a series of tips about bash scripting.
Solution
With Bash scripts you can create complex solutions. That makes the need of iterative statements a must. For example, if you want to read and process each line of a file, you will have to use an iterative statement. For those who don't know what an iterative statement is, we can think on it as repeatedly executing a portion of code, known as the loop body, until a controlling expression (or condition) reaches a specific value.
Iterative Statements on Bash Scripting
Basically, Bash Scripts allows three different iterative statements:
- For Loop
- While Loop
- Until Loop
Now I will describe each of these statements and differences.
Bash For Loop
This statement is present in all computer languages. Of course, each language has its own implementation, but the basic idea is still the same. The For Loop repeats the execution of a group of statements over a set of items. Those items can be a sequence of numbers or text strings like the name of files in a directory.
The basic syntax of the For Loop in Bash Scripting is as follows:
for [Iteration Variable] in [Iteration Array]; do [Iteration Statements] ... done
- [Iteration Variable]: This is a scalar variable (i.e. not an array) that holds one item at a time of [Iteration Array].
- [Iteration Array]: This can be either an array variable, a group of numbers or a command that returns a set of items like filenames.
- [Iteration Statements]: This contains the list of statements to be executed each iteration.
Now I will show you different examples of the For Loop. Let's consider the next case when the For Loop contains a sequence of numbers in the iteration array.
#!/bin/bash
echo "Let's count some numbers!:"
for i in {1..5}; do
echo $i
done
echo "Let's count some random numbers!:"
for i in {1,7,5,9}; do
echo $i
done
On the previous script there are two For Loop statements. The first, loops through a set composed of the succession of the numbers 1 to 5. The other For Loop, does this with a set of nonconsecutive numbers. Notice that since we are defining a set, we need to use curly brackets.
The next screen capture shows the output of the execution.
Let's take into consideration the following script when the iteration array is the execution result of a command, which in this case is the sqlcmd command to list the name of the databases in our instance.
#!/bin/bash echo "Let's list our databases!:" for i in $(sqlcmd -S LINUXSQL -U sa -P 'Pa$$w0rd' -Q 'set nocount on; SELECT name FROM sys.databases ' -h -1); do echo $i done
Notice that since we are executing a command to get the iteration array, we need to use the dollar sign and the command enclosed with parenthesis instead of curly brackets like in the previous example.
If you want to use the result of a SQL statement executed with sqlcmd as the source for the iterations, be aware that by default when you execute any SQL statement in sqlcmd the result includes the header and the row count. To avoid that I included "set nocount on" which disables the output of the row count. Also, I used the –h parameter that manages how many rows will be written until the headers are reprinted. This parameter requires that you specify before it the number of rows written until the headers are reprinted, but you can also specify -1 (minus one) which is equivalent to disabling the headers.
Bash While Loop
The While Loop executes a set of commands as long as control condition remains true. If the control condition is a command, then its execution status must return zero for the iteration statements to execute. In the case where the control condition is an expression like a comparison of a variable with another, then the result of this comparison must return true.
Here is the basic syntax of the While Loop:
while [Control condition] do [Iteration Statements] done
You can stop the execution of a While Loop by using the "break" statement. This way even when the control condition remains true the while block stops its execution and Bash keeps running the following steps of the script. Another way to stop the execution of a While Loop in case you accidentally make an endless loop, is to send a kill signal to the process (you must use another terminal) or press CTRL+C. The difference between these methods and the "break" statement is that these methods stop the execution of the While Loop as well as the script and the "break" statement only stops the While Loop execution.
Now I will show you a few examples on how to use the While Loop.
In the next example I will show you a countdown from 5 to zero by using an endless loop with a break statement. Notice that I have to add an IF statement in order to check the value of the variable.
!/bin/bash
echo "Let's do a countdown!:"
VAR=5
while [ $VAR ];
do
echo $VAR
let VAR=VAR-1
if [[ $VAR == 0 ]]; then
break
fi
done
The next screen capture shows the execution of the previous script.
Of course, there is another way that requires less code and is easier to debug, because we get rid of the IF statement and perform the evaluation in the control condition of the While Loop. Take a loop at the next script.
#!/bin/bash echo "Let's do a countdown!:" VAR=5 while [ $VAR -gt 0 ]; do echo $VAR let VAR=VAR-1 done
On the next screen capture you can see that the output of this script is equal to the output of the previous script.
Bash Until Loop
This iterative statement is almost identical to the While Loop, but it has a crucial difference. In the While Loop the execution of the iteration statements continues as long as the control condition remains true; but in the Until Loop the execution of the iteration statements continues as long as the control condition remains false.
Take a look at the next script when I rewrite the countdown from 5 to zero. Notice that in the While Loop the control condition was testing if the variable was greater than zero; in contrary the Until Loop the control condition is testing if the variable is equal to zero.
#!/bin/bash echo "Let's do a countdown!:" VAR=5 until [ $VAR == 0 ]; do echo $VAR let VAR=VAR-1 done
Next Steps
- Stay tuned for the next tips in this series.
- If you haven't read the first tip of this series you can do so here: Introduction to Bash Scripting for SQL Server DBAs.
- If you want to learn more about the source for the iteration array using T-SQL code via sqlcmd, you can take a look at this tip: Introduction to SQL Server's sqlcmd utility.
- Are you new to SQL Server running on Linux? You must read this tip: Getting Started with SQL Server on Linux.
- Are you a SQL Server DBA new to Linux? Here you will find 7 Things Every SQL Server DBA Should Know About Linux.
- In this tip you will find the Top 10 Linux Commands for SQL Server DBAs.
- For more, take a look at SQL Server on Linux Tips Category.
About the author
Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
