Example usage for java.sql ResultSet TYPE_SCROLL_INSENSITIVE

Introduction

In this page you can find the example usage for java.sql ResultSet TYPE_SCROLL_INSENSITIVE.

Prototype

int TYPE_SCROLL_INSENSITIVE

To view the source code for java.sql ResultSet TYPE_SCROLL_INSENSITIVE.

Click Source Link

Document

The constant indicating the type for a ResultSet object that is scrollable but generally not sensitive to changes to the data that underlies the ResultSet.

Usage

From source file:edu.ku.brc.specify.dbsupport.SpecifySchemaUpdateService.java

/**
 * Fixes the Schema for Database Version 1.2
 * @param conn/*from   w w w .j a  v a 2 s . c o m*/
 * @throws Exception
 */
private boolean doFixesForDBSchemaVersions(final Connection conn, final String databaseName) throws Exception {
    /////////////////////////////
    // PaleoContext
    /////////////////////////////
    getTableNameAndTitleForFrame(PaleoContext.getClassTableId());
    Integer len = getFieldLength(conn, databaseName, "paleocontext", "Text1");
    alterFieldLength(conn, databaseName, "paleocontext", "Text1", 32, 64);
    alterFieldLength(conn, databaseName, "paleocontext", "Text2", 32, 64);

    len = getFieldLength(conn, databaseName, "paleocontext", "Remarks");
    if (len == null) {
        int count = BasicSQLUtils.getCountAsInt("SELECT COUNT(*) FROM paleocontext");
        int rv = update(conn, "ALTER TABLE paleocontext ADD Remarks VARCHAR(60)");
        if (rv != count) {
            errMsgList.add("Error updating PaleoContext.Remarks");
            return false;
        }
    }
    frame.incOverall();

    DBConnection dbc = DBConnection.getInstance();

    /////////////////////////////
    // FieldNotebookPage
    /////////////////////////////
    getTableNameAndTitleForFrame(FieldNotebookPage.getClassTableId());
    len = getFieldLength(conn, databaseName, "fieldnotebookpage", "PageNumber");
    if (len != null && len == 16) {
        alterFieldLength(conn, databaseName, "fieldnotebookpage", "PageNumber", 16, 32);
        update(conn, "ALTER TABLE fieldnotebookpage ALTER COLUMN ScanDate DROP DEFAULT");
    }
    frame.incOverall();

    /////////////////////////////
    // Project Table
    /////////////////////////////
    alterFieldLength(conn, databaseName, "project", "projectname", 50, 128);
    frame.incOverall();

    /////////////////////////////
    // AttachmentImageAttribute Table
    /////////////////////////////
    if (doesTableExist(databaseName, "attachmentimageattribute")) {
        alterFieldLength(conn, databaseName, "attachmentimageattribute", "CreativeCommons", 128, 500);
        frame.incOverall();
    }

    /////////////////////////////
    // LocalityDetail
    /////////////////////////////

    String tblName = getTableNameAndTitleForFrame(LocalityDetail.getClassTableId());

    boolean statusOK = true;
    String sql = String.format(
            "SELECT COUNT(*) FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE TABLE_SCHEMA = '%s' AND TABLE_NAME = 'localitydetail' AND COLUMN_NAME = 'UtmScale' AND DATA_TYPE = 'varchar'",
            dbc.getDatabaseName());
    int count = BasicSQLUtils.getCountAsInt(sql);
    if (count > 0) {
        Vector<Object[]> values = query("SELECT ld.LocalityDetailID, ld.UtmScale, l.LocalityName "
                + "FROM localitydetail ld INNER JOIN locality l ON ld.LocalityID = l.LocalityID WHERE ld.UtmScale IS NOT NULL");

        update(conn, "ALTER TABLE localitydetail DROP COLUMN UtmScale");
        addColumn(conn, databaseName, tblName, "UtmScale", "FLOAT", "UtmOrigLongitude");
        addColumn(conn, databaseName, tblName, "MgrsZone", "VARCHAR(4)", "UtmScale");

        HashMap<String, String> badLocalitiesHash = new HashMap<String, String>();

        try {
            PreparedStatement pStmt = conn
                    .prepareStatement("UPDATE localitydetail SET UtmScale=? WHERE LocalityDetailID=?");

            for (Object[] row : values) {
                Integer locDetailId = (Integer) row[0];
                String scale = (String) row[1];
                String locName = (String) row[2];

                scale = StringUtils.contains(scale, ',') ? StringUtils.replace(scale, ",", "") : scale;
                if (!StringUtils.isNumeric(scale)) {
                    badLocalitiesHash.put(locName, scale);
                    continue;
                }

                float scaleFloat = 0.0f;
                try {
                    scaleFloat = Float.parseFloat(scale);

                } catch (NumberFormatException ex) {
                    badLocalitiesHash.put(locName, scale);
                    continue;
                }

                pStmt.setFloat(1, scaleFloat);
                pStmt.setInt(2, locDetailId);
                pStmt.execute();
            }
            pStmt.close();

        } catch (SQLException ex) {
            statusOK = false;
        }

        if (badLocalitiesHash.size() > 0) {
            try {
                File file = new File(
                        UIRegistry.getUserHomeDir() + File.separator + "localitydetailerrors.html");
                TableWriter tblWriter = new TableWriter(file.getAbsolutePath(), "Locality Detail Errors");
                tblWriter.startTable();
                tblWriter.logHdr(new String[] { "Locality Name", "Scale" });

                for (String key : badLocalitiesHash.keySet()) {
                    tblWriter.log(key, badLocalitiesHash.get(key));
                }
                tblWriter.endTable();
                tblWriter.flush();
                tblWriter.close();

                UIRegistry.showLocalizedError("LOC_DETAIL_ERRORS", badLocalitiesHash.size(),
                        file.getAbsoluteFile());

                badLocalitiesHash.clear();

                if (file.exists()) {
                    try {
                        AttachmentUtils.openURI(file.toURI());

                    } catch (Exception ex) {
                        ex.printStackTrace();
                    }
                }
            } catch (IOException ex) {
                ex.printStackTrace();
            }
        }
    } else {
        addColumn(conn, databaseName, tblName, "UtmScale", "FLOAT", "UtmOrigLongitude");
    }
    frame.incOverall();

    //////////////////////////////////////////////
    // collectingeventattribute Schema 1.3
    //////////////////////////////////////////////
    DBMSUserMgr dbmsMgr = DBMSUserMgr.getInstance();
    if (dbmsMgr.connectToDBMS(itUserNamePassword.first, itUserNamePassword.second, dbc.getServerName())) {
        boolean status = true;

        Connection connection = dbmsMgr.getConnection();
        try {
            // Add New Fields to Determination
            tblName = getTableNameAndTitleForFrame(Determination.getClassTableId());
            addColumn(conn, databaseName, tblName, "VarQualifier",
                    "ALTER TABLE %s ADD COLUMN %s VARCHAR(16) AFTER Qualifier");
            addColumn(conn, databaseName, tblName, "SubSpQualifier",
                    "ALTER TABLE %s ADD COLUMN %s VARCHAR(16) AFTER VarQualifier");
            frame.incOverall();

            // CollectingEventAttributes
            sql = String.format(
                    "SELECT COUNT(*) FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE TABLE_SCHEMA = '%s' AND TABLE_NAME = 'collectingeventattribute' AND COLUMN_NAME = 'CollectionMemberID'",
                    dbc.getDatabaseName());
            count = BasicSQLUtils.getCountAsInt(sql);

            connection.setCatalog(dbc.getDatabaseName());

            //int numCEAttrs = BasicSQLUtils.getCountAsInt("SELECT COUNT(*) FROM collectingeventattribute");
            if (count > 0) {
                HashMap<Integer, Integer> collIdToDispIdHash = new HashMap<Integer, Integer>();
                sql = "SELECT UserGroupScopeId, DisciplineID FROM collection";
                for (Object[] cols : query(sql)) {
                    Integer colId = (Integer) cols[0];
                    Integer dspId = (Integer) cols[1];
                    collIdToDispIdHash.put(colId, dspId);
                }

                count = BasicSQLUtils.getCountAsInt("SELECT COUNT(*) FROM collectingeventattribute");

                IdMapperMgr.getInstance().setDBs(connection, connection);
                IdTableMapper mapper = new IdTableMapper("ceattrmapper", "id",
                        "SELECT CollectingEventAttributeID, CollectionMemberID FROM collectingeventattribute",
                        true, false);
                mapper.setFrame(frame);
                mapper.mapAllIdsNoIncrement(count > 0 ? count : null);

                Statement stmt = null;
                try {
                    getTableNameAndTitleForFrame(CollectingEventAttribute.getClassTableId());

                    stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                            ResultSet.CONCUR_READ_ONLY);
                    update(conn, "DROP INDEX COLEVATSColMemIDX on collectingeventattribute");
                    update(conn, "ALTER TABLE collectingeventattribute DROP COLUMN CollectionMemberID");
                    update(conn, "ALTER TABLE collectingeventattribute ADD COLUMN DisciplineID int(11)");
                    update(conn, "CREATE INDEX COLEVATSDispIDX ON collectingeventattribute(DisciplineID)");

                    double inc = count > 0 ? (100.0 / (double) count) : 0;
                    double cnt = 0;
                    int percent = 0;
                    frame.setProcess(0, 100);
                    frame.setProcessPercent(true);

                    PreparedStatement pStmt = conn.prepareStatement(
                            "UPDATE collectingeventattribute SET DisciplineID=? WHERE CollectingEventAttributeID=?");
                    ResultSet rs = stmt
                            .executeQuery("SELECT CollectingEventAttributeID FROM collectingeventattribute");
                    while (rs.next()) {
                        Integer ceAttrId = rs.getInt(1);
                        Integer oldColId = mapper.get(ceAttrId);
                        if (oldColId != null) {
                            Integer dispId = collIdToDispIdHash.get(oldColId);
                            if (dispId != null) {
                                pStmt.setInt(1, dispId);
                                pStmt.setInt(2, ceAttrId);
                                pStmt.execute();

                            } else {
                                log.debug("Error getting hashed DisciplineID from Old Collection ID[" + oldColId
                                        + "]  ceAttrId[" + ceAttrId + "]");
                            }
                        } else {
                            log.debug("Error getting mapped  Collection ID[" + oldColId + "]  ceAttrId["
                                    + ceAttrId + "]");
                        }

                        cnt += inc;
                        if (((int) cnt) > percent) {
                            percent = (int) cnt;
                            frame.setProcess(percent);
                        }
                    }
                    rs.close();
                    pStmt.close();

                    frame.setProcess(100);

                } catch (SQLException ex) {
                    ex.printStackTrace();

                } finally {
                    if (stmt != null)
                        stmt.close();
                }
                mapper.cleanup();
            }
            frame.incOverall();

            //-----------------------------
            // Collectors
            //-----------------------------
            sql = String.format(
                    "SELECT COUNT(*) FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE TABLE_SCHEMA = '%s' AND TABLE_NAME = 'collector' AND COLUMN_NAME = 'CollectionMemberID'",
                    dbc.getDatabaseName());
            count = BasicSQLUtils.getCountAsInt(sql);
            if (count > 0) {
                HashMap<Integer, Integer> collIdToDivIdHash = new HashMap<Integer, Integer>();
                sql = "SELECT c.UserGroupScopeId, d.DivisionID FROM collection c INNER JOIN discipline d ON c.DisciplineID = d.UserGroupScopeId";
                for (Object[] cols : query(sql)) {
                    Integer colId = (Integer) cols[0];
                    Integer divId = (Integer) cols[1];
                    collIdToDivIdHash.put(colId, divId);
                }

                count = BasicSQLUtils.getCountAsInt("SELECT COUNT(*) FROM collector");

                IdMapperMgr.getInstance().setDBs(connection, connection);
                IdTableMapper mapper = new IdTableMapper("collectormap", "id",
                        "SELECT CollectorID, CollectionMemberID FROM collector", true, false);
                mapper.setFrame(frame);
                mapper.mapAllIdsNoIncrement(count > 0 ? count : null);

                getTableNameAndTitleForFrame(Collector.getClassTableId());
                Statement stmt = null;
                try {
                    stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                            ResultSet.CONCUR_READ_ONLY);
                    update(conn, "DROP INDEX COLTRColMemIDX on collector");
                    update(conn, "ALTER TABLE collector DROP COLUMN CollectionMemberID");
                    update(conn, "ALTER TABLE collector ADD COLUMN DivisionID INT(11)");
                    update(conn, "CREATE INDEX COLTRDivIDX ON collector(DivisionID)");

                    double inc = count > 0 ? (100.0 / (double) count) : 0;
                    double cnt = 0;
                    int percent = 0;
                    frame.setProcess(0, 100);
                    frame.setProcessPercent(true);

                    PreparedStatement pStmt = conn
                            .prepareStatement("UPDATE collector SET DivisionID=? WHERE CollectorID=?");
                    ResultSet rs = stmt.executeQuery("SELECT CollectorID FROM collector");
                    while (rs.next()) {
                        Integer coltrId = rs.getInt(1);
                        Integer oldColId = mapper.get(coltrId);
                        if (oldColId != null) {
                            Integer divId = collIdToDivIdHash.get(oldColId);
                            if (divId != null) {
                                pStmt.setInt(1, divId);
                                pStmt.setInt(2, coltrId);
                                pStmt.execute();

                            } else {
                                log.debug("Error getting hashed DisciplineID from Old Collection ID[" + oldColId
                                        + "]");
                            }
                        } else {
                            log.debug("Error getting mapped Collector ID[" + oldColId + "]");
                        }

                        cnt += inc;
                        if (((int) cnt) > percent) {
                            percent = (int) cnt;
                            frame.setProcess(percent);
                        }
                    }
                    rs.close();
                    pStmt.close();

                    frame.setProcess(100);

                } catch (SQLException ex) {
                    ex.printStackTrace();

                } finally {
                    if (stmt != null)
                        stmt.close();
                }
                mapper.cleanup();

                frame.incOverall();
            }

        } catch (Exception ex) {
            ex.printStackTrace();

        } finally {
            frame.getProcessProgress().setIndeterminate(true);
            frame.setDesc("Loading updated schema...");

            if (!status) {
                //UIRegistry.showLocalizedError("SCHEMA_UPDATE_ERROR", errMsgStr);
                JTextArea ta = UIHelper.createTextArea();
                ta.setText(errMsgStr);
                CellConstraints cc = new CellConstraints();
                PanelBuilder pb = new PanelBuilder(new FormLayout("f:p:g", "f:p:g"));
                pb.add(new JScrollPane(ta, JScrollPane.VERTICAL_SCROLLBAR_AS_NEEDED,
                        JScrollPane.HORIZONTAL_SCROLLBAR_AS_NEEDED), cc.xy(1, 1));
                pb.setDefaultDialogBorder();

                CustomDialog dlg = new CustomDialog((Frame) UIRegistry.getTopWindow(),
                        getResourceString("SCHEMA_UPDATE_ERROR"), true, pb.getPanel());
                UIHelper.centerAndShow(dlg);
            }

            dbmsMgr.close();
        }
    }

    return statusOK;
}

---

From source file:edu.ku.brc.specify.conversion.GenericDBConversion.java

/**
 * Converts all the CollectionObject Physical records and CollectionObjectCatalog Records into
 * the new schema Preparation table.//  w  ww.  ja  va2 s  .c o m
 * @return true if no errors
 */
public boolean convertPreparationRecords(final Hashtable<Integer, Map<String, PrepType>> collToPrepTypeHash) {
    TableWriter tblWriter = convLogger.getWriter("convertPreparations.html", "Preparations");

    deleteAllRecordsFromTable(newDBConn, "preparation", BasicSQLUtils.myDestinationServerType);

    TimeLogger timeLogger = new TimeLogger();

    // BasicSQLUtils.setIdentityInsertONCommandForSQLServer(newDBConn, "preparation",
    // BasicSQLUtils.myDestinationServerType);
    try {
        Statement stmt = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);
        stmt.setFetchSize(Integer.MIN_VALUE);
        StringBuilder str = new StringBuilder();

        List<String> oldFieldNames = new ArrayList<String>();

        StringBuilder sql = new StringBuilder("SELECT ");
        List<String> names = getFieldNamesFromSchema(oldDBConn, "collectionobject");

        sql.append(buildSelectFieldList(names, "co"));
        sql.append(", ");
        oldFieldNames.addAll(names);

        names = getFieldNamesFromSchema(oldDBConn, "collectionobjectcatalog");
        sql.append(buildSelectFieldList(names, "cc"));
        oldFieldNames.addAll(names);

        String sqlPostfix = " FROM collectionobject co LEFT JOIN collectionobjectcatalog cc ON co.CollectionObjectID = cc.CollectionObjectCatalogID "
                + "WHERE NOT (co.DerivedFromID IS NULL) AND CatalogSeriesID IS NOT NULL ORDER BY co.CollectionObjectID";
        sql.append(sqlPostfix);

        log.info(sql);

        List<FieldMetaData> newFieldMetaData = getFieldMetaDataFromSchema(newDBConn, "preparation");

        log.info("Number of Fields in (New) Preparation " + newFieldMetaData.size());
        for (FieldMetaData field : newFieldMetaData) {
            log.info(field.getName());
        }

        String sqlStr = sql.toString();
        log.debug(sql);

        log.debug("------------------------ Old Names");
        Map<String, Integer> oldNameIndex = new Hashtable<String, Integer>();
        int inx = 1;
        for (String name : oldFieldNames) {
            oldNameIndex.put(name, inx++);
            log.debug("OldName: " + name + " " + (inx - 1));
        }
        log.debug("------------------------");

        Hashtable<String, String> newToOld = new Hashtable<String, String>();
        newToOld.put("PreparationID", "CollectionObjectID");
        newToOld.put("CollectionObjectID", "DerivedFromID");
        newToOld.put("StorageLocation", "Location");

        boolean doDebug = false;
        ResultSet rs = stmt.executeQuery(sqlStr);

        if (!rs.next()) {
            rs.close();
            stmt.close();
            setProcess(0, 0);
            return true;
        }

        Statement prepStmt = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);
        prepStmt.setFetchSize(Integer.MIN_VALUE);

        IdTableMapper prepIdMapper = idMapperMgr.addTableMapper("CollectionObject", "CollectionObjectID",
                doDeleteAllMappings);

        if (shouldCreateMapTables) {
            String sql2 = "SELECT c.CollectionObjectID FROM collectionobject c WHERE NOT (c.DerivedFromID IS NULL) ORDER BY c.CollectionObjectID";
            prepIdMapper.mapAllIds(sql2);

        } else {
            prepIdMapper = (IdTableMapper) idMapperMgr.get("preparation", "PreparationID");
        }

        String insertStmtStr = null;
        boolean shouldCheckPrepAttrs = BasicSQLUtils.getCountAsInt(
                "SELECT COUNT(*) FROM preparation WHERE PreparedByID IS NOT NULL OR PreparedDate IS NOT NULL") > 0;
        Statement prepTypeStmt = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);
        prepTypeStmt.setFetchSize(Integer.MIN_VALUE);

        PartialDateConv partialDateConv = new PartialDateConv();

        prepIdMapper.setShowLogErrors(false);

        int totalPrepCount = BasicSQLUtils.getCountAsInt(oldDBConn, "SELECT COUNT(*)" + sqlPostfix);
        setProcess(0, totalPrepCount);

        Statement updateStatement = newDBConn.createStatement();

        //int     prepDateInx     = oldNameIndex.get("CatalogedDate") + 1;
        int lastEditedByInx = oldNameIndex.get("LastEditedBy");
        Integer idIndex = oldNameIndex.get("CollectionObjectID");
        Integer catSeriesIdInx = oldNameIndex.get("CatalogSeriesID");
        int count = 0;
        do {
            partialDateConv.nullAll();

            Integer preparedById = null;
            if (shouldCheckPrepAttrs) {
                Integer recordId = rs.getInt(idIndex + 1);

                String subQueryStr = "select PreparedByID, PreparedDate from preparation where PreparationID = "
                        + recordId;
                ResultSet subQueryRS = prepTypeStmt.executeQuery(subQueryStr);

                if (subQueryRS.next()) {
                    preparedById = subQueryRS.getInt(1);
                    getPartialDate(rs.getObject(2), partialDateConv);
                } else {
                    partialDateConv.setDateStr("NULL");
                    partialDateConv.setPartial("NULL");
                }
                subQueryRS.close();
            }

            Integer catSeriesId = rs.getInt(catSeriesIdInx);
            //log.debug("catSeriesId "+catSeriesId+"  catSeriesIdInx "+catSeriesIdInx);
            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 Preps)");
            }

            if (collectionIdList.size() == 0) {
                UIRegistry.showError(
                        "There are NO Collections assigned to the same CatalogSeries and we can't handle that right now.");
                return false;
            }

            if (collectionIdList.size() > 1) {
                UIRegistry.showError(
                        "There are multiple Collections assigned to the same CatalogSeries and we can't handle that right now.");
                return false;
            }

            Integer colId = collectionIdList.get(0);
            Collection collection = collIdToCollObj.get(colId);
            if (collection == null) {
                Session localSession = HibernateUtil.getCurrentSession();
                List<Collection> colList = (List<Collection>) localSession
                        .createQuery("FROM Collection WHERE id = " + colId).list();
                if (colList == null || colList.size() == 0) {
                    UIRegistry.showError("The collection is null for Catalog Series ID: " + catSeriesId);
                    return false;
                }
                collection = colList.get(0);
                collIdToCollObj.put(colId, collection);
            }
            Map<String, PrepType> prepTypeMap = collToPrepTypeHash.get(collectionIdList.get(0));

            String lastEditedBy = rs.getString(lastEditedByInx);

            /*
             * int catNum = rs.getInt(oldNameIndex.get("CatalogNumber")+1); doDebug = catNum ==
             * 30972;
             * 
             * if (doDebug) { log.debug("CatalogNumber "+catNum);
             * log.debug("CollectionObjectID
             * "+rs.getInt(oldNameIndex.get("CollectionObjectID")+1));
             * log.debug("DerivedFromID
             * "+rs.getInt(oldNameIndex.get("DerivedFromID"))); }
             */

            str.setLength(0);

            if (insertStmtStr == null) {
                StringBuffer fieldList = new StringBuffer();
                fieldList.append("( ");
                for (int i = 0; i < newFieldMetaData.size(); i++) {
                    if ((i > 0) && (i < newFieldMetaData.size())) {
                        fieldList.append(", ");
                    }

                    String newFieldName = newFieldMetaData.get(i).getName();
                    fieldList.append(newFieldName + " ");
                }
                fieldList.append(")");
                insertStmtStr = "INSERT INTO preparation " + fieldList + " VALUES (";
            }
            str.append(insertStmtStr);

            Integer oldId = null;
            boolean isError = false;

            for (int i = 0; i < newFieldMetaData.size(); i++) {
                if (i > 0) {
                    str.append(", ");
                }

                String newFieldName = newFieldMetaData.get(i).getName();
                String mappedName = newToOld.get(newFieldName);
                //log.debug("["+newFieldName+"]["+mappedName+"]");

                if (mappedName != null) {
                    newFieldName = mappedName;
                } else {
                    mappedName = newFieldName;
                }

                if (i == 0) {
                    oldId = rs.getInt(1);
                    Integer newId = prepIdMapper.get(oldId);
                    if (newId == null) {
                        isError = true;
                        break;
                        //throw new RuntimeException("Preparations - Couldn't find new ID for old ID["+oldId+"]");
                    }
                    str.append(newId);

                } else if (newFieldName.equals("PreparedByID")) {
                    if (agentIdMapper != null) {
                        str.append(getStrValue(agentIdMapper.get(preparedById)));
                    } else {
                        str.append("NULL");
                        //log.error("No Map for PreparedByID[" + preparedById + "]");
                    }

                } else if (newFieldName.equals("PreparedDate")) {
                    str.append(partialDateConv.getDateStr());

                } else if (newFieldName.equals("PreparedDatePrecision")) {
                    str.append(partialDateConv.getPartial());

                } else if (newFieldName.equals("DerivedFromIDX")) {
                    // skip

                } else if (newFieldName.equals("PreparationAttributeID")) {
                    Integer id = rs.getInt(idIndex + 1);
                    Object data = prepIdMapper.get(id);
                    if (data == null) {
                        // throw new RuntimeException("Couldn't map ID for new
                        // PreparationAttributesID [CollectionObjectID]["+id+"]");
                        str.append("NULL");

                    } else {
                        ResultSet prepRS = prepStmt.executeQuery(
                                "select PreparationID from preparation where PreparationID = " + id);
                        if (prepRS.first()) {
                            str.append(getStrValue(data));
                        } else {
                            str.append("NULL");
                        }
                        prepRS.close();
                    }

                } else if (newFieldName.equals("CountAmt")) {
                    Integer value = rs.getInt("Count");
                    if (rs.wasNull()) {
                        value = null;
                    }
                    str.append(getStrValue(value));

                } else if (newFieldName.equalsIgnoreCase("SampleNumber")
                        || newFieldName.equalsIgnoreCase("Status") || newFieldName.equalsIgnoreCase("YesNo3")) {
                    str.append("NULL");

                } else if (newFieldName.equalsIgnoreCase("Version")) {
                    str.append("0");

                } else if (newFieldName.equalsIgnoreCase("CollectionMemberID")) {
                    str.append(getCollectionMemberId());

                } else if (newFieldName.equalsIgnoreCase("TimestampCreated")) {
                    Object value = rs.getString(oldNameIndex.get("TimestampCreated"));
                    if (value == null) {
                        value = new Timestamp(Calendar.getInstance().getTime().getTime());
                    }
                    str.append(getStrValue(value, newFieldMetaData.get(i).getType()));

                } else if (newFieldName.equalsIgnoreCase("TimestampModified")) {
                    Object value = rs.getString(oldNameIndex.get("TimestampModified"));
                    if (value == null) {
                        value = new Timestamp(Calendar.getInstance().getTime().getTime());
                    }
                    str.append(getStrValue(value, newFieldMetaData.get(i).getType()));

                } else if (newFieldName.equalsIgnoreCase("ModifiedByAgentID")) {
                    str.append(getModifiedByAgentId(lastEditedBy));

                } else if (newFieldName.equalsIgnoreCase("CreatedByAgentID")) {
                    str.append(getCreatorAgentId(null));

                } else if (newFieldName.equals("PrepTypeID")) {
                    String value = rs.getString(oldNameIndex.get("PreparationMethod"));
                    if (value == null || value.length() == 0) {
                        value = "n/a";
                    }

                    /*if (value.equalsIgnoreCase("Slide"))
                    {
                    PrepType prepType = prepTypeMap.get(value.toLowerCase());
                    if (prepType != null)
                    {
                        Integer prepTypeId = prepType.getPrepTypeId();
                        System.err.println(String.format("%s -> %d %s", value, prepTypeId, prepType.getName()));
                    }
                    }*/

                    PrepType prepType = prepTypeMap.get(value.toLowerCase());
                    if (prepType != null) {
                        Integer prepTypeId = prepType.getPrepTypeId();
                        if (prepTypeId != null) {
                            str.append(getStrValue(prepTypeId));

                        } else {
                            str.append("NULL");
                            String msg = "***************** Couldn't find PreparationMethod[" + value
                                    + "] in PrepTypeMap";
                            log.error(msg);
                            tblWriter.log(msg);

                        }
                    } else {
                        String msg = "Couldn't find PrepType[" + value + "] creating it.";
                        log.info(msg);
                        tblWriter.log(msg);

                        prepType = new PrepType();
                        prepType.initialize();
                        prepType.setName(value);
                        prepType.setCollection(collection);

                        prepTypeMap.put(value, prepType);

                        Session tmpSession = null;
                        try {
                            tmpSession = HibernateUtil.getCurrentSession();
                            Transaction trans = tmpSession.beginTransaction();
                            trans.begin();
                            tmpSession.save(prepType);
                            trans.commit();

                            str.append(getStrValue(prepType.getPrepTypeId()));

                        } catch (Exception ex) {
                            ex.printStackTrace();
                            throw new RuntimeException(ex);

                        }
                    }

                } else if (newFieldName.equals("StorageID") || newFieldName.equals("Storage")) {
                    str.append("NULL");

                } else {
                    Integer index = oldNameIndex.get(newFieldName);
                    Object data;
                    if (index == null) {
                        // convertPreparationRecords
                        String msg = "convertPreparationRecords - Couldn't find new field name[" + newFieldName
                                + "] in old field name in index Map";
                        log.warn(msg);
                        //stmt.close();
                        //throw new RuntimeException(msg);
                        data = null;
                    } else {
                        data = rs.getObject(index);
                    }
                    if (idMapperMgr != null && mappedName.endsWith("ID") && !mappedName.endsWith("GUID")) {
                        //log.debug(mappedName);

                        IdMapperIFace idMapper;
                        if (mappedName.equals("DerivedFromID")) {
                            idMapper = idMapperMgr.get("collectionobjectcatalog", "CollectionObjectCatalogID");

                        } else {
                            idMapper = idMapperMgr.get("collectionobject", mappedName);
                        }

                        if (idMapper != null) {
                            //Object prevObj = data;
                            data = idMapper.get((Integer) data);
                            if (data == null) {
                                String msg = "The mapped value came back null for old record Id [" + oldId
                                        + "] field [" + mappedName + "] => [" + data + "]";
                                log.error(msg);
                                tblWriter.logError(msg);
                                isError = true;
                                break;
                            }
                        } else {
                            String msg = "The could find mapper collectionobject_" + mappedName
                                    + " for old record Id [" + oldId + "] field=[" + data + "]";
                            log.error(msg);
                            tblWriter.logError(msg);
                            isError = true;
                            break;
                        }
                    }
                    str.append(getStrValue(data, newFieldMetaData.get(i).getType()));

                }
            }

            str.append(")");
            // log.info("\n"+str.toString());
            if (hasFrame) {
                if (count % 500 == 0) {
                    setProcess(count);
                    log.info("Preparation Records: " + count);
                }

            } else {
                if (count % 2000 == 0) {
                    log.info("Preparation Records: " + count);
                }
            }

            if (!isError) {
                try {

                    // updateStatement.executeUpdate("SET FOREIGN_KEY_CHECKS = 0");
                    if (BasicSQLUtils.myDestinationServerType != BasicSQLUtils.SERVERTYPE.MS_SQLServer) {
                        removeForeignKeyConstraints(newDBConn, "preparation",
                                BasicSQLUtils.myDestinationServerType);
                    }

                    if (doDebug) {
                        log.debug(str.toString());
                    }
                    //log.debug(str.toString());
                    updateStatement.executeUpdate(str.toString());

                } catch (Exception e) {
                    log.error("Error trying to execute: " + str.toString());
                    log.error("Count: " + count);
                    e.printStackTrace();
                    log.error(e);
                    throw new RuntimeException(e);
                }
            }

            count++;
            // if (count == 1) break;
        } while (rs.next());

        prepTypeStmt.close();
        prepStmt.close();
        updateStatement.clearBatch();
        updateStatement.close();
        updateStatement = null;

        if (hasFrame) {
            if (count % 2000 == 0) {
                final int cnt = count;
                SwingUtilities.invokeLater(new Runnable() {
                    @Override
                    public void run() {
                        setProcess(cnt);
                    }
                });
            }

        } else {
            if (count % 2000 == 0) {
                log.info("Processed CollectionObject " + count + " records.");
            }
        }

        String postSQL = " FROM collectionobject co "
                + "INNER JOIN collectionobjectcatalog cc ON co.CollectionObjectID = cc.CollectionObjectCatalogID "
                + "WHERE NOT (co.DerivedFromID IS NOT NULL) AND Location IS NOT NULL";

        int cntTotal = BasicSQLUtils.getCountAsInt(oldDBConn, "SELECT COUNT(*) " + postSQL);
        if (cntTotal > 0) {
            frame.setProcess(0, cntTotal);
            frame.setDesc("Moving Location data to Preparations...");

            IdMapperIFace idMapper = idMapperMgr.get("collectionobjectcatalog", "CollectionObjectCatalogID");

            PreparedStatement pStmt = newDBConn
                    .prepareStatement("UPDATE preparation SET StorageLocation=? WHERE CollectionObjectID=?");
            stmt = oldDBConn.createStatement();
            sqlStr = "SELECT CollectionObjectID, Location " + postSQL;
            log.debug(sqlStr);

            rs = stmt.executeQuery(sqlStr);
            int cnt = 0;
            while (rs.next()) {
                int id = rs.getInt(1);
                String locStr = rs.getString(2);

                Integer newId = idMapper.get(id);
                if (newId != null) {
                    pStmt.setString(1, locStr);
                    pStmt.setInt(2, newId);
                    pStmt.execute();
                }
                cnt++;
                if (cnt % 100 == 0) {
                    frame.setProcess(cnt);
                }
            }
            rs.close();
            stmt.close();
            pStmt.close();
            frame.setProcess(cntTotal);

        }

    } catch (SQLException e) {
        e.printStackTrace();
        log.error(e);
        throw new RuntimeException(e);
    }

    tblWriter.log(String.format("Preparations Processing Time: %s", timeLogger.end()));
    tblWriter.close();

    return true;
}

---

From source file:edu.ku.brc.specify.conversion.GenericDBConversion.java

/**
 * Converts all the Determinations.//from   w  w  w. ja  va 2  s  .com
 * @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;
}

---

From source file:edu.ku.brc.specify.conversion.GenericDBConversion.java

/**
 * Converts all the CollectionObject and CollectionObjectCatalog Records into the new schema
 * CollectionObject table. All "logical" records are moved to the CollectionObject table and all
 * "physical" records are moved to the Preparation table.
 * @return true if no errors//from  w w w .ja  va  2 s .  c o  m
 */
@SuppressWarnings("cast")
public boolean convertCollectionObjects(final boolean useNumericCatNumbers, final boolean usePrefix) {
    final String ZEROES = "000000000";

    UIFieldFormatterIFace formatter0 = UIFieldFormatterMgr.getInstance().getFormatter("CatalogNumber");
    log.debug(formatter0);

    UIFieldFormatterIFace formatter = UIFieldFormatterMgr.getInstance().getFormatter("CatalogNumberNumeric");
    log.debug(formatter);

    DisciplineType dt;
    Discipline discipline = (Discipline) AppContextMgr.getInstance().getClassObject(Discipline.class);
    if (discipline != null) {
        System.out.println("discipline.getType()[" + discipline.getType() + "]");
        dt = DisciplineType.getDiscipline(discipline.getType());
    } else {
        Vector<Object[]> list = query(newDBConn, "SELECT Type FROM discipline");
        String typeStr = (String) list.get(0)[0];
        System.out.println("typeStr[" + typeStr + "]");
        dt = DisciplineType.getDiscipline(typeStr);
    }

    Pair<Integer, Boolean> objTypePair = dispToObjTypeHash.get(dt.getDisciplineType());
    if (objTypePair == null) {
        System.out.println("objTypePair is null dt[" + dt.getName() + "][" + dt.getTitle() + "]");

        for (STD_DISCIPLINES key : dispToObjTypeHash.keySet()) {
            Pair<Integer, Boolean> p = dispToObjTypeHash.get(key);
            System.out.println("[" + key + "] [" + p.first + "][" + p.second + "]");
        }

    } else if (objTypePair.first == null) {
        System.out.println("objTypePair.first is null dt[" + dt + "]");

        for (STD_DISCIPLINES key : dispToObjTypeHash.keySet()) {
            Pair<Integer, Boolean> p = dispToObjTypeHash.get(key);
            System.out.println("[" + key + "] [" + p.first + "][" + p.second + "]");
        }

    }
    //int objTypeId  = objTypePair.first;
    //boolean isEmbedded = objTypePair.second;

    idMapperMgr.dumpKeys();
    IdHashMapper colObjTaxonMapper = (IdHashMapper) idMapperMgr.get("ColObjCatToTaxonType".toLowerCase());
    IdHashMapper colObjAttrMapper = (IdHashMapper) idMapperMgr
            .get("biologicalobjectattributes_BiologicalObjectAttributesID");
    IdHashMapper colObjMapper = (IdHashMapper) idMapperMgr
            .get("collectionobjectcatalog_CollectionObjectCatalogID");

    colObjTaxonMapper.setShowLogErrors(false); // NOTE: TURN THIS ON FOR DEBUGGING or running new Databases through it
    colObjAttrMapper.setShowLogErrors(false);

    //IdHashMapper stratMapper    = (IdHashMapper)idMapperMgr.get("stratigraphy_StratigraphyID");
    //IdHashMapper stratGTPMapper = (IdHashMapper)idMapperMgr.get("stratigraphy_GeologicTimePeriodID");

    String[] fieldsToSkip = { "ContainerID", "ContainerItemID", "AltCatalogNumber", "GUID", "ContainerOwnerID",
            "RepositoryAgreementID", "GroupPermittedToView", // this may change when converting Specify 5.x
            "CollectionObjectID", "VisibilitySetBy", "ContainerOwnerID", "InventoryDate", "ObjectCondition",
            "Notifications", "ProjectNumber", "Restrictions", "YesNo3", "YesNo4", "YesNo5", "YesNo6",
            "FieldNotebookPageID", "ColObjAttributesID", "DNASequenceID", "AppraisalID", "TotalValue",
            "Description", "SGRStatus", "OCR", "ReservedText", "Text3" };

    HashSet<String> fieldsToSkipHash = new HashSet<String>();
    for (String fName : fieldsToSkip) {
        fieldsToSkipHash.add(fName);
    }

    TableWriter tblWriter = convLogger.getWriter("convertCollectionObjects.html", "Collection Objects");

    String msg = "colObjTaxonMapper: " + colObjTaxonMapper.size();
    log.info(msg);
    tblWriter.log(msg);

    setIdentityInsertONCommandForSQLServer(newDBConn, "collectionobject",
            BasicSQLUtils.myDestinationServerType);

    deleteAllRecordsFromTable(newDBConn, "collectionobject", BasicSQLUtils.myDestinationServerType); // automatically closes the connection

    TreeSet<String> badSubNumberCatNumsSet = new TreeSet<String>();

    TimeLogger timeLogger = new TimeLogger();

    try {
        Statement stmt = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);
        stmt.setFetchSize(Integer.MIN_VALUE);
        StringBuilder str = new StringBuilder();

        List<String> oldFieldNames = new ArrayList<String>();

        StringBuilder sql = new StringBuilder("select ");
        List<String> names = getFieldNamesFromSchema(oldDBConn, "collectionobject");

        sql.append(buildSelectFieldList(names, "collectionobject"));
        sql.append(", ");
        oldFieldNames.addAll(names);

        names = getFieldNamesFromSchema(oldDBConn, "collectionobjectcatalog");
        sql.append(buildSelectFieldList(names, "collectionobjectcatalog"));
        oldFieldNames.addAll(names);

        String fromClause = " FROM collectionobject Inner Join collectionobjectcatalog ON "
                + "collectionobject.CollectionObjectID = collectionobjectcatalog.CollectionObjectCatalogID "
                + "WHERE (collectionobject.DerivedFromID IS NULL) AND collectionobjectcatalog.CollectionObjectCatalogID = ";
        sql.append(fromClause);

        log.info(sql);
        String sqlStr = sql.toString();

        List<FieldMetaData> newFieldMetaData = getFieldMetaDataFromSchema(newDBConn, "collectionobject");

        log.info("Number of Fields in New CollectionObject " + newFieldMetaData.size());

        Map<String, Integer> oldNameIndex = new Hashtable<String, Integer>();
        int inx = 1;
        log.info("---- Old Names ----");
        for (String name : oldFieldNames) {
            log.info("[" + name + "][" + inx + "]");
            oldNameIndex.put(name, inx++);
        }

        log.info("---- New Names ----");
        for (FieldMetaData fmd : newFieldMetaData) {
            log.info("[" + fmd.getName() + "]");
        }
        String tableName = "collectionobject";

        Statement newStmt = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);
        newStmt.setFetchSize(Integer.MIN_VALUE);
        ResultSet rsLooping = newStmt.executeQuery(
                "SELECT OldID, NewID FROM collectionobjectcatalog_CollectionObjectCatalogID ORDER BY OldID");

        if (hasFrame) {
            if (rsLooping.last()) {
                setProcess(0, rsLooping.getRow());
                rsLooping.first();

            } else {
                rsLooping.close();
                stmt.close();
                return true;
            }
        } else {
            if (!rsLooping.first()) {
                rsLooping.close();
                stmt.close();
                return true;
            }
        }

        int boaCnt = BasicSQLUtils.getCountAsInt(oldDBConn, "SELECT COUNT(*) FROM biologicalobjectattributes"); // ZZZ

        PartialDateConv partialDateConv = new PartialDateConv();

        Statement stmt2 = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);
        stmt2.setFetchSize(Integer.MIN_VALUE);

        int catNumInx = oldNameIndex.get("CatalogNumber");
        int catDateInx = oldNameIndex.get("CatalogedDate");
        int catSeriesIdInx = oldNameIndex.get("CatalogSeriesID");
        int lastEditedByInx = oldNameIndex.get("LastEditedBy");

        /*int     grpPrmtViewInx    = -1;
        Integer grpPrmtViewInxObj = oldNameIndex.get("GroupPermittedToView");
        if (grpPrmtViewInxObj != null)
        {
        grpPrmtViewInx = grpPrmtViewInxObj + 1;
        }*/

        Hashtable<Integer, CollectionInfo> oldCatSeriesIDToCollInfo = new Hashtable<Integer, CollectionInfo>();
        for (CollectionInfo ci : collectionInfoShortList) {
            if (ci.getCatSeriesId() != null) {
                oldCatSeriesIDToCollInfo.put(ci.getCatSeriesId(), ci);
            }
        }

        String insertStmtStr = null;

        /*String catIdTaxIdStrBase = "SELECT cc.CollectionObjectCatalogID, cc.CatalogSeriesID, ct.TaxonomyTypeID "
                                + "FROM collectionobjectcatalog AS cc "
                                + "Inner Join collectionobject AS co ON cc.CollectionObjectCatalogID = co.CollectionObjectID "
                                + "Inner Join collectiontaxonomytypes as ct ON co.CollectionObjectTypeID = ct.BiologicalObjectTypeID "
                                + "where cc.CollectionObjectCatalogID = ";*/

        int colObjAttrsNotMapped = 0;
        int count = 0;
        boolean skipRecord = false;
        do {
            String catSQL = sqlStr + rsLooping.getInt(1);
            //log.debug(catSQL);
            ResultSet rs = stmt.executeQuery(catSQL);
            if (!rs.next()) {
                log.error("Couldn't find CO with old  id[" + rsLooping.getInt(1) + "] " + catSQL);
                continue;
            }

            partialDateConv.nullAll();

            skipRecord = false;

            CollectionInfo collInfo = oldCatSeriesIDToCollInfo.get(rs.getInt(catSeriesIdInx));

            /*String catIdTaxIdStr = catIdTaxIdStrBase + rs.getInt(1);
            //log.info(catIdTaxIdStr);
                    
            ResultSet rs2 = stmt2.executeQuery(catIdTaxIdStr);
            if (!rs2.next())
            {
            log.info("QUERY failed to return results:\n"+catIdTaxIdStr+"\n");
            continue;
            }
            Integer catalogSeriesID = rs2.getInt(2);
            Integer taxonomyTypeID  = rs2.getInt(3);
            Integer newCatSeriesId  = collectionHash.get(catalogSeriesID + "_" + taxonomyTypeID);
            String  prefix          = prefixHash.get(catalogSeriesID + "_" + taxonomyTypeID);
            rs2.close();
                    
            if (newCatSeriesId == null)
            {
            msg = "Can't find " + catalogSeriesID + "_" + taxonomyTypeID;
            log.info(msg);
            tblWriter.logError(msg);
            continue;
            }*/

            /*if (false)
            {
            String stratGTPIdStr = "SELECT co.CollectionObjectID, ce.CollectingEventID, s.StratigraphyID, g.GeologicTimePeriodID FROM collectionobject co " +
                "LEFT JOIN collectingevent ce ON co.CollectingEventID = ce.CollectingEventID  " +
                "LEFT JOIN stratigraphy s ON ce.CollectingEventID = s.StratigraphyID  " +
                "LEFT JOIN geologictimeperiod g ON s.GeologicTimePeriodID = g.GeologicTimePeriodID  " +
                "WHERE co.CollectionObjectID  = " + rs.getInt(1);
            log.info(stratGTPIdStr);
            rs2 = stmt2.executeQuery(stratGTPIdStr);
                    
            Integer coId = null;
            Integer ceId = null;
            Integer stId = null;
            Integer gtpId = null;
            if (rs2.next())
            {
                coId = rs2.getInt(1);
                ceId = rs2.getInt(2);
                stId = rs2.getInt(3);
                gtpId = rs2.getInt(4);
            }
            rs2.close();
            }*/

            String catalogNumber = null;
            String colObjId = null;

            str.setLength(0);

            if (insertStmtStr == null) {
                StringBuffer fieldList = new StringBuffer();
                fieldList.append("( ");
                for (int i = 0; i < newFieldMetaData.size(); i++) {
                    if ((i > 0) && (i < newFieldMetaData.size())) {
                        fieldList.append(", ");
                    }
                    String newFieldName = newFieldMetaData.get(i).getName();
                    fieldList.append(newFieldName + " ");
                }
                fieldList.append(")");
                insertStmtStr = "INSERT INTO collectionobject " + fieldList + "  VALUES (";
            }
            str.append(insertStmtStr);

            for (int i = 0; i < newFieldMetaData.size(); i++) {
                if (i > 0) {
                    str.append(", ");
                }

                String newFieldName = newFieldMetaData.get(i).getName();

                if (i == 0) {
                    Integer oldColObjId = rs.getInt(1);
                    Integer newColObjId = colObjMapper.get(oldColObjId);

                    if (newColObjId == null) {
                        msg = "Couldn't find new ColObj Id for old [" + oldColObjId + "]";
                        tblWriter.logError(msg);
                        showError(msg);
                        throw new RuntimeException(msg);
                    }

                    colObjId = getStrValue(newColObjId);
                    if (contains(colObjId, '.')) {
                        String msgStr = String.format("CatalogNumber '%d' contains a decimal point.", colObjId);
                        log.debug(msgStr);
                        tblWriter.logError(msgStr);
                        skipRecord = true;
                        break;
                    }
                    str.append(colObjId);

                    if (useNumericCatNumbers) {
                        catalogNumber = rs.getString(catNumInx);

                        if (catalogNumber != null) {
                            int catNumInt = (int) Math.abs(rs.getDouble(catNumInx));
                            catalogNumber = Integer.toString(catNumInt);

                            if (catalogNumber.length() > 0 && catalogNumber.length() < ZEROES.length()) {
                                catalogNumber = "\"" + ZEROES.substring(catalogNumber.length()) + catalogNumber
                                        + "\"";

                            } else if (catalogNumber.length() > ZEROES.length()) {
                                showError(
                                        "Catalog Number[" + catalogNumber + "] is too long for formatter of 9");
                            }

                        } else {
                            String mssg = "Empty catalog number.";
                            log.debug(mssg);
                            //showError(msg);
                            tblWriter.logError(mssg);
                        }

                    } else {
                        String prefix = collInfo.getCatSeriesPrefix();

                        float catNum = rs.getFloat(catNumInx);
                        catalogNumber = "\"" + (usePrefix && isNotEmpty(prefix) ? (prefix + "-") : "")
                                + String.format("%9.0f", catNum).trim() + "\"";
                    }

                    int subNumber = rs.getInt(oldNameIndex.get("SubNumber"));
                    if (subNumber < 0 || rs.wasNull()) {
                        badSubNumberCatNumsSet.add(catalogNumber);

                        skipRecord = true;
                        //msg = "Collection Object is being skipped because SubNumber is less than zero CatalogNumber["+ catalogNumber + "]";
                        //log.error(msg);
                        //tblWriter.logError(msg);
                        //showError(msg);
                        break;
                    }

                } else if (fieldsToSkipHash.contains(newFieldName)) {
                    str.append("NULL");

                } else if (newFieldName.equals("CollectionID")) // User/Security changes
                {
                    str.append(collInfo.getCollectionId());

                } else if (newFieldName.equals("Version")) // User/Security changes
                {
                    str.append("0");

                } else if (newFieldName.equals("CreatedByAgentID")) // User/Security changes
                {
                    str.append(getCreatorAgentId(null));

                } else if (newFieldName.equals("ModifiedByAgentID")) // User/Security changes
                {
                    String lastEditedByStr = rs.getString(lastEditedByInx);
                    str.append(getModifiedByAgentId(lastEditedByStr));

                } else if (newFieldName.equals("CollectionMemberID")) // User/Security changes
                {
                    str.append(collInfo.getCollectionId());

                } else if (newFieldName.equals("PaleoContextID")) {
                    str.append("NULL");// newCatSeriesId);

                } else if (newFieldName.equals("CollectionObjectAttributeID")) // User/Security changes
                {
                    Object idObj = rs.getObject(1);
                    if (idObj != null) {
                        Integer coId = rs.getInt(1);
                        Integer newId = colObjAttrMapper.get(coId);
                        if (newId != null) {
                            str.append(getStrValue(newId));
                        } else {
                            if (boaCnt > 0)
                                colObjAttrsNotMapped++;
                            str.append("NULL");
                        }
                    } else {
                        str.append("NULL");
                    }

                } else if (newFieldName.equals("CatalogedDate")) {
                    if (partialDateConv.getDateStr() == null) {
                        getPartialDate(rs.getObject(catDateInx), partialDateConv);
                    }
                    str.append(partialDateConv.getDateStr());

                } else if (newFieldName.equals("CatalogedDatePrecision")) {
                    if (partialDateConv.getDateStr() == null) {
                        getPartialDate(rs.getObject(catDateInx), partialDateConv);
                    }
                    str.append(partialDateConv.getPartial());

                } else if (newFieldName.equals("CatalogedDateVerbatim")) {
                    if (partialDateConv.getDateStr() == null) {
                        getPartialDate(rs.getObject(catDateInx), partialDateConv);
                    }
                    str.append(partialDateConv.getVerbatim());

                } else if (newFieldName.equals("Availability")) {
                    str.append("NULL");

                } else if (newFieldName.equals("CatalogNumber")) {
                    str.append(catalogNumber);

                } else if (newFieldName.equals("Visibility")) // User/Security changes
                {
                    //str.append(grpPrmtViewInx > -1 ? rs.getObject(grpPrmtViewInx) : "NULL");
                    str.append("0");

                } else if (newFieldName.equals("VisibilitySetByID")) // User/Security changes
                {
                    str.append("NULL");

                } else if (newFieldName.equals("CountAmt")) {
                    Integer index = oldNameIndex.get("Count1");
                    if (index == null) {
                        index = oldNameIndex.get("Count");
                    }
                    Object countObj = rs.getObject(index);
                    if (countObj != null) {
                        str.append(getStrValue(countObj, newFieldMetaData.get(i).getType()));
                    } else {
                        str.append("NULL");
                    }

                } else {
                    Integer index = oldNameIndex.get(newFieldName);
                    Object data;
                    if (index == null) {
                        msg = "convertCollectionObjects - Couldn't find new field name[" + newFieldName
                                + "] in old field name in index Map";
                        log.warn(msg);
                        //                            tblWriter.logError(msg);
                        //                            showError(msg);
                        data = null;
                        // for (String key : oldNameIndex.keySet())
                        // {
                        // log.info("["+key+"]["+oldNameIndex.get(key)+"]");
                        // }
                        //stmt.close();
                        //throw new RuntimeException(msg);
                    } else {

                        data = rs.getObject(index);
                    }
                    if (data != null) {
                        int idInx = newFieldName.lastIndexOf("ID");
                        if (idMapperMgr != null && idInx > -1) {
                            IdMapperIFace idMapper = idMapperMgr.get(tableName, newFieldName);
                            if (idMapper != null) {
                                Integer origValue = rs.getInt(index);
                                data = idMapper.get(origValue);
                                if (data == null) {
                                    msg = "No value [" + origValue + "] in map  [" + tableName + "]["
                                            + newFieldName + "]";
                                    log.error(msg);
                                    tblWriter.logError(msg);
                                    //showError(msg);
                                }
                            } else {
                                msg = "No Map for [" + tableName + "][" + newFieldName + "]";
                                log.error(msg);
                                tblWriter.logError(msg);
                                //showError(msg);
                            }
                        }
                    }
                    str.append(getStrValue(data, newFieldMetaData.get(i).getType()));
                }
            }

            if (!skipRecord) {
                str.append(")");
                // log.info("\n"+str.toString());
                if (hasFrame) {
                    if (count % 500 == 0) {
                        setProcess(count);
                    }
                    if (count % 5000 == 0) {
                        log.info("CollectionObject Records: " + count);
                    }

                } else {
                    if (count % 2000 == 0) {
                        log.info("CollectionObject Records: " + count);
                    }
                }

                try {
                    Statement updateStatement = newDBConn.createStatement();
                    if (BasicSQLUtils.myDestinationServerType != BasicSQLUtils.SERVERTYPE.MS_SQLServer) {
                        removeForeignKeyConstraints(newDBConn, BasicSQLUtils.myDestinationServerType);
                    }
                    // updateStatement.executeUpdate("SET FOREIGN_KEY_CHECKS = 0");
                    //if (count < 50) System.err.println(str.toString());

                    updateStatement.executeUpdate(str.toString());
                    updateStatement.clearBatch();
                    updateStatement.close();
                    updateStatement = null;

                } catch (SQLException e) {
                    log.error("Count: " + count);
                    log.error("Key: [" + colObjId + "][" + catalogNumber + "]");
                    log.error("SQL: " + str.toString());
                    e.printStackTrace();
                    log.error(e);
                    showError(e.getMessage());
                    rs.close();
                    stmt.close();
                    throw new RuntimeException(e);
                }

                count++;
            } else {
                tblWriter.logError("Skipping - CatNo:" + catalogNumber);
            }
            // if (count > 10) break;

            rs.close();

        } while (rsLooping.next());

        /*if (boaCnt > 0)
        {
        msg = "CollectionObjectAttributes not mapped: " + colObjAttrsNotMapped + " out of "+boaCnt;
        log.info(msg);
        tblWriter.logError(msg);
        }*/

        stmt2.close();

        if (hasFrame) {
            setProcess(count);
        } else {
            log.info("Processed CollectionObject " + count + " records.");
        }

        tblWriter.log(String.format("Collection Objects Processing Time: %s", timeLogger.end()));

        tblWriter.log("Processed CollectionObject " + count + " records.");
        rsLooping.close();
        newStmt.close();
        stmt.close();

        tblWriter.append(
                "<br><br><b>Catalog Numbers rejected because the SubNumber was NULL or less than Zero</b><br>");
        tblWriter.startTable();
        tblWriter.logHdr("Catalog Number");
        for (String catNum : badSubNumberCatNumsSet) {
            tblWriter.log(catNum);
        }
        tblWriter.endTable();

    } catch (SQLException e) {
        setIdentityInsertOFFCommandForSQLServer(newDBConn, "collectionobject",
                BasicSQLUtils.myDestinationServerType);
        e.printStackTrace();
        log.error(e);
        tblWriter.logError(e.getMessage());
        showError(e.getMessage());
        throw new RuntimeException(e);

    } finally {
        tblWriter.close();
    }
    setIdentityInsertOFFCommandForSQLServer(newDBConn, "collectionobject",
            BasicSQLUtils.myDestinationServerType);

    return true;
}

---

From source file:edu.ku.brc.specify.conversion.GenericDBConversion.java

/**
 * @return/*from w  ww  . j ava2s .c  o  m*/
 */
public boolean convertLoanPreparations() {
    setIdentityInsertOFFCommandForSQLServer(newDBConn, "determination", BasicSQLUtils.myDestinationServerType);
    setIdentityInsertONCommandForSQLServer(newDBConn, "loanpreparation", BasicSQLUtils.myDestinationServerType);

    deleteAllRecordsFromTable(newDBConn, "loanpreparation", BasicSQLUtils.myDestinationServerType); // automatically closes the connection

    if (getNumRecords(oldDBConn, "loanphysicalobject") == 0) {
        setIdentityInsertOFFCommandForSQLServer(newDBConn, "loanpreparation",
                BasicSQLUtils.myDestinationServerType);
        return true;
    }

    Integer recCount = getCount(oldDBConn, "SELECT count(*) FROM loan WHERE Category = 0 ORDER BY LoanID");
    if (recCount == null || recCount == 0) {
        return true;
    }

    TableWriter tblWriter = convLogger.getWriter("convertLoanPreparations.html", "Loan Preparations");

    IdTableMapper loanPrepsMapper = (IdTableMapper) IdMapperMgr.getInstance().get("loanphysicalobject",
            "LoanPhysicalObjectID");
    if (loanPrepsMapper == null) {
        String msg = "LoanPrepsMapper not found. (This was a fatal error).";
        tblWriter.logError(msg);
        log.error(msg);
        return false;
    }

    TimeLogger timeLogger = new TimeLogger();

    try {
        Map<String, String> colNewToOldMap = createFieldNameMap(
                new String[] { "PreparationID", "PhysicalObjectID", });

        Statement stmt = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);
        stmt.setFetchSize(Integer.MIN_VALUE);
        StringBuilder str = new StringBuilder();

        List<String> oldFieldNames = new ArrayList<String>();

        StringBuilder sql = new StringBuilder("SELECT ");
        List<String> names = getFieldNamesFromSchema(oldDBConn, "loanphysicalobject");

        sql.append(buildSelectFieldList(names, "loanphysicalobject"));
        oldFieldNames.addAll(names);

        sql.append(
                " FROM loanphysicalobject INNER JOIN loan ON loanphysicalobject.LoanID = loan.LoanID WHERE loan.Category = 0");

        log.info(sql);

        List<FieldMetaData> newFieldMetaData = getFieldMetaDataFromSchema(newDBConn, "loanpreparation");

        log.info("Number of Fields in New loanpreparation " + 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 = "loanphysicalobject";

        int loanPhysIdIndex = oldNameIndex.get("LoanPhysicalObjectID");
        int quantityIndex = oldNameIndex.get("Quantity");
        int quantityRetIndex = oldNameIndex.get("QuantityReturned");
        int quantityResIndex = oldNameIndex.get("QuantityResolved");
        int lastEditedByInx = oldNameIndex.get("LastEditedBy");

        log.info(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;
            }
        }

        String insertStmtStr = null;

        int count = 0;
        do {
            boolean skipInsert = false;

            int quantity = getIntValue(rs, quantityIndex);
            int quantityResolved = getIntValue(rs, quantityResIndex);
            int quantityReturned = getIntValue(rs, quantityRetIndex);
            Boolean isResolved = quantityReturned == quantity || quantityResolved == quantity;
            String lastEditedBy = rs.getString(lastEditedByInx);

            str.setLength(0);

            if (insertStmtStr == null) {
                StringBuffer fieldList = new StringBuffer();
                fieldList.append("( ");
                for (int i = 0; i < newFieldMetaData.size(); i++) {
                    if ((i > 0) && (i < newFieldMetaData.size())) {
                        fieldList.append(", ");
                    }
                    String newFieldName = newFieldMetaData.get(i).getName();
                    fieldList.append(newFieldName + " ");
                }
                fieldList.append(")");
                insertStmtStr = "INSERT INTO loanpreparation " + fieldList + " VALUES (";
            }

            str.append(insertStmtStr);

            /*int    loanPhysId = rs.getInt(loanPhysIdIndex);
            String loanNumber = BasicSQLUtils.querySingleObj(oldDBConn, "SELECT LoanNumber FROM loan l INNER JOIN loanphysicalobject lp ON l.LoanID = lp.LoanID WHERE LoanPhysicalObjectID = "+loanPhysId);
            if (loanNumber != null && loanNumber.equals("25"))
            {
            debug = true;
            System.out.println("-------------------------------------\n"+loanPhysId);
            }*/

            for (int i = 0; i < newFieldMetaData.size(); i++) {
                if (i > 0)
                    str.append(", ");

                String newFieldName = newFieldMetaData.get(i).getName();

                if (i == 0) {
                    Integer oldId = rs.getInt(loanPhysIdIndex);
                    Integer newID = loanPrepsMapper.get(oldId);
                    if (newID != null) {
                        str.append(getStrValue(newID));
                    } else {
                        String msg = String.format(
                                "loanPhysIdIndex: %d; Old Id: %d could be mapped to a new ID. (This was a fatal error).",
                                loanPhysIdIndex, oldId);
                        tblWriter.logError(msg);
                        log.error(msg);
                        return false;
                    }

                } else if (newFieldName.equals("ReceivedComments")) {
                    str.append("NULL");

                } else if (newFieldName.equals("IsResolved")) {
                    str.append(getStrValue(isResolved));

                } else if (newFieldName.equalsIgnoreCase("Version")) {
                    str.append("0");

                } else if (newFieldName.equalsIgnoreCase("DisciplineID")) {
                    str.append(getDisciplineId());

                } else if (newFieldName.equalsIgnoreCase("ModifiedByAgentID")) {
                    str.append(getModifiedByAgentId(lastEditedBy));

                } else if (newFieldName.equalsIgnoreCase("CreatedByAgentID")) {
                    str.append(getCreatorAgentId(null));

                } 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 = "convertLoanPreparations - Couldn't find new field name[" + newFieldName
                                + "] in old field name in index Map";
                        log.warn(msg);
                        //                            stmt.close();
                        //                            tblWriter.logError(msg);
                        //                            showError(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) {
                            IdMapperIFace idMapper = idMapperMgr.get(tableName, oldMappedColName);
                            if (idMapper != null) {
                                Integer oldId = rs.getInt(index);
                                data = idMapper.get(oldId);
                                if (data == null) {
                                    String msg = "No Map ID for [" + tableName + "][" + oldMappedColName
                                            + "] for ID[" + oldId + "]";
                                    log.error(msg);
                                    tblWriter.logError(msg);
                                    skipInsert = true;
                                }
                            } else {
                                String msg = "No Map for [" + tableName + "][" + oldMappedColName + "]";
                                log.error(msg);
                                tblWriter.logError(msg);
                                skipInsert = true;
                            }
                        }
                    }
                    str.append(getStrValue(data, newFieldMetaData.get(i).getType()));
                }
            }
            str.append(")");

            if (hasFrame) {
                if (count % 500 == 0) {
                    setProcess(count);
                }

            } else {
                if (count % 2000 == 0) {
                    log.info("LoanPreparation Records: " + count);
                }
            }

            try {
                if (!skipInsert) {
                    Statement updateStatement = newDBConn.createStatement();
                    if (BasicSQLUtils.myDestinationServerType != BasicSQLUtils.SERVERTYPE.MS_SQLServer) {
                        removeForeignKeyConstraints(newDBConn, BasicSQLUtils.myDestinationServerType);
                    }
                    // log.debug("executring: " + str.toString());
                    // updateStatement.executeUpdate("SET FOREIGN_KEY_CHECKS = 0");
                    updateStatement.executeUpdate(str.toString());
                    updateStatement.clearBatch();
                    updateStatement.close();
                    updateStatement = null;
                }

            } catch (SQLException e) {
                log.error("Count: " + count);
                e.printStackTrace();
                log.error(e);
                rs.close();
                stmt.close();
                throw new RuntimeException(e);
            }

            count++;
            // if (count > 10) break;
        } while (rs.next());

        if (hasFrame) {
            setProcess(count);
            log.info("Processed LoanPreparation " + count + " records.");
        } else {
            log.info("Processed LoanPreparation " + count + " records.");
        }
        rs.close();

        stmt.close();

    } catch (SQLException e) {
        e.printStackTrace();
        log.error(e);
        setIdentityInsertOFFCommandForSQLServer(newDBConn, "LoanPreparation",
                BasicSQLUtils.myDestinationServerType);
        throw new RuntimeException(e);
    }
    log.info("Done processing LoanPhysicalObject");
    setIdentityInsertOFFCommandForSQLServer(newDBConn, "LoanPreparation",
            BasicSQLUtils.myDestinationServerType);

    //tblWriter.log(String.format("Loan Preps Processing Time: %s", timeLogger.end()));
    tblWriter.close();

    return true;

}

---

From source file:edu.ku.brc.specify.conversion.GenericDBConversion.java

/**
 * Converts all the LoanPhysicalObjects.
 * @return true if no errors/*from  w  ww .  ja va 2 s  .co  m*/
 */
public boolean convertGiftPreparations() {
    setIdentityInsertOFFCommandForSQLServer(newDBConn, "determination", BasicSQLUtils.myDestinationServerType);
    setIdentityInsertONCommandForSQLServer(newDBConn, "giftpreparation", BasicSQLUtils.myDestinationServerType);

    deleteAllRecordsFromTable(newDBConn, "giftpreparation", BasicSQLUtils.myDestinationServerType); // automatically closes the connection

    if (getNumRecords(oldDBConn, "loanphysicalobject") == 0) {
        setIdentityInsertOFFCommandForSQLServer(newDBConn, "giftpreparation",
                BasicSQLUtils.myDestinationServerType);
        return true;
    }

    Integer recCount = getCount(oldDBConn, "SELECT count(*) FROM loan WHERE Category = 1 ORDER BY LoanID");
    if (recCount == null || recCount == 0) {
        return true;
    }

    // This mapping is used by Gifts
    IdMapperIFace giftsIdMapper = IdMapperMgr.getInstance().get("gift", "GiftID");
    //if (shouldCreateMapTables)
    //{
    //   giftsIdMapper.mapAllIdsWithSQL();
    //}

    // This mapping is used by Gifts Preps

    IdMapperIFace giftPrepsIdMapper = IdMapperMgr.getInstance().get("giftphysicalobject", "id");

    TableWriter tblWriter = convLogger.getWriter("convertGiftPreparations.html", "Gift Preparations");
    TimeLogger timeLogger = new TimeLogger();

    try {
        Map<String, String> colNewToOldMap = createFieldNameMap(
                new String[] { "PreparationID", "PhysicalObjectID" });

        Statement stmt = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);
        stmt.setFetchSize(Integer.MIN_VALUE);
        StringBuilder str = new StringBuilder();

        List<String> oldFieldNames = new ArrayList<String>();

        StringBuilder sql = new StringBuilder("SELECT ");
        List<String> names = getFieldNamesFromSchema(oldDBConn, "loanphysicalobject");

        sql.append(buildSelectFieldList(names, "loanphysicalobject"));
        oldFieldNames.addAll(names);

        sql.append(
                " FROM loanphysicalobject INNER JOIN loan ON loanphysicalobject.LoanID = loan.LoanID WHERE loan.Category = 1 ORDER BY loanphysicalobject.LoanPhysicalObjectID");

        log.info(sql);

        List<FieldMetaData> newFieldMetaData = getFieldMetaDataFromSchema(newDBConn, "giftpreparation");

        log.info("Number of Fields in New giftpreparation " + newFieldMetaData.size());
        String sqlStr = sql.toString();

        colNewToOldMap.put("GiftID", "LoanID");

        Map<String, Integer> oldNameIndex = new Hashtable<String, Integer>();
        int inx = 1;
        for (String name : oldFieldNames) {
            oldNameIndex.put(name, inx++);
        }

        String tableName = "loanphysicalobject";

        //int quantityIndex   = oldNameIndex.get("Quantity");
        int lastEditedByInx = oldNameIndex.get("LastEditedBy");
        int loanPhysIdIndex = oldNameIndex.get("LoanPhysicalObjectID");

        log.info(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;
            }
        }

        String insertStmtStr = null;

        int count = 0;
        do {
            //int quantity         = getIntValue(rs, quantityIndex);
            String lastEditedBy = rs.getString(lastEditedByInx);

            str.setLength(0);

            if (insertStmtStr == null) {
                StringBuffer fieldList = new StringBuffer();
                fieldList.append("( ");
                for (int i = 0; i < newFieldMetaData.size(); i++) {
                    if ((i > 0) && (i < newFieldMetaData.size())) {
                        fieldList.append(", ");
                    }
                    String newFieldName = newFieldMetaData.get(i).getName();
                    fieldList.append(newFieldName + " ");
                }
                fieldList.append(")");
                insertStmtStr = "INSERT INTO giftpreparation " + fieldList + " VALUES (";
            }
            str.append(insertStmtStr);

            for (int i = 0; i < newFieldMetaData.size(); i++) {
                if (i > 0)
                    str.append(", ");

                String newFieldName = newFieldMetaData.get(i).getName();

                if (i == 0) {
                    Integer oldId = rs.getInt(loanPhysIdIndex);
                    Integer newID = giftPrepsIdMapper.get(oldId);
                    if (newID != null) {
                        str.append(getStrValue(newID));
                    } else {
                        String msg = String.format(
                                "loanPhysIdIndex: %d; Old Id: %d could be mapped to a new ID. (This was a fatal error).",
                                loanPhysIdIndex, oldId);
                        tblWriter.logError(msg);
                        log.error(msg);
                        return false;
                    }

                } else if (newFieldName.equals("ReceivedComments")) {
                    str.append("NULL");

                } else if (newFieldName.equalsIgnoreCase("Version")) {
                    str.append("0");

                } else if (newFieldName.equalsIgnoreCase("DisciplineID")) {
                    str.append(getDisciplineId());

                } else if (newFieldName.equalsIgnoreCase("ModifiedByAgentID")) {
                    str.append(getModifiedByAgentId(lastEditedBy));

                } else if (newFieldName.equalsIgnoreCase("CreatedByAgentID")) {
                    str.append(getCreatorAgentId(null));

                } 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 = "convertGiftPreparations - Couldn't find new field name[" + newFieldName
                                + "] in old field name in index Map";
                        log.warn(msg);
                        //                            stmt.close();
                        //                            tblWriter.logError(msg);
                        //                            showError(msg);
                        //                            throw new RuntimeException(msg);
                        data = null;
                    } else {
                        data = rs.getObject(index);
                    }
                    if (data != null) {
                        if (newFieldName.equalsIgnoreCase("GiftID")) {
                            data = giftsIdMapper.get((Integer) data);

                        } else {
                            int idInx = newFieldName.lastIndexOf("ID");
                            if (idMapperMgr != null && idInx > -1) {
                                IdMapperIFace idMapper = idMapperMgr.get(tableName, oldMappedColName);
                                if (idMapper != null) {
                                    Integer oldId = rs.getInt(index);
                                    data = idMapper.get(oldId);
                                    if (data == null) {
                                        String msg = "No Map ID for [" + tableName + "][" + oldMappedColName
                                                + "] for ID[" + oldId + "]";
                                        log.error(msg);
                                        tblWriter.logError(msg);
                                    }
                                } else {
                                    String msg = "No Map for [" + tableName + "][" + oldMappedColName + "]";
                                    log.error(msg);
                                    tblWriter.logError(msg);
                                }
                            }
                        }
                    }
                    str.append(getStrValue(data, newFieldMetaData.get(i).getType()));
                }
            }
            str.append(")");

            if (hasFrame) {
                if (count % 500 == 0) {
                    setProcess(count);
                }

            } else {
                if (count % 2000 == 0) {
                    log.info("LoanPreparation Records: " + count);
                }
            }

            try {
                Statement updateStatement = newDBConn.createStatement();
                if (BasicSQLUtils.myDestinationServerType != BasicSQLUtils.SERVERTYPE.MS_SQLServer) {
                    removeForeignKeyConstraints(newDBConn, BasicSQLUtils.myDestinationServerType);
                }
                // log.debug("executring: " + str.toString());
                // updateStatement.executeUpdate("SET FOREIGN_KEY_CHECKS = 0");
                updateStatement.executeUpdate(str.toString());
                updateStatement.clearBatch();
                updateStatement.close();
                updateStatement = null;

            } catch (SQLException e) {
                log.error("Count: " + count);
                e.printStackTrace();
                log.error(e);
                rs.close();
                stmt.close();
                throw new RuntimeException(e);
            }

            count++;
            // if (count > 10) break;
        } while (rs.next());

        if (hasFrame) {
            setProcess(count);
            log.info("Processed LoanPreparation " + count + " records.");
        } else {
            log.info("Processed LoanPreparation " + count + " records.");
        }
        rs.close();
        stmt.close();

        tblWriter.log(String.format("Determinations Processing Time: %s", timeLogger.end()));

    } catch (SQLException e) {
        e.printStackTrace();
        log.error(e);
        setIdentityInsertOFFCommandForSQLServer(newDBConn, "LoanPreparation",
                BasicSQLUtils.myDestinationServerType);
        throw new RuntimeException(e);
    }
    log.info("Done processing LoanPhysicalObject");
    setIdentityInsertOFFCommandForSQLServer(newDBConn, "LoanPreparation",
            BasicSQLUtils.myDestinationServerType);

    tblWriter.close();
    return true;

}

---

From source file:edu.ku.brc.specify.conversion.GenericDBConversion.java

/**
 * @param tableName//  w ww . j av a 2  s . com
 */
protected void convertLocalityExtraInfo(final String tableName, final boolean isGeoCoordDetail) {
    removeForeignKeyConstraints(newDBConn, BasicSQLUtils.myDestinationServerType);

    String capName = capitalize(tableName);
    TableWriter tblWriter = convLogger.getWriter(capName + ".html", capName);
    setTblWriter(tblWriter);
    IdHashMapper.setTblWriter(tblWriter);

    setDesc("Converting " + capName);

    List<String> localityDetailNamesTmp = getFieldNamesFromSchema(newDBConn, tableName);

    List<String> localityDetailNames = new ArrayList<String>();
    Hashtable<String, Boolean> nameHash = new Hashtable<String, Boolean>();

    for (String fieldName : localityDetailNamesTmp) {
        localityDetailNames.add(fieldName);
        nameHash.put(fieldName, true);
        System.out.println("[" + fieldName + "]");
    }

    String fieldList = buildSelectFieldList(localityDetailNames, null);
    log.info(fieldList);

    IdMapperIFace locIdMapper = idMapperMgr.get("locality", "LocalityID");
    IdMapperIFace agtIdMapper = idMapperMgr.get("agent", "AgentID");

    Statement updateStatement = null;
    try {
        updateStatement = newDBConn.createStatement();

        Hashtable<String, Boolean> usedFieldHash = new Hashtable<String, Boolean>();

        Statement stmt = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);
        stmt.setFetchSize(Integer.MIN_VALUE);
        Integer countRows = getCount(
                "select count(LocalityID) from locality,geography where locality.GeographyID = geography.GeographyID");
        if (countRows != null) {
            frame.setProcess(0, countRows);
        }

        ResultSet rs = stmt.executeQuery(
                "select locality.*,geography.* from locality LEFT JOIN geography on locality.GeographyID = geography.GeographyID ");

        StringBuilder colSQL = new StringBuilder();
        StringBuilder valuesSQL = new StringBuilder();

        int rows = 0;
        while (rs.next()) {
            usedFieldHash.clear();
            valuesSQL.setLength(0);

            boolean hasData = false;
            ResultSetMetaData metaData = rs.getMetaData();
            int cols = metaData.getColumnCount();
            for (int i = 1; i <= cols; i++) {
                String colName = metaData.getColumnName(i); // Old Column Name

                if (colName.equals("GeoRefDetBy")) {
                    colName = "AgentID";
                }

                if ((nameHash.get(colName) == null || usedFieldHash.get(colName) != null)
                        && !colName.startsWith("Range")) {
                    if (rows == 0) {
                        log.debug("Skipping[" + colName + "]");
                    }
                    continue;
                }

                usedFieldHash.put(colName, true);

                if (rows == 0) {
                    System.err.println("[" + colName + "]");

                    if (colName.equals("Range")) {
                        if (!isGeoCoordDetail) {
                            if (colSQL.length() > 0)
                                colSQL.append(",");
                            colSQL.append("RangeDesc");
                        }

                    } else if (isGeoCoordDetail) {
                        if (!colName.equals("RangeDirection")) {
                            if (colSQL.length() > 0)
                                colSQL.append(",");
                            colSQL.append(colName);
                        }

                    } else {
                        if (colSQL.length() > 0)
                            colSQL.append(",");
                        colSQL.append(colName);
                    }
                }

                String value;
                if (colName.equals("LocalityID")) {
                    Integer oldId = rs.getInt(i);
                    Integer newId = locIdMapper.get(oldId);
                    if (newId != null) {
                        value = Integer.toString(newId);
                    } else {
                        String msg = "Couldn't map LocalityId oldId[" + rs.getInt(i) + "]";
                        log.error(msg);
                        tblWriter.logError(msg);
                        value = "NULL";
                    }

                } else if (isGeoCoordDetail && colName.equals("GeoRefDetDate")) {
                    Integer dateInt = rs.getInt(i);
                    value = getStrValue(dateInt, "date");

                } else if (colName.startsWith("YesNo")) {
                    Integer bool = rs.getInt(i);
                    if (bool == null) {
                        value = "NULL";

                    } else if (bool == 0) {
                        value = "0";
                    } else {
                        value = "1";
                    }
                } else if (isGeoCoordDetail && colName.equals("AgentID")) {
                    Integer agentID = (Integer) rs.getObject(i);
                    if (agentID != null) {
                        Integer newID = agtIdMapper.get(agentID);
                        if (newID != null) {
                            value = newID.toString();
                        } else {
                            String msg = "Couldn't map GeoRefDetBY (Agent) oldId[" + agentID + "]";
                            log.error(msg);
                            tblWriter.logError(msg);
                            value = "NULL";
                        }
                    } else {
                        value = "NULL";
                    }

                } else if (colName.equals("Range") || colName.equals("RangeDirection")) {
                    if (!isGeoCoordDetail) {
                        String range = rs.getString(i);
                        range = escapeStringLiterals(range);
                        if (range != null) {
                            hasData = true;
                            value = "'" + range + "'";
                        } else {
                            value = "NULL";
                        }
                    } else {
                        value = null;
                    }
                } else {
                    Object obj = rs.getObject(i);
                    if (obj != null && !colName.equals("TimestampCreated")
                            && !colName.equals("TimestampModified")) {
                        hasData = true;
                    }
                    /*if (obj instanceof String)
                    {
                    String str = (String)obj;
                    int inx = str.indexOf('\'');
                    if (inx > -1)
                    {
                        obj = escapeStringLiterals(str);
                    }
                    }*/
                    value = getStrValue(obj);
                }
                // log.debug(colName+" ["+value+"]");

                if (value != null) {
                    if (valuesSQL.length() > 0) {
                        valuesSQL.append(",");
                    }
                    valuesSQL.append(value);
                }
            }

            if (hasData) {
                String insertSQL = "INSERT INTO " + tableName + " (" + colSQL.toString()
                        + ", Version, CreatedByAgentID, ModifiedByAgentID) " + " VALUES(" + valuesSQL.toString()
                        + ", 0, " + getCreatorAgentId(null) + "," + getModifiedByAgentId(null) + ")";

                /*if (true)
                {
                log.info(insertSQL);
                }*/
                try {
                    updateStatement.executeUpdate(insertSQL);
                    updateStatement.clearBatch();

                } catch (Exception ex) {
                    System.out.println("isGeoCoordDetail: " + isGeoCoordDetail);
                    System.out.println(insertSQL);
                    ex.printStackTrace();
                }
            }
            rows++;
            if (rows % 500 == 0) {
                frame.setProcess(rows);
            }
        }

        rs.close();
        stmt.close();

    } catch (Exception ex) {
        ex.printStackTrace();

    } finally {
        try {
            updateStatement.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

}

---

From source file:edu.ku.brc.specify.conversion.GenericDBConversion.java

/**
 * /*from w ww  . j  a  va 2  s  .  c om*/
 */
public void convertLocality() {
    int errorsToShow = BasicSQLUtils.SHOW_ALL;
    log.debug("Preparing to convert localities");
    // Ignore these field names from new table schema when mapping IDs

    setIdentityInsertOFFCommandForSQLServer(newDBConn, "collectionobject",
            BasicSQLUtils.myDestinationServerType);
    setIdentityInsertONCommandForSQLServer(newDBConn, "locality", BasicSQLUtils.myDestinationServerType);
    deleteAllRecordsFromTable("locality", BasicSQLUtils.myDestinationServerType);

    Hashtable<String, String> newToOldColMap = new Hashtable<String, String>();
    newToOldColMap.put("Visibility", "GroupPermittedToView");

    String[] fieldsToIgnore = new String[] { "GML", "NamedPlaceExtent", "GeoRefAccuracyUnits", "GeoRefDetRef",
            "GeoRefDetDate", "GeoRefDetBy", "NoGeoRefBecause", "GeoRefRemarks", "GeoRefVerificationStatus",
            "NationalParkName", "VisibilitySetBy", "GeoRefDetByID", "Drainage", // TODO make sure this is right, meg added due to conversion non-mapping errors????
            "Island", // TODO make sure this is right, meg added due to conversion non-mapping errors????
            "IslandGroup", // TODO make sure this is right, meg added due to conversion non-mapping errors????
            "WaterBody", // TODO make sure this is right, meg added due to conversion non-mapping errors????
            "Version", "CreatedByAgentID", "CollectionMemberID", "ShortName", "DisciplineID", "GUID", "GML",
            "SrcLatLongUnit", "Visibility", "VisibilitySetByID",
            // Special String
            "LocalityName", "NamedPlace", "RelationToNamedPlace", "SGRStatus", "PaleoContextID", "Text3",
            "Text4", "Text5", "VerbatimLatitude", "VerbatimLongitude" };

    setFieldsToIgnoreWhenMappingNames(fieldsToIgnore);

    errorsToShow &= ~BasicSQLUtils.SHOW_NULL_FK; // Turn off this error for LocalityID
    errorsToShow &= ~BasicSQLUtils.SHOW_NAME_MAPPING_ERROR; //Assuming new sp6 fields do not have sp5 equivalent
    setShowErrors(errorsToShow);

    TableWriter tblWriter = convLogger.getWriter("Locality.html", "Localities");
    setTblWriter(tblWriter);
    IdHashMapper.setTblWriter(tblWriter);

    Map<String, String> mappedFields = createFieldNameMap(
            new String[] { "ModifiedByAgentID", "LastEditedBy", });

    String sql = "SELECT locality.*,g.* FROM locality LEFT JOIN geography g on locality.GeographyID = g.GeographyID WHERE locality.GeographyID IS NOT NULL";

    if (copyTable(oldDBConn, newDBConn, sql, "locality", "locality", mappedFields, null,
            BasicSQLUtils.mySourceServerType, BasicSQLUtils.myDestinationServerType)) {
        log.info("Locality/Geography copied ok.");
    } else {
        log.error("Copying locality/geography (fields) to new Locality");
    }

    setFieldsToIgnoreWhenMappingNames(fieldsToIgnore);

    sql = "SELECT * FROM locality WHERE locality.GeographyID IS NULL";

    if (copyTable(oldDBConn, newDBConn, sql, "locality", "locality", mappedFields, null,
            BasicSQLUtils.mySourceServerType, BasicSQLUtils.myDestinationServerType)) {
        log.info("Locality/Geography copied ok.");
    } else {
        log.error("Copying locality/geography (fields) to new Locality");
    }

    frame.setProcess(0, BasicSQLUtils.getCountAsInt("SELECT COUNT(*) FROM locality"));

    PreparedStatement pStmt = null;
    Statement stmt = null;
    sql = "SELECT LocalityID, LocalityName, NamedPlace, RelationToNamedPlace, Remarks FROM locality ORDER BY LocalityID";
    try {
        IdMapperIFace locMapper = idMapperMgr.get("locality_LocalityID");
        pStmt = newDBConn.prepareStatement(
                "UPDATE locality SET LocalityName=?, NamedPlace=?, RelationToNamedPlace=?, Remarks=? WHERE LocalityID=?");
        stmt = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        stmt.setFetchSize(Integer.MIN_VALUE);

        ResultSet rs = stmt.executeQuery(sql);
        int cnt = 0;
        while (rs.next()) {
            int oldId = rs.getInt(1);
            Integer newId = locMapper.get(oldId);
            if (newId != null) {
                pStmt.setString(1, rs.getString(2));
                pStmt.setString(2, rs.getString(3));
                pStmt.setString(3, rs.getString(4));
                pStmt.setString(4, rs.getString(5));
                pStmt.setInt(5, newId);

                pStmt.execute();

                /*if (!pStmt.execute())
                {
                if ()
                String msg = "Error Updating OldId ["+rs.getInt(1)+"] NewId ["+newId+"]";
                log.error(msg);
                tblWriter.logErrors(Integer.toString(rs.getInt(1)), msg);
                }*/

            } else {
                String msg = "No Mapping for OldId [" + rs.getInt(1) + "]";
                log.error(msg);
                tblWriter.logErrors(Integer.toString(rs.getInt(1)), msg);
            }
            cnt++;
            if (cnt % 500 == 0) {
                frame.setProcess(cnt);
            }
        }

        frame.setProcess(cnt);

    } catch (Exception ex) {
        log.error(ex);
        tblWriter.logErrors("Exception", ex.toString());
    } finally {
        try {
            if (stmt != null)
                stmt.close();
            if (pStmt != null)
                pStmt.close();

        } catch (SQLException ex) {

        }
    }

    convertLocalityExtraInfo("localitydetail", false);
    convertLocalityExtraInfo("geocoorddetail", true);

    setFieldsToIgnoreWhenMappingNames(null);
    setIdentityInsertOFFCommandForSQLServer(newDBConn, "locality", BasicSQLUtils.myDestinationServerType);

    setTblWriter(null);
    IdHashMapper.setTblWriter(null);
}

---

From source file:edu.ku.brc.specify.conversion.GenericDBConversion.java

/**
 * @param treeDef//from w w  w.java 2s  .c o  m
 * @throws SQLException
 */
public void convertGeography(final GeographyTreeDef treeDef, final String dispName, final boolean firstTime)
        throws SQLException {
    TableWriter tblWriter = convLogger.getWriter("Geography" + (dispName != null ? dispName : "") + ".html",
            "Geography");
    setTblWriter(tblWriter);

    IdHashMapper.setTblWriter(tblWriter);

    if (firstTime) {
        // empty out any pre-existing records
        deleteAllRecordsFromTable(newDBConn, "geography", BasicSQLUtils.myDestinationServerType);
    }

    IdTableMapper geoIdMapper = (IdTableMapper) IdMapperMgr.getInstance().get("geography", "GeographyID");
    if (geoIdMapper == null) {
        // create an ID mapper for the geography table (mainly for use in converting localities)
        geoIdMapper = IdMapperMgr.getInstance().addTableMapper("geography", "GeographyID");
    } else {
        geoIdMapper.clearRecords();
    }

    Hashtable<Integer, Geography> oldIdToGeoMap = new Hashtable<Integer, Geography>();

    // get a Hibernate session for saving the new records
    Session localSession = HibernateUtil.getCurrentSession();
    HibernateUtil.beginTransaction();

    // get all of the old records
    String sql = "SELECT GeographyID,ContinentOrOcean,Country,State,County,LastEditedBy FROM geography";
    Statement statement = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
            ResultSet.CONCUR_READ_ONLY);
    statement.setFetchSize(Integer.MIN_VALUE);

    ResultSet oldGeoRecords = statement.executeQuery(sql);

    fixGeography("ContinentOrOcean");
    fixGeography("Country");
    fixGeography("State");
    fixGeography("County");

    if (hasFrame) {
        if (oldGeoRecords.last()) {
            setProcess(0, oldGeoRecords.getRow());
            oldGeoRecords.first();
        }
    } else {
        oldGeoRecords.first();
    }

    // setup the root Geography record (planet Earth)
    Geography planetEarth = new Geography();
    planetEarth.initialize();
    planetEarth.setName("Earth");
    planetEarth.setCommonName("Earth");
    planetEarth.setRankId(0);
    planetEarth.setDefinition(treeDef);
    for (GeographyTreeDefItem defItem : treeDef.getTreeDefItems()) {
        if (defItem.getRankId() == 0) {
            planetEarth.setDefinitionItem(defItem);
            break;
        }
    }
    GeographyTreeDefItem defItem = treeDef.getDefItemByRank(0);
    planetEarth.setDefinitionItem(defItem);

    int counter = 0;
    // for each old record, convert the record
    do {
        if (counter % 500 == 0) {
            if (hasFrame) {
                setProcess(counter);

            } else {
                log.info("Converted " + counter + " geography records");
            }
        }

        // grab the important data fields from the old record
        int oldId = oldGeoRecords.getInt(1);
        String cont = fixSize(tblWriter, oldId, "continent", oldGeoRecords.getString(2), 64);
        String country = fixSize(tblWriter, oldId, "country", oldGeoRecords.getString(3), 64);
        String state = fixSize(tblWriter, oldId, "state", oldGeoRecords.getString(4), 64);
        String county = fixSize(tblWriter, oldId, "county", oldGeoRecords.getString(5), 64);
        String lastEditedBy = oldGeoRecords.getString(6);

        Integer agtId = getCreatorAgentId(lastEditedBy);
        Agent createdByAgent = getCreatedByAgent(localSession, agtId);
        Agent modifiedByAgent = getAgentObj(localSession, getCurAgentModifierID());

        /*cont    = isNotEmpty(county)  && cont.equals("null")    ? null : cont;
        country = isNotEmpty(country) && country.equals("null") ? null : country;
        state   = isNotEmpty(state)   && state.equals("null")   ? null : state;
        county  = isNotEmpty(county)  && county.equals("null")  ? null : county;
        */

        if (isEmpty(cont) && isEmpty(country) && isEmpty(state) && isEmpty(county)) {
            //String msg = "For Record Id["+oldId+"] Continent, Country, State and County are all null.";
            //log.error(msg);
            //tblWriter.logError(msg);

            cont = "Undefined";
            country = "Undefined";
            state = "Undefined";
            county = "Undefined";

        } else if (isEmpty(cont) && isEmpty(country) && isEmpty(state)) {
            //String msg = "For Record Id["+oldId+"] Continent, Country and State are all null.";
            //log.error(msg);
            //tblWriter.logError(msg);

            cont = "Undefined";
            country = "Undefined";
            state = "Undefined";

        } else if (isEmpty(cont) && isEmpty(country)) {
            //String msg = "For Record Id["+oldId+"] Country is null.";
            //log.error(msg);
            //tblWriter.logError(msg);

            cont = "Undefined";
            country = "Undefined";

        } else if (isEmpty(cont)) {
            //String msg = "For Record Id["+oldId+"] Country is null.";
            //log.error(msg);
            //tblWriter.logError(msg);

            cont = "Undefined";
        }

        // create a new Geography object from the old data
        List<Geography> newGeos = convertOldGeoRecord(cont, country, state, county, createdByAgent,
                modifiedByAgent, planetEarth);
        if (newGeos.size() > 0) {
            Geography lowestLevel = newGeos.get(newGeos.size() - 1);

            oldIdToGeoMap.put(oldId, lowestLevel);
        }

        counter++;

    } while (oldGeoRecords.next());

    if (hasFrame) {
        setProcess(counter);

    } else {
        log.info("Converted " + counter + " geography records");
    }

    TreeHelper.fixFullnameForNodeAndDescendants(planetEarth);
    planetEarth.setNodeNumber(1);
    fixNodeNumbersFromRoot(planetEarth);

    localSession.save(planetEarth);

    HibernateUtil.commitTransaction();
    log.info("Converted " + counter + " geography records");

    if (shouldCreateMapTables) {
        // add all of the ID mappings
        for (Integer oldId : oldIdToGeoMap.keySet()) {
            Geography geo = oldIdToGeoMap.get(oldId);
            geoIdMapper.put(oldId, geo.getId());
        }
    }

    if (firstTime) {
        // set up Geography foreign key mapping for locality
        idMapperMgr.mapForeignKey("Locality", "GeographyID", "Geography", "GeographyID");
    }
}

---

From source file:edu.ku.brc.specify.conversion.GenericDBConversion.java

/**
 * @param treeDef/*from   w  w  w  . j a  v a 2s  .  c  o  m*/
 * @throws SQLException
 */
public void convertLithoStratGeneral(final LithoStratTreeDef treeDef, final LithoStrat earth,
        final TableWriter tblWriter, final String srcTableName) throws SQLException {
    Statement stmt = null;
    ResultSet rs = null;
    String s = "";
    try {
        // get a Hibernate session for saving the new records
        Session localSession = HibernateUtil.getCurrentSession();
        HibernateUtil.beginTransaction();

        int count = BasicSQLUtils.getCountAsInt(oldDBConn, "SELECT COUNT(*) FROM " + srcTableName);
        if (count < 1)
            return;

        if (hasFrame) {
            setProcess(0, count);
        }

        // create an ID mapper for the geography table (mainly for use in converting localities)
        IdHashMapper lithoStratIdMapper = IdMapperMgr.getInstance().addHashMapper("stratigraphy_StratigraphyID",
                true);
        if (lithoStratIdMapper == null) {
            UIRegistry.showError("The lithoStratIdMapper was null.");
            return;
        }

        IdMapperIFace gtpIdMapper = IdMapperMgr.getInstance().get("geologictimeperiod", "GeologicTimePeriodID");

        IdMapperIFace ceMapper = IdMapperMgr.getInstance().get("collectingevent", "CollectingEventID");
        if (ceMapper == null) {
            ceMapper = IdMapperMgr.getInstance().addTableMapper("collectingevent", "CollectingEventID", null,
                    false);
        }
        String sql = String.format(
                "SELECT s.StratigraphyID, s.SuperGroup, s.Group, s.Formation, s.Member, s.Bed, Remarks, "
                        + "Text1, Text2, Number1, Number2, YesNo1, YesNo2, GeologicTimePeriodID FROM %s s "
                        + "ORDER BY s.StratigraphyID",
                srcTableName);

        stmt = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        stmt.setFetchSize(Integer.MIN_VALUE);
        rs = stmt.executeQuery(sql);

        Map<Integer, Pair<Integer, Integer>> stratHash = new HashMap<Integer, Pair<Integer, Integer>>();

        int stratsWithNoGTP = 0;
        int stratsWithNoMappedGTP = 0;
        int missingCEMapping = 0;

        int lithoCnt = 0;

        int counter = 0;
        // for each old record, convert the record
        while (rs.next()) {
            if (counter % 500 == 0) {
                if (hasFrame) {
                    setProcess(counter);

                } else {
                    log.info("Converted " + counter + " Stratigraphy records");
                }
            }

            // grab the important data fields from the old record
            int oldStratId = rs.getInt(1); // This is a one-to-one with CollectingEvent
            String superGroup = rs.getString(2);
            String lithoGroup = rs.getString(3);
            String formation = rs.getString(4);
            String member = rs.getString(5);
            String bed = rs.getString(6);
            String remarks = escapeStringLiterals(rs.getString(7));
            String text1 = escapeStringLiterals(rs.getString(8));
            String text2 = escapeStringLiterals(rs.getString(9));
            Double number1 = rs.getObject(10) != null ? rs.getDouble(10) : null;
            Double number2 = rs.getObject(11) != null ? rs.getDouble(11) : null;
            Boolean yesNo1 = rs.getObject(12) != null ? rs.getBoolean(12) : null;
            Boolean yesNo2 = rs.getObject(13) != null ? rs.getBoolean(13) : null;
            Integer oldGTPId = rs.getObject(14) != null ? rs.getInt(14) : null;

            // Check to see if there is any Litho information OR an GTP Id
            // If both are missing then skip the record.
            boolean hasLithoFields = isNotEmpty(superGroup) || isNotEmpty(lithoGroup) || isNotEmpty(formation)
                    || isNotEmpty(member);
            if (!hasLithoFields && oldGTPId == null) {
                continue;
            }

            Integer gtpId = null;
            if (oldGTPId != null) {
                gtpId = gtpIdMapper.get(oldGTPId);
                if (gtpId == null) {
                    tblWriter.logError("Old GTPID[" + gtpId
                            + "] in the Strat record could not be mapped for Old StratID[" + oldStratId + "]");
                    stratsWithNoMappedGTP++;
                }
            } else {
                stratsWithNoGTP++;
            }

            // There may not be any Litho information to add to the LithoStrat tree, 
            // but it did have GTP Information if we got here
            Integer lithoStratID = null;
            if (hasLithoFields) {
                // create a new Geography object from the old data
                LithoStrat[] newStrats = convertOldStratRecord(superGroup, lithoGroup, formation, member, bed,
                        remarks, text1, text2, number1, number2, yesNo1, yesNo2, earth, localSession);

                LithoStrat newStrat = getLastLithoStrat(newStrats);
                counter++;
                lithoCnt += newStrats.length;

                // Map Old LithoStrat ID to the new Tree Id
                //System.out.println(oldStratId + " " + newStrat.getLithoStratId());
                if (newStrat != null) {
                    lithoStratID = newStrat.getLithoStratId();
                    lithoStratIdMapper.put(oldStratId, newStrat.getLithoStratId());
                } else {
                    String msg = String.format("Strat Fields were all null for oldID", oldStratId);
                    tblWriter.logError(msg);
                    log.error(msg);
                    missingCEMapping++;
                }
            }
            if (lithoStratID != null || gtpId != null) {
                Integer newCEId = ceMapper.get(oldStratId);
                if (newCEId == null) {
                    String msg = String.format("No CE mapping for Old StratId %d, when they are a one-to-one.",
                            oldStratId);
                    tblWriter.logError(msg);
                    log.error(msg);
                    missingCEMapping++;
                } else {
                    stratHash.put(newCEId, new Pair<Integer, Integer>(gtpId, lithoStratID));
                }
            }
        }
        stmt.close();

        System.out.println("lithoCnt: " + lithoCnt);

        if (hasFrame) {
            setProcess(counter);
        } else {
            log.info("Converted " + counter + " Stratigraphy records");
        }

        TreeHelper.fixFullnameForNodeAndDescendants(earth);
        earth.setNodeNumber(1);
        fixNodeNumbersFromRoot(earth);

        HibernateUtil.commitTransaction();
        log.info("Converted " + counter + " Stratigraphy records");

        rs.close();

        Statement updateStatement = newDBConn.createStatement();

        int ceCnt = BasicSQLUtils.getCountAsInt(oldDBConn,
                "SELECT Count(CollectingEventID) FROM collectingevent");
        int stratCnt = BasicSQLUtils.getCountAsInt(oldDBConn,
                String.format("SELECT Count(CollectingEventID) FROM collectingevent "
                        + "INNER JOIN %s ON CollectingEventID = StratigraphyID", srcTableName));

        String msg = String.format("There are %d CE->Strat and %d CEs. The diff is %d", stratCnt, ceCnt,
                (ceCnt - stratCnt));
        tblWriter.log(msg);
        log.debug(msg);

        // Create a PaleoContext for each ColObj
        stmt = newDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        stmt.setFetchSize(Integer.MIN_VALUE);

        int processCnt = BasicSQLUtils
                .getCountAsInt("SELECT COUNT(*) FROM collectionobject WHERE CollectingEventID IS NOT NULL");
        if (frame != null) {
            frame.setDesc("Converting PaleoContext...");
            frame.setProcess(0, processCnt);
        }

        TreeSet<Integer> missingStratIds = new TreeSet<Integer>();

        int missingStrat = 0;
        int missingGTP = 0;
        int coUpdateCnt = 0;
        int cnt = 0;
        sql = "SELECT CollectionObjectID, CollectingEventID FROM collectionobject WHERE CollectingEventID IS NOT NULL ORDER BY CollectionObjectID";
        rs = stmt.executeQuery(sql);
        while (rs.next()) {
            int coId = rs.getInt(1); // New CO Id
            Integer ceId = rs.getInt(2); // New CE Id

            Pair<Integer, Integer> strat = stratHash.get(ceId);
            Integer newLithoId = null;
            Integer gtpId = null;
            if (strat != null) {
                gtpId = strat.getFirst();
                newLithoId = strat.getSecond();
            }

            if (newLithoId == null) {
                missingStrat++;
                missingStratIds.add(ceId);
                if (gtpId == null)
                    continue;
            }

            try {
                String updateStr = "INSERT INTO paleocontext (TimestampCreated, TimestampModified, DisciplineID, Version, CreatedByAgentID, ModifiedByAgentID, LithoStratID, ChronosStratID) "
                        + "VALUES ('" + nowStr + "','" + nowStr + "'," + getDisciplineId() + ", 0, "
                        + getCreatorAgentId(null) + "," + getModifiedByAgentId(null) + ","
                        + (newLithoId != null ? newLithoId : "NULL") + "," + (gtpId != null ? gtpId : "NULL")
                        + ")";
                updateStatement.executeUpdate(updateStr, Statement.RETURN_GENERATED_KEYS);

                Integer paleoContextID = getInsertedId(updateStatement);
                if (paleoContextID == null) {
                    throw new RuntimeException("Couldn't get the Agent's inserted ID");
                }

                String sqlUpdate = "UPDATE collectionobject SET PaleoContextID=" + paleoContextID
                        + " WHERE CollectionObjectID = " + coId;
                updateStatement.executeUpdate(sqlUpdate);
                coUpdateCnt++;
            } catch (SQLException e) {
                e.printStackTrace();
                log.error(e);
                showError(e.getMessage());
                throw new RuntimeException(e);
            }
            processCnt++;
            if (frame != null && cnt % 100 == 0)
                frame.setProcess(cnt);
        }
        rs.close();
        stmt.close();

        if (frame != null)
            frame.setProcess(processCnt);

        msg = String.format("There are %d unmappable Strat Records and %d unmappable GTP records.",
                missingStrat, missingGTP);
        tblWriter.log(msg);
        log.debug(msg);

        msg = String.format("There are %d CO records updated.", coUpdateCnt);
        tblWriter.log(msg);
        log.debug(msg);
        updateStatement.close();

        msg = String.format("No CE mapping for Old StratId Count: %d", missingCEMapping);
        tblWriter.logError(msg);
        log.error(msg);

        msg = String.format("Strats with No GTP Count: %d", stratsWithNoGTP);
        tblWriter.logError(msg);
        log.error(msg);

        msg = String.format("Strats with missing Mapping to GTP Count: %d", stratsWithNoMappedGTP);
        tblWriter.logError(msg);
        log.error(msg);

        msg = String.format("Number of Old StratIds mapped to a new Strat ID Count: %d",
                lithoStratIdMapper.size());
        tblWriter.logError(msg);
        log.error(msg);

        StringBuilder sb = new StringBuilder();
        sb.append("Missing New Strat: ");
        if (missingStratIds.size() == 0)
            sb.append("None");

        for (Integer id : missingStratIds) {
            sb.append(String.format("%d, ", id));
        }
        tblWriter.logError(sb.toString());
        log.error(sb.toString());

    } catch (Exception ex) {
        ex.printStackTrace();
    }

    // Now in this Step we Add the PaleoContext to the Collecting Events

}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• »