Patient History Search report fails if 'Limit number of results to' parameter cleared
Description
If the ‘Limit number of results to’ parameter of the Patient History Search.jrxml report is cleared, it fails with:
14 Jul 2022 09:48:01,756 ERROR DialogErrorHandler,qtp1281168969-25:72 - admin - Failed to generate report Patient History Search.jrxml: Error executing SQL statement for: Patient History Search.
org.openvpms.report.ReportException: Failed to generate report Patient History Search.jrxml: Error executing SQL statement for: Patient History Search.
at org.openvpms.report.jasper.AbstractJasperIMReport.generate(AbstractJasperIMReport.java:306) ~[openvpms-reports-2.3.0.10-SNAPSHOT.jar:2.3]
....
Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'null' at line 39
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) ~[mysql-connector-java-8.0.27.jar:8.0.27]
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.27.jar:8.0.27]
at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953) ~[mysql-connector-java-8.0.27.jar:8.0.27]
at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1009) ~[mysql-connector-java-8.0.27.jar:8.0.27]
at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83) ~[commons-dbcp2-2.1.jar:2.1]
at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83) ~[commons-dbcp2-2.1.jar:2.1]
at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:312) ~[jasperreports-6.16.0.jar:6.16.0-48579d909b7943b64690c65c71e07e0b80981928]
Environment
None
Activity
Tim Anderson August 22, 2022 at 1:08 AM
Fixed to exclude the limit clause if the field is cleared. Note that this could have performance implications if the date range is too big. The template can be loaded using: toolbox template --load 'Patient History Search' --size A4
If the ‘Limit number of results to’ parameter of the Patient History Search.jrxml report is cleared, it fails with:
14 Jul 2022 09:48:01,756 ERROR DialogErrorHandler,qtp1281168969-25:72 - admin - Failed to generate report Patient History Search.jrxml: Error executing SQL statement for: Patient History Search. org.openvpms.report.ReportException: Failed to generate report Patient History Search.jrxml: Error executing SQL statement for: Patient History Search. at org.openvpms.report.jasper.AbstractJasperIMReport.generate(AbstractJasperIMReport.java:306) ~[openvpms-reports-2.3.0.10-SNAPSHOT.jar:2.3] .... Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'null' at line 39 at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) ~[mysql-connector-java-8.0.27.jar:8.0.27] at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.27.jar:8.0.27] at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953) ~[mysql-connector-java-8.0.27.jar:8.0.27] at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1009) ~[mysql-connector-java-8.0.27.jar:8.0.27] at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83) ~[commons-dbcp2-2.1.jar:2.1] at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83) ~[commons-dbcp2-2.1.jar:2.1] at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:312) ~[jasperreports-6.16.0.jar:6.16.0-48579d909b7943b64690c65c71e07e0b80981928]