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.trackedentity
attributeid=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 t
rackedentityinstance 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.trackedenti
tyattributeid=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 JOI
N 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
_______________________________________________
Mailing list: https://launchpad.net/~dhis2-devs
Post to : [email protected]
Unsubscribe : https://launchpad.net/~dhis2-devs
More help : https://help.launchpad.net/ListHelp
