public void startSqlBuild() throws JAXBUtilException, I2B2Exception { LogTimingUtil logTimingUtil = new LogTimingUtil(); logTimingUtil.setStartTime(); execQuery(this.queryXML, null, 0); logTimingUtil.setEndTime(); if (processTimingFlag.equalsIgnoreCase(ProcessTimingReportUtil.INFO) || processTimingFlag.equalsIgnoreCase(ProcessTimingReportUtil.DEBUG)) { ProcessTimingReportUtil ptrUtil = new ProcessTimingReportUtil(this.dataSourceLookup); this.processTimingMessageBuffer.append( ptrUtil.buildProcessTiming(logTimingUtil, "BUILD SQL", null)); } }
/** * Function accepts parameter in Map. The patient count will be obfuscated if the user is OBFUS */ public void generateResult(Map param) throws CRCTimeOutException, I2B2DAOException { SetFinderConnection sfConn = (SetFinderConnection) param.get("SetFinderConnection"); SetFinderDAOFactory sfDAOFactory = (SetFinderDAOFactory) param.get("SetFinderDAOFactory"); // String patientSetId = (String)param.get("PatientSetId"); String queryInstanceId = (String) param.get("QueryInstanceId"); String TEMP_DX_TABLE = (String) param.get("TEMP_DX_TABLE"); String resultInstanceId = (String) param.get("ResultInstanceId"); // String itemKey = (String) param.get("ItemKey"); String resultTypeName = (String) param.get("ResultOptionName"); String processTimingFlag = (String) param.get("ProcessTimingFlag"); int obfuscatedRecordCount = (Integer) param.get("ObfuscatedRecordCount"); int transactionTimeout = (Integer) param.get("TransactionTimeout"); TransactionManager tm = (TransactionManager) param.get("TransactionManager"); this.setDbSchemaName(sfDAOFactory.getDataSourceLookup().getFullSchema()); Map ontologyKeyMap = (Map) param.get("setFinderResultOntologyKeyMap"); String serverType = (String) param.get("ServerType"); CallOntologyUtil ontologyUtil = (CallOntologyUtil) param.get("CallOntologyUtil"); List<String> roles = (List<String>) param.get("Roles"); String tempTableName = ""; PreparedStatement stmt = null; boolean errorFlag = false, timeoutFlag = false; String itemKey = ""; int actualTotal = 0, obsfcTotal = 0; boolean obfscDataRoleFlag = false; try { LogTimingUtil logTimingUtil = new LogTimingUtil(); logTimingUtil.setStartTime(); obfscDataRoleFlag = checkDataObscRole(sfDAOFactory.getOriginalDataSourceLookup(), roles); itemKey = getItemKeyFromResultType(sfDAOFactory, resultTypeName); log.debug("Result type's " + resultTypeName + " item key value " + itemKey); LogTimingUtil subLogTimingUtil = new LogTimingUtil(); subLogTimingUtil.setStartTime(); ConceptsType conceptsType = ontologyUtil.callGetChildren(itemKey); if (conceptsType != null && conceptsType.getConcept().size() < 1) { throw new I2B2DAOException( "Could not fetch children result type " + resultTypeName + " item key [ " + itemKey + " ]"); } subLogTimingUtil.setEndTime(); if (processTimingFlag != null) { if (processTimingFlag.trim().equalsIgnoreCase(ProcessTimingReportUtil.DEBUG)) { ProcessTimingReportUtil ptrUtil = new ProcessTimingReportUtil(sfDAOFactory.getDataSourceLookup()); ptrUtil.logProcessTimingMessage( queryInstanceId, ptrUtil.buildProcessTiming( subLogTimingUtil, "BUILD - " + resultTypeName + " : Ontology Call(GetChildren) ", "")); } } String itemCountSql = " select count(distinct PATIENT_NUM) as item_count from " + this.getDbSchemaName() + "observation_fact obs_fact " + " where obs_fact.patient_num in (select patient_num from " + TEMP_DX_TABLE + " ) " + " and obs_fact.concept_cd in (select concept_cd from " + this.getDbSchemaName() + "concept_dimension where concept_path like ?)"; ResultType resultType = new ResultType(); resultType.setName(resultTypeName); stmt = sfConn.prepareStatement(itemCountSql); CancelStatementRunner csr = new CancelStatementRunner(stmt, transactionTimeout); Thread csrThread = new Thread(csr); csrThread.start(); for (ConceptType conceptType : conceptsType.getConcept()) { String dimCode = this.getDimCodeInSqlFormat(conceptType); itemCountSql = " select count(distinct PATIENT_NUM) as item_count from " + this.getDbSchemaName() + " observation_fact " + " where " + " patient_num in (select patient_num from " + TEMP_DX_TABLE + " ) and " + conceptType.getFacttablecolumn() + " IN (select " + conceptType.getFacttablecolumn() + " from " + getDbSchemaName() + conceptType.getTablename() + " " + " where " + conceptType.getColumnname() + " " + conceptType.getOperator() + " " + dimCode + ")"; stmt = sfConn.prepareStatement(itemCountSql); // // smuniraju: Currently, in postgres, a timeout value > 0 will result in "setQueryTimeout // is not yet implemented" // stmt.setQueryTimeout(transactionTimeout); int queryTimeout = (serverType.equalsIgnoreCase(DAOFactoryHelper.POSTGRES)) ? 0 : transactionTimeout; stmt.setQueryTimeout(queryTimeout); log.debug("Executing count sql [" + itemCountSql + "]"); // subLogTimingUtil.setStartTime(); ResultSet resultSet = stmt.executeQuery(); if (csr.getSqlFinishedFlag()) { timeoutFlag = true; throw new CRCTimeOutException("The query was canceled."); } resultSet.next(); int demoCount = resultSet.getInt("item_count"); subLogTimingUtil.setEndTime(); if (processTimingFlag != null) { if (processTimingFlag.trim().equalsIgnoreCase(ProcessTimingReportUtil.DEBUG)) { ProcessTimingReportUtil ptrUtil = new ProcessTimingReportUtil(sfDAOFactory.getDataSourceLookup()); ptrUtil.logProcessTimingMessage( queryInstanceId, ptrUtil.buildProcessTiming( subLogTimingUtil, "BUILD - " + resultTypeName + " : COUNT SQL for " + conceptType.getDimcode() + " ", "sql=" + itemCountSql)); } } // actualTotal += demoCount; if (obfscDataRoleFlag) { GaussianBoxMuller gaussianBoxMuller = new GaussianBoxMuller(); demoCount = (int) gaussianBoxMuller.getNormalizedValueForCount(demoCount); obsfcTotal += demoCount; } DataType mdataType = new DataType(); mdataType.setValue(String.valueOf(demoCount)); mdataType.setColumn(conceptType.getName()); mdataType.setType("int"); resultType.getData().add(mdataType); } csr.setSqlFinishedFlag(); csrThread.interrupt(); stmt.close(); edu.harvard.i2b2.crc.datavo.i2b2result.ObjectFactory of = new edu.harvard.i2b2.crc.datavo.i2b2result.ObjectFactory(); BodyType bodyType = new BodyType(); bodyType.getAny().add(of.createResult(resultType)); ResultEnvelopeType resultEnvelop = new ResultEnvelopeType(); resultEnvelop.setBody(bodyType); JAXBUtil jaxbUtil = CRCJAXBUtil.getJAXBUtil(); StringWriter strWriter = new StringWriter(); subLogTimingUtil.setStartTime(); jaxbUtil.marshaller(of.createI2B2ResultEnvelope(resultEnvelop), strWriter); subLogTimingUtil.setEndTime(); tm.begin(); IXmlResultDao xmlResultDao = sfDAOFactory.getXmlResultDao(); xmlResultDao.createQueryXmlResult(resultInstanceId, strWriter.toString()); // if (processTimingFlag != null) { if (!processTimingFlag.trim().equalsIgnoreCase(ProcessTimingReportUtil.NONE)) { ProcessTimingReportUtil ptrUtil = new ProcessTimingReportUtil(sfDAOFactory.getDataSourceLookup()); if (processTimingFlag.trim().equalsIgnoreCase(ProcessTimingReportUtil.DEBUG)) { ptrUtil.logProcessTimingMessage( queryInstanceId, ptrUtil.buildProcessTiming(subLogTimingUtil, "JAXB - " + resultTypeName, "")); } logTimingUtil.setEndTime(); ptrUtil.logProcessTimingMessage( queryInstanceId, ptrUtil.buildProcessTiming(logTimingUtil, "BUILD - " + resultTypeName, "")); } } tm.commit(); } catch (com.microsoft.sqlserver.jdbc.SQLServerException sqlServerEx) { // if the setQueryTimeout worked, then the message would be timed // out if (sqlServerEx.getMessage().indexOf("timed out") > -1) { timeoutFlag = true; throw new CRCTimeOutException(sqlServerEx.getMessage(), sqlServerEx); } else if (sqlServerEx .getMessage() .indexOf( // if the stmt.cancel() // worked, then this // exception is // thrown "The query was canceled.") > -1) { timeoutFlag = true; throw new CRCTimeOutException(sqlServerEx.getMessage(), sqlServerEx); } else { errorFlag = true; log.error("Sqlserver error while executing sql", sqlServerEx); throw new I2B2DAOException("Sqlserver error while executing sql", sqlServerEx); } } catch (SQLException sqlEx) { // catch oracle query timeout error ORA-01013 if (sqlEx.toString().indexOf("ORA-01013") > -1) { timeoutFlag = true; throw new CRCTimeOutException(sqlEx.getMessage(), sqlEx); } if (sqlEx.getMessage().indexOf("The query was canceled.") > -1) { timeoutFlag = true; throw new CRCTimeOutException(sqlEx.getMessage(), sqlEx); } errorFlag = true; log.error("Error while executing sql", sqlEx); throw new I2B2DAOException("Error while executing sql", sqlEx); } catch (Exception sqlEx) { errorFlag = true; log.error("QueryResultPatientSetGenerator.generateResult:" + sqlEx.getMessage(), sqlEx); throw new I2B2DAOException( "QueryResultPatientSetGenerator.generateResult:" + sqlEx.getMessage(), sqlEx); } finally { IQueryResultInstanceDao resultInstanceDao = sfDAOFactory.getPatientSetResultDAO(); if (errorFlag) { resultInstanceDao.updatePatientSet( resultInstanceId, QueryStatusTypeId.STATUSTYPE_ID_ERROR, 0); } else { // set the setsize and the description of the result instance if // the user role is obfuscated if (timeoutFlag == false) { // check if the query completed try { tm.begin(); String obfusMethod = "", description = null; if (obfscDataRoleFlag) { obfusMethod = IQueryResultInstanceDao.OBSUBTOTAL; // add () to the result type description // read the description from result type } else { obfuscatedRecordCount = actualTotal; } IQueryResultTypeDao resultTypeDao = sfDAOFactory.getQueryResultTypeDao(); List<QtQueryResultType> resultTypeList = resultTypeDao.getQueryResultTypeByName(resultTypeName); // add "(Obfuscated)" in the description // description = resultTypeList.get(0) // .getDescription() // + " (Obfuscated) "; String queryName = sfDAOFactory .getQueryMasterDAO() .getQueryDefinition( sfDAOFactory .getQueryInstanceDAO() .getQueryInstanceByInstanceId(queryInstanceId) .getQtQueryMaster() .getQueryMasterId()) .getName(); resultInstanceDao.updatePatientSet( resultInstanceId, QueryStatusTypeId.STATUSTYPE_ID_FINISHED, null, // obsfcTotal, obfuscatedRecordCount, actualTotal, obfusMethod); description = resultTypeList.get(0).getDescription() + " for \"" + queryName + "\""; // set the result instance description resultInstanceDao.updateResultInstanceDescription(resultInstanceId, description); tm.commit(); } catch (NotSupportedException e) { throw new I2B2DAOException( "Failed to write obfuscated description " + e.getMessage(), e); } catch (SystemException e) { throw new I2B2DAOException( "Failed to write obfuscated description " + e.getMessage(), e); } catch (SecurityException e) { throw new I2B2DAOException( "Failed to write obfuscated description " + e.getMessage(), e); } catch (IllegalStateException e) { throw new I2B2DAOException( "Failed to write obfuscated description " + e.getMessage(), e); } catch (RollbackException e) { throw new I2B2DAOException( "Failed to write obfuscated description " + e.getMessage(), e); } catch (HeuristicMixedException e) { throw new I2B2DAOException( "Failed to write obfuscated description " + e.getMessage(), e); } catch (HeuristicRollbackException e) { throw new I2B2DAOException( "Failed to write obfuscated description " + e.getMessage(), e); } } } } }