-2

The output of my sql query has multiple columns and contains string values which contains space. I need to write bash script where in i have to read values into variable and then use it further in the script also insert them into another database.

When i store output into an array the string value gets split based on space and stored into different indexes in array. How can i handle this situation in bash script.

CMD="SELECT * FROM upload where upload_time>='2020-11-18 00:19:48' LIMIT 1;"
output=($(mysql $DBCONNECT --database=uploads -N --execute="$CMD"))
echo ${output[9]}

Output:
version test_id upload_time parser_result 25 567 2020-11-18 00:19:48 <p1>box crashed with exit status 0</p1>

The upload time "2020-11-18 00:19:48" gets stored in two indexes. The more problematic is 'parser_result' value which is string. '<p1>box crashed with exit status 0</p1>' gets stored in different indexes splitting based on space.

${output[8]} contains '<p1>box'
${output[9]} contains 'crashed'

Database is very huge and i need to parse every row in it. Since string value can be anything i am unable come up with generic code. What is the best way to handle this scenario. Am using bash scripting for the first time!! I have to use bash script since this script will run as a cron job inside docker container.

Ashwini
  • 21
  • 3

1 Answers1

1

The fields are separated by TAB. Use that as your $IFS to parse the result.

IFS=$'\t' output=($(mysql $DBCONNECT --database=uploads -N --execute="$CMD"))
echo "${output[9]}"

If $DBCONNECT contains options separated with spaces, you need to do this in two steps, since it's using $IFS to split that as well.

result=$(mysql $DBCONNECT --database=uploads -N --execute="$CMD")
IFS=$'\t' output=($result)
echo "${ouptut[9]}"
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • Thank you. But am getting below error with changes you suggested. ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/run/mysqld/mysqld.sock' (2) – Ashwini Oct 11 '21 at 22:11
  • See https://stackoverflow.com/questions/13365553/setting-an-argument-with-bash for the correct way to put multiple command parameters in a variable like `$DBCONNECT`, instead of separating them with spaces. – Barmar Oct 11 '21 at 22:13
  • Thank you so much it did solve my issue. Thank you !!!!! – Ashwini Oct 11 '21 at 22:25