Using raw SQL query as parameter (Dynamic queryString)

[adummy832]

First of all great work in this extension @geekcom, Really loving it! 🎉 Btw, how can i use some Raw SQL as a parameter to make a dynamic query inside a report.

# From PHP to PhpJasper:
$query = 'SELECT R.id, R.ris_no, R.ris_date, R.rcc, CONCAT_WS(" / ", O.acronym, D.acronym, S.acronym) AS office FROM `ris` `R` LEFT JOIN `office` `O` ON O.id = R.office_id LEFT JOIN `division` `D` ON D.id = R.division_id LEFT JOIN `section` `S` ON S.id = R.section_id WHERE (`R`.`ris_date` BETWEEN '2019-02-20' AND '2019-03-19') AND (`R`.`office_id`='1')';

$options['params'] = [
    'QUERY' => $query
];

# Generate, Standard stuff
PhpJasper->process($jasperPath, $outFilePath, $options)->execute();

I have the QUERY parameter defined directly in my report and using it as a queryString. I also edited the PhpJasper class on line 100 because BASH reads double-quoted commands and produces a ton of error.

# From:
$this->command .= " " . $key . '="' . $value . '" ' . " ";

# Generated command:
jasperstarter --locale en process "C:/xampp/htdocs/ams/backend/web/jasper/my-report.jrxml" -o "C:/xampp/htdocs/ams/backend/web/reports/1550733840-my-report" -f pdf -P  QUERY="SELECT R.id, R.ris_no, R.ris_date, R.rcc, CONCAT_WS(" / ", O.acronym, D.acronym, S.acronym) AS office FROM `ris` `R` LEFT JOIN `office` `O` ON O.id = R.office_id LEFT JOIN `division` `D` ON D.id = R.division_id LEFT JOIN `section` `S` ON S.id = R.section_id WHERE (`R`.`ris_date` BETWEEN '2019-02-20' AND '2019-03-19') AND (`R`.`office_id`='1')"   -t mysql -u root -H localhost -n myDB --db-port 3306 --db-driver com.mysql.jdbc.Driver --db-url jdbc:mysql://localhost/myDB --jdbc-dir C:/xampp/htdocs/ams/backend/web/jdbc

# To:
$this->command .= " ".$key.'=\''.$value.'\' '." ";

# Generated command:
jasperstarter --locale en process "C:/xampp/htdocs/ams/backend/web/jasper/my-report.jrxml" -o "C:/xampp/htdocs/ams/backend/web/reports/1550733840-my-report" -f pdf -P  QUERY='SELECT R.id, R.ris_no, R.ris_date, R.rcc, CONCAT_WS(" / ", O.acronym, D.acronym, S.acronym) AS office FROM `ris` `R` LEFT JOIN `office` `O` ON O.id = R.office_id LEFT JOIN `division` `D` ON D.id = R.division_id LEFT JOIN `section` `S` ON S.id = R.section_id WHERE (`R`.`ris_date` BETWEEN '2019-02-20' AND '2019-03-19') AND (`R`.`office_id`='1')'   -t mysql -u root -H localhost -n myDB --db-port 3306 --db-driver com.mysql.jdbc.Driver --db-url jdbc:mysql://localhost/myDB --jdbc-dir C:/xampp/htdocs/ams/backend/web/jdbc

Ran the latter command. It runs perfectly but generated a pdf without a single data.

But when i ran it on the JasperSoft software itself it generates the pdf perfectly with the QUERY parameter supplied directly. Is this some kind of a bug ? Am i missing something ? I have been on this for hours now. Hoping you could help. Thanks @geekcom

[geekcom]

Hi @adummy832 i believe everything is correct, did you create the parameter in the report .jrxml? I recommend Jaspersoft Studio to manipulate your reports visually.

In you .jrxml you need create a parameter:

<parameter name="query" class="java.lang.String"/>

and pass your parameters to you report.

$P!{query}

thanks to to use PHPJasper.