Insert query with shell variable with AWK

Shell Programming and Scripting
1

Hi,
I'm a first timer with Unix so pardon my ignorance.
I'm trying to read a comma separated file from the same folder where the script is and insert the value in a DB2 table.
I'm using AWK for the same. I'm getting `)' not expected error.
I'm not sure but for me it doesn't look like detailed error message. :wall:

The input file looks like this:

value1,value2,value3

The script is like this

awk '{
        nbr=split($2,arr,",");
        for(ix=1;ix<=nbr;ix++) 
        product_code=`echo 'print arr[ix]'` 
        db2 "INSERT INTO TABLE_NAME (PRODUCT_CODE) values (\'${product_code}\') 
where NOT EXISTS 
(select * from PRICING_PRODUCT_CODES where PRODUCT_CODE = \'${product_code}\');"
    }' CODES.txt
2

Try:

#gawk '{print "db2 \"INSERT INTO TABLE_NAME (PRODUCT_CODE) values ("$1") etc..\""}'  RS=',|\n' infile                               
db2 "INSERT INTO TABLE_NAME (PRODUCT_CODE) values (value1) etc.."
db2 "INSERT INTO TABLE_NAME (PRODUCT_CODE) values (value2) etc.."
db2 "INSERT INTO TABLE_NAME (PRODUCT_CODE) values (value3) etc.."
db2 "INSERT INTO TABLE_NAME (PRODUCT_CODE) values (value4) etc.."
db2 "INSERT INTO TABLE_NAME (PRODUCT_CODE) values (value5) etc.."
db2 "INSERT INTO TABLE_NAME (PRODUCT_CODE) values (value6) etc.."
3

I changed the query to this

awk '{print "db2 \"INSERT INTO TABLE_NAME (PRODUCT_CODE) values ("$1",) where NOT EXISTS (select * from TABLE_NAME where PRODUCT_CODE=("$1")\""}'  RS=',' PRICING_PRODUCT_CODES.txt

And got the below error

 The error context is
        {print "db2 \"INSERT INTO PRICING_PRODUCT_CODES (PRODUCT_CODE, NETWORK_NAME, ACTIVE) values >>>  ("$1",some <<< 
 awk: 0602-502 The statement cannot be correctly parsed. The source line is 1.
 awk: 0602-512 The string ,somevalue cannot contain a newline character. The source line is 1.
    awk: 0602-540 There is a missing ) character.
4

Try the gawk version.

5

I tried replacing 'awk' with 'gawk' and I get,

gawk:  not found.

Could you please tell me what is wrong with the script I had written the first time.

awk '{         nbr=split($2,arr,",");         for(ix=1;ix<=nbr;ix++)          product_code=`echo 'print arr[ix]'`          db2 "INSERT INTO TABLE_NAME (PRODUCT_CODE) values (\'${product_code}\')  where NOT EXISTS  (select * from TABLE_NAME where PRODUCT_CODE = \'${product_code}\');"     }' CODES.txt

Thanks in advance!:slight_smile:

6

Should be something like this:

awk '{         
nbr=split($0,arr,",")
for(ix=1;ix<=nbr;ix++){
   print "db2 \"INSERT INTO TABLE_NAME (PRODUCT_CODE) values ("arr[ix]") " 
   print "where NOT EXISTS  (select * from TABLE_NAME where PRODUCT_CODE = "arr[ix]");\"" 
   } infile
7

Thanks Klashxx for instant replies.

I changed the script to look like this.

awk '{         
        nbr=split($0,arr,",")
        for(ix=1;ix<=nbr;ix++){
        print "db2 \"INSERT INTO PRICING_PRODUCT_CODES (PRODUCT_CODE, NETWORK_NAME, ACTIVE) values (\'"arr[ix]"\',\'some value\',1) " 
        print "where NOT EXISTS (select * from TABLE_NAME where PRODUCT_CODE = \'"arr[ix]"\');\"" 
    }' PRICING_PRODUCT_CODES.txt

And I got this error

0403-057 Syntax error at line 33 : `)' is not expected.

Basically, I want insert queries like this

Insert into table(col1,col2) values('valuefromfile1','staticvalue1') where ....;
Insert into table(col1,col2) values('valuefromfile2','staticvalue2') where ....;
8

That's not the requirement you gave first , post a clear input file and the expected output.

9

I am able to generate the queries as desired. But I don't know how to execute them from inside the awk block?