I am unable to read single VALUE from mysql Query in awk.I am able to read it in bash but not in awk. I want to read the result variable in awk as I want to fire the query only based on some dynamic conditions written in awk.

awk '{
 cmd="(mysql --host='abc' --user='def' --password='..' --database='ghf' -sNe \"select VALUE from table where KEY='171-125';\")"
 cmd|getline $lastdiff;
 print "diff is "lastdiff;

diff is

diff is

I have tried with grep -Eo \" [0-9]* \" also/ It is not helping. I have used both system() or cmd | getline to get the result into variable but it is always returning 0.

 sudo awk 'BEGIN {
>  myvalue=system("mysql --host='abc' --user='def' --password='..' --database='ghf' -sNe \"select VALUE from table where KEY='171-125';\" ");
>  print "diff is ",myvalue;
> }'
diff is  0

Bash is giving the result

mysql --host='abc' --user='def' --password='..' --database='ghf' -sNe "select VALUE from table where KEY='171-125';"

I want to read the numeric VALUE into lastdiff variable in awk. Any recommended solutions.

  • system() will return the exit status of the command, not the output of it. – Kusalananda Aug 22 '17 at 13:51
mysql ... | awk '{ print "diff is", $0 }'

Or just

mysql ... 'SELECT CONCAT("diff is ", VALUE) FROM ...'

From within awk:

awk 'BEGIN { cmd = "mysql ..." }
     { cmd | getline value;
       printf("diff is %s\n", value); # or print "diff is", value;
       close(cmd) }'

This will run the command once for each line of input to the awk script. Without the close(), it would be run once and give value the value of each successive output line of the command.

Your script uses $lastdiff rather than lastdiff with getline. In awk, $lastdiff refers to field number lastdiff of the current input record.

  • It is giving the output as 0 while the expected value is 1.Thanks. – Aashu Aug 22 '17 at 10:50
  • Change the format specifier in printf() from %d to %s. The string read may not be a pure integer? – Kusalananda Aug 22 '17 at 10:55
  • with %s empty output just like print "diff is", value. It is printing only "diff is ".It is floating field. Even with %f it is taking 0.000. The expected output should be 1. – Aashu Aug 22 '17 at 10:59
  • @Aashu I tested the exact code above with cmd = "ls /" and it works for me. I get diff is followed by the first name from my root partition. Make sure that cmd is set correctly (quoting and such things). – Kusalananda Aug 22 '17 at 11:01
  • @Aashu Also, the command should not be in (...). – Kusalananda Aug 22 '17 at 11:05

You will need to create the variable in bash before passing it to awk with the -v flag and so:

cmd=$(mysql --host='abc' --user='def' --password='..' --database='ghf' -sNe \"select VALUE from table where KEY='171-125';\")
awk -v cmd=$cmd '{ some awk script }'

Alternatively, if you want to parse the output of mysql, do:

awk '{ some awk script }' <<< $cmd
  • I want to fire the query only based on the certain dynamic condition in awk.Thanks in between. – Aashu Aug 22 '17 at 10:33
  • 1
    You can fire the query from within awk with the system function but getting the returned output back into a variable will be difficult and the system function itself runs the risk of injection. – Raman Sailopal Aug 22 '17 at 10:37
  • @Aashu This was not clear from the question. – Kusalananda Aug 22 '17 at 10:37
  • @Kusalananda My bad,I have re-phrased the question. – Aashu Aug 22 '17 at 10:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.