Set variables from file, then loop

Shell Programming and Scripting
1

Hello,

I am very, very new to shell scripting, but what I'm attempting to do is read in a list of user ID's to create on a database system from a CSV flat file, and for each entry run the "create user" script. I've gotten pretty far but I'm having trouble with the looping mechanism.... Any input would be greatly appreciated.

FILE=/path/to/the/infile
cat ${FILE} | sed 's/,/ /g' | while read ID
do
set${ID}

The rest of the code is a SQL script that uses ${ID} as the username. How do I wrap the SQL script into the loop so that it will execute the script for each entry in the file?

Things that work as expected are: passing the variable into the SQL script (was able to run some tests and get it working), and executing the SQL script from the .ksh against the target database system.

Thanks in advance.

*edit*
Edited to add, currently it sets the variable ${ID} as every item in the CSV file, without the commas and with a space inbetween, which of course causes a syntax error in the SQL.

2

Can we see the entire script, please?

3

Sorry. I thought it might be overkill. Script is below (edited out a few things like passwords).

FILE=/path/to/infile
cat ${FILE} | sed 's/,/ /g' | while read ID
do
set${ID}

bteq <<-EOF
.logon $LOGON_STRING_DEV;

CREATE USER ${ID} FROM PROD_JOB_USERS  AS
       PERM=0,                            
       PASSWORD = ********             
       ACCOUNT='********'            
       DEFAULT DATABASE=PROD_DB;       
GRANT  DROP DATABASE ON ${ID} TO ALL PROD_DBA;                        
GRANT  DROP USER ON ${ID} TO ALL PROD_DBA;                        
GRANT  ALL ON ${ID} TO DBC WITH GRANT OPTION;
GRANT BATCH_JOB_ROLE TO ${ID};
MODIFY USER ${ID} AS DEFAULT ROLE = ALL;             

.quit;
EOF
done
4

Can you display a sample of your csv file?

5

Sure, the entire contents of the CSV are below:

NB203A,NB203B,NB203C,NB203D,NB203E,NB203F,NB203G,NB203H,NB203I,NB203J,NB203K,NB203L,NB203M,NB203N,NB203O,NB203P,NB203Q,NB203R,NB203S,NB203T,NB203U,NB203V,NB203W,NB203X,NB203Y,NB203Z
6

What does set${ID} do?

What you need to do is build up a file with the appropriate commands and then feed that to bteq afterwards.

CMD_FILE=/tmp/a

echo ".logon $LOGON_STRING_DEV;" > $CMD_FILE
cat ${FILE} | sed 's/,/ /g' | tr ' ' '\n' | while read ID
do

cat <<EOF >> $CMD_FILE
CREATE USER ${ID} FROM PROD_JOB_USERS  AS
       PERM=0,                            
       PASSWORD = ********             
       ACCOUNT='********'            
       DEFAULT DATABASE=PROD_DB;       
GRANT  DROP DATABASE ON ${ID} TO ALL PROD_DBA;                        
GRANT  DROP USER ON ${ID} TO ALL PROD_DBA;                        
GRANT  ALL ON ${ID} TO DBC WITH GRANT OPTION;
GRANT BATCH_JOB_ROLE TO ${ID};
MODIFY USER ${ID} AS DEFAULT ROLE = ALL;             
EOF

done

echo ".quit" >> $CMD_FILE

cat $CMD_FILE | bteq

Something like this. Notice how you need lines in order for the whileloop to work as you imagine, so I made each entry a new line instead of it being on the same line.

7

You can loop this way:

for mEach in $(tr ',' '\n' < ${FILE}); do
  echo "Each <${mEach}>"
done
1 Like
8

That did the trick! I modified it to fit of course, but that did the trick. Thank you very much!

I also removed

set ${ID}

per Nitrodist's suggestion. I don't think it was really doing anything.

9

That crams the entire file into a variable, which may silently ignore anything too large to fit in one shell variable.

If you have BASH:

while read -d , VAR
do
        echo $VAR
done < filename

If you don't, a temp file:

tr -d ',' '\n' < infile > /tmp/$$
while read VAR
do
        echo "$VAR"
done < /tmp/$$
rm -f /tmp/$$