Friday, October 7, 2022

[SOLVED] Bash script with a SQL query which returns multiple rows and 2 columns. And i want to loop through each row and store it in variable

Issue

I am working on a Bash script which ha a sql query in it and it returns multiple rows and 2 columns. example output:

|db_name | used_% |
|--------|--------|
|row 1   |29%     |
|row 2   |45%     |
|row 3   |60%     |

and so on

I want to loop through each row in the table and store db_name and its used_% as variables. and i want to run the script every 10 seconds.

I am at a point where my code is giving me list of db_name and used_% as a single variable.

My code:

#!/bin/bash

while :
do 
 QUERY=$("select db_name, used_% from Table1;")
 DBNAME=$(echo "$QUERY" | cut -d"|" -f1)
 USED_PERCENT=$(echo "$QUERY" | cut -d"|" -f2)
 echo "PUTVAL \'$DBNAME'/gauge-used_percent\" interval 10 N:${USED_PERCENT#"${USED_PERCENT%%[!:space:]]*}"}"
 sleep 10
done

I would really appreciate if someone can help me with the logic to loop over the db_names and its used_%.

Thanks!


Solution

I guess you are looking for something like

#!/bin/bash

sqlclient "select db_name, used_% from Table1;" |
while read -r dbname used_pct; do
   echo "PUTVAL \'$dbname'/gauge-used_percent\" interval 10 N:$used_pct"
done

where sqlclient should be replaced with the command to actually run the SQL query, ideally without headers or other formatting in the output.



Answered By - tripleee
Answer Checked By - Cary Denson (WPSolving Admin)
at