using for in loop with sql output

Question

I have a roles.txt file that contains

admin
user

SELECT ROLE_NAME FROM SENTRY_ROLE WHERE ROLE_NAME produces:

admin

Im trying to echo all the roles from roles.txt that are not in the sql_output (user in this example). This is what i have so far:

for filename in roles.txt
do
sql_output=$(mysql -N -e "SELECT ROLE_NAME FROM SENTRY_ROLE WHERE ROLE_NAME = '$filename';") 


if [ -z "$sql_output" ]
then
  echo "sql_output is empty" #this is where i want to echo all the roles that are not in the output of sql_output AKA "user"
else
  echo "\$sql_output is in sql_output"
  fi
done

glenn jackman · Accepted Answer · 2018-04-25 14:51:00Z

1

That's not how you iterate over the lines of a file. See http://mywiki.wooledge.org/BashFAQ/001

I would do this, with a single mysql call:

# Iassume this does not produce any extraneous output,
# just a list of role names, one per line
sql_output=$( mysql -N -e "SELECT distinct ROLE_NAME FROM SENTRY_ROLE" ) 

missing_roles=$( comm -23 roles.txt <(echo "$sql_output") )

See http://manpages.ubuntu.com/manpages/bionic/en/man1/comm.1.html

answered Apr 25, 2018 at 14:51

glenn jackman

249k42 gold badges233 silver badges362 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

TheNewGuy Over a year ago

Trying to make this work, its not liking echo $sql_output. syntax error near unexpected token ('. comm -23 test.txt <(echo "$sql_output") )'

glenn jackman Over a year ago

That is bash Process Substitution syntax: use #!/bin/bash or if you need to use /bin/sh, then redirect the mysql output to a file and use that filename in place of the <(echo ...)

TheNewGuy Over a year ago

the reason i was using if then is that if there are missing roles i wanted to create them with sql command

glenn jackman Over a year ago

If you have an index on that table, you might want to consider using INSERT IGNORE and then you don't need to do the query and filtering at all.

Collectives™ on Stack Overflow

using for in loop with sql output

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related