Read paragraph from file

Shell Programming and Scripting
1

Hi,

I have a file containing SQL commands in following format. I need to run the SQLs separately and also print the status of SQL, successful/unsuccessful.

File : SQL.dat

## SQL1
select * from dual;

## SQL2
select user from dual10;

Expected output:

SQL1:PASS
SQL2:FAIL

Started with kind of a FOR loop (as mentioned below). But this prints the entire SQLs in file. I want SQL1 to be executed then SQL2 and so on. Please suggest.

for Code in $(cat SQL.dat | egrep "^##")
do
    SQLID=`echo "$Code" | tr -d "##"`
    SQL=`cat SQL.dat | sed -n "/$Code/,/;/p"`
    RunSQL $SQL
done

Thanks for your time.

3

Don't use for to read lines. Use a while read loop.

This will loop over the file and grab the statement name from your ## lines. probably only good for single-line statements

#!/bin/bash

while read -r sql; do
  if [[ $sql = \#* ]]; then
    stmt=${sql#* }
  elif [[ $sql = *[![:space:]]* ]]; then  # has something besides blank
    if RunSQL "$sql"; then
      echo "$stmt:PASS"
    else
      echo "$stmt:FAIL"
    fi
  fi
done < "SQL.dat"
4

Thanks for your reply.

But the code suggested does grep for SQL commands if spanned across multiple lines.

5

You can try this adaption of neutronscott's proposal, but be aware it needs an empty line at file end:

while read -r TMP
  do if [[ $TMP = \#* ]]
       then     stmt=${TMP#* }
       elif [[ $TMP = *[![:space:]]* ]]
         then sql="$sql $TMP"
         else
           if RunSQL "$sql"
             then       echo "$stmt:PASS"
             else       echo "$stmt:FAIL"
           fi
         sql="";
       fi
  done < file

If that can't be taken for granted, you'll need to add the entire RunSQL paragraph again at script end. Or you could go for the terminating semicolon.