I’m trying to implement a SQLDataSet definition report. Right now I’m able to run it if the query has no parameters. However with parameters I don’t. Below is my setup. Am I doing something wrong?
I’ve defined a SQL Dataset with the following query:
select t1.*
from appointmentscheduling_appointment t1, appointmentscheduling_time_slot t2,
appointmentscheduling_appointment_block t3, provider t4, person t5
where t1.time_slot_id = t2.time_slot_id
and t2.appointment_block_id = t3.appointment_block_id
and t3.provider_id = t4.provider_id
and t4.person_id = t5.person_id
and t2.start_date >= @start_date
and t2.end_date <= @end_date
and t3.location_id = @location_id
and t3.provider_id = @provider_id
On this dataset, I’ve defined the following parameters:
After it I created a report definition, and added the above dataset to it:
I also added a report design, bui I think it doesn’t matter for this question.
Note: I’ve noticed on the docs, that parameters are usually referenced using a colon sign ( : ), but from what I’ve read mysql started using @ sign instead.
P.S: Another question, is it possible to run report processor only if it’s returned any rows?
You have almost got correct. But when you pass parameters to your SQL dataset you need to refer to them starting with a colon.
For E.g.: and t2.start_date >= :startDate
Edit: I saw that in your SQL query you have used different names. Use the same parameter name which you have given in parameter definition. You have used start_date but it according to your parameter definitions it must be corrected to startDate
@judeniroshan, thanks for your answer.
Maybe I wasn’t explicit enough, but mysql is not accepting parameters with colon.It gives a Mysql Syntax error. For what I’ve seen somewhere which I don’t find right now, the accepted parameter indicator is now @ sign.
I’ve tried to correct, the parameters name, as you stated, but stil no luck.
Maybe, it’s not big help, but while debugging SqlDataSetEvaluator class, I saw that:
1 - It makes some kind of recursive access to this same class
2 - on the last access(second I think), the context lost the parameters, whilst in the first access I can see the entered paramenters on the context variable.
select t1.*
from appointmentscheduling_appointment t1, appointmentscheduling_time_slot t2,
appointmentscheduling_appointment_block t3, provider t4, person t5
where t1.time_slot_id = t2.time_slot_id
and t2.appointment_block_id = t3.appointment_block_id
and t3.provider_id = t4.provider_id
and t4.person_id = t5.person_id
and t2.start_date >= :startDate
and t2.end_date <= :endDate
and t3.location_id = :location
and t3.provider_id = :providerPerson
Please try above query. This should retrieve data if you have qualified data in your database.
@judeniroshan, as I already said, it gives an error. But again, I tried the SQL you sent me, and the exception is the following:
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 '= :startDate
and t2.end_date <= :endDate
and t3.location_id = :location' at line 8
So, what seems be happening is that the MySql engine, doesn’t accept colon sign as valid syntax, since when I change it to @, it doesn’t give any error. I’m using an development environment with an openmrs-sdk created mysql docker, which contains the following mysql version:
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| innodb_version | 5.6.36 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.6.36 |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+------------------------------+
@judeniroshan, as I said, it won’t work. Mysql database isn’t accepting parameters with that syntax. Please read all my post. There is another problem.
Hi @fmalmeida,
I have tried your SQL query and created a SQL Dataset. I didn’t encounter any exceptions while running that. This is the entire SQL query I ran.
select t1.*
from appointmentscheduling_appointment t1, appointmentscheduling_time_slot t2,
appointmentscheduling_appointment_block t3, provider t4, person t5
where t1.time_slot_id = t2.time_slot_id
and t2.appointment_block_id = t3.appointment_block_id
and t3.provider_id = t4.provider_id
and t4.person_id = t5.person_id
and t2.start_date >= :startDate
and t2.end_date <= :endDate
and t3.location_id = :location
and t3.provider_id = :providerPerson
Below is the pop-up window which asked for the parameter values for that query.
P.S: For your other question, I’m not quite sure whether that is possible or not. I don’t have much experience about report processors at the time of I’m writing this response to you
@judeniroshan, I think you should have an older mysql engine, an so you can use the colon syntax. But there should exist some bug in the reporting module. Can you tell me which version of reporting module, do you have installed?
@judeniroshan, so after all, it still doesn’t work. It works on the dataset definition preview, but not in the report definition.
I reproduced the test in the reference application quality installation, and the behaiour is the same, having the following cause exception:
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 ':startDate
and t2.start_date <= :endDate' at line 8
@fmalmeida, my guess is that you are not properly mapping your parameters down from your report definition into your data set definition. This does not happen automatically - you need to do so explicitly. So in your attachment above:
Make sure here, that if you click on “Edit Mappings” next to your TESTE-SQL-DATASET that you have mapped the parameters through as appropriate from the report definition parameters into the data set definition parameters.
@mseaton, that’s it. Feeling somehow dumb as I didn’t get it .
Anyway, maybe you can help me just with another question, that’s if it’s possible to run processor(EmailProcessor) conditionally?
For example, if number of lines from the resultset > 0, then send email, i.e., don’t send when no results.
@fmalmeida, this is not something that is built into the existing EmailReportProcessor configuration, but it would be straightforward to add it in. The configuration properties that the EmailReportProcessor supports are defined here. These are then used within the process(Report report, Properties configuration) method to influence the behavior of the processor. So, to support your use case above, you’d need to add another supported configuration property which is utilized in the process method.
.
Anyway, maybe you can help me just with another question, that’s if it’s possible to run processor(EmailProcessor) conditionally?
For example, if number of lines from the resultset > 0, then send email, i.e., don’t send when no results.