dhis2-devs team mailing list archive

["jason.p.pickering" <jason.p.pickering@xxxxxxxxx>]

One more thing that is not obvious. If I was doing this in SQL, I
would write this statement..

INSERT INTO datavaluearchive
(SELECT d.* FROM datavalue as d
where d.periodid in
(SELECT periodid from period where startdate >='1990-01-01'
and enddate <='2007-12-31'))

In           /dhis2/dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/dataarchive/jdbc/
JdbcDataArchiveStore.java I see.


            "INSERT INTO datavaluearchive ( " +
                "SELECT d.* FROM datavalue AS d " +
                "JOIN period as p USING (periodid) " +
                "WHERE p.startdate>'" + startDate + "' " +
                "AND p.enddate<='" + endDate + "' );" +

which has a greater than and not a greater than and equal to sign. For
instance, if I want to archive data for January 2009 I would execute

Blah blah...

(SELECT periodid from period where startdate >='2009-01-01'
and enddate <='2009-01-31'))

This statement...
(SELECT periodid from period where startdate >'2009-01-01'
and enddate <='2009-01-31'))

returns nothing.


On Thu, Apr 22, 2010 at 3:27 PM, jason.p.pickering
<jason.p.pickering@xxxxxxxxx> wrote:
> Public bug reported:
>
> Attempted to archive data from 1990-01-01 to 2007-12-31. Was greeted
> with this error message. Seems to be a problem with /dhis2/dhis-2/dhis-
> web/dhis-web-maintenance/dhis-web-maintenance-dataadmin/src/main/webapp
> /dhis-web-maintenance-dataadmin/dataArchiveForm.vm.
>
>
> Caused by: org.postgresql.util.PSQLException: ERROR: invalid input syntax for type date: "Mon Jan 01 00:00:00 CAT 1990"
>        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2062)
>        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1795)
>        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
>        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:479)
>        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:353)
>        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:345)
>        at com.mchange.v2.c3p0.impl.NewProxyStatement.execute(NewProxyStatement.java:1006)
>        at org.springframework.jdbc.core.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:422)
>        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:396)
>
> ** Affects: dhis2
>     Importance: Undecided
>         Status: New
>
> --
> data-archive-throws-exception
> https://bugs.launchpad.net/bugs/568416
> You received this bug notification because you are a direct subscriber
> of the bug.
>
> Status in DHIS 2 - District Health Information Software: New
>
> Bug description:
> Attempted to archive data from 1990-01-01 to 2007-12-31. Was greeted with this error message. Seems to be a problem with /dhis2/dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-dataadmin/src/main/webapp/dhis-web-maintenance-dataadmin/dataArchiveForm.vm.
>
>
> Caused by: org.postgresql.util.PSQLException: ERROR: invalid input syntax for type date: "Mon Jan 01 00:00:00 CAT 1990"
>        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2062)
>        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1795)
>        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
>        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:479)
>        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:353)
>        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:345)
>        at com.mchange.v2.c3p0.impl.NewProxyStatement.execute(NewProxyStatement.java:1006)
>        at org.springframework.jdbc.core.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:422)
>        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:396)
>
> To unsubscribe from this bug, go to:
> https://bugs.launchpad.net/dhis2/+bug/568416/+subscribe
>


-- 
--
Jason P. Pickering
email: jason.p.pickering@xxxxxxxxx
tel:+260968395190

-- 
data-archive-throws-exception
https://bugs.launchpad.net/bugs/568416
You received this bug notification because you are a member of DHIS 2
developers, which is subscribed to DHIS.

Status in DHIS 2 - District Health Information Software: New

Bug description:
Attempted to archive data from 1990-01-01 to 2007-12-31. Was greeted with this error message. Seems to be a problem with /dhis2/dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-dataadmin/src/main/webapp/dhis-web-maintenance-dataadmin/dataArchiveForm.vm. 


Caused by: org.postgresql.util.PSQLException: ERROR: invalid input syntax for type date: "Mon Jan 01 00:00:00 CAT 1990"
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2062)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1795)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:479)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:353)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:345)
	at com.mchange.v2.c3p0.impl.NewProxyStatement.execute(NewProxyStatement.java:1006)
	at org.springframework.jdbc.core.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:422)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:396)