dhis2-devs team mailing list archive

Thread
Date
[Bug 1489818] [NEW] Schedule Automated Message giving exception

To: dhis2-devs@xxxxxxxxxxxxxxxxxxx
From: "Harsh.atal" <harsh.atal@xxxxxxxxx>
Date: Fri, 28 Aug 2015 10:07:12 -0000
Reply-to: Bug 1489818 <1489818@xxxxxxxxxxxxxxxxxx>
Sender: bounces@xxxxxxxxxxxxx
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
Follow ups

[Bug 1489818] Re: Schedule Automated Message giving exception
From: Lars Helge Øverland, 2015-10-12
[Bug 1489818] Re: Schedule Automated Message giving exception
From: Thu Tran, 2015-09-30
[Bug 1489818] Re: Schedule Automated Message giving exception
From: Lars Helge Øverland, 2015-08-29