Example usage for java.sql ResultSet CONCUR_READ_ONLY

List of usage examples for java.sql ResultSet CONCUR_READ_ONLY

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. ResultSet
  6. CONCUR_READ_ONLY

Introduction

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

Prototype

int CONCUR_READ_ONLY

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

Click Source Link

Document

The constant indicating the concurrency mode for a ResultSet object that may NOT be updated.

Usage

From source file:net.sourceforge.msscodefactory.v1_10.MSSBamPg8.MSSBamPg8AnyObjTable.java

public MSSBamAnyObjBuff[] readAllBuff(MSSBamAuthorization Authorization) {
    final String S_ProcName = "readAllBuff";
    if (!schema.isTransactionOpen()) {
        throw CFLib.getDefaultExceptionFactory().newUsageException(getClass(), S_ProcName,
                "Transaction not open");
    }// ww w .j a  va  2s.co  m
    try {
        Connection cnx = schema.getCnx();
        String sql = S_sqlSelectAnyObjBuff + "WHERE " + "anyo.ClassCode = 'ANYO' " + "ORDER BY "
                + "anyo.Id ASC";
        Statement stmt = cnx.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        ResultSet resultSet = stmt.executeQuery(sql);
        List<MSSBamAnyObjBuff> buffList = new ArrayList<MSSBamAnyObjBuff>();
        while (resultSet.next()) {
            MSSBamAnyObjBuff buff = unpackAnyObjResultSetToBuff(resultSet);
            buffList.add(buff);
        }
        return (buffList.toArray(new MSSBamAnyObjBuff[0]));
    } catch (SQLException e) {
        throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
    }
}

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:net.sourceforge.msscodefactory.v1_10.MSSBamPg8.MSSBamPg8AnyObjTable.java

public MSSBamAnyObjBuff readBuffByIdIdx(MSSBamAuthorization Authorization, long Id) {
    final String S_ProcName = "readBuffByIdIdx";
    try {/*www. j  av  a  2 s.  c o m*/
        Connection cnx = schema.getCnx();
        String sql = S_sqlSelectAnyObjBuff + "WHERE " + "anyo.Id = " + Long.toString(Id) + " ";
        Statement stmt = cnx.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        ResultSet resultSet = stmt.executeQuery(sql);
        if (resultSet.next()) {
            MSSBamAnyObjBuff buff = unpackAnyObjResultSetToBuff(resultSet);
            if (resultSet.next()) {
                resultSet.last();
                throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName,
                        "Did not expect multi-buff response, " + resultSet.getRow() + " rows selected");
            }
            return (buff);
        } else {
            return (null);
        }
    } catch (SQLException e) {
        throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
    }
}

From source file:net.sourceforge.msscodefactory.v1_10.MSSBamPg8.MSSBamPg8AnyObjTable.java

public MSSBamAnyObjBuff[] readBuffByTenantIdx(MSSBamAuthorization Authorization, long TenantId) {
    final String S_ProcName = "readBuffByTenantIdx";
    try {//from  w  w  w .j a v a 2s  .  c  om
        Connection cnx = schema.getCnx();
        String sql = S_sqlSelectAnyObjBuff + "WHERE " + "anyo.TenantId = " + Long.toString(TenantId) + " "
                + "ORDER BY " + "anyo.Id ASC";
        Statement stmt = cnx.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        ResultSet resultSet = stmt.executeQuery(sql);
        List<MSSBamAnyObjBuff> buffList = new ArrayList<MSSBamAnyObjBuff>();
        while (resultSet.next()) {
            MSSBamAnyObjBuff buff = unpackAnyObjResultSetToBuff(resultSet);
            buffList.add(buff);
        }
        return (buffList.toArray(new MSSBamAnyObjBuff[0]));
    } catch (SQLException e) {
        throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
    }
}

From source file:net.sourceforge.msscodefactory.v1_10.MSSBamPg8.MSSBamPg8AnyObjTable.java

public MSSBamAnyObjBuff[] readBuffByScopeIdx(MSSBamAuthorization Authorization, Long ScopeId) {
    final String S_ProcName = "readBuffByScopeIdx";
    try {//from   w ww  .  j a va2s.  co  m
        Connection cnx = schema.getCnx();
        String sql = S_sqlSelectAnyObjBuff + "WHERE "
                + ((ScopeId == null) ? "anyo.ScopeId is null " : "anyo.ScopeId = " + ScopeId.toString() + " ")
                + "ORDER BY " + "anyo.Id ASC";
        Statement stmt = cnx.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        ResultSet resultSet = stmt.executeQuery(sql);
        List<MSSBamAnyObjBuff> buffList = new ArrayList<MSSBamAnyObjBuff>();
        while (resultSet.next()) {
            MSSBamAnyObjBuff buff = unpackAnyObjResultSetToBuff(resultSet);
            buffList.add(buff);
        }
        return (buffList.toArray(new MSSBamAnyObjBuff[0]));
    } catch (SQLException e) {
        throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
    }
}

From source file:net.sourceforge.msscodefactory.v1_10.MSSBamPg8.MSSBamPg8AnyObjTable.java

public MSSBamAnyObjBuff[] readBuffByAuthorIdx(MSSBamAuthorization Authorization, Long AuthorId) {
    final String S_ProcName = "readBuffByAuthorIdx";
    try {//from w w w  .  ja v  a 2 s.com
        Connection cnx = schema.getCnx();
        String sql = S_sqlSelectAnyObjBuff + "WHERE " + ((AuthorId == null) ? "anyo.AuthorId is null "
                : "anyo.AuthorId = " + AuthorId.toString() + " ") + "ORDER BY " + "anyo.Id ASC";
        Statement stmt = cnx.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        ResultSet resultSet = stmt.executeQuery(sql);
        List<MSSBamAnyObjBuff> buffList = new ArrayList<MSSBamAnyObjBuff>();
        while (resultSet.next()) {
            MSSBamAnyObjBuff buff = unpackAnyObjResultSetToBuff(resultSet);
            buffList.add(buff);
        }
        return (buffList.toArray(new MSSBamAnyObjBuff[0]));
    } catch (SQLException e) {
        throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
    }
}

From source file:net.sourceforge.msscodefactory.v1_10.MSSBamPg8.MSSBamPg8AnyObjTable.java

public MSSBamAnyObjBuff readBuffByUNameIdx(MSSBamAuthorization Authorization, Long ScopeId, String Name) {
    final String S_ProcName = "readBuffByUNameIdx";
    try {//from   w  w w . j  av  a 2  s  .  c om
        Connection cnx = schema.getCnx();
        String sql = S_sqlSelectAnyObjBuff + "WHERE "
                + ((ScopeId == null) ? "anyo.ScopeId is null " : "anyo.ScopeId = " + ScopeId.toString() + " ")
                + "AND " + "anyo.Name = " + MSSBamPg8Schema.getQuotedString(Name) + " ";
        Statement stmt = cnx.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        ResultSet resultSet = stmt.executeQuery(sql);
        if (resultSet.next()) {
            MSSBamAnyObjBuff buff = unpackAnyObjResultSetToBuff(resultSet);
            if (resultSet.next()) {
                resultSet.last();
                throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName,
                        "Did not expect multi-buff response, " + resultSet.getRow() + " rows selected");
            }
            return (buff);
        } else {
            return (null);
        }
    } catch (SQLException e) {
        throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
    }
}

From source file:net.sourceforge.msscodefactory.v1_10.MSSBamPg8.MSSBamPg8AnyObjTable.java

public void updateAnyObj(MSSBamAuthorization Authorization, MSSBamAnyObjBuff Buff) {
    final String S_ProcName = "updateAnyObj";
    try {//w  w  w .j av  a 2  s  . com
        Connection cnx = schema.getCnx();
        String ClassCode = Buff.getClassCode();
        long TenantId = Buff.getRequiredTenantId();
        long Id = Buff.getRequiredId();
        Long ScopeId = Buff.getOptionalScopeId();
        String Name = Buff.getRequiredName();
        String ShortName = Buff.getOptionalShortName();
        String Label = Buff.getOptionalLabel();
        String ShortDescription = Buff.getOptionalShortDescription();
        String Description = Buff.getOptionalDescription();
        Long AuthorId = Buff.getOptionalAuthorId();

        int Revision = Buff.getRequiredRevision();
        MSSBamAnyObjBuff readBuff = readBuffByIdIdx(Authorization, Id);
        int oldRevision = readBuff.getRequiredRevision();
        if (oldRevision != Revision) {
            throw CFLib.getDefaultExceptionFactory().newCollisionDetectedException(getClass(), S_ProcName,
                    Buff);
        }
        int newRevision = Revision + 1;
        String sql = "UPDATE mssbam110.any_obj " + "SET " + "TenantId = "
                + MSSBamPg8Schema.getInt64String(TenantId) + ", " + "Id = " + MSSBamPg8Schema.getInt64String(Id)
                + ", " + "ScopeId = " + ((ScopeId != null) ? MSSBamPg8Schema.getInt64String(ScopeId) : "null")
                + ", " + "Name = " + MSSBamPg8Schema.getQuotedString(Name) + ", " + "short_name = "
                + ((ShortName != null) ? MSSBamPg8Schema.getQuotedString(ShortName) : "null") + ", "
                + "Label = " + ((Label != null) ? MSSBamPg8Schema.getQuotedString(Label) : "null") + ", "
                + "short_descr = "
                + ((ShortDescription != null) ? MSSBamPg8Schema.getQuotedString(ShortDescription) : "null")
                + ", " + "descr = "
                + ((Description != null) ? MSSBamPg8Schema.getQuotedString(Description) : "null") + ", "
                + "AuthorId = " + ((AuthorId != null) ? MSSBamPg8Schema.getInt64String(AuthorId) : "null")
                + ", " + "Revision = " + Integer.toString(newRevision) + " " + "WHERE " + "Id = "
                + Long.toString(Id) + " " + "AND " + "Revision = " + Integer.toString(Revision);
        Statement stmt = cnx.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        int rowsAffected = stmt.executeUpdate(sql);
        if (rowsAffected != 1) {
            throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName,
                    "Expected 1 row to be affected by update, not " + rowsAffected);
        }
        Buff.setRequiredRevision(newRevision);
    } catch (SQLException e) {
        throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
    }
}

From source file:net.sourceforge.msscodefactory.v1_10.MSSBamPg8.MSSBamPg8AnyObjTable.java

public void deleteAnyObj(MSSBamAuthorization Authorization, MSSBamAnyObjBuff Buff) {
    final String S_ProcName = "deleteAnyObj";
    try {//from   www .j a  v  a2s  .  c o  m
        Connection cnx = schema.getCnx();
        long TenantId = Buff.getRequiredTenantId();
        long Id = Buff.getRequiredId();
        Long ScopeId = Buff.getOptionalScopeId();
        String Name = Buff.getRequiredName();
        String ShortName = Buff.getOptionalShortName();
        String Label = Buff.getOptionalLabel();
        String ShortDescription = Buff.getOptionalShortDescription();
        String Description = Buff.getOptionalDescription();
        Long AuthorId = Buff.getOptionalAuthorId();

        int Revision = Buff.getRequiredRevision();
        MSSBamAnyObjBuff readBuff = readBuffByIdIdx(Authorization, Id);
        int oldRevision = readBuff.getRequiredRevision();
        if (oldRevision != Revision) {
            throw CFLib.getDefaultExceptionFactory().newCollisionDetectedException(getClass(), S_ProcName,
                    Buff);
        }
        String sql = "DELETE FROM mssbam110.any_obj " + "WHERE " + "Id = " + Long.toString(Id) + " " + "AND "
                + "Revision = " + Integer.toString(Revision);
        ;
        Statement stmt = cnx.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        int rowsAffected = stmt.executeUpdate(sql);
        if (rowsAffected != 1) {
            throw CFLib.getDefaultExceptionFactory().newRuntimeException(getClass(), S_ProcName,
                    "Expected 1 row to be affected by delete, not " + rowsAffected);
        }
    } catch (SQLException e) {
        throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
    }
}

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.//from   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;
}