dhis2-devs team mailing list archive

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

Public bug reported:

This may be related to #348703. Values greater that 2^31 cause the DHIS
1.4 file import to fail silently. This error is as follows, as occurs
with the ODBC connector driver, so it needs to be caught early.

* ERROR 12:57:12,283 The process threw exception (ProcessExecutor.java [Thread-17])
java.lang.RuntimeException: Query with RowHandler failed
	at org.hisp.dhis.importexport.dhis14.file.query.IbatisQueryManager.queryWithRowhandler(IbatisQueryManager.java:141)
	at org.hisp.dhis.importexport.dhis14.file.query.IbatisQueryManager.queryWithRowhandler(IbatisQueryManager.java:124)
	at org.hisp.dhis.importexport.dhis14.file.importer.DefaultDhis14FileImportService.importRoutineDataValues(DefaultDhis14FileImportService.java:753)
	at org.hisp.dhis.importexport.dhis14.file.importer.DefaultDhis14FileImportService.importData(DefaultDhis14FileImportService.java:281)
	at org.hisp.dhis.importexport.ImportInternalProcess.executeStatements(ImportInternalProcess.java:84)
	at org.hisp.dhis.system.process.AbstractStatementInternalProcess.execute(AbstractStatementInternalProcess.java:85)
	at org.hisp.dhis.system.process.AbstractStatementInternalProcess.execute(AbstractStatementInternalProcess.java:37)
	at org.amplecode.cave.process.ProcessExecutor.run(ProcessExecutor.java:126)
	at java.lang.Thread.run(Thread.java:619)
Caused by: com.ibatis.common.jdbc.exception.NestedSQLException:   
--- The error occurred in sqlmap/routineDataValue.sqlmap.xml.  
--- The error occurred while applying a result map.  
--- Check the routineDataValue.routineDataValueResultMap.  
--- Check the result mapping for the 'value' property.  
--- Cause: java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver]Numeric value out of range on column number 7 (EntryNumber)
Caused by: java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver]Numeric value out of range on column number 7 (EntryNumber)
	at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:185)
	at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithRowHandler(GeneralStatement.java:133)
	at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryWithRowHandler(SqlMapExecutorDelegate.java:644)
	at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryWithRowHandler(SqlMapSessionImpl.java:121)
	at com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryWithRowHandler(SqlMapClientImpl.java:98)
	at org.hisp.dhis.importexport.dhis14.file.query.IbatisQueryManager.queryWithRowhandler(IbatisQueryManager.java:135)
	... 8 more
Caused by: java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver]Numeric value out of range on column number 7 (EntryNumber)
	at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6957)
	at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7114)
	at sun.jdbc.odbc.JdbcOdbc.SQLGetDataInteger(JdbcOdbc.java:3811)
	at sun.jdbc.odbc.JdbcOdbcResultSet.getDataInteger(JdbcOdbcResultSet.java:5638)
	at sun.jdbc.odbc.JdbcOdbcResultSet.getInt(JdbcOdbcResultSet.java:583)
	at sun.jdbc.odbc.JdbcOdbcResultSet.getInt(JdbcOdbcResultSet.java:601)
	at com.ibatis.sqlmap.engine.type.IntegerTypeHandler.getResult(IntegerTypeHandler.java:35)
	at com.ibatis.sqlmap.engine.mapping.result.BasicResultMap.getPrimitiveResultMappingValue(BasicResultMap.java:565)
	at com.ibatis.sqlmap.engine.mapping.result.BasicResultMap.getResults(BasicResultMap.java:319)
	at com.ibatis.sqlmap.engine.execution.SqlExecutor.handleResults(SqlExecutor.java:394)
	at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQuery(SqlExecutor.java:185)
	at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExecuteQuery(GeneralStatement.java:205)
	at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:173)
	... 13 more


I would suggest that at least as a workaround the following procedure could possibly be tried?

1) Check to see if such problems exists. Lets get a list of ID´s to
exclude from the import process..

SELECT RoutineDataID FROM RoutineData WHERE EntryNumber > 2^31

2) Inform the user that there are some values that cannot be imported.

2) Then I guess the import process could start, excluding the list of
RoutineDataID´s from that list.

This is a big problem in places where figures like budget values reach
into the billions or trillions.

** Affects: dhis2
     Importance: Undecided
         Status: New

-- 
DHIS 2 cannot import values greater than 2^31 from DHIS 1.4
https://bugs.launchpad.net/bugs/383127
You received this bug notification because you are a member of DHIS 2
developers, which is subscribed to DHIS.

Status in District Health Information Software 2: New

Bug description:
This may be related to #348703. Values greater that 2^31 cause the DHIS 1.4 file import to fail silently. This error is as follows, as occurs with the ODBC connector driver, so it needs to be caught early. 

* ERROR 12:57:12,283 The process threw exception (ProcessExecutor.java [Thread-17])
java.lang.RuntimeException: Query with RowHandler failed
	at org.hisp.dhis.importexport.dhis14.file.query.IbatisQueryManager.queryWithRowhandler(IbatisQueryManager.java:141)
	at org.hisp.dhis.importexport.dhis14.file.query.IbatisQueryManager.queryWithRowhandler(IbatisQueryManager.java:124)
	at org.hisp.dhis.importexport.dhis14.file.importer.DefaultDhis14FileImportService.importRoutineDataValues(DefaultDhis14FileImportService.java:753)
	at org.hisp.dhis.importexport.dhis14.file.importer.DefaultDhis14FileImportService.importData(DefaultDhis14FileImportService.java:281)
	at org.hisp.dhis.importexport.ImportInternalProcess.executeStatements(ImportInternalProcess.java:84)
	at org.hisp.dhis.system.process.AbstractStatementInternalProcess.execute(AbstractStatementInternalProcess.java:85)
	at org.hisp.dhis.system.process.AbstractStatementInternalProcess.execute(AbstractStatementInternalProcess.java:37)
	at org.amplecode.cave.process.ProcessExecutor.run(ProcessExecutor.java:126)
	at java.lang.Thread.run(Thread.java:619)
Caused by: com.ibatis.common.jdbc.exception.NestedSQLException:   
--- The error occurred in sqlmap/routineDataValue.sqlmap.xml.  
--- The error occurred while applying a result map.  
--- Check the routineDataValue.routineDataValueResultMap.  
--- Check the result mapping for the 'value' property.  
--- Cause: java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver]Numeric value out of range on column number 7 (EntryNumber)
Caused by: java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver]Numeric value out of range on column number 7 (EntryNumber)
	at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:185)
	at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithRowHandler(GeneralStatement.java:133)
	at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryWithRowHandler(SqlMapExecutorDelegate.java:644)
	at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryWithRowHandler(SqlMapSessionImpl.java:121)
	at com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryWithRowHandler(SqlMapClientImpl.java:98)
	at org.hisp.dhis.importexport.dhis14.file.query.IbatisQueryManager.queryWithRowhandler(IbatisQueryManager.java:135)
	... 8 more
Caused by: java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver]Numeric value out of range on column number 7 (EntryNumber)
	at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6957)
	at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7114)
	at sun.jdbc.odbc.JdbcOdbc.SQLGetDataInteger(JdbcOdbc.java:3811)
	at sun.jdbc.odbc.JdbcOdbcResultSet.getDataInteger(JdbcOdbcResultSet.java:5638)
	at sun.jdbc.odbc.JdbcOdbcResultSet.getInt(JdbcOdbcResultSet.java:583)
	at sun.jdbc.odbc.JdbcOdbcResultSet.getInt(JdbcOdbcResultSet.java:601)
	at com.ibatis.sqlmap.engine.type.IntegerTypeHandler.getResult(IntegerTypeHandler.java:35)
	at com.ibatis.sqlmap.engine.mapping.result.BasicResultMap.getPrimitiveResultMappingValue(BasicResultMap.java:565)
	at com.ibatis.sqlmap.engine.mapping.result.BasicResultMap.getResults(BasicResultMap.java:319)
	at com.ibatis.sqlmap.engine.execution.SqlExecutor.handleResults(SqlExecutor.java:394)
	at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQuery(SqlExecutor.java:185)
	at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExecuteQuery(GeneralStatement.java:205)
	at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:173)
	... 13 more


I would suggest that at least as a workaround the following procedure could possibly be tried?

1) Check to see if such problems exists. Lets get a list of ID´s to exclude from the import process..

SELECT RoutineDataID FROM RoutineData WHERE EntryNumber > 2^31 

2) Inform the user that there are some values that cannot be imported. 

2) Then I guess the import process could start, excluding the list of RoutineDataID´s from that list. 

This is a big problem in places where figures like budget values reach into the billions or trillions.