Introduction to Bash Scripting for SQL Server: Declaration of Variables and Constants

By: Daniel Farina   |   Updated: 2018-11-27   |   Comments   |   Related: > SQL Server on Linux

Problem

You're starting to work with SQL Server running on Linux. When reviewing the system, you noticed that someone setup backup tasks executing inside a Bash script and you have no idea how to work with Bash scripts. In this tip I will show you the basics on how to use variables and constants in a Bash script.

Solution

Every scripting language allows the user to work with variables and the most advanced allow the use of constants. For example, if you ever edited a batch file in the MS-DOS days you know that there was no chance to use constants per se. Of course, constants are a special case of variables that don’t allow modifications. What you can do with a constant, you can also do with a variable if you never change its value and that was what we had to do in MS-DOS batch files.

Variables and Constants in Bash Scripting

Bash scripts allows variable declaration as well as constant definition. There are two ways to declare a variable. The easiest is as follows:

MyVariable=Content

Notice that there is no space between the variable name, the equal sign and the content of the variable. When we use this method for declaring variables, the content of the variable can be a string or an integer. In other words, it is not a typed variable.

When you want to refer to the content of a variable, like when you write it on the screen, you just have to put a dollar sign in front of the variable name.

echo $MyVariable

But if you want to do some operations other than displaying the value of a variable like performing a sum or concatenation you don’t have to use the dollar sign. You just have to use the variable names. If you find this confusing you can use the following rule of thumb. If you are writing a script and need to refer to the value of a variable and not to the variable itself just put a dollar sign in front of it.

Take a look at the next example when I am creating a script with one string variable and one integer variable, then I echo both variables.

#!/bin/bash

MyVariable="I will do some math!: "
Number=1

echo $MyVariable $Number + $Number = $((Number + Number))

The last line of the code may look complex, but it is not. Starting from left to right the "+" and the "=" signs are considered as text. The echo command echoes characters not variables. Remember the rule of thumb I told you? By putting the dollar sign I am referring to the text value of the variables. The other part of the statement, $((Number + Number)), pay close attention to the parenthesis. Parentheses are always evaluated from the inner to the outmost, a basic rule of algebra. The first parenthesis is (Number + Number), notice that Number is not preceded by a dollar sign. So according to the rule of thumb, we are referring to the variable Number and not to its content. Since the Number variable is numeric, we are doing a sum. Now the outer most parenthesis is preceded by a dollar sign so we are referring to the value of the expression (Number + Number).

I created a graphic to show what that line does.

On the next screen capture you will see the output of the previous script execution.

Using the Declare Statement in Bash Scripts

It could be the case that you may want to force variable typing. For example, you may want to declare a variable in a fashion that it only can be assigned numbers. In such case you have to use the declare statement as follows:

declare option MyVariable=Value

If you change the "-" and use a "+" it makes the opposite of the option. For example, if you want to declare a numeric variable you will use "-i", if you want to declare a string variable you have to use "+i".

I have rewritten the previous script using strict types. Also, I forced the MyVariable variable as a constant (i.e. by setting the variable with the –r option as read only I am declaring it as a de facto constant). Take a look.

#!/bin/bash

declare +i -r MyVariable="I will do some math!: "
declare -i Number=1

echo $MyVariable $Number + $Number = $((Number + Number))

As you can see on the next image the output is identical.

Now let’s see with the next example what happens when we try to write to a constant or a string in an integer variable.

#!/bin/bash

declare +i -r MyVariable="I will do some math!: "
declare -i Number=1
MyVariable="something"
Number="blah"
echo $MyVariable $Number + $Number = $((Number + Number))

As you can see on the next image, bash reports that we have an error on the line that violates the read only restriction for MyVariable variable. Additionally, take a look at the sum and you will notice that even when the assignment of a string into an integer variable didn’t report any error, it behaves as if we had set the variable to zero.

Another Way to Declare Constants

There is also another way you can use to declare constants which consists on using the keyword "readonly" instead of "declare".

readonly MyConstant=Value

Here is how to rewrite the previous script using readonly instead of declare.

#!/bin/bash

readonly MyVariable="I will do some math!: "
declare -i Number=1

echo $MyVariable $Number + $Number = $((Number + Number))

On the next screen capture you can see that the result is exactly the same.

Defining Arrays in a Variable in Bash Scripting

Bash also allows us to define arrays in our scripts. To do so we have to use parenthesis and spaces to separate each item. Take a look.

MyArray=(this is an array)

In the previous example the variable MyArray is an array that contains 4 items. The first item has the position zero. In the next table, you can see each position with its corresponding value.

In order to refer to a specific item in the array you have to use braces.

#This references the position number 2 of the array. Remember that it starts from zero
MyArray[2]=something

And if you want to refer to its contents you must add curly braces; and of course, the dollar sign (remember the rule of thumb?).

#This references to the content of position number 2 of the array.
echo ${MyArray[2]}

The next code will aid your understanding.

#!/bin/bash

MyArray=(this is an array)

#Setting MyVar Variable with last two items of MyArray
MyVar=(${MyArray[2]}${MyArray[3]})

echo $MyVar

#Replacing position two.
MyArray[2]="was"

echo ${MyArray[2]}

On the next screen capture you can see the previous script execution.

As you can see in the previous image, items in an array start with zero like in the C language.

Creating Arrays with the Declare Statement

We saw that we can use the declare statement to define a variable as an array. It gives us the advantage that we don’t need to specify the value for its items in the declaration.

declare –a MyArray

If you take a look at the next example you will see that I have declared an array without specifying any boundaries and I set position two and three with data without defining positions zero and one.

#!/bin/bash

declare -a MyArray

MyArray[2]="Hello"
MyArray[3]=" World"

echo ${MyArray[2]}${MyArray[3]} 

echo Array at Position 0: ${MyArray[0]}

On the next screen capture you can see the result of the execution of the previous script.

Bash Scripting Associative Arrays

In the previous section we saw numeric arrays. They are called numeric because the index is in numeric form (i.e. MyVar[0], MyVar[2]). Now we will learn about associative arrays, in other words, arrays whose index is a keyword instead of a number. In order to create associative arrays, we must use the –A option in the declare statement as follows.

declare –A MyArray

Take a look at the next example where I create an associative array that at its first position named sqlcmd has the path to the sqlcmd program. Additionally, the second position named BackupFolder contains the directory to save the backups; and finally, at the last position named BackupLog is the log file path.

#!/bin/bash

declare -A MyArray

MyArray[sqlcmd]="/opt/mssql-tools/bin/sqlcmd"
MyArray[BackupFolder]="/var/backups"
MyArray[BackupLog]="/var/log/sqlbackup_$(date +%Y-%m-%d.%H:%M:%S).log"

echo ${MyArray[sqlcmd]}
echo ${MyArray[BackupFolder]}
echo ${MyArray[BackupLog]}

Notice that on the declaration of BackupLog item I used the expression $(date +%Y-%m-%d.%H:%M:%S) in the text string. When bash parses that line it replaces the expression $(date +%Y-%m-%d.%H:%M:%S) with the output of the execution of the command "date +%Y-%m-%d.%H:%M:%S" which is the current date in in the format YYYY-MM-DD.hh:mm:ss.

On the next screen capture you can see the output of the previous script as well as the result of executing the "date +%Y-%m-%d.%H:%M:%S" command.

Next Steps

• Stay tuned for the next tip in this series.
• Are you new on 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 tips, 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