2

I am trying very simple thing using shell script on BASH shell. I am trying to get keyids of all Users into an array in a shell script. I will later loop them for further processing.

I wrote very simple code. But I am stuck while converting a space separated QUERY_RESULTS string into an array.

I tried various options e.g. set -A , read -a, array=( $ QUERY_RESULTS } ) etc. Nothing is working for me. Every-thing I try, gives me some error.

For e.g.

  • set -A gives me illegal option -A.
  • read -a also gives me illegal option -a.
  • array=( $ QUERY_RESULTS } ); gives me ( unexpected.

Below is my code. Please help me here. 

#IFS="";
echo "Get list of Active Users from Table ";
QUERY_RESULTS=$( mysql -u username password -e "SELECT keyid from users where isActive = 1" );
echo $QUERY_RESULTS;
#eval `read -a arr <<$QUERY_RESULTS`;
#array=( $ QUERY_RESULTS } );
echo $array;
Learn More
  • 1,535
  • 4
  • 29
  • 51
  • Does your shell support arrays? Which shell is this? – melpomene Jan 28 '13 at 16:13
  • That seems unlikely because bash's `read` does support `-a`. – melpomene Jan 28 '13 at 16:21
  • Your question gave me hints, but ultimately I need more. If you want to capture the result of a MySql query into an array, you can do this: https://stackoverflow.com/a/57061108/470749 – Ryan Jul 16 '19 at 15:51

3 Answers3

2

Did you try something like this:

read -r -a array <<< $QUERY_RESULTS

Note the "<<<" instead of "<<"

If the contents of QUERY_RESULTS consist of multiple lines you may want to do it this way:

mapfile -t array <<< $QUERY_RESULTS
William
  • 4,787
  • 2
  • 15
  • 14
1

I could achieve my target using following code. However still I am having problem in converting QUERY RESULT to string.

echo "Get list of Active Users from Table ";
QUERY_RESULTS=$( mysql -u username password -e "SELECT keyid from users where isActive = 1" );

for i in `echo $QUERY_RESULTS | tr ' ' '\n' |sed  '1d' | tr '\n' ' '`
do
sh doAction.sh $i;
done
Learn More
  • 1,535
  • 4
  • 29
  • 51
1

In bash, you can get the array and string as below:

query="SELECT keyid from users where isActive = 1;" 
QUERY_RESULTS=($(mysql -u username password -e "$query"))

or 

QUERY_RESULTS=(`mysql -u username password -e "$query"`)
QUERY_RESULTS_STR=$(printf "%s," "${QUERY_RESULTS[@]}")

Pls do notice that you need the "()" out of the mysql cmd, which make the result a bash array that can be used in a loop. And "%s,", the "," is the delimiter to separate the elements when change to str, you can change to other delimiter if needed.

zhihong
  • 1,808
  • 2
  • 24
  • 34