CODEWITHSUNDEEP
awksedsql-parser
1stenjoydmoment
1stenjoydmoment

Reputation: 249

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

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?

Upvotes: 0

Views: 123

Answers (2)

Ed Morton
Ed Morton

Reputation: 203655

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.

Upvotes: 2

RavinderSingh13
RavinderSingh13

Reputation: 133528

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.

Upvotes: 4

Related Questions