Public bug reported:
We have made a few template reminders for program stages and are trying to test them. Few months back we had tested the same and they were working. Now they are not.
On clicking the execute button no messages are sent and the tomcat log shows this error (I suspect this has to do with the change of program type field)-
* INFO 2015-08-28 14:44:14,852 Start to prepare reminder messages: 00:00:00.001 (Clock.java [taskScheduler-2])
* ERROR 2015-08-28 14:44:14,915 Unexpected error occurred in scheduled task. (TaskUtils.java [taskScheduler-2])
org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [ ( SELECT pi.programinstanceid, pav.value as phonenumber, prm.templatemessage, org.name as orgunitName, pg.name as programName, pi.dateofincident , pi.enrollmentdate,(DATE(now()) - DATE(pi.enrollmentdate) ) as days_since_erollment_date, (DATE(now()) - DATE(pi.dateofincident) ) as days_since_incident_date FROM trackedentityinstance p INNER JOIN programinstance pi ON p.trackedentityinstanceid=pi.trackedentityinstanceid INNER JOIN program pg ON pg.programid=pi.programid INNER JOIN organisationunit org ON org.organisationunitid = p.organisationunitid INNER JOIN trackedentityinstancereminder prm ON prm.programid = pi.programid INNER JOIN trackedentityattributevalue pav ON pav.trackedentityinstanceid=p.trackedentityinstanceid INNER JOIN trackedentityattribute pa ON pa.trackedentityattributeid=pav.trackedentityattributeid WHERE pi.status= 0 and prm.templatemessage is not NULL and prm.templatemessage != '' and pg.type=1 and prm.daysallowedsendmessage is not null and pa.valuetype='phoneNumber' and ( DATE(now()) - DATE(pi.enrollmentdate) ) = prm.daysallowedsendmessage and prm.whenToSend is null and prm.dateToCompare='enrollmentdate' and prm.sendto = 1 ) UNION ( SELECT pi.programinstanceid, org.phonenumber, prm.templatemessage, org.name as orgunitName, pg.name as programName, pi.dateofincident, pi.enrollmentdate,(DATE(now()) - DATE(pi.enrollmentdate) ) as days_since_erollment_date, (DATE(now()) - DATE(pi.dateofincident) ) as days_since_incident_date FROM trackedentityinstance p INNER JOIN programinstance pi ON p.trackedentityinstanceid=pi.trackedentityinstanceid INNER JOIN program pg ON pg.programid=pi.programid INNER JOIN organisationunit org ON org.organisationunitid = p.organisationunitid INNER JOIN trackedentityinstancereminder prm ON prm.programid = pi.programid WHERE pi.status = 0 and org.phonenumber is not NULL and org.phonenumber != '' and prm.templatemessage is not NULL and prm.templatemessage != '' and pg.type=1 and prm.daysallowedsendmessage is not null and ( DATE(now()) - DATE( pi.enrollmentdate ) ) = prm.daysallowedsendmessage and prm.dateToCompare='enrollmentdate' and prm.whenToSend is null and prm.sendto = 3 ) UNION ( SELECT pi.programinstanceid, uif.phonenumber, prm.templatemessage, org.name as orgunitName, pg.name as programName, pi.dateofincident ,pi.enrollmentdate,(DATE(now()) - DATE(pi.enrollmentdate) ) as days_since_erollment_date, (DATE(now()) - DATE(pi.dateofincident) ) as days_since_incident_date FROM trackedentityinstance p INNER JOIN programinstance pi ON p.trackedentityinstanceid=pi.trackedentityinstanceid INNER JOIN program pg ON pg.programid=pi.programid INNER JOIN organisationunit org ON org.organisationunitid = p.organisationunitid INNER JOIN trackedentityinstancereminder prm ON prm.programid = pi.programid INNER JOIN usermembership ums ON ums.organisationunitid = p.organisationunitid INNER JOIN userinfo uif ON uif.userinfoid = ums.userinfoid WHERE pi.status= 0 and uif.phonenumber is not NULL and uif.phonenumber != '' and prm.templatemessage is not NULL and prm.templatemessage != '' and pg.type=1 and prm.daysallowedsendmessage is not null and ( DATE(now()) - DATE( enrollmentdate ) ) = prm.daysallowedsendmessage and prm.dateToCompare='enrollmentdate' and prm.sendto = 4 ) UNION ( select pi.programinstanceid, uif.phonenumber,prm.templatemessage, org.name as orgunitName , pg.name as programName, pi.dateofincident, pi.enrollmentdate, (DATE(now()) - DATE(pi.enrollmentdate) ) as days_since_erollment_date, (DATE(now()) - DATE(pi.dateofincident) ) as days_since_incident_date from trackedentityinstance p INNER JOIN programinstance pi ON p.trackedentityinstanceid=pi.trackedentityinstanceid INNER JOIN program pg ON pg.programid=pi.programid INNER JOIN organisationunit org ON org.organisationunitid = p.organisationunitid INNER JOIN trackedentityinstancereminder prm ON prm.programid = pg.programid INNER JOIN usergroupmembers ugm ON ugm.usergroupid = prm.usergroupid INNER JOIN userinfo uif ON uif.userinfoid = ugm.userid WHERE pi.status= 0 and uif.phonenumber is not NULL and uif.phonenumber != '' and prm.templatemessage is not NULL and prm.templatemessage != '' and pg.type=1 and prm.daysallowedsendmessage is not null and ( DATE(now()) - DATE(enrollmentdate) ) = prm.daysallowedsendmessage and prm.whentosend is null and prm.sendto = 5 ) ]; nested exception is org.postgresql.util.PSQLException: ERROR: operator does not exist: character varying = integer
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Position: 1077
About DHIS 2
Web API:
Browse it here
Current user:
admin
Version:
2.21-SNAPSHOT
Build revision:
19826
Build date:
2015-08-28 15:24
User agent:
Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/43.0.2357.81 Safari/537.36
Server date:
2015-08-28 15:35
Last analytics table generation:
2015-08-27 18:08
Time since last analytics table generation:
21 h, 27 m, 28 s
Environment variable:
DHIS2_HOME
External configuration directory:
/home/hisp/development/dhis_home
Database type:
PostgreSQL
Database name:
spis_v2
Database user:
postgres
Java opts:
Java home:
/usr/lib/jvm/jdk1.8.0_45/jre
Java temporary directory:
/opt/apache-tomcat-8.0.23/temp
Java version:
1.8.0_45
Java vendor:
Oracle Corporation
OS name:
Linux
OS architecture:
amd64
OS version:
3.16.0-23-generic
Server memory:
Mem Total in JVM: 905 Free in JVM: 518 Max Limit: 1735
CPU cores:
4
** Affects: dhis2
Importance: Undecided
Status: New
** Patch added: "changed program tye condition from "=1" to "like 'WITH_REGISTRATION'""
https://bugs.launchpad.net/bugs/1489818/+attachment/4453766/+files/templateReminderPatch.patch
···
--
You received this bug notification because you are a member of DHIS 2
developers, which is subscribed to DHIS.
https://bugs.launchpad.net/bugs/1489818
Title:
Schedule Automated Message giving exception
Status in DHIS:
New
Bug description:
We have made a few template reminders for program stages and are trying to test them. Few months back we had tested the same and they were working. Now they are not.
On clicking the execute button no messages are sent and the tomcat log shows this error (I suspect this has to do with the change of program type field)-
* INFO 2015-08-28 14:44:14,852 Start to prepare reminder messages: 00:00:00.001 (Clock.java [taskScheduler-2])
* ERROR 2015-08-28 14:44:14,915 Unexpected error occurred in scheduled task. (TaskUtils.java [taskScheduler-2])
org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [ ( SELECT pi.programinstanceid, pav.value as phonenumber, prm.templatemessage, org.name as orgunitName, pg.name as programName, pi.dateofincident , pi.enrollmentdate,(DATE(now()) - DATE(pi.enrollmentdate) ) as days_since_erollment_date, (DATE(now()) - DATE(pi.dateofincident) ) as days_since_incident_date FROM trackedentityinstance p INNER JOIN programinstance pi ON p.trackedentityinstanceid=pi.trackedentityinstanceid INNER JOIN program pg ON pg.programid=pi.programid INNER JOIN organisationunit org ON org.organisationunitid = p.organisationunitid INNER JOIN trackedentityinstancereminder prm ON prm.programid = pi.programid INNER JOIN trackedentityattributevalue pav ON pav.trackedentityinstanceid=p.trackedentityinstanceid INNER JOIN trackedentityattribute pa ON pa.trackedentityattributeid=pav.trackedentityattributeid WHERE pi.status= 0 and prm.templatemessage is not NULL and prm.templatemessage != '' and pg.type=1 and prm.daysallowedsendmessage is not null and pa.valuetype='phoneNumber' and ( DATE(now()) - DATE(pi.enrollmentdate) ) = prm.daysallowedsendmessage and prm.whenToSend is null and prm.dateToCompare='enrollmentdate' and prm.sendto = 1 ) UNION ( SELECT pi.programinstanceid, org.phonenumber, prm.templatemessage, org.name as orgunitName, pg.name as programName, pi.dateofincident, pi.enrollmentdate,(DATE(now()) - DATE(pi.enrollmentdate) ) as days_since_erollment_date, (DATE(now()) - DATE(pi.dateofincident) ) as days_since_incident_date FROM trackedentityinstance p INNER JOIN programinstance pi ON p.trackedentityinstanceid=pi.trackedentityinstanceid INNER JOIN program pg ON pg.programid=pi.programid INNER JOIN organisationunit org ON org.organisationunitid = p.organisationunitid INNER JOIN trackedentityinstancereminder prm ON prm.programid = pi.programid WHERE pi.status = 0 and org.phonenumber is not NULL and org.phonenumber != '' and prm.templatemessage is not NULL and prm.templatemessage != '' and pg.type=1 and prm.daysallowedsendmessage is not null and ( DATE(now()) - DATE( pi.enrollmentdate ) ) = prm.daysallowedsendmessage and prm.dateToCompare='enrollmentdate' and prm.whenToSend is null and prm.sendto = 3 ) UNION ( SELECT pi.programinstanceid, uif.phonenumber, prm.templatemessage, org.name as orgunitName, pg.name as programName, pi.dateofincident ,pi.enrollmentdate,(DATE(now()) - DATE(pi.enrollmentdate) ) as days_since_erollment_date, (DATE(now()) - DATE(pi.dateofincident) ) as days_since_incident_date FROM trackedentityinstance p INNER JOIN programinstance pi ON p.trackedentityinstanceid=pi.trackedentityinstanceid INNER JOIN program pg ON pg.programid=pi.programid INNER JOIN organisationunit org ON org.organisationunitid = p.organisationunitid INNER JOIN trackedentityinstancereminder prm ON prm.programid = pi.programid INNER JOIN usermembership ums ON ums.organisationunitid = p.organisationunitid INNER JOIN userinfo uif ON uif.userinfoid = ums.userinfoid WHERE pi.status= 0 and uif.phonenumber is not NULL and uif.phonenumber != '' and prm.templatemessage is not NULL and prm.templatemessage != '' and pg.type=1 and prm.daysallowedsendmessage is not null and ( DATE(now()) - DATE( enrollmentdate ) ) = prm.daysallowedsendmessage and prm.dateToCompare='enrollmentdate' and prm.sendto = 4 ) UNION ( select pi.programinstanceid, uif.phonenumber,prm.templatemessage, org.name as orgunitName , pg.name as programName, pi.dateofincident, pi.enrollmentdate, (DATE(now()) - DATE(pi.enrollmentdate) ) as days_since_erollment_date, (DATE(now()) - DATE(pi.dateofincident) ) as days_since_incident_date from trackedentityinstance p INNER JOIN programinstance pi ON p.trackedentityinstanceid=pi.trackedentityinstanceid INNER JOIN program pg ON pg.programid=pi.programid INNER JOIN organisationunit org ON org.organisationunitid = p.organisationunitid INNER JOIN trackedentityinstancereminder prm ON prm.programid = pg.programid INNER JOIN usergroupmembers ugm ON ugm.usergroupid = prm.usergroupid INNER JOIN userinfo uif ON uif.userinfoid = ugm.userid WHERE pi.status= 0 and uif.phonenumber is not NULL and uif.phonenumber != '' and prm.templatemessage is not NULL and prm.templatemessage != '' and pg.type=1 and prm.daysallowedsendmessage is not null and ( DATE(now()) - DATE(enrollmentdate) ) = prm.daysallowedsendmessage and prm.whentosend is null and prm.sendto = 5 ) ]; nested exception is org.postgresql.util.PSQLException: ERROR: operator does not exist: character varying = integer
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Position: 1077
About DHIS 2
Web API:
Browse it here
Current user:
admin
Version:
2.21-SNAPSHOT
Build revision:
19826
Build date:
2015-08-28 15:24
User agent:
Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/43.0.2357.81 Safari/537.36
Server date:
2015-08-28 15:35
Last analytics table generation:
2015-08-27 18:08
Time since last analytics table generation:
21 h, 27 m, 28 s
Environment variable:
DHIS2_HOME
External configuration directory:
/home/hisp/development/dhis_home
Database type:
PostgreSQL
Database name:
spis_v2
Database user:
postgres
Java opts:
Java home:
/usr/lib/jvm/jdk1.8.0_45/jre
Java temporary directory:
/opt/apache-tomcat-8.0.23/temp
Java version:
1.8.0_45
Java vendor:
Oracle Corporation
OS name:
Linux
OS architecture:
amd64
OS version:
3.16.0-23-generic
Server memory:
Mem Total in JVM: 905 Free in JVM: 518 Max Limit: 1735
CPU cores:
4
To manage notifications about this bug go to:
https://bugs.launchpad.net/dhis2/+bug/1489818/+subscriptions