How to store results of multiple sql queries in shell variables in Bash?

Question

I need to store in array the results retrieved from the database. So far this is basically what I'm trying with no success.

 Myarray=$(echo "SELECT A, B, C FROM table_a" | mysql database -u $user -p$password)

My understanding of bash commands is not very good as you can see. thank you.

Solution 1

This works:

#!/bin/bash
results=($(mysql --user root -pwelcome ts -Bse "SELECT type, network_id, subnet_msk FROM remote_subnet;"))

cnt=${#results[@]}
for (( i=0 ; i<cnt ; i++ ))
do
    echo "Record No. $i: ${results[$i]}"
    fieldA=${results[0]};
    fieldB=${results[1]};
    fieldC=${results[2]};
done

Solution 2

The following worked for me:

results=($(executeQuery "SELECT fieldA, fieldB FROM tableName;")) count=${#results[@]} echo $count for (( i=2 ; i<count ; i++ )) do fieldA=${results[$i]}; echo $id i=$((i+1)) fieldB=${results[$i]}; echo $fieldB done

Comments

[G-Man Says 'Reinstate Monica']

What you have looks like a good start. What do you mean by "no success"? What happens when you do this?

[Fimo]

i have many error like 'mysql Ver 14.14 Distrib 5.5.40, for debian-linux-gnu (x86_64) using readline 6.3 Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Usage: mysql [OPTIONS] [database] -?, --help Display this help and exit. -I, --help Synonym for -? --auto-rehash Enable automatic rehashing. One doesn't need to use 'rehash' to get table and field completion

[G-Man Says 'Reinstate Monica']

So, does echo "SELECT A, B, C FROM table_a" | mysql database -u $user -p$password produce the results that you want to capture? If it doesn't, you need to get that working first, and then worry about the command substitution.

[bwDraco]

Can you explain what this code does and how it solves the OP's problem?

[Marianna]

Did you test this? Are you sure the myarray=$ should have a "$"? Are you making some assumptions about the format of the data being returned from the query? Bash only supports single dimensional arrays? How are you dealing with the rows and columns? This looks too easy.

[jherran]

It does what he originally asked, store the result of the query in a bash variable. It didn't deal with neither rows nor columns.

[crafter]

cnt=${#results[@]} is giving the number of characters in the returned output, not the number of lines.

[Zenobia Panvelwalla]

"executeQuery" is a function that will execute my SQL query in MySQL

[Zenobia Panvelwalla]

mysql -h $dbHostName -u $dbUserName -p$dbPassword -D $dbName -N -e "$inputSQL" . => The '-N' allows you to start the for loop from 0 and not 2.