CODEWITHSUNDEEP
awk
shantanuo
shantanuo

Reputation: 32306

using variables in awk print

The contents of myfile.txt file are as follows:

| dbname                 |
| dbname1                |
| dbname2                |

The following command is expected to generate the output as shown below:

cat myfile.txt | awk '{print "mysql -uroot -Bse \"call mysql.p_check_fk_constraint_violations('\'$2\'','\''%'\'')\""}'

Expected Output:

mysql -uroot -Bse "call mysql.p_check_fk_constraint_violations('dbname','%')"
mysql -uroot -Bse "call mysql.p_check_fk_constraint_violations('dbname1','%')"
mysql -uroot -Bse "call mysql.p_check_fk_constraint_violations('dbname2','%')"

But the actual output is:

mysql -uroot -Bse "call mysql.p_check_fk_constraint_violations('','%')"

How do I add the database names in awk statement?

Upvotes: 2

Views: 498

Answers (4)

jaypal singh
jaypal singh

Reputation: 77085

This should do it - 

[jaypal~/Temp]$ cat db.file 
| dbname                 |
| dbname1                |
| dbname2                |

Here we are substituting the second field with your text and using "&" matches the field getting substituted. 

[jaypal~/Temp]$ awk -F\| '{sub($2,"mysql \-uroot \-Bse \"call mysql.p_check_fk_constraint_violations\(&,\%\)\""); print $2}' db.file 
mysql -uroot -Bse "call mysql.p_check_fk_constraint_violations( dbname                 ,%)"
mysql -uroot -Bse "call mysql.p_check_fk_constraint_violations( dbname1                ,%)"
mysql -uroot -Bse "call mysql.p_check_fk_constraint_violations( dbname2                ,%)"

Alternatively as Teudimundo suggested, you can do -

[jaypal~/Temp]$ cat db.file | awk '{print "mysql -uroot -Bse \"call mysql.p_check_fk_constraint_violations("$2",'%')\""}'
mysql -uroot -Bse "call mysql.p_check_fk_constraint_violations(dbname,%)"
mysql -uroot -Bse "call mysql.p_check_fk_constraint_violations(dbname1,%)"
mysql -uroot -Bse "call mysql.p_check_fk_constraint_violations(dbname2,%)"

UPDATE

[jaypal~/Temp]$ cat db.file | awk '{print "mysql -uroot -Bse \"call mysql.p_check_fk_constraint_violations('"'"'"$2"'"'"', '"'"'%'"'"')"}'
mysql -uroot -Bse "call mysql.p_check_fk_constraint_violations('dbname', '%')
mysql -uroot -Bse "call mysql.p_check_fk_constraint_violations('dbname1', '%')
mysql -uroot -Bse "call mysql.p_check_fk_constraint_violations('dbname2', '%')

[jaypal~/Temp]$ awk '{ print $2 }' db.file | awk '{sub($1,"mysql \-uroot \-Bse \"call mysql.p_check_fk_constraint_violations\('"'"'&'"'"','"'"'%'"'"'\)\""); print $0}'
mysql -uroot -Bse "call mysql.p_check_fk_constraint_violations('dbname','%')"
mysql -uroot -Bse "call mysql.p_check_fk_constraint_violations('dbname1','%')"
mysql -uroot -Bse "call mysql.p_check_fk_constraint_violations('dbname2','%')"

Upvotes: 2

Dimitre Radoulov
Dimitre Radoulov

Reputation: 27990

It suppose that it will be far better to use mysql procedure, instead of jumping in and out of mysql using shell scripting ... I'm not familiar with mysql's procedural language, but I'm sure that if you search Internet you can quickly come up with a simple procedure, something like this:

delimiter //
drop procedure run_proc //

create procedure run_proc()
begin
  declare done boolean default 0;
  declare l_db_name varchar(100);
  declare cur_db_names cursor
   for
     select 
       schema_name 
     from 
       information_schema.schemata;
  declare continue handler for 
    sqlstate '02000' set done=1;

   open cur_db_names;
   repeat
   fetch cur_db_names into l_db_name;
     call mysql.p_check_fk_constraint_violations(l_db_name,'%');
   until done end repeat;
   close cur_db_names;
end;
//

delimiter ;

call run_proc;

Upvotes: 0

Michael J. Barber
Michael J. Barber

Reputation: 25032

You're running into problems because of the single quotes, as Teudimundo said. To fix it, you need to replace each single quote ' that you'd like to embed with this '"'"', giving this awk command:

awk '{print "mysql -uroot -Bse \"call mysql.p_check_fk_constraint_violations('"'"'$2'"'"', '"'"'%'"'"')"}'

This works because the '"'"' first ends the single-quoted string for the awk command, begins a new double-quoted string containing a single quote, then starts a new single-quoted string with the rest of the awk command. Since adjacent strings are concatenated in the shell, this strange-seeming approach produces the string you need.

Upvotes: 1

Teudimundo
Teudimundo

Reputation: 2670

here : '" $2 "' you are closing the first awk ' char, and so " $2 " is interpreted by the shell.

Upvotes: 1

Related Questions