what is the better way read a Block of SQL statements from a file using Shell Script?

Question

I am having a file like below , which has many SQL statements. I want to read a particular block of SQL whenever needed.

MyFile.SQL

#QUERY1
SET ECHO OFF NEWP 0 SPA 0 PAGES 0 FEED OFF HEAD OFF TRIMS ON TAB OFF
SET VERIFY OFF
SET NUMFORMAT 99999999999999999
Select max(time_created) from test.table1 where cust=1;
EXIT;

#QUERY2
SET ECHO OFF NEWP 0 SPA 0 PAGES 0 FEED OFF HEAD OFF TRIMS ON TAB OFF
SET VERIFY OFF
SET NUMFORMAT 99999999999999999
Select count(*) from test.table1 where cust=1;
EXIT;


#QUERY3
SET ECHO OFF NEWP 0 SPA 0 PAGES 0 FEED OFF HEAD OFF TRIMS ON TAB OFF
SET VERIFY OFF
SET NUMFORMAT 99999999999999999
Select count(*) from test.table12 where acct=1;

EXIT;

I am using below command

x=$(cat test.sql )
echo $x | awk -F'COUNT_QUERY' '{ print $0 }'

Could someone help with some good approach to this?

Could you please do add sample of expected output into your post and let us know and let us know then. — RavinderSingh13, Jul 15 '20 at 07:18
@RavinderSingh13 : If I want to extract only QUERY2 then My expected output should be #QUERY2 SET ECHO OFF NEWP 0 SPA 0 PAGES 0 FEED OFF HEAD OFF TRIMS ON TAB OFF SET VERIFY OFF SET NUMFORMAT 99999999999999999 Select count(*) from test.table1 where cust=1; EXIT; — 1stenjoydmoment, Jul 15 '20 at 07:30

RavinderSingh13 · Accepted Answer · 2020-07-15T12:56:51.070

4

Could you please try following, written and tested with shown samples in GNU awk.

awk '/^#QUERY2$/{found=1} found; /^EXIT/ && found{exit}'  Input_file

Explanation: Adding detailed explanation for above.

awk '                 ##Starting awk program from here.
/^#QUERY2/{           ##Checking condition if line starts with #QUERY2 then do following.
  found=1             ##Setting found value as 1 here.
}
found;                ##Checking condition if found is SET then print that line.
/^EXIT/ && found{     ##Checking condition if line starts with EXIT and found is SET then do following.
  exit                ##exit from program from here.
}
'  Input_file         ##Mentioning Input_file name here.

edited Jul 15 '20 at 12:56

answered Jul 15 '20 at 07:35

RavinderSingh13

130,504
14
57
93

1

@1stenjoydmoment, it check if line has `#QUERY2` then printing it from that line to `EXIT` so is string query2 present in your file? – RavinderSingh13 Jul 15 '20 at 07:45
Yes. Its working. Thanks for the pointer. My bad I ran with another file. can please provide link for this documentation "shown samples in GNU awk" for my understanding – 1stenjoydmoment Jul 15 '20 at 07:49
@1stenjoydmoment, I have added detailed explanation for above and for learning awk you could use https://stackoverflow.com/tags/awk/info which has great learning info in it cheers. – RavinderSingh13 Jul 15 '20 at 08:04

Ed Morton · Answer 2 · 2020-07-15T12:59:06.887

Data like yours where records are separated by blank lines is why awk has a "paragraph mode" that's activated by setting RS to null:

$ awk -v RS= '/^#QUERY2\n/' file
#QUERY2
SET ECHO OFF NEWP 0 SPA 0 PAGES 0 FEED OFF HEAD OFF TRIMS ON TAB OFF
SET VERIFY OFF
SET NUMFORMAT 99999999999999999
Select count(*) from test.table1 where cust=1;
EXIT;

See When RS is set to the empty string ... in https://www.gnu.org/software/gawk/manual/gawk.html#Multiple-Line for more info.

what is the better way read a Block of SQL statements from a file using Shell Script?

2 Answers2