Reading Multi Line SQL in UNIX

Shell Programming and Scripting
1

Hello,

Currently, I am reading few queries from the below .sql file

--SOURCE TABLE NAME
--SOURCE QUERY
SEL COL1, COL2, COL3, COL4, 
COL5, COL6, COL7 WHERE COL5 = '2015-11-04 16:24:00.000000' FROM SOURCE TABLE;

--TARGET TABLE NAME
--TARGET QUERY
SEL COLUMN1, COLUMN2, COLUMN3, COLUMN4, 
COLUMN5, COLUMN6, COLUMN7 FROM TARGET TABLE;
0,1

The code used to read the contents of the .sql file is being displayed below:

validate() {
    queryNum=0
    while true
    do
        ((queryNum++))
        read tableName
        read comment
        read sourceQuery 
        read blankLine
        read tableName
        read comment
        read targetQuery 
        read primaryKeyCols || break
        read blankLine
        exQuery "$sourceQuery" sourceResults.txt 
        exQuery "$targetQuery" targetResults.txt         
    done < $1
}

I am using Bash. The only disadvantage with this approach is, I can't read the SQL query if it is written in multi lines. It has to be on a single line to make it work.
I want to be more flexible while reading the queries. Is there any way that I can read the multi line SQL in unix. Please help me out.

Thanks for your help.

Regards,
R

---------- Post updated 12-01-15 at 11:30 AM ---------- Previous update was 11-30-15 at 01:28 PM ----------

---------- Post updated at 12:34 PM ---------- Previous update was at 11:30 AM ----------

Any insights on this?

2

Very difficult parse. Are you sure there can be some kind of arbitrary 0,1 on a line like you have at the end of your sample? It would be easy if all non-sql lines were either empty or contained comments.... but that 0,1... what's up with that?

3

Well, those are Primary Key columns.
Those are mandatory without those 0,1..I wont be able to compare source and the target columns..But I can try putting them in comments..

4

What if after your "read SourceKey"
you check to see if the last character is a comma
then, if so, do a "read SourceKey2" & combine the two

Repeat for the other key field

5

Alternatively, if you want to find the queries, knowing the allowed query keywords (e.g. SEL, SELE, SELEC, SELECT if all are possible and any other valid SQL keywords)... this would also make the parse pretty trivial.

I'd read the whole line and see if it begins a query (however you want to do that)... then set a flag to be in a query... and look for line (could be same line) ending in semi-colon to terminate the query. Skipping lines that can't be a query.