Problem with export of Event data

We have several forms for neonatal, child and maternal death audits that are using the Single Event without registration module.

When I try to Export the data from the Import-Export menu I get an error regardless of which data set.

It appears to be a problem with the date parameter in the SQL query. Below is the relevant section of the log:

type Exception report

message

description The server encountered an internal error () that prevented it from fulfilling this request.

exception

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.dao.DataIntegrityViolationException: StatementCallback; SQL [select p.uid as p_uid, ps.uid as ps_uid, psi.uid as psi_uid, psi.status as psi_status, ou.uid as ou_uid, psi.executiondate as psi_executiondate, psi.completeduser as psi_completeduser, pdv.value as pdv_value, pdv.storedby as pdv_storedby, pdv.providedelsewhere as pdv_providedelsewhere, de.uid as de_uid from program p left join programstage ps on ps.programid=p.programid left join programstageinstance psi on ps.programstageid=psi.programstageid left join organisationunit ou on (psi.organisationunitid=ou.organisationunitid) left join patientdatavalue pdv on psi.programstageinstanceid=pdv.programstageinstanceid left join dataelement de on pdv.dataelementid=de.dataelementid where p.programid in (6130) and ps.programstageid in (6131) and ou.organisationunitid in (756) and (psi.executiondate >= ‘Tue Jan 01 00:00:00 CAT 2013’ and psi.executiondate <= ‘Thu Nov 21 00:00:00 CAT 2013’) order by psi_uid;]; ERROR: invalid input syntax for type date: “Tue Jan 01 00:00:00 CAT 2013”

Position: 805; nested exception is org.postgresql.util.PSQLException: ERROR: invalid input syntax for type date: “Tue Jan 01 00:00:00 CAT 2013”

Has this been reported yet? I can’t get the query to run through PgAdmin either unless I remove the date parameters.

Randy Wilson

Hi Randy,

I haven’t seen that issue before. I will have a look at it, I think it might be related to patientdatavalue using date, and not timestamp without timezone (as is being used in other places).

···


Morten

On Thu, Nov 21, 2013 at 8:16 AM, Wilson,Randy rwilson@msh.org wrote:

We have several forms for neonatal, child and maternal death audits that are using the Single Event without registration module.

When I try to Export the data from the Import-Export menu I get an error regardless of which data set.

It appears to be a problem with the date parameter in the SQL query. Below is the relevant section of the log:

type Exception report

message

description The server encountered an internal error () that prevented it from fulfilling this request.

exception

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.dao.DataIntegrityViolationException: StatementCallback; SQL [select p.uid as p_uid, ps.uid as ps_uid, psi.uid as psi_uid, psi.status as psi_status, ou.uid as ou_uid, psi.executiondate as psi_executiondate, psi.completeduser as psi_completeduser, pdv.value as pdv_value, pdv.storedby as pdv_storedby, pdv.providedelsewhere as pdv_providedelsewhere, de.uid as de_uid from program p left join programstage ps on ps.programid=p.programid left join programstageinstance psi on ps.programstageid=psi.programstageid left join organisationunit ou on (psi.organisationunitid=ou.organisationunitid) left join patientdatavalue pdv on psi.programstageinstanceid=pdv.programstageinstanceid left join dataelement de on pdv.dataelementid=de.dataelementid where p.programid in (6130) and ps.programstageid in (6131) and ou.organisationunitid in (756) and (psi.executiondate >= ‘Tue Jan 01 00:00:00 CAT 2013’ and psi.executiondate <= ‘Thu Nov 21 00:00:00 CAT 2013’) order by psi_uid;]; ERROR: invalid input syntax for type date: “Tue Jan 01 00:00:00 CAT 2013”

Position: 805; nested exception is org.postgresql.util.PSQLException: ERROR: invalid input syntax for type date: “Tue Jan 01 00:00:00 CAT 2013”

Has this been reported yet? I can’t get the query to run through PgAdmin either unless I remove the date parameters.

Randy Wilson


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

HI Morten,

Yes in the table psi_executiondate is stored in the format ‘2013-06-19’

I also find that the orgunit selection doesn’t display data from children. When I want all data for the country, I can’t select ‘Rwanda’ – it gives me nothing. If I remove the orgunitid filter I see all the data in PgAdmin. I’ve added the line in red because we can then use this in a pivot table for analysis.

I’ve been having trouble extracting the data from the Individual reports data export – I think there may be issues with some of the records (there are many blanks and partial records in the system- from the early testing days that I haven’t figured out how to delete without going into each facility and deleting blank records through the data entry module. Maybe with the data I have now through this query, I should be able to identify the psi_uids with problems and delete them from the backend.

select p.uid as p_uid, ps.uid as ps_uid, psi.uid as psi_uid, psi.status as psi_status, ou.uid as ou_uid,

ou.name as facilitname, ou.code as fosaid, de.name as dataelementname,

psi.executiondate as psi_executiondate, psi.completeduser as psi_completeduser, pdv.value as pdv_value,

pdv.storedby as pdv_storedby, pdv.providedelsewhere as pdv_providedelsewhere, de.uid as de_uid from program p

left join programstage ps on ps.programid=p.programid

left join programstageinstance psi on ps.programstageid=psi.programstageid

left join organisationunit ou on (psi.organisationunitid=ou.organisationunitid)

left join patientdatavalue pdv on psi.programstageinstanceid=pdv.programstageinstanceid

left join dataelement de on pdv.dataelementid=de.dataelementid

where p.programid in (6130) and ps.programstageid in (6131) order by psi_uid;

Randy

···

Hi Randy,

I haven’t seen that issue before. I will have a look at it, I think it might be related to patientdatavalue using date, and not timestamp without timezone (as is being used in other places).

Morten

On Thu, Nov 21, 2013 at 8:16 AM, Wilson,Randy rwilson@msh.org wrote:

We have several forms for neonatal, child and maternal death audits that are using the Single Event without registration module.

When I try to Export the data from the Import-Export menu I get an error regardless of which data set.

It appears to be a problem with the date parameter in the SQL query. Below is the relevant section of the log:

type Exception report

message

description The server encountered an internal error () that prevented it from fulfilling this request.

exception

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.dao.DataIntegrityViolationException: StatementCallback; SQL [select p.uid as p_uid, ps.uid as ps_uid, psi.uid as psi_uid, psi.status as psi_status, ou.uid as ou_uid, psi.executiondate as psi_executiondate, psi.completeduser as psi_completeduser, pdv.value as pdv_value, pdv.storedby as pdv_storedby, pdv.providedelsewhere as pdv_providedelsewhere, de.uid as de_uid from program p left join programstage ps on ps.programid=p.programid left join programstageinstance psi on ps.programstageid=psi.programstageid left join organisationunit ou on (psi.organisationunitid=ou.organisationunitid) left join patientdatavalue pdv on psi.programstageinstanceid=pdv.programstageinstanceid left join dataelement de on pdv.dataelementid=de.dataelementid where p.programid in (6130) and ps.programstageid in (6131) and ou.organisationunitid in (756) and (psi.executiondate >= ‘Tue Jan 01 00:00:00 CAT 2013’ and psi.executiondate <= ‘Thu Nov 21 00:00:00 CAT 2013’) order by psi_uid;]; ERROR: invalid input syntax for type date: “Tue Jan 01 00:00:00 CAT 2013”

Position: 805; nested exception is org.postgresql.util.PSQLException: ERROR: invalid input syntax for type date: “Tue Jan 01 00:00:00 CAT 2013”

Has this been reported yet? I can’t get the query to run through PgAdmin either unless I remove the date parameters.

Randy Wilson


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

Yes, this is actually by choice. It will only export exactly the orgUnit
you have selected. We might look into adding a
"includeChildren"/"includeDescendants" as we have other places in the
Web-API.

Are you going to import this data on a later stage? if not, have you also
looked at the analytics web-api for events? You can read the docs on [1],
of course, the data will have a delay since they are working with the
analytics tables and not the patientdatavalue table directly.

[1] http://www.dhis2.org/doc/snapshot/en/user/html/ch25s24.html

···

On Thu, Nov 21, 2013 at 10:30 AM, Wilson,Randy <rwilson@msh.org> wrote:

I also find that the orgunit selection doesn’t display data from
children. When I want all data for the country, I can’t select ‘Rwanda’ –
it gives me nothing. If I remove the orgunitid filter I see all the data
in PgAdmin. I’ve added the line in red because we can then use this in a
pivot table for analysis.

--
Morten

I have backported (potential) a fix for this to 2.13, please test out the latest stable if you can.

···


Morten

On Thu, Nov 21, 2013 at 10:38 AM, Morten Olav Hansen mortenoh@gmail.com wrote:

On Thu, Nov 21, 2013 at 10:30 AM, Wilson,Randy rwilson@msh.org wrote:

I also find that the orgunit selection doesn’t display data from children. When I want all data for the country, I can’t select ‘Rwanda’ – it gives me nothing. If I remove the orgunitid filter I see all the data in PgAdmin. I’ve added the line in red because we can then use this in a pivot table for analysis.

Yes, this is actually by choice. It will only export exactly the orgUnit you have selected. We might look into adding a “includeChildren”/“includeDescendants” as we have other places in the Web-API.

Are you going to import this data on a later stage? if not, have you also looked at the analytics web-api for events? You can read the docs on [1], of course, the data will have a delay since they are working with the analytics tables and not the patientdatavalue table directly.

[1] http://www.dhis2.org/doc/snapshot/en/user/html/ch25s24.html


Morten