StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POPass results from a MySQL procedure to the Linux command line
text
Body
copied!<p>I have a procedure which runs the following select:</p> <pre><code>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' ) from ca_client_account ca, ca_tranche tr where @pTradingPeriodMonth between tr.dt_value and ifnull( tr.dt_withdrawal, @pTradingPeriodMonth ) and ca.client_account_id = tr.client_account_id order by ca.client_id; </code></pre> <p>and produces the following results:</p> <pre><code>| 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 </code></pre> <p>I would like to output the results from the procedure directly to the Linux command line so that a HTML page with each of the variables is created. This procedure will be run from a bash script within the folder in which I would like to create the HTML files.</p> <p>For Completeness this is the complete procedure.</p> <pre><code>-- --------------------------------------------------------------------------------- -- pUIGetCliStmtGenList -- -- This procedure returns the Clients who statements need to be generated for -- -- --------------------------------------------------------------------------------- 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 the list of people a statement needs to be created for' begin -- declare vClient_id VARCHAR(3); declare vAmtTradedSystemCcy DECIMAL(13,2); declare vCount INT; -- declare EXIT handler for 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' ) FROM ca_client_account ca, ca_tranche tr INTO WHERE @pTradingPeriodMonth between tr.dt_value and ifnull( tr.dt_withdrawal, @pTradingPeriodMonth ) and ca.client_account_id = tr.client_account_id ORDER BY ca.client_id; -- -- If there are no clients if vCount <> 0 then call pRdHandleError( 1002, 'pUiGetCliStmtGenFlag', vHint ); end if; -- -- ELSE -- Output to Linux Command Line and Generate the HTML pages -- Then Convert the pages to pdf -- set pResult = 1; -- end; // delimiter ; </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload