List of usage examples for java.sql SQLException toString
public String toString()
From source file:gov.nih.nci.cadsr.sentinel.database.DBAlertOracle.java
/** * Add the Report recipients to the sn_report_view_ext table. One (1) row * per recipient./* www . j av a 2 s . com*/ * * @param rec_ * The Alert definition to store in the database. * @return 0 if successful, otherwise the Oracle error code. * @throws java.sql.SQLException * On error with rollback(). */ private int insertRecipients(AlertRec rec_) throws SQLException { // Add the Recipient record(s). String insert = ""; int rc = 0; PreparedStatement pstmt = null; try { for (int ndx = 0; ndx < rec_.getRecipients().length; ++ndx) { // As we only populate 1 of possible 3 columns, the Insert // statement // will be dynamically configured. insert = "insert into sbrext.sn_recipient_view_ext "; String temp = rec_.getRecipients(ndx); if (temp.charAt(0) == '/') { // It must be a Context name. insert = insert + "(rep_idseq, conte_idseq, created_by)"; temp = temp.substring(1); } else if (temp.indexOf('@') > -1) { // It must be an email address. insert = insert + "(rep_idseq, email, created_by)"; if (temp.length() > DBAlert._MAXEMAILLEN) { temp = temp.substring(0, DBAlert._MAXEMAILLEN); rec_.setRecipients(ndx, temp); } } else if (temp.startsWith("http://") || temp.startsWith("https://")) { // It is an process URL remove the slash at the end of URL if it exists if (temp.endsWith("/")) { temp = temp.substring(0, temp.lastIndexOf("/")); } insert = insert + "(rep_idseq, email, created_by)"; if (temp.length() > DBAlert._MAXEMAILLEN) { temp = temp.substring(0, DBAlert._MAXEMAILLEN); rec_.setRecipients(ndx, temp); } } else { // It's a user name. insert = insert + "(rep_idseq, ua_name, created_by)"; } insert = insert + " values (?, ?, ?)"; // Update pstmt = _conn.prepareStatement(insert); pstmt.setString(1, rec_.getReportRecNum()); pstmt.setString(2, temp); pstmt.setString(3, _user); pstmt.executeUpdate(); pstmt.close(); pstmt = null; } // Remember to commit. It appears that we may flagging a commit when // the recipients list // is empty, however, the recipients list is never to be empty and // the other calling methods // depend on this to set the flag. _needCommit = true; } catch (SQLException ex) { // Ooops... _conn.rollback(); _errorCode = DBAlertUtil.getSQLErrorCode(ex); _errorMsg = _errorCode + ": " + insert + "\n\n" + ex.toString(); _logger.error(_errorMsg); rc = _errorCode; } finally { closeCursors(pstmt, null); } return rc; }
From source file:gov.nih.nci.cadsr.sentinel.database.DBAlertOracle.java
/** * Insert the Report details for the Alert definition into the database and * retrieve the new report id.//from w ww . j a v a2s . com * * @param rec_ * The Alert definition to be stored in the database. * @return 0 if successful, otherwise the database error code. * @throws java.sql.SQLException * On error with rollback(). */ private int insertReport(AlertRec rec_) throws SQLException { // Add the Report record. String insert = "begin insert into sbrext.sn_report_view_ext " + "(al_idseq, comments, include_property_ind, style, send, acknowledge_ind, assoc_lvl_num, created_by) " + "values (?, ?, ?, ?, ?, ?, ?, ?) return rep_idseq into ?; end;"; CallableStatement pstmt = null; int rc = 0; try { pstmt = _conn.prepareCall(insert); pstmt.setString(1, rec_.getAlertRecNum()); pstmt.setString(2, rec_.getIntro(false)); pstmt.setString(3, rec_.getIncPropSectString()); pstmt.setString(4, rec_.getReportStyleString()); pstmt.setString(5, rec_.getReportEmptyString()); pstmt.setString(6, rec_.getReportAckString()); pstmt.setInt(7, rec_.getIAssocLvl()); pstmt.setString(8, _user); pstmt.registerOutParameter(9, Types.CHAR); pstmt.executeUpdate(); // We need the record id to populate the foreign keys for other // tables. rec_.setReportRecNum(pstmt.getString(9)); } catch (SQLException ex) { // Ooops... rec_.setAlertRecNum(null); _conn.rollback(); _errorCode = DBAlertUtil.getSQLErrorCode(ex); _errorMsg = _errorCode + ": " + insert + "\n\n" + ex.toString(); _logger.error(_errorMsg); rc = _errorCode; } finally { closeCursors(pstmt, null); } return rc; }
From source file:gov.nih.nci.cadsr.sentinel.database.DBAlertOracle.java
/** * Insert the properties for the Alert definition and retrieve the new * database generated ID for this Alert. * * @param rec_/*from w ww .ja v a 2 s . com*/ * The Alert to be stored in the database. * @return 0 if successful, otherwise the database error code. * @throws java.sql.SQLException * On error with rollback(). */ private int insertProperties(AlertRec rec_) throws SQLException { // Define the SQL insert. Remember date_created, date_modified, creator // and modifier are controlled // by triggers. Also (as of 10/21/2004) after the insert the // date_modified is still set by the insert // trigger. String insert = "begin insert into sbrext.sn_alert_view_ext " + "(name, auto_freq_unit, al_status, begin_date, end_date, status_reason, auto_freq_value, created_by) " + "values (?, ?, ?, ?, ?, ?, ?, ?) return al_idseq into ?; end;"; CallableStatement pstmt = null; int rc = 0; cleanRec(rec_); try { // Set all the SQL arguments. pstmt = _conn.prepareCall(insert); pstmt.setString(1, rec_.getName()); pstmt.setString(2, rec_.getFreqString()); pstmt.setString(3, rec_.getActiveString()); pstmt.setTimestamp(4, rec_.getStart()); pstmt.setTimestamp(5, rec_.getEnd()); pstmt.setString(6, rec_.getInactiveReason(false)); pstmt.setInt(7, rec_.getDay()); pstmt.setString(8, _user); pstmt.registerOutParameter(9, Types.CHAR); // Insert the new record and flag a commit for later. pstmt.executeUpdate(); // We need the record id to populate the foreign keys for other // tables. rec_.setAlertRecNum(pstmt.getString(9)); } catch (SQLException ex) { // Ooops... rec_.setAlertRecNum(null); _conn.rollback(); _errorCode = DBAlertUtil.getSQLErrorCode(ex); _errorMsg = _errorCode + ": " + insert + "\n\n" + ex.toString(); _logger.error(_errorMsg); rc = _errorCode; } finally { closeCursors(pstmt, null); } return rc; }
From source file:gov.nih.nci.cadsr.sentinel.database.DBAlertOracle.java
/** * Read the Query clause from the database for the Alert definition * specified./*from w ww.jav a2s . co m*/ * * @param rec_ * The Alert record to contain the Query clause. * @return 0 if successful, otherwise the database error code. */ private int selectQuery(AlertRec rec_) { String select = "select record_type, data_type, property, value " + "from sbrext.sn_query_view_ext " + "where al_idseq = ?"; // order // by // record_type // ASC, // data_type // ASC, // property // ASC"; PreparedStatement pstmt = null; ResultSet rs = null; int rc = 0; try { pstmt = _conn.prepareStatement(select); pstmt.setString(1, rec_.getAlertRecNum()); rs = pstmt.executeQuery(); Vector<String> context = new Vector<String>(); Vector<String> actype = new Vector<String>(); Vector<String> scheme = new Vector<String>(); Vector<String> schemeitem = new Vector<String>(); Vector<String> form = new Vector<String>(); Vector<String> protocol = new Vector<String>(); Vector<String> creator = new Vector<String>(); Vector<String> modifier = new Vector<String>(); Vector<String> workflow = new Vector<String>(); Vector<String> regis = new Vector<String>(); Vector<String> cregis = new Vector<String>(); Vector<String> cwork = new Vector<String>(); // After reading the query set we have to partition it into the // appropriate individual // variables. As the data stored in the query is internal // representations there is // no point to attempting to order it. Also for any one Alert there // isn't enough rows // to warrant the extra coding or logical overhead. while (rs.next()) { char rtype = rs.getString(1).charAt(0); String dtype = rs.getString(2); String value = rs.getString(4); if (rtype == _CRITERIA) { if (dtype.equals(_CONTEXT)) context.add(value); else if (dtype.equals(_FORM)) form.add(value); else if (dtype.equals(_PROTOCOL)) protocol.add(value); else if (dtype.equals(_SCHEME)) scheme.add(value); else if (dtype.equals(_SCHEMEITEM)) schemeitem.add(value); else if (dtype.equals(_CREATOR)) creator.add(value); else if (dtype.equals(_MODIFIER)) modifier.add(value); else if (dtype.equals(_ACTYPE)) actype.add(value); else if (dtype.equals(_REGISTER)) cregis.add(value); else if (dtype.equals(_STATUS)) cwork.add(value); else if (dtype.equals(_DATEFILTER)) { rec_.setDateFilter(value); } } else if (rtype == _MONITORS) { if (dtype.equals(_STATUS)) workflow.add(value); else if (dtype.equals(_REGISTER)) regis.add(value); else if (dtype.equals(_VERSION)) { rec_.setAVersion(rs.getString(3)); rec_.setActVerNum(value); } } } // Move the data into appropriate arrays within the Alert record to // simplify use // downstream. String list[] = null; if (context.size() == 0) { rec_.setContexts(null); } else { list = new String[context.size()]; for (int ndx = 0; ndx < list.length; ++ndx) list[ndx] = (String) context.get(ndx); rec_.setContexts(list); } if (actype.size() == 0) { rec_.setACTypes(null); } else { list = new String[actype.size()]; for (int ndx = 0; ndx < list.length; ++ndx) list[ndx] = (String) actype.get(ndx); rec_.setACTypes(list); } if (protocol.size() == 0) { rec_.setProtocols(null); } else { list = new String[protocol.size()]; for (int ndx = 0; ndx < list.length; ++ndx) list[ndx] = (String) protocol.get(ndx); rec_.setProtocols(list); } if (form.size() == 0) { rec_.setForms(null); } else { list = new String[form.size()]; for (int ndx = 0; ndx < list.length; ++ndx) list[ndx] = (String) form.get(ndx); rec_.setForms(list); } if (scheme.size() == 0) { rec_.setSchemes(null); } else { list = new String[scheme.size()]; for (int ndx = 0; ndx < list.length; ++ndx) list[ndx] = (String) scheme.get(ndx); rec_.setSchemes(list); } if (schemeitem.size() == 0) { rec_.setSchemeItems(null); } else { list = new String[schemeitem.size()]; for (int ndx = 0; ndx < list.length; ++ndx) list[ndx] = (String) schemeitem.get(ndx); rec_.setSchemeItems(list); } if (cregis.size() == 0) { rec_.setCRegStatus(null); } else { list = new String[cregis.size()]; for (int ndx = 0; ndx < list.length; ++ndx) list[ndx] = (String) cregis.get(ndx); rec_.setCRegStatus(list); } if (cwork.size() == 0) { rec_.setCWorkflow(null); } else { list = new String[cwork.size()]; for (int ndx = 0; ndx < list.length; ++ndx) list[ndx] = (String) cwork.get(ndx); rec_.setCWorkflow(list); } if (creator.size() == 0) { rec_.setCreators(null); } else { list = new String[creator.size()]; for (int ndx = 0; ndx < list.length; ++ndx) list[ndx] = (String) creator.get(ndx); rec_.setCreators(list); } if (modifier.size() == 0) { rec_.setModifiers(null); } else { list = new String[modifier.size()]; for (int ndx = 0; ndx < list.length; ++ndx) list[ndx] = (String) modifier.get(ndx); rec_.setModifiers(list); } if (workflow.size() == 0) { rec_.setAWorkflow(null); } else { list = new String[workflow.size()]; for (int ndx = 0; ndx < list.length; ++ndx) list[ndx] = (String) workflow.get(ndx); rec_.setAWorkflow(list); } if (regis.size() == 0) { rec_.setARegis(null); } else { list = new String[regis.size()]; for (int ndx = 0; ndx < list.length; ++ndx) list[ndx] = (String) regis.get(ndx); rec_.setARegis(list); } // Create the summary string now the data is loaded. rec_.setSummary(buildSummary(rec_)); } catch (SQLException ex) { // Ooops... _errorCode = DBAlertUtil.getSQLErrorCode(ex); _errorMsg = _errorCode + ": " + select + "\n\n" + ex.toString(); _logger.error(_errorMsg); rc = _errorCode; } finally { closeCursors(pstmt, rs); } return rc; }
From source file:gov.nih.nci.cadsr.sentinel.database.DBAlertOracle.java
/** * Add the Query details to the Alert in the database. * * @param rec_//from w w w . j av a 2 s .c om * The Alert definition to be added to the database. * @return 0 if successful, otherwise the database error code. * @throws java.sql.SQLException * On error with rollback(). */ private int insertQuery(AlertRec rec_) throws SQLException { String insert = "insert into sbrext.sn_query_view_ext (al_idseq, record_type, data_type, property, value, created_by) " + "values (?, ?, ?, ?, ?, ?)"; int marker = 0; PreparedStatement pstmt = null; int rc = 0; try { pstmt = _conn.prepareStatement(insert); pstmt.setString(1, rec_.getAlertRecNum()); pstmt.setString(2, "C"); pstmt.setString(6, _user); // We only want to record those items selected by the user that // require special processing. For // example, if (All) contexts were selected by the user we do not // record (All) in the database // because the downstream processing of the Alert only cares about // looking for specific criteria // and monitors. In other words, we don't want to waste time // checking the context when (All) was // selected because it will always logically test true. ++marker; if (!rec_.isCONTall()) { pstmt.setString(3, _CONTEXT); pstmt.setString(4, "CONTE_IDSEQ"); for (int ndx = 0; ndx < rec_.getContexts().length; ++ndx) { // Update pstmt.setString(5, rec_.getContexts(ndx)); pstmt.executeUpdate(); } } ++marker; if (!rec_.isPROTOall()) { pstmt.setString(3, _PROTOCOL); pstmt.setString(4, "PROTO_IDSEQ"); for (int ndx = 0; ndx < rec_.getProtocols().length; ++ndx) { // Update pstmt.setString(5, rec_.getProtocols(ndx)); pstmt.executeUpdate(); } } ++marker; if (!rec_.isFORMSall()) { pstmt.setString(3, _FORM); pstmt.setString(4, "QC_IDSEQ"); for (int ndx = 0; ndx < rec_.getForms().length; ++ndx) { // Update pstmt.setString(5, rec_.getForms(ndx)); pstmt.executeUpdate(); } } ++marker; if (!rec_.isCSall()) { pstmt.setString(3, _SCHEME); pstmt.setString(4, "CS_IDSEQ"); for (int ndx = 0; ndx < rec_.getSchemes().length; ++ndx) { // Update pstmt.setString(5, rec_.getSchemes(ndx)); pstmt.executeUpdate(); } } ++marker; if (!rec_.isCSIall()) { pstmt.setString(3, _SCHEMEITEM); pstmt.setString(4, "CSI_IDSEQ"); for (int ndx = 0; ndx < rec_.getSchemeItems().length; ++ndx) { // Update pstmt.setString(5, rec_.getSchemeItems(ndx)); pstmt.executeUpdate(); } } ++marker; if (rec_.getCreators(0).equals(Constants._STRALL) == false) { pstmt.setString(3, _CREATOR); pstmt.setString(4, "UA_NAME"); for (int ndx = 0; ndx < rec_.getCreators().length; ++ndx) { // Update pstmt.setString(5, rec_.getCreators(ndx)); pstmt.executeUpdate(); } } ++marker; if (rec_.getModifiers(0).equals(Constants._STRALL) == false) { pstmt.setString(3, _MODIFIER); pstmt.setString(4, "UA_NAME"); for (int ndx = 0; ndx < rec_.getModifiers().length; ++ndx) { // Update pstmt.setString(5, rec_.getModifiers(ndx)); pstmt.executeUpdate(); } } ++marker; if (!rec_.isACTYPEall()) { pstmt.setString(3, _ACTYPE); pstmt.setString(4, "ABBREV"); for (int ndx = 0; ndx < rec_.getACTypes().length; ++ndx) { // Update pstmt.setString(5, rec_.getACTypes(ndx)); pstmt.executeUpdate(); } } ++marker; if (rec_.getDateFilter() != DBAlert._DATECM) { pstmt.setString(3, _DATEFILTER); pstmt.setString(4, "CODE"); pstmt.setString(5, Integer.toString(rec_.getDateFilter())); pstmt.executeUpdate(); } ++marker; if (!rec_.isCRSall()) { pstmt.setString(3, _REGISTER); pstmt.setString(4, "REGISTRATION_STATUS"); for (int ndx = 0; ndx < rec_.getCRegStatus().length; ++ndx) { // Update pstmt.setString(5, rec_.getCRegStatus(ndx)); pstmt.executeUpdate(); } } ++marker; if (!rec_.isCWFSall()) { pstmt.setString(3, _STATUS); pstmt.setString(4, "ASL_NAME"); for (int ndx = 0; ndx < rec_.getCWorkflow().length; ++ndx) { // Update pstmt.setString(5, rec_.getCWorkflow(ndx)); pstmt.executeUpdate(); } } marker += 100; pstmt.setString(2, "M"); ++marker; if (rec_.getAWorkflow(0).equals(Constants._STRANY) == false) { pstmt.setString(3, _STATUS); pstmt.setString(4, "ASL_NAME"); for (int ndx = 0; ndx < rec_.getAWorkflow().length; ++ndx) { // Update pstmt.setString(5, rec_.getAWorkflow(ndx)); pstmt.executeUpdate(); } } ++marker; if (rec_.getARegis(0).equals(Constants._STRANY) == false) { pstmt.setString(3, _REGISTER); pstmt.setString(4, "REGISTRATION_STATUS"); for (int ndx = 0; ndx < rec_.getARegis().length; ++ndx) { // Update pstmt.setString(5, rec_.getARegis(ndx)); pstmt.executeUpdate(); } } ++marker; if (rec_.getAVersion() != DBAlert._VERANYCHG) { pstmt.setString(3, _VERSION); pstmt.setString(4, rec_.getAVersionString()); pstmt.setString(5, rec_.getActVerNum()); pstmt.executeUpdate(); } // Remember to commit. ++marker; _needCommit = true; } catch (SQLException ex) { // Ooops... _conn.rollback(); _errorCode = DBAlertUtil.getSQLErrorCode(ex); _errorMsg = "(" + marker + "): " + _errorCode + ": " + insert + "\n\n" + ex.toString(); _logger.error(_errorMsg); rc = _errorCode; } finally { closeCursors(pstmt, null); } return rc; }
From source file:com.mysql.stresstool.RunnableQueryInsertPCH.java
public void run() { BufferedReader d = null;// w w w.j a va2 s . co m Connection conn = null; if (conn == null) { try { long execTime = 0; int pkStart = 0; int pkEnds = 0; int intDeleteInterval = 0; int intBlobInterval = 0; int intBlobIntervalLimit = StressTool.getNumberFromRandom(4).intValue(); ThreadInfo thInfo; long threadTimeStart = System.currentTimeMillis(); active = true; thInfo = new ThreadInfo(); thInfo.setId(this.ID); thInfo.setType("insert"); thInfo.setStatusActive(this.isActive()); StressTool.setInfo(this.ID, thInfo); boolean lazy = false; int lazyInterval = 0; for (int repeat = 0; repeat <= repeatNumber; repeat++) { try { if (conn != null && !conn.isClosed()) { conn.close(); } SoftReference sf = new SoftReference( DriverManager.getConnection((String) jdbcUrlMap.get("jdbcUrl"))); conn = (Connection) sf.get(); } catch (SQLException ex) { ex.printStackTrace(); } Statement stmt = null; // ResultSet rs = null; // ResultSet rs2 = null; conn.setAutoCommit(false); stmt = conn.createStatement(); stmt.execute("SET AUTOCOMMIT=0"); ResultSet rs = null; int ServerId = 0; String query = null; ArrayList insert1 = null; ArrayList insert2 = null; int pk = 0; { SoftReference sf = new SoftReference( stmt.executeQuery("show global variables like 'SERVER_ID'")); rs = (ResultSet) sf.get(); } rs.next(); ServerId = rs.getInt(2); if (repeat > 0 && lazyInterval < 500) { lazy = true; ++lazyInterval; } else { lazy = false; lazyInterval = 0; } intBlobInterval = StressTool.getNumberFromRandom(10).intValue(); // intBlobInterval++; //IMPLEMENTING lazy Vector v = null; { SoftReference sf = new SoftReference(this.getTablesValues(lazy, ServerId)); v = (Vector) sf.get(); } insert1 = (ArrayList<String>) v.get(0); insert2 = (ArrayList<String>) v.get(1); // System.out.println(insert1); // System.out.println(insert2); // pk = ((Integer) v.get(2)).intValue(); int[] iLine = { 0, 0 }; // pkStart = StressTool.getNumberFromRandom(2147483647).intValue(); // pkEnds = StressTool.getNumberFromRandom(2147483647).intValue(); try { long timeStart = System.currentTimeMillis(); if (this.ignoreBinlog) stmt.execute("SET sql_log_bin=0"); stmt.execute("SET GLOBAL max_allowed_packet=10737418"); if (dbType.equals("MySQL") && !engine.toUpperCase().equals("BRIGHTHOUSE")) stmt.execute("BEGIN"); else stmt.execute("COMMIT"); // stmt.execute("SET TRANSACTION NAME 'TEST'"); { Iterator<String> it = insert1.iterator(); while (it.hasNext()) { stmt.addBatch(it.next()); } } if (!this.doSimplePk) { // System.out.println("Blob insert value :" + intBlobInterval); if (intBlobInterval > intBlobIntervalLimit) { Iterator<String> it = insert2.iterator(); while (it.hasNext()) { stmt.addBatch(it.next()); } // intBlobInterval=0; } } iLine = stmt.executeBatch(); stmt.clearBatch(); // System.out.println("Query1 = " + insert1); // System.out.println("Query2 = " + insert2); // stmt.execute("START TRANSACTION"); // stmt.execute(insert1); // iLine = stmt.executeBatch(); // conn.commit(); long timeEnds = System.currentTimeMillis(); execTime = (timeEnds - timeStart); } catch (Exception sqle) { conn.rollback(); if (StressTool.getErrorLogHandler() != null) { StressTool.getErrorLogHandler().appendToFile(("FAILED QUERY1==" + insert1)); StressTool.getErrorLogHandler().appendToFile(("FAILED QUERY2==" + insert2)); StressTool.getErrorLogHandler().appendToFile(sqle.toString()); } else { sqle.printStackTrace(); System.out.println("FAILED QUERY1==" + insert1); System.out.println("FAILED QUERY2==" + insert2); sqle.printStackTrace(); System.exit(1); } //conn.close(); //this.setJdbcUrl(jdbcUrl); //System.out.println("Query Insert TH RE-INIZIALIZING"); } finally { // conn.commit(); stmt.execute("COMMIT"); rs.close(); stmt.close(); rs = null; stmt = null; // intDeleteInterval++; if (doLog) { System.out.println("Query Insert TH = " + this.getID() + " Loop N = " + repeat + " " + iLine[0] + "|" + ((iLine.length > 1) ? iLine[1] : 0) + " Exec Time(ms) =" + execTime + " Running = " + repeat + " of " + repeatNumber + " to go =" + (repeatNumber - repeat) + " Using Lazy=" + lazy); } } thInfo.setExecutedLoops(repeat); if (sleepFor > 0 || this.getSleepWrite() > 0) { if (this.getSleepWrite() > 0) { Thread.sleep(getSleepWrite()); } else Thread.sleep(sleepFor); } conn.close(); conn = null; } long threadTimeEnd = System.currentTimeMillis(); this.executionTime = (threadTimeEnd - threadTimeStart); // this.setExecutionTime(executionTime); active = false; // System.out.println("Query Insert TH = " + this.getID() + " COMPLETED! TOTAL TIME = " + execTime + "(ms) Sec =" + (execTime/1000)); thInfo.setExecutionTime(executionTime); thInfo.setStatusActive(false); StressTool.setInfo(this.ID, thInfo); return; } catch (Exception ex) { if (StressTool.getErrorLogHandler() != null) { StressTool.getErrorLogHandler().appendToFile(ex.toString() + "\n"); } else ex.printStackTrace(); try { conn.close(); conn = null; } catch (SQLException e) { // TODO Auto-generated catch block if (StressTool.getErrorLogHandler() != null) { StressTool.getErrorLogHandler().appendToFile(e.toString() + "\n"); conn = null; } else e.printStackTrace(); } } } }
From source file:at.alladin.rmbt.controlServer.SyncResource.java
@Post("json") public String request(final String entity) { addAllowOrigin();/*from w ww .jav a 2s .c om*/ JSONObject request = null; final ErrorList errorList = new ErrorList(); final JSONObject answer = new JSONObject(); String answerString; System.out.println(MessageFormat.format(labels.getString("NEW_SYNC_REQUEST"), getIP())); if (entity != null && !entity.isEmpty()) // try parse the string to a JSON object try { request = new JSONObject(entity); String lang = request.optString("language"); // Load Language Files for Client final List<String> langs = Arrays .asList(settings.getString("RMBT_SUPPORTED_LANGUAGES").split(",\\s*")); if (langs.contains(lang)) { errorList.setLanguage(lang); labels = ResourceManager.getSysMsgBundle(new Locale(lang)); } else lang = settings.getString("RMBT_DEFAULT_LANGUAGE"); // System.out.println(request.toString(4)); if (conn != null) { final JSONArray syncList = new JSONArray(); UUID uuid = null; if (request.optString("uuid").length() > 0) uuid = UUID.fromString(request.getString("uuid")); if (uuid != null && request.optString("sync_code").length() == 0) { String syncCode = ""; try { final PreparedStatement st = conn .prepareStatement("SELECT rmbt_get_sync_code(CAST (? AS UUID)) AS code"); st.setString(1, uuid.toString()); final ResultSet rs = st.executeQuery(); if (rs.next()) syncCode = rs.getString("code"); else errorList.addError("ERROR_DB_GET_SYNC_SQL"); // errorList.addError(MessageFormat.format(labels.getString("ERROR_DB_GET_CLIENT"), // new Object[] {uuid})); rs.close(); st.close(); } catch (final SQLException e) { e.printStackTrace(); errorList.addError("ERROR_DB_GET_SYNC_SQL"); // errorList.addError("ERROR_DB_GET_CLIENT_SQL"); } if (errorList.getLength() == 0) { final JSONObject jsonItem = new JSONObject(); //lower case code is easier to enter on mobile devices jsonItem.put("sync_code", syncCode.toLowerCase(Locale.US)); syncList.put(jsonItem); } } else if (uuid != null && request.optString("sync_code").length() > 0) { final String syncCode = request.getString("sync_code").toUpperCase(Locale.US); int syncGroup1 = 0; int uid1 = 0; int syncGroup2 = 0; int uid2 = 0; String msgTitle = labels.getString("SYNC_SUCCESS_TITLE"); String msgText = labels.getString("SYNC_SUCCESS_TEXT"); boolean error = false; try { PreparedStatement st = conn.prepareStatement( "SELECT * FROM client WHERE sync_code = ? AND sync_code_timestamp + INTERVAL '1 month' > NOW()"); st.setString(1, syncCode); ResultSet rs = st.executeQuery(); if (rs.next()) { syncGroup1 = rs.getInt("sync_group_id"); uid1 = rs.getInt("uid"); } else { msgTitle = labels.getString("SYNC_CODE_TITLE"); msgText = labels.getString("SYNC_CODE_TEXT"); error = true; // errorList.addError(MessageFormat.format(labels.getString("ERROR_DB_GET_CLIENT"), // new Object[] {uuid})); } rs.close(); st.close(); st = conn.prepareStatement("SELECT * FROM client WHERE uuid = CAST(? AS UUID)"); st.setString(1, uuid.toString()); rs = st.executeQuery(); if (rs.next()) { syncGroup2 = rs.getInt("sync_group_id"); uid2 = rs.getInt("uid"); } else { msgTitle = labels.getString("SYNC_UUID_TITLE"); msgText = labels.getString("SYNC_UUID_TEXT"); error = true; // errorList.addError(MessageFormat.format(labels.getString("ERROR_DB_GET_CLIENT"), // new Object[] {uuid})); } rs.close(); st.close(); if (syncGroup1 > 0 && syncGroup1 == syncGroup2) { msgTitle = labels.getString("SYNC_GROUP_TITLE"); msgText = labels.getString("SYNC_GROUP_TEXT"); error = true; } if (uid1 > 0 && uid1 == uid2) { msgTitle = labels.getString("SYNC_CLIENT_TITLE"); msgText = labels.getString("SYNC_CLIENT_TEXT"); error = true; } if (!error) if (syncGroup1 == 0 && syncGroup2 == 0) { int key = 0; // create new group st = conn.prepareStatement("INSERT INTO sync_group(tstamp) " + "VALUES(now())", Statement.RETURN_GENERATED_KEYS); int affectedRows = st.executeUpdate(); if (affectedRows == 0) errorList.addError("ERROR_DB_STORE_SYNC_GROUP"); else { rs = st.getGeneratedKeys(); if (rs.next()) // Retrieve the auto generated // key(s). key = rs.getInt(1); rs.close(); } st.close(); if (key > 0) { st = conn.prepareStatement( "UPDATE client SET sync_group_id = ? WHERE uid = ? OR uid = ?"); st.setInt(1, key); st.setInt(2, uid1); st.setInt(3, uid2); affectedRows = st.executeUpdate(); if (affectedRows == 0) errorList.addError("ERROR_DB_UPDATE_SYNC_GROUP"); } } else if (syncGroup1 == 0 && syncGroup2 > 0) { // add 1 to 2 st = conn.prepareStatement("UPDATE client SET sync_group_id = ? WHERE uid = ?"); st.setInt(1, syncGroup2); st.setInt(2, uid1); final int affectedRows = st.executeUpdate(); if (affectedRows == 0) errorList.addError("ERROR_DB_UPDATE_SYNC_GROUP"); } else if (syncGroup1 > 0 && syncGroup2 == 0) { // add 2 to 1 st = conn .prepareStatement("UPDATE client SET sync_group_id = ? WHERE uid = ? "); st.setInt(1, syncGroup1); st.setInt(2, uid2); final int affectedRows = st.executeUpdate(); if (affectedRows == 0) errorList.addError("ERROR_DB_UPDATE_SYNC_GROUP"); } else if (syncGroup1 > 0 && syncGroup2 > 0) { // add all of 2 to 1 st = conn.prepareStatement( "UPDATE client SET sync_group_id = ? WHERE sync_group_id = ?"); st.setInt(1, syncGroup1); st.setInt(2, syncGroup2); int affectedRows = st.executeUpdate(); if (affectedRows == 0) errorList.addError("ERROR_DB_UPDATE_SYNC_GROUP"); else { // Delete empty group st = conn.prepareStatement("DELETE FROM sync_group WHERE uid = ?"); st.setInt(1, syncGroup2); affectedRows = st.executeUpdate(); if (affectedRows == 0) errorList.addError("ERROR_DB_DELETE_SYNC_GROUP"); } } } catch (final SQLException e) { e.printStackTrace(); errorList.addError("ERROR_DB_GET_SYNC_SQL"); // errorList.addError("ERROR_DB_GET_CLIENT_SQL"); } if (errorList.getLength() == 0) { final JSONObject jsonItem = new JSONObject(); jsonItem.put("msg_title", msgTitle); jsonItem.put("msg_text", msgText); jsonItem.put("success", !error); syncList.put(jsonItem); } } answer.put("sync", syncList); } else errorList.addError("ERROR_DB_CONNECTION"); } catch (final JSONException e) { errorList.addError("ERROR_REQUEST_JSON"); System.out.println("Error parsing JSDON Data " + e.toString()); } else errorList.addErrorString("Expected request is missing."); try { answer.putOpt("error", errorList.getList()); } catch (final JSONException e) { System.out.println("Error saving ErrorList: " + e.toString()); } answerString = answer.toString(); return answerString; }
From source file:edu.ku.brc.specify.conversion.GenericDBConversion.java
/** * Converts Object Defs.//from ww w. ja v a 2s. c om * @param specifyUserId * @return true on success, false on failure */ public boolean convertCollectionObjectTypes(final int specifyUserId) { try { HashSet<Integer> hashSet = new HashSet<Integer>(); StringBuilder inSB = new StringBuilder(); for (CollectionInfo ci : collectionInfoShortList) { if (!hashSet.contains(ci.getTaxonomyTypeId())) { if (inSB.length() > 0) inSB.append(','); inSB.append(ci.getTaxonomyTypeId()); hashSet.add(ci.getTaxonomyTypeId()); } } StringBuilder sb = new StringBuilder( "SELECT TaxonomyTypeID FROM taxonomytype WHERE TaxonomyTypeId in ("); sb.append(inSB); sb.append(')'); log.debug(sb.toString()); // This mapping is used by Discipline //for (Object txTypIdObj : BasicSQLUtils.querySingleCol(oldDBConn, sb.toString())) //{ // Integer txTypId = (Integer)txTypIdObj; // taxonomyTypeMapper.put(txTypId, getNextIndex()); //} // Create a Hashtable to track which IDs have been handled during the conversion process deleteAllRecordsFromTable(newDBConn, "datatype", BasicSQLUtils.myDestinationServerType); deleteAllRecordsFromTable(newDBConn, "discipline", BasicSQLUtils.myDestinationServerType); deleteAllRecordsFromTable(newDBConn, "collection", BasicSQLUtils.myDestinationServerType); // BasicSQLUtils.deleteAllRecordsFromTable(newDBConn, "collection_colobjdef"); Hashtable<Integer, Integer> newColObjIDTotaxonomyTypeID = new Hashtable<Integer, Integer>(); TableWriter tblWriter = convLogger.getWriter("convertCollectionObjectTypes.html", "Collection Object Type"); // Create a Hashed List of CollectionInfo for each unique TaxonomyTypeId // where the TaxonomyTypeId is a Discipline HashMap<Integer, Vector<CollectionInfo>> collDispHash = new HashMap<Integer, Vector<CollectionInfo>>(); for (CollectionInfo info : collectionInfoShortList) { Vector<CollectionInfo> colInfoList = collDispHash.get(info.getTaxonomyTypeId()); if (colInfoList == null) { colInfoList = new Vector<CollectionInfo>(); collDispHash.put(info.getTaxonomyTypeId(), colInfoList); } colInfoList.add(info); } String dateTimeNow = dateTimeFormatter.format(now); int collectionCnt = 0; for (Integer taxonTypeId : collDispHash.keySet()) { Vector<CollectionInfo> collInfoList = collDispHash.get(taxonTypeId); // Pick any of the CollectionInfo objects because they will // all share the same Discipline CollectionInfo info = null; for (CollectionInfo ci : collInfoList) { if (ci.getCatSeriesId() != null) { info = ci; break; } } if (info == null) { //UIRegistry.showError("No viable CatSeriesId to create Discipline. \n(Picking one...)"); info = collInfoList.get(0); //System.exit(0); } String taxonomyTypeName = info.getTaxonomyTypeName(); Integer taxonomyTypeID = info.getTaxonomyTypeId(); String lastEditedBy = null; String msg = "Creating a new Discipline for taxonomyTypeName[" + taxonomyTypeName + "] disciplineType[" + disciplineType.getTitle() + "]"; log.info(msg); tblWriter.log(msg); DisciplineType disciplineTypeObj = info.getDisciplineTypeObj(); if (disciplineTypeObj == null) { disciplineTypeObj = getStandardDisciplineName(info.getTaxonomyTypeName(), info.getColObjTypeName(), info.getCatSeriesName()); } taxonomyTypeName = disciplineTypeObj.getName(); // Figure out what type of standard data type this is from the // CollectionObjectTypeName setIdentityInsertOFFCommandForSQLServer(newDBConn, "datatype", BasicSQLUtils.myDestinationServerType); int dataTypeId = createDataType(); if (dataTypeId == -1) { msg = "**** Had to Skip record because of DataType mapping error[" + taxonomyTypeName + "]"; log.error(msg); tblWriter.logError(msg); System.exit(1); } String taxTypeName = info.getTaxonomyTypeName(); lastEditedBy = info.getCatSeriesLastEditedBy(); taxonomyTypeID = info.getTaxonomyTypeId(); //System.err.println(String.format("TaxonomyTypeName: %s taxonomyTypeID: %d", taxTypeName, taxonomyTypeID, info.get)); //--------------------------------------------------------------------------------- //-- Create Discipline //--------------------------------------------------------------------------------- //Integer newColObjDefID = getNextIndex();//taxonomyTypeMapper.get(taxonomyTypeID); //if (newColObjDefID == null) //{ // UIRegistry.showError("Was unable to map old TaxonomyTypeId["+taxonomyTypeID+"] to new ColectionObjectDefId. \nSeries Name: ["+info.getCatSeriesName()+"]\n(Exiting...)"); // //System.exit(0); //} // use the old CollectionObjectTypeName as the new Discipline name setIdentityInsertONCommandForSQLServer(newDBConn, "discipline", BasicSQLUtils.myDestinationServerType); Statement updateStatement = newDBConn.createStatement(); StringBuilder strBuf2 = new StringBuilder(); curDisciplineID = getNextIndex(); info.setDisciplineId(curDisciplineID); // adding DivisioniID strBuf2.setLength(0); strBuf2.append( "INSERT INTO discipline (DisciplineID, TimestampModified, Type, Name, TimestampCreated, "); strBuf2.append( "DataTypeID, GeographyTreeDefID, GeologicTimePeriodTreeDefID, TaxonTreeDefID, DivisionID, "); strBuf2.append( "CreatedByAgentID, ModifiedByAgentID, Version, UserGroupScopeId, PaleoContextChildTable, IsPaleoContextEmbedded) VALUES ("); strBuf2.append(info.getDisciplineId() + ","); strBuf2.append("'" + dateTimeNow + "',"); // TimestampModified strBuf2.append("'" + disciplineTypeObj.getName() + "',"); strBuf2.append("'" + disciplineTypeObj.getTitle() + "',"); strBuf2.append("'" + dateTimeNow + "',"); // TimestampCreated strBuf2.append(dataTypeId + ","); strBuf2.append("1,"); // GeographyTreeDefID strBuf2.append("1,"); // GeologicTimePeriodTreeDefID strBuf2.append("1,"); // TaxonTreeDefID strBuf2.append(division.getDivisionId() + ","); // DivisionID strBuf2.append(getCreatorAgentId(null) + "," + getModifiedByAgentIdForAgent(lastEditedBy) + ",0, "); strBuf2.append(curDisciplineID + ","); // UserGroupScopeId strBuf2.append("'collectionobject', true)"); //PaleoContextChildTable, IsPaleoContextEmbedded // strBuf2.append("NULL)");// UserPermissionID//User/Security changes log.info(strBuf2.toString()); removeForeignKeyConstraints(newDBConn, BasicSQLUtils.myDestinationServerType); updateStatement.executeUpdate(strBuf2.toString()); updateStatement.clearBatch(); updateStatement.close(); updateStatement = null; setIdentityInsertOFFCommandForSQLServer(newDBConn, "discipline", BasicSQLUtils.myDestinationServerType); //Integer disciplineID = getHighestId(newDBConn, "DisciplineID", "discipline"); newColObjIDTotaxonomyTypeID.put(curDisciplineID, taxonomyTypeID); msg = "**** Created new discipline[" + taxonomyTypeName + "] is dataType [" + dataTypeId + "]"; log.info(msg); tblWriter.log(msg); Session localSession = HibernateUtil.getNewSession(); for (CollectionInfo collInfo : collInfoList) { Integer catalogSeriesID = collInfo.getCatSeriesId(); String seriesName = collInfo.getCatSeriesName(); String prefix = collInfo.getCatSeriesPrefix(); String remarks = collInfo.getCatSeriesRemarks(); collInfo.setDisciplineId(curDisciplineID); AutoNumberingScheme cns = null; if (catalogSeriesID != null && isNotEmpty(seriesName)) { cns = catSeriesToAutoNumSchemeHash.get(catalogSeriesID); if (cns == null) { try { cns = new AutoNumberingScheme(); cns.initialize(); cns.setIsNumericOnly(true); cns.setSchemeClassName(""); cns.setSchemeName(seriesName); cns.setTableNumber(CollectionObject.getClassTableId()); Transaction trans = localSession.beginTransaction(); localSession.save(cns); trans.commit(); catSeriesToAutoNumSchemeHash.put(catalogSeriesID, cns); } catch (Exception ex) { ex.printStackTrace(); throw new RuntimeException(ex); } } } else { seriesName = taxTypeName; } Integer catNumSchemeId = cns != null ? cns.getAutoNumberingSchemeId() : null; collInfo.setCollectionId(getNextIndex()); curCollectionID = collInfo.getCollectionId(); msg = "**** Created new Collection [" + seriesName + "] is curCollectionID [" + curCollectionID + "]"; log.info(msg); updateStatement = newDBConn.createStatement(); strBuf.setLength(0); strBuf.append( "INSERT INTO collection (CollectionID, DisciplineID, CollectionName, Code, Remarks, CatalogFormatNumName, "); strBuf.append( "IsEmbeddedCollectingEvent, TimestampCreated, TimestampModified, CreatedByAgentID, ModifiedByAgentID, "); strBuf.append("Version, UserGroupScopeId) VALUES ("); strBuf.append(curCollectionID + ","); strBuf.append(curDisciplineID + ","); strBuf.append(getStrValue(seriesName) + ","); strBuf.append(getStrValue(prefix) + ","); strBuf.append(getStrValue(remarks) + ","); strBuf.append("'CatalogNumberNumeric',"); strBuf.append((isSharingCollectingEvents ? 0 : 1) + ","); strBuf.append("'" + dateTimeFormatter.format(now) + "',"); // TimestampModified strBuf.append("'" + dateTimeFormatter.format(now) + "',"); // TimestampCreated strBuf.append(getCreatorAgentId(null) + "," + getModifiedByAgentId(lastEditedBy) + ", 0, "); strBuf.append(curCollectionID); // UserGroupScopeId strBuf.append(")"); log.debug(strBuf.toString()); updateStatement.executeUpdate(strBuf.toString()); //curCollectionID = getInsertedId(updateStatement); updateStatement.clearBatch(); updateStatement.close(); updateStatement = null; if (catNumSchemeId != null && catalogSeriesID != null) { joinCollectionAndAutoNum(curCollectionID, catNumSchemeId); String hashKey = catalogSeriesID + "_" + taxonomyTypeID; Integer newCatSeriesID = getHighestId(newDBConn, "CollectionID", "collection"); collectionHash.put(hashKey, newCatSeriesID); if (isNotEmpty(prefix)) { prefixHash.put(hashKey, prefix); } msg = "Collection New[" + newCatSeriesID + "] [" + seriesName + "] [" + prefix + "] curDisciplineID[" + curDisciplineID + "]"; } else { msg = "Collection New[" + seriesName + "] [" + prefix + "] curDisciplineID[" + curDisciplineID + "]"; } log.info(msg); tblWriter.log(msg); //recordCnt++; //msg = "Collection Join Records: " + recordCnt; //log.info(msg); //tblWriter.log(msg); //rs.close(); //stmt.close(); collectionCnt++; } // Collection for loop localSession.close(); } // for loop tblWriter.close(); for (CollectionInfo ci : collectionInfoShortList) { if (ci.getCatSeriesId() != null) { log.debug("Cat Series: " + ci.getCatSeriesId() + " " + ci.getCollectionId()); Vector<Integer> colList = catSeriesToNewCollectionID.get(ci.getCatSeriesId()); if (colList == null) { colList = new Vector<Integer>(); catSeriesToNewCollectionID.put(ci.getCatSeriesId(), colList); } colList.add(ci.getCollectionId()); } } for (Integer catSeriesId : catSeriesToNewCollectionID.keySet()) { Vector<Integer> colList = catSeriesToNewCollectionID.get(catSeriesId); if (colList.size() > 1) { UIRegistry.showError( "There are multiple Collections assigned to the same CatalogSeries and we can't handle that right now."); return false; } } return true; } catch (SQLException e) { e.printStackTrace(); log.error(e); showError(e.toString()); System.exit(0); } return false; }
From source file:edu.ku.brc.specify.conversion.GenericDBConversion.java
/** * Converts all the Determinations.//from w ww .jav a 2 s . c om * @return true if no errors */ public boolean convertDeterminationRecords() { TableWriter tblWriter = convLogger.getWriter("convertDeterminations.html", "Determinations"); setIdentityInsertONCommandForSQLServer(newDBConn, "determination", BasicSQLUtils.myDestinationServerType); deleteAllRecordsFromTable(newDBConn, "determination", BasicSQLUtils.myDestinationServerType); // automatically closes the connection if (getNumRecords(oldDBConn, "determination") == 0) { return true; } TimeLogger timeLogger = new TimeLogger(); String oldDetermination_Current = "Current"; String oldDetermination_Date = "Date"; /*if (BasicSQLUtils.mySourceServerType == BasicSQLUtils.SERVERTYPE.MySQL) { oldDetermination_Date = "Date1"; oldDetermination_Current = "IsCurrent"; }*/ Map<String, String> colNewToOldMap = createFieldNameMap( new String[] { "CollectionObjectID", "BiologicalObjectID", // meg is this right? "IsCurrent", oldDetermination_Current, "DeterminedDate", oldDetermination_Date, // want to change over to DateField TODO Meg!!! "TaxonID", "TaxonNameID" }); try { Statement stmt = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); stmt.setFetchSize(Integer.MIN_VALUE); List<String> oldFieldNames = new ArrayList<String>(); StringBuilder sql = new StringBuilder("SELECT "); List<String> names = getFieldNamesFromSchema(oldDBConn, "determination"); sql.append(buildSelectFieldList(names, "determination")); oldFieldNames.addAll(names); sql.append( ", cc.CatalogSeriesID AS CatSeriesID FROM determination Inner Join collectionobjectcatalog AS cc ON determination.BiologicalObjectID = cc.CollectionObjectCatalogID"); log.info(sql); if (BasicSQLUtils.mySourceServerType == BasicSQLUtils.SERVERTYPE.MS_SQLServer) { log.debug("FIXING select statement to run against SQL Server......."); log.debug("old string: " + sql.toString()); String currentSQL = sql.toString(); currentSQL = currentSQL.replaceAll("Current", "[" + "Current" + "]"); log.debug("new string: " + currentSQL); sql = new StringBuilder(currentSQL); } oldFieldNames.add("CatSeriesID"); log.info(sql); List<FieldMetaData> newFieldMetaData = getFieldMetaDataFromSchema(newDBConn, "determination"); log.info("Number of Fields in New Determination " + newFieldMetaData.size()); String sqlStr = sql.toString(); Map<String, Integer> oldNameIndex = new Hashtable<String, Integer>(); int inx = 1; for (String name : oldFieldNames) { oldNameIndex.put(name, inx++); } String tableName = "determination"; //int isCurrentInx = oldNameIndex.get(oldDetermination_Current) + 1; log.info(sqlStr); System.err.println(sqlStr); ResultSet rs = stmt.executeQuery(sqlStr); if (hasFrame) { if (rs.last()) { setProcess(0, rs.getRow()); rs.first(); } else { rs.close(); stmt.close(); return true; } } else { if (!rs.first()) { rs.close(); stmt.close(); return true; } } PartialDateConv partialDateConv = new PartialDateConv(); IdMapperIFace detIdMapper = IdMapperMgr.getInstance().get("determination", "DeterminationID"); IdMapperIFace colObjIdMapper = idMapperMgr.get("collectionobjectcatalog", "CollectionObjectCatalogID"); IdMapperIFace colObjCatIdMapper = idMapperMgr.get("collectionobject", "CollectionObjectID"); Integer catSeriesIdInx = oldNameIndex.get("CatSeriesID"); Integer oldRecIDInx = oldNameIndex.get("DeterminationID"); int lastEditedByInx = oldNameIndex.get("LastEditedBy"); Integer detDateInx = oldNameIndex.get("Date"); System.err.println("catSeriesIdInx: " + catSeriesIdInx); HashMap<String, Integer> nameToInxHash = new HashMap<String, Integer>(); StringBuffer fieldList = new StringBuffer(); StringBuilder insertQuesDB = new StringBuilder(); for (int i = 0; i < newFieldMetaData.size(); i++) { if (i > 0) { fieldList.append(','); insertQuesDB.append(','); } String newFieldName = newFieldMetaData.get(i).getName(); fieldList.append(newFieldName); insertQuesDB.append('?'); nameToInxHash.put(newFieldName, (i + 1)); System.out.println(newFieldName + " " + (i + 1)); } String insertStmtStr = "INSERT INTO determination (" + fieldList + ") VALUES (" + insertQuesDB.toString() + ')'; log.debug(insertStmtStr); PreparedStatement pStmt = newDBConn.prepareStatement(insertStmtStr); int count = 0; do { partialDateConv.nullAll(); String lastEditedBy = rs.getString(lastEditedByInx); Integer catSeriesId = rs.getInt(catSeriesIdInx); if (catSeriesId != null && rs.wasNull()) { String msg = String.format( "Error - The Determination had a null CatalogSeries DeterminationID %d; it's CollectionObjectID: %d", rs.getInt(1), rs.getInt(6)); log.error(msg); tblWriter.logError(msg); //if (rs.next()) //{ continue; //} //break; } Vector<Integer> collectionIdList = catSeriesToNewCollectionID.get(catSeriesId); if (collectionIdList == null) { //Integer colObjId = rs.getInt(idIndex); throw new RuntimeException("There are no Collections mapped to CatSeriesId[" + catSeriesId + "] (converting Determinations)"); } if (collectionIdList.size() > 1) { UIRegistry.showError( "There are multiple Collections assigned to the same CatalogSeries and we can't handle that right now."); } Integer collectionId = collectionIdList.get(0); if (collectionId == null) { throw new RuntimeException("CollectionId is null when mapped from CatSeriesId"); } this.curCollectionID = collectionId; boolean isError = false; for (int i = 0; i < newFieldMetaData.size(); i++) { String newFieldName = newFieldMetaData.get(i).getName(); int fldInx = nameToInxHash.get(newFieldName); if (i == 0) { Integer recId = rs.getInt(oldRecIDInx); Integer newId = detIdMapper.get(recId); if (newId != null) { pStmt.setInt(fldInx, newId); } else { String msg = String.format("Error - Unable to map old id %d to new Id", recId); log.error(msg); tblWriter.logError(msg); isError = true; continue; } } else if (newFieldName.equals("Version")) // User/Security changes { pStmt.setInt(fldInx, 0); } else if (newFieldName.equals("DeterminedDate")) { //System.out.println("["+rs.getObject(detDateInx)+"]"); if (partialDateConv.getDateStr() == null) { getPartialDate(rs.getObject(detDateInx), partialDateConv); } if (!partialDateConv.isNull()) { int len = partialDateConv.getDateStr().length(); if (len == 12) { String tsStr = partialDateConv.getDateStr().length() == 12 ? partialDateConv.getDateStr().substring(1, 11) : partialDateConv.getDateStr(); pStmt.setString(fldInx, tsStr); } else { if (!partialDateConv.getDateStr().equals("NULL")) log.error("Determined Date was in error[" + partialDateConv.getDateStr() + "]"); pStmt.setObject(fldInx, null); } } else { pStmt.setObject(fldInx, null); } /* if (partialDateConv.getDateStr() == null) { getPartialDate(rs.getObject(detDateInx), partialDateConv); } if (isNotEmpty(partialDateConv.getDateStr())) { try { Date tsDate = sdf.parse(partialDateConv.getDateStr()); pStmt.setTimestamp(fldInx, new Timestamp(tsDate.getTime())); } catch (ParseException e) { e.printStackTrace(); pStmt.setObject(fldInx, null); } } else { pStmt.setObject(fldInx, null); } */ } else if (newFieldName.equals("DeterminedDatePrecision")) { if (partialDateConv.getDateStr() == null) { getPartialDate(rs.getObject(detDateInx), partialDateConv); } if (partialDateConv.getPartial() != null) { if (partialDateConv.getPartial().length() > 1) { pStmt.setInt(fldInx, 1); } else { pStmt.setInt(fldInx, Integer.parseInt(partialDateConv.getPartial())); } } else { pStmt.setInt(fldInx, 1); } } else if (newFieldName.equals("CreatedByAgentID")) // User/Security changes { Integer agentId = getCreatorAgentId(null); pStmt.setInt(fldInx, agentId); } else if (newFieldName.equals("ModifiedByAgentID")) // User/Security changes { Integer agentId = getModifiedByAgentId(lastEditedBy); pStmt.setInt(fldInx, agentId); } else if (newFieldName.equals("Qualifier") || newFieldName.equals("SubSpQualifier") || newFieldName.equals("VarQualifier") || newFieldName.equals("Addendum") || newFieldName.equals("AlternateName") || newFieldName.equals("NameUsage") || newFieldName.equals("GUID") || newFieldName.equals("PreferredTaxonID")) { pStmt.setObject(fldInx, null); } else if (newFieldName.equals("CollectionMemberID")) // User/Security changes { pStmt.setInt(fldInx, getCollectionMemberId()); } else { Integer index = null; String oldMappedColName = colNewToOldMap.get(newFieldName); if (oldMappedColName != null) { index = oldNameIndex.get(oldMappedColName); } else { index = oldNameIndex.get(newFieldName); oldMappedColName = newFieldName; } Object data; if (index == null) { String msg = "convertDeterminationRecords - Couldn't find new field name[" + newFieldName + "] in old field name in index Map"; log.warn(msg); // stmt.close(); // tblWriter.logError(msg); // throw new RuntimeException(msg); data = null; } else { data = rs.getObject(index); } if (data != null) { int idInx = newFieldName.lastIndexOf("ID"); if (idMapperMgr != null && idInx > -1) { Integer oldId = (Integer) data; IdMapperIFace idMapper; if (oldMappedColName.equals("BiologicalObjectID")) { data = colObjIdMapper.get(oldId); if (data == null) { data = colObjCatIdMapper.get(oldId); } } else { idMapper = idMapperMgr.get(tableName, oldMappedColName); if (idMapper != null) { data = idMapper.get(oldId); } else { String msg = "No Map for [" + tableName + "][" + oldMappedColName + "]"; log.error(msg); tblWriter.logError(msg); isError = true; break; } } if (data == null) { String msg = "The determination with recordID[" + rs.getInt(oldRecIDInx) + "] could not find a mapping for record ID[" + oldId + "] for Old Field[" + oldMappedColName + "]"; log.debug(msg); tblWriter.logError(msg); tblWriter.log(ConvertVerifier.dumpSQL(oldDBConn, "SELECT * FROM determination WHERE DeterminationId = " + rs.getInt(oldRecIDInx))); if (isValueRequired(tableName, newFieldName)) { msg = "For table[" + tableName + "] the field [" + newFieldName + "] is null and can't be. Old value[" + oldId + "]"; log.error(msg); tblWriter.logError(msg); } isError = true; break; } } } //fixTimestamps(newFieldName, newFieldMetaData.get(i).getType(), data, str); FieldMetaData fldMetaData = newFieldMetaData.get(i); if (fldMetaData == null) { String msg = "For table[" + tableName + "] the field [" + newFieldName + "] FieldMeataDate was null for index[" + i + "]"; log.error(msg); tblWriter.logError(msg); } else { //System.out.println(fldMetaData.getName()+" "+fldMetaData.getSqlType()+" "+fldMetaData.getType()); BasicSQLUtils.setData(pStmt, newFieldMetaData.get(i).getSqlType(), fldInx, data); } } } if (hasFrame) { if (count % 500 == 0) { setProcess(count); } } else { if (count % 2000 == 0) { log.info("Determination Records: " + count); } } if (!isError) { try { if (pStmt.executeUpdate() != 1) { log.error("Count: " + count); log.error("Error inserting record."); } } catch (SQLException e) { log.error("Count: " + count); e.printStackTrace(); log.error(e); rs.close(); stmt.close(); showError(e.toString()); throw new RuntimeException(e); } } count++; // if (count > 10) break; } while (rs.next()); pStmt.close(); if (hasFrame) { setProcess(count); } else { log.info("Processed Determination " + count + " records."); } rs.close(); stmt.close(); tblWriter.log(String.format("Determination Processing Time: %s", timeLogger.end())); tblWriter.close(); } catch (SQLException e) { e.printStackTrace(); log.error(e); throw new RuntimeException(e); } setIdentityInsertOFFCommandForSQLServer(newDBConn, "determination", BasicSQLUtils.myDestinationServerType); return true; }