how to get data with spaces from database in the same variable using shell scripting

Question

When I run the following code, the data being stored in variables department, testDate and testTime becomes mixture of each other when there is space in data, e.g if 'computer science' is the department name then 'computer' is stored in variable department and 'science' is stored in testDate variable, but obviously I want to store the whole 'computer science' in variable department and also data for variables
testDate and testTime as there is also problem that you can observe in the output. How can I fix this problem?

mysql -uroot -proot -Dproject_ivr_db -rN --execute "SELECT Dpartment,TestDate,
TestTime FROM entrytests_datetime WHERE Discipline='msc'" |
while read department testDate testTime 
do

    echo "V,department=$department"
    echo "V,testDate=$testDate"
    echo "V,testTime=$testTime"

done

echo "E,resume"

output:

  V,department=computer
  V,testDate=science
  V,testTime=first february 2013    nine thirty a m
  V,department=electronics
  V,testDate=first
  V,testTime=february 2013  ten thirty a m
  E,resume

Do you mean using parameters or variables like this link: http://stackoverflow.com/questions/10229324/pass-parameter-to-mysql-script-command-line ? — Cyber Slueth Omega, Feb 21 '13 at 06:48

score 1 · Answer 1 · edited Jun 20 '20 at 09:12

Basically, you have to ensure that the fields are separated in the output from mysql by some character other than a space, and you have to tell the shell about which character that is.

Telling the shell is done with the IFS variable.

Telling the mysql command is done with the --batch or -B option:

--batch, -B

Print results using tab as the column separator, with each row on a new line. With this option, mysql does not use the history file.

Batch mode results in nontabular output format and escaping of special characters. Escaping may be disabled by using raw mode; see the description for the --raw option.

So:

IFS="   "   # Tab (only) in quotes
mysql -B -uroot -proot -Dproject_ivr_db -rN --execute "SELECT Dpartment,TestDate,
TestTime FROM entrytests_datetime WHERE Discipline='msc'" |
while read department testDate testTime 
do

    echo "V,department=$department"
    echo "V,testDate=$testDate"
    echo "V,testTime=$testTime"

done

echo "E,resume"

how to get data with spaces from database in the same variable using shell scripting

1 Answers1