Calling SQL statements through system in awk to illustrate the use of quotation marks

A practical example: in awk, the database is connected through the system call and a select operation is made, and the Where condition in the SELECT statement comes from the first domain ($) of a file.

Cat file  1001100210031004  ...

1. Use awk to achieve:

awk ' {System ("Mysql-u root-proot mydatabase-bse \" Select Id,name from tables where id= "" \047 "$ \047" "\042")}
   
     '
    file
   

Why is the statement like the above possible to achieve the purpose? First, let's break down the statement in detail, because with so many quotes and escape characters, it does seem to make a big head. Look directly at the contents of the system statement (note: To see more clearly, the substrings separated by double quotation marks are separated by a space and a carriage return.) ）：

" mysql-u root-proot mydatabase-bse \ "Select Id,name from tables where id= " " \047 "  $ 1 " \047 " " \042 "

Explanation: \047 and \042 are both single-and double-quote escaped symbols (they can be seen in the shell terminal by Echo-ne "\047″ and Echo-ne" \042″ output, and the matching quotes are marked in the same color.) \ "Paired with \042, \047 and \047, and then stitching together the 5 pieces to form a complete statement.

2. The statement can also be written as:

awk ' {System ("Mysql-u root-proot mydatabase-bse \" Select Id,name from tables where id='\ '""$ 1"\" \ "")}'  file

quotation marks decomposition:

awk '{System ("... id='\'      '      " $"    '\ ''\"     ")}'file    #^The outermost quotation marks (either single or double quotes) are interpreted by the shell, and the shell treats the contents of the quotation marks as a non-detachable whole, removes the outermost quotation marks and passes them to awk, and the double quotation marks are joined together with no spaces, and are still passed to awk as a positional parameter. #^ Close the single quotation mark so that \'passed to awk an escaped'                                     #^Open Single Quotes #^ Close the double quotes, good quote $1                                                   #^awkthe first double quotes of the system parameter #^Shell's outer single quotation mark, to pass an escaped single quote to awk #^The shell escapes the single quote to awk, it cannot be empty, it is empty, and the awk program is disconnected.^shell explanation of the outer front single quotation mark, front cannot be empty #^double quotes passed to the shell of the child process, empty before and after #^awk double quotes, front nullable

3. Store the result of the SQL statement as an awk variable

Export dbfile=./freepbx.db # Defines the shell variable, freepbx.db the Sqlite3 database file for FreePBXEcho|awk '{var=1000; ("Sqlite3'$dbfile'\ "Select Name,sipname from Users where extension=" var "\" ") | Getline data;print Data}'#引号分解:Echo|awk '{var=1000; ("Sqlite3'$dbfile'\ "Select ... extension=" var "\" ") | Getline data;print Data}'           #^outermost, interpreted by the shell #^ Call the shell to execute the command in parentheses and remove the parentheses to error:/bin/SH:-C: Line0: Looking for a matching '"' is encountered unexpected file terminator;/bin/sh:-C: Line 1: syntax error: unexpected end of file                                #^closing single quotes, passing shell variables #^Open Single quotation mark #^Close double quotes, passing the awk variable #^Open double Quotes #^ Close double quotes

To use the system to invoke the SQL statement in awk to illustrate using the quotation marks