Pass results from a MySQL procedure to the Linux command line -
i have procedure runs following select:
select distinct concat( 'php /home/rudyerd-systems/frontend/clientstatementgenerator.php ', left(user(), locate('@',user())-1), space(1), ca.client_id, space(1), date_format( @ptradingperiodmonth, '%y%m' ), space(1), date_format( @ptradingperiodmonth, '%y%m' ), ' > /home/rudyerd-systems.sco/frontend/bash/new_statement_test.html' ) ca_client_account ca, ca_tranche tr @ptradingperiodmonth between tr.dt_value , ifnull( tr.dt_withdrawal, @ptradingperiodmonth ) , ca.client_account_id = tr.client_account_id order ca.client_id; and produces following results:
| php /home/rudyerd-systems/frontend/clientstatementgenerator.php scorbet atp 1205 1205 > /home/rudyerd-systems.sco/frontend/bash/new_statement_test.html | | php /home/rudyerd-systems/frontend/clientstatementgenerator.php scorbet blb 1205 1205 > /home/rudyerd-systems.sco/frontend/bash/new_statement_test.html | | php /home/rudyerd-systems/frontend/clientstatementgenerator.php scorbet car 1205 1205 > /home/rudyerd-systems.sco/frontend/bash/new_statement_test.html | | php /home/rudyerd-systems/frontend/clientstatementgenerator.php scorbet cdr 1205 1205 > /home/rudyerd-systems.sco/frontend/bash/new_statement_test.html | | php /home/rudyerd-systems/frontend/clientstatementgenerator.php scorbet csp 1205 1205 > /home/rudyerd-systems.sco/frontend/bash/new_statement_test.html | | php /home/rudyerd-systems/frontend/clientstatementgenerator.php scorbet dgc 1205 1205 > /home/rudyerd-systems.sco/frontend/bash/new_statement_test.html | | php /home/rudyerd-systems/frontend/clientstatementgenerator.php scorbet fdr 1205 1205 > /home/rudyerd-systems.sco/frontend/bash/new_statement_test.html | | php /home/rudyerd-systems/frontend/clientstatementgenerator.php scorbet fvn 1205 1205 > /home/rudyerd-systems.sco/frontend/bash/new_statement_test.html | | php /home/rudyerd-systems/frontend/clientstatementgenerator.php scorbet jlm 1205 1205 > /home/rudyerd-systems.sco/frontend/bash/new_statement_test.html | | php /home/rudyerd-systems/frontend/clientstatementgenerator.php scorbet jra 1205 1205 > /home/rudyerd-systems.sco/frontend/bash/new_statement_test.html | | php /home/rudyerd-systems/frontend/clientstatementgenerator.php scorbet jrp 1205 1205 > /home/rudyerd-systems.sco/frontend/bash/new_statement_test.html | | php /home/rudyerd-systems/frontend/clientstatementgenerator.php scorbet mjo 1205 1205 > /home/rudyerd-systems.sco/frontend/bash/new_statement_test.html | | php /home/rudyerd-systems/frontend/clientstatementgenerator.php scorbet mpw 1205 1205 > /home/rudyerd-systems.sco/frontend/bash/new_statement_test.html | | php /home/rudyerd-systems/frontend/clientstatementgenerator.php scorbet ntc 1205 1205 > /home/rudyerd-systems.sco/frontend/bash/new_statement_test.html | | php /home/rudyerd-systems/frontend/clientstatementgenerator.php scorbet rja 1205 1205 > /home/rudyerd-systems.sco/frontend/bash/new_statement_test.html | | php /home/rudyerd-systems/frontend/clientstatementgenerator.php scorbet rmm 1205 1205 > /home/rudyerd-systems.sco/frontend/bash/new_statement_test.html | | php /home/rudyerd-systems/frontend/clientstatementgenerator.php scorbet sjc 1205 1205 > /home/rudyerd-systems.sco/frontend/bash/new_statement_test.html | | php /home/rudyerd-systems/frontend/clientstatementgenerator.php scorbet udb 1205 1205 > /home/rudyerd-systems.sco/frontend/bash/new_statement_test.html i output results procedure directly linux command line html page each of variables created. procedure run bash script within folder in create html files.
for completeness complete procedure.
-- --------------------------------------------------------------------------------- -- puigetclistmtgenlist -- -- procedure returns clients statements need generated -- -- --------------------------------------------------------------------------------- drop procedure if exists puigetclistmtgenlist; delimiter // create procedure puigetclistmtgenlist( in ptradingperiodmonth date, in pusername varchar, in ppassword varchar, out presult int ) modifies sql data comment 'gathers list of people statement needs created for' begin -- declare vclient_id varchar(3); declare vamttradedsystemccy decimal(13,2); declare vcount int; -- declare exit handler sqlwarning, sqlexception call prdhandleerror( 10000, 'puigetclistmtgenlist', vhint ); -- *********************************************************************************************************** set vhint = ptradingperiodmonth; set presult = 0; select distinct concat( 'php /home/rudyerd-systems/frontend/clientstatementgenerator.php ', left(user(), locate('@',user())-1), space(1), ca.client_id, space(1), date_format( @ptradingperiod, '%y%m' ), space(1), date_format( @ptradingperiod, '%y%m' ), ' > /home/rudyerd-systems.sco/frontend/bash/'ca.client_id''@ptradingperiod'statement.html' ) ca_client_account ca, ca_tranche tr @ptradingperiodmonth between tr.dt_value , ifnull( tr.dt_withdrawal, @ptradingperiodmonth ) , ca.client_account_id = tr.client_account_id order ca.client_id; -- -- if there no clients if vcount <> 0 call prdhandleerror( 1002, 'puigetclistmtgenflag', vhint ); end if; -- -- else -- output linux command line , generate html pages -- convert pages pdf -- set presult = 1; -- end; // delimiter ;
assuming output show intended runnable script , change > >> or add 1 of field values output filename they're unique, might you're after:
mysql -b -u username -p dbname < script.sql | sed 's/|//' | sh the sed command removes pipe character if it's present. piping whole thing through sh executes each line running php given arguments , redirecting output given file. may need change mysql arguments suit needs.
Comments
Post a Comment