Example usage for java.sql Statement setFetchSize

List of usage examples for java.sql Statement setFetchSize

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. Statement
  6. setFetchSize

Introduction

In this page you can find the example usage for java.sql Statement setFetchSize.

Prototype

void setFetchSize(int rows) throws SQLException;

Source Link

Document

Gives the JDBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed for ResultSet objects generated by this Statement.

Usage

From source file:com.taobao.tddl.jdbc.group.TGroupStatement.java

/**
 * setBaseStatementStatement/*ww  w  .  j ava  2s  . c o m*/
 */
private Statement createStatementInternal(Connection conn, boolean isBatch) throws SQLException {
    Statement stmt;
    if (isBatch)
        stmt = conn.createStatement();
    else {
        int resultSetHoldability = this.resultSetHoldability;
        if (resultSetHoldability == -1) //setResultSetHoldability
            resultSetHoldability = conn.getHoldability();

        stmt = conn.createStatement(this.resultSetType, this.resultSetConcurrency, resultSetHoldability);
    }

    setBaseStatement(stmt); //Statement
    stmt.setQueryTimeout(queryTimeout); //
    stmt.setFetchSize(fetchSize);
    stmt.setMaxRows(maxRows);

    return stmt;
}

From source file:com.nridge.core.ds.rdbms.hsqldb.HDBSQLTable.java

private void query(String aSQLStatement, DataTable aTable, int aLimit) throws NSException {
    Logger appLogger = mAppMgr.getLogger(this, "query");

    appLogger.trace(mAppMgr.LOGMSG_TRACE_ENTER);

    Statement stmtQuery = null;
    appLogger.debug(aSQLStatement);/*  ww  w.  j a v  a 2  s.c o  m*/
    Connection jdbcConnection = mSQLConnection.getJDBCConnection();
    try {
        stmtQuery = jdbcConnection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        if (aLimit > 0)
            stmtQuery.setFetchSize(aLimit);
        stmtQuery.setEscapeProcessing(mSQLConnection.isStatementEscapingEnabled());
        mSQLConnection.setLastStatement(aSQLStatement);
        ResultSet resultSet = stmtQuery.executeQuery(aSQLStatement);
        while (resultSet.next())
            addTableRowFromResultSet(aTable, resultSet);
    } catch (SQLException e) {
        throw new NSException("RDBMS Query Error: " + aSQLStatement + " : " + e.getMessage(), e);
    } finally {
        if (stmtQuery != null) {
            try {
                stmtQuery.close();
            } catch (SQLException ignored) {
            }
        }
    }

    appLogger.trace(mAppMgr.LOGMSG_TRACE_DEPART);
}

From source file:com.nridge.core.ds.rdbms.hsqldb.HDBSQLTable.java

private void queryFunction(String aSQLStatement, DataTable aTable, int aLimit) throws NSException {
    Logger appLogger = mAppMgr.getLogger(this, "queryFunction");

    appLogger.trace(mAppMgr.LOGMSG_TRACE_ENTER);

    Statement stmtQuery = null;
    appLogger.debug(aSQLStatement);/*from w w  w.  j a v a2  s  .  co  m*/
    Connection jdbcConnection = mSQLConnection.getJDBCConnection();
    try {
        stmtQuery = jdbcConnection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        if (aLimit > 0)
            stmtQuery.setFetchSize(aLimit);
        stmtQuery.setEscapeProcessing(mSQLConnection.isStatementEscapingEnabled());
        mSQLConnection.setLastStatement(aSQLStatement);
        ResultSet resultSet = stmtQuery.executeQuery(aSQLStatement);
        while (resultSet.next())
            addTableRowFromFunctionResultSet(aTable, resultSet);
    } catch (SQLException e) {
        throw new NSException("RDBMS Query Error: " + aSQLStatement + " : " + e.getMessage(), e);
    } finally {
        if (stmtQuery != null) {
            try {
                stmtQuery.close();
            } catch (SQLException ignored) {
            }
        }
    }

    appLogger.trace(mAppMgr.LOGMSG_TRACE_DEPART);
}

From source file:edu.ku.brc.specify.toycode.mexconabio.CopyFromGBIF.java

/**
 * /*ww w.  j  a v a2 s . c o  m*/
 */
public void processMissingGenusSpecies() {
    String pSQL = "UPDATE raw SET genus=?, species=?, subspecies=? WHERE id = ?";

    String where = " WHERE genus IS NULL AND species IS NULL AND scientific_name IS NOT NULL";
    String gbifSQLBase = "SELECT id, scientific_name FROM raw" + where;

    long totalRecs = BasicSQLUtils.getCount(srcDBConn, "SELECT COUNT(*) FROM raw " + where);
    long procRecs = 0;
    long startTime = System.currentTimeMillis();
    int secsThreshold = 0;

    PrintWriter pw = null;

    final double HRS = 1000.0 * 60.0 * 60.0;

    Statement gStmt = null;
    PreparedStatement pStmt = null;

    try {
        pw = new PrintWriter("gbif.log");

        pStmt = dbConn.prepareStatement(pSQL);

        System.out.println("Total Records: " + totalRecs);
        pw.println("Total Records: " + totalRecs);

        gStmt = srcDBConn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        gStmt.setFetchSize(Integer.MIN_VALUE);

        System.out.println(gbifSQLBase);

        ResultSet gRS = gStmt.executeQuery(gbifSQLBase);
        while (gRS.next()) {
            int id = gRS.getInt(1);
            pStmt.setObject(4, id);

            String[] gs = StringUtils.split(gRS.getString(2), ' ');
            switch (gs.length) {
            case 1:
                pStmt.setString(1, gs[0]);
                pStmt.setString(2, null);
                pStmt.setString(3, null);
                break;

            case 2:
                pStmt.setString(1, gs[0]);
                pStmt.setString(2, gs[1]);
                pStmt.setString(3, null);
                break;

            case 3:
                pStmt.setString(1, gs[0]);
                pStmt.setString(2, gs[1]);
                pStmt.setString(3, gs[2]);
                break;

            default:
                continue;
            }
            try {
                pStmt.executeUpdate();

            } catch (Exception ex) {
                System.err.println("For ID[" + gRS.getObject(1) + "][" + gRS.getObject(2) + "]");
                ex.printStackTrace();
                pw.print("For ID[" + gRS.getObject(1) + "] " + ex.getMessage());
                pw.flush();
            }

            procRecs++;
            if (procRecs % 10000 == 0) {
                long endTime = System.currentTimeMillis();
                long elapsedTime = endTime - startTime;

                double avergeTime = (double) elapsedTime / (double) procRecs;

                double hrsLeft = (((double) elapsedTime / (double) procRecs) * (double) totalRecs - procRecs)
                        / HRS;

                int seconds = (int) (elapsedTime / 60000.0);
                if (secsThreshold != seconds) {
                    secsThreshold = seconds;

                    String msg = String.format(
                            "Elapsed %8.2f hr.mn   Ave Time: %5.2f    Percent: %6.3f  Hours Left: %8.2f ",
                            ((double) (elapsedTime)) / HRS, avergeTime,
                            100.0 * ((double) procRecs / (double) totalRecs), hrsLeft);
                    System.out.println(msg);
                    pw.println(msg);
                    pw.flush();
                }
            }
        }

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

    } finally {
        try {
            if (gStmt != null) {
                gStmt.close();
            }
            if (pStmt != null) {
                pStmt.close();
            }
            pw.close();

        } catch (Exception ex) {

        }
    }
    System.out.println("Done transferring.");
    pw.println("Done transferring.");
}

From source file:edu.ku.brc.specify.toycode.mexconabio.CopyFromGBIF.java

/**
 * /*ww w.  ja v  a2s  . c  om*/
 */
public void index() {
    IndexWriter writer = null;
    try {
        analyzer = new StandardAnalyzer(Version.LUCENE_36);

        FileUtils.deleteDirectory(INDEX_DIR);

        System.out.println("Indexing to directory '" + INDEX_DIR + "'...");

        long totalRecs = BasicSQLUtils.getCount(srcDBConn, "SELECT COUNT(*) FROM raw");
        long procRecs = 0;
        long startTime = System.currentTimeMillis();
        int secsThreshold = 0;

        PrintWriter pw = null;

        final double HRS = 1000.0 * 60.0 * 60.0;

        Statement stmt = null;

        try {
            writer = new IndexWriter(FSDirectory.open(INDEX_DIR), analyzer, true,
                    IndexWriter.MaxFieldLength.LIMITED);

            pw = new PrintWriter("gbif.log");

            System.out.println("Total Records: " + totalRecs);
            pw.println("Total Records: " + totalRecs);

            stmt = srcDBConn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
            stmt.setFetchSize(Integer.MIN_VALUE);

            //String[]          fldNames = {"id", "cn", "gn", "sp", "cln", "ctr", "yr", "mn", "dy"};
            ResultSet rs = stmt.executeQuery(
                    "SELECT id, catalogue_number, genus, species, collector_num, collector_name, year, month, day, state_province, county FROM raw");// LIMIT 100000,1000");
            ResultSetMetaData rsmd = rs.getMetaData();

            StringBuilder sb = new StringBuilder();
            while (rs.next()) {
                String id = rs.getString(1);
                Document doc = new Document();
                doc.add(new Field("id", id.toString(), Field.Store.YES, Field.Index.NO));

                sb.setLength(0);
                for (int i = 2; i <= rsmd.getColumnCount(); i++) {
                    String val = rs.getString(i);
                    if (StringUtils.isNotEmpty(val)) {
                        sb.append(val);
                        sb.append(' ');
                    }
                }
                doc.add(new Field("contents", sb.toString(), Field.Store.NO, Field.Index.ANALYZED));

                writer.addDocument(doc);

                procRecs++;
                if (procRecs % 10000 == 0) {
                    long endTime = System.currentTimeMillis();
                    long elapsedTime = endTime - startTime;

                    double timePerRecord = (elapsedTime / procRecs);

                    double hrsLeft = ((totalRecs - procRecs) * timePerRecord) / HRS;

                    int seconds = (int) (elapsedTime / 60000.0);
                    if (secsThreshold != seconds) {
                        secsThreshold = seconds;

                        String msg = String.format("Elapsed %8.2f hr.mn   Percent: %6.3f  Hours Left: %8.2f ",
                                ((double) (elapsedTime)) / HRS,
                                100.0 * ((double) procRecs / (double) totalRecs), hrsLeft);
                        System.out.println(msg);
                        pw.println(msg);
                        pw.flush();
                    }
                }
            }

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

        } catch (IOException e) {
            e.printStackTrace();
            System.out.println("IOException adding Lucene Document: " + e.getMessage());

        } finally {

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

        }

    } catch (IOException e) {
        e.printStackTrace();

        System.out.println(" caught a " + e.getClass() + "\n with message: " + e.getMessage());

    } finally {
        analyzer.close();
        analyzer = null;

        if (writer != null) {
            try {
                System.out.println("Optimizing...");
                writer.optimize();
                writer.close();
                System.out.println("Done Optimizing.");

            } catch (CorruptIndexException e) {
                e.printStackTrace();

            } catch (IOException e) {
                e.printStackTrace();
            }
            writer = null;
        }
    }
}

From source file:gobblin.source.extractor.extract.jdbc.JdbcExtractor.java

/**
 * Execute query using JDBC simple Statement Set fetch size
 *
 * @param cmds commands - query, fetch size
 * @return JDBC ResultSet/*from  w w w  .  j a  v a  2  s.co  m*/
 * @throws Exception
 */
private CommandOutput<?, ?> executeSql(List<Command> cmds) {
    String query = null;
    int fetchSize = 0;

    for (Command cmd : cmds) {
        if (cmd instanceof JdbcCommand) {
            JdbcCommandType type = (JdbcCommandType) cmd.getCommandType();
            switch (type) {
            case QUERY:
                query = cmd.getParams().get(0);
                break;
            case FETCHSIZE:
                fetchSize = Integer.parseInt(cmd.getParams().get(0));
                break;
            default:
                this.log.error("Command " + type.toString() + " not recognized");
                break;
            }
        }
    }

    this.log.info("Executing query:" + query);
    ResultSet resultSet = null;
    try {
        this.jdbcSource = createJdbcSource();
        this.dataConnection = this.jdbcSource.getConnection();
        Statement statement = this.dataConnection.createStatement();

        if (fetchSize != 0 && this.getExpectedRecordCount() > 2000) {
            statement.setFetchSize(fetchSize);
        }
        final boolean status = statement.execute(query);
        if (status == false) {
            this.log.error("Failed to execute sql:" + query);
        }
        resultSet = statement.getResultSet();
    } catch (Exception e) {
        this.log.error("Failed to execute sql:" + query + " ;error-" + e.getMessage(), e);
    }

    CommandOutput<JdbcCommand, ResultSet> output = new JdbcCommandOutput();
    output.put((JdbcCommand) cmds.get(0), resultSet);
    return output;
}

From source file:com.commander4j.db.JDBUserReport.java

public LinkedList<JDBListData> getUserReportIds() {
    LinkedList<JDBListData> groupUserReportList = new LinkedList<JDBListData>();
    Statement stmt;
    ResultSet rs;/*w  w w . j av  a  2  s.co  m*/
    setErrorMessage("");
    Icon icon = new ImageIcon();
    int index = 0;
    boolean show = false;

    try {
        stmt = Common.hostList.getHost(getHostID()).getConnection(getSessionID()).createStatement();
        stmt.setFetchSize(250);
        rs = stmt.executeQuery(Common.hostList.getHost(getHostID()).getSqlstatements()
                .getSQL("JDBUserReport.getUserReportIDs"));

        while (rs.next()) {
            // TO DO
            // IF user is private check username and group membership.
            if (adminUser) {
                show = true;
            } else {
                show = false;
                if (rs.getString("PRIVATE").equals("N")) {
                    show = true;
                } else {
                    if (rs.getString("USER_ID").equals(Common.userList.getUser(getSessionID()).getUserId())) {
                        show = true;
                    }

                    ugm.setUserId(Common.userList.getUser(getSessionID()).getUserId());
                    ugm.setGroupId(rs.getString("GROUP_ID"));

                    if (ugm.isValidUserGroupMembership()) {
                        show = true;
                    }

                }
            }

            if (show == true) {
                JDBUserReport ur = new JDBUserReport(getHostID(), getSessionID());
                ur.getPropertiesfromResultSet(rs);
                icon = getUserReportIcon(rs.getString("ENABLED"), rs.getString("DESTINATION"));
                JDBListData mld = new JDBListData(icon, index, true, ur);

                groupUserReportList.addLast(mld);
            }
        }
        rs.close();
        stmt.close();

    } catch (SQLException e) {
        setErrorMessage(e.getMessage());
    }

    return groupUserReportList;
}

From source file:cc.tooyoung.common.db.JdbcTemplate.java

/**
 * Prepare the given JDBC Statement (or PreparedStatement or CallableStatement),
 * applying statement settings such as fetch size, max rows, and query timeout.
 * @param stmt the JDBC Statement to prepare
 * @throws SQLException if thrown by JDBC API
 * @see #setFetchSize/*ww w  .  java2  s .  co  m*/
 * @see #setMaxRows
 * @see #setQueryTimeout
 * @see org.springframework.jdbc.datasource.DataSourceUtils#applyTransactionTimeout
 */
protected void applyStatementSettings(DataSource dataSource, Statement stmt) throws SQLException {
    int fetchSize = getFetchSize();
    if (fetchSize > 0) {
        stmt.setFetchSize(fetchSize);
    }
    int maxRows = getMaxRows();
    if (maxRows > 0) {
        stmt.setMaxRows(maxRows);
    }
    DataSourceUtils.applyTimeout(stmt, dataSource, getQueryTimeout());
}

From source file:edu.ku.brc.specify.toycode.mexconabio.AnalysisWithGBIFToGBIF.java

@Override
public void process(final int type, final int options) {
    calcMaxScore();/*from w ww  .j  a v a2s.c  om*/

    String gbifSQL = "SELECT DISTINCT id, catalogue_number, genus, species, subspecies, latitude, longitude, country, state_province, collector_name, locality, year, month, day, collector_num ";

    String fromClause1a = "FROM raw WHERE collector_num LIKE ? AND year = ? AND genus = ?";
    String fromClause1b = "FROM raw WHERE collector_num IS NULL AND year = ? AND genus = ?";
    //String fromClause2  = "FROM raw WHERE collector_num IS NULL AND year = ? AND month = ? AND genus = ? AND id <> ?";

    //                        1       2           3        4           5         6          7         8           9               10          11       12    13    14      15
    String postSQL = "FROM raw WHERE collector_num IS NOT NULL GROUP BY collector_num, year, genus";
    String srcSQL = "SELECT id, catalogue_number, genus, species, subspecies, latitude, longitude, country, state_province, collector_name, locality, year, month, day, collector_num "
            + postSQL + " ORDER BY collector_num";

    String grphashSQL = "SELECT name FROM group_hash";

    String gbifgbifInsert = "INSERT INTO gbifgbif (reltype, score, GBIFID, SNIBID) VALUES (?,?,?,?)";

    Statement stmt = null;
    PreparedStatement gStmt1a = null;
    PreparedStatement gStmt1b = null;
    //PreparedStatement gStmt2  = null;
    PreparedStatement gsStmt = null;

    Object[] refRow = new Object[NUM_FIELDS];
    Object[] cmpRow = new Object[NUM_FIELDS];

    long totalRecs = BasicSQLUtils.getCount(dbSrcConn, "SELECT COUNT(*) FROM group_hash");
    long procRecs = 0;
    long startTime = System.currentTimeMillis();
    int secsThreshold = 0;

    String blank = "X?";

    PrintWriter pw = null;
    try {
        pw = new PrintWriter("scoring_gbifgbif.log");

        gStmt1a = dbGBIFConn.prepareStatement(gbifSQL + fromClause1a);
        gStmt1b = dbGBIFConn.prepareStatement(gbifSQL + fromClause1b);

        //gStmt2 = dbGBIFConn.prepareStatement(gbifSQL + fromClause2);
        gsStmt = dbDstConn.prepareStatement(gbifgbifInsert);

        stmt = dbSrcConn.createStatement(ResultSet.FETCH_FORWARD, ResultSet.CONCUR_READ_ONLY);
        stmt.setFetchSize(Integer.MIN_VALUE);

        System.out.println("Starting Query... " + totalRecs);
        pw.println("Starting Query... " + totalRecs);
        System.out.flush();
        pw.flush();

        HashSet<Integer> idHash = new HashSet<Integer>();
        int writeCnt = 0;
        ResultSet rs = stmt.executeQuery(grphashSQL);

        System.out
                .println(String.format("Starting Processing... Total Records %d  Max Score: %d  Threshold: %d",
                        totalRecs, maxScore, thresholdScore));
        pw.println(String.format("Starting Processing... Total Records %d  Max Score: %d  Threshold: %d",
                totalRecs, maxScore, thresholdScore));
        System.out.flush();
        pw.flush();

        Vector<Object[]> group = new Vector<Object[]>();
        ArrayList<Integer> ids = new ArrayList<Integer>();
        while (rs.next()) {
            String[] tokens = StringUtils.split(rs.getString(1), '_');

            String colNum = tokens[0].trim();
            String year = tokens[1].trim();
            String genus = tokens[2].trim();

            if (StringUtils.isEmpty(colNum) || colNum.equals(blank))
                colNum = null;
            if (StringUtils.isEmpty(year) || year.equals(blank))
                year = null;
            if (StringUtils.isEmpty(genus) || genus.equals(blank))
                genus = null;

            PreparedStatement gStmt1;
            if (colNum != null) {
                gStmt1 = gStmt1a;
                gStmt1.setString(1, "%" + colNum + "%");
            } else {
                gStmt1 = gStmt1b;
                gStmt1.setString(1, null);
            }
            gStmt1.setString(2, year);
            gStmt1.setString(3, genus);
            ResultSet gRS = gStmt1.executeQuery();

            ids.clear();
            int maxNonNullTot = -1;
            int maxNonNullInx = -1;
            int inx = 0;
            while (gRS.next()) {

                Object[] row = getRow();
                int cnt = fillRowWithScore(row, gRS);
                if (cnt > maxNonNullTot) {
                    maxNonNullInx = inx;
                    maxNonNullTot = cnt;
                }
                group.add(row);
                ids.add(gRS.getInt(1));
                inx++;
            }
            gRS.close();

            if (inx < 2) {
                for (Object[] r : group) {
                    recycleRow(r);
                }
                group.clear();
                continue;
            }

            System.arraycopy(group.get(maxNonNullInx), 0, refRow, 0, refRow.length);

            Integer srcId = ids.get(maxNonNullInx);

            for (int i = 0; i < group.size(); i++) {
                if (i != maxNonNullInx) {
                    int score = score(refRow, group.get(i));

                    if (score > thresholdScore) {
                        writeCnt++;

                        int gbifID = ids.get(i);
                        gsStmt.setInt(1, 1); // reltype
                        gsStmt.setInt(2, score); // score
                        gsStmt.setInt(3, gbifID);
                        gsStmt.setInt(4, srcId);
                        gsStmt.executeUpdate();

                        idHash.add(gbifID);
                    }
                }
            }

            idHash.clear();

            for (Object[] r : group) {
                recycleRow(r);
            }
            group.clear();

            if (gStmt1 == gStmt1b) {
                continue;
            }

            gStmt1 = gStmt1b;
            gStmt1.setString(1, year);
            gStmt1.setString(2, genus);

            gRS = gStmt1.executeQuery();
            while (gRS.next()) {
                fillRowWithScore(cmpRow, gRS);

                int gbifID = gRS.getInt(1);
                if (gbifID == srcId)
                    continue;

                int score = score(refRow, cmpRow);

                if (score > thresholdScore) {
                    writeCnt++;
                    gsStmt.setInt(1, 1); // reltype
                    gsStmt.setInt(2, score); // score
                    gsStmt.setInt(3, gbifID);
                    gsStmt.setInt(4, srcId);
                    gsStmt.executeUpdate();
                }
            }
            gRS.close();

            procRecs++;
            if (procRecs % 500 == 0) {
                long endTime = System.currentTimeMillis();
                long elapsedTime = endTime - startTime;

                double timePerRecord = (elapsedTime / procRecs);

                double hrsLeft = ((totalRecs - procRecs) * timePerRecord) / HRS;

                int seconds = (int) (elapsedTime / 60000.0);
                if (secsThreshold != seconds) {
                    secsThreshold = seconds;

                    String msg = String.format("Elapsed %8.2f hr.mn   Percent: %6.3f  Hours Left: %8.2f ",
                            ((double) (elapsedTime)) / HRS, 100.0 * ((double) procRecs / (double) totalRecs),
                            hrsLeft);
                    System.out.println(msg);
                    pw.println(msg);
                    pw.flush();
                }
            }
        }
        rs.close();

        System.out.println("Done.");
        pw.println("Done.");

    } catch (Exception ex) {
        ex.printStackTrace();
    } finally {
        try {
            if (stmt != null) {
                stmt.close();
            }
            if (gStmt1a != null) {
                gStmt1a.close();
            }
            if (gStmt1b != null) {
                gStmt1b.close();
            }
            /*if (gStmt2 != null)
            {
            gStmt2.close();
            }*/
        } catch (Exception ex) {

        }
    }
    System.out.println("Done.");
    pw.println("Done.");
    pw.flush();
    pw.close();
}

From source file:edu.ku.brc.specify.plugins.ipadexporter.VerifyCollectionDlg.java

/**
 * /*from w ww  .  ja va  2 s. c  o m*/
 */
private void processResults() {
    //loadAndPushResourceBundle("stats");

    RelationshipType paleoRelType = isCollectionPaleo ? ExportPaleo.discoverPaleRelationshipType()
            : RelationshipType.eTypeError;

    boolean hasCritical = false;

    UIRegistry.setDoShowAllResStrErors(false);
    //logMsg("Verifying the Collection...");

    File tmpFile = ipadExporter.getConfigFile(VERIFY_XML);
    if (tmpFile != null && tmpFile.exists()) {
        Statement stmt0 = null;
        try {
            Element root = XMLHelper.readFileToDOM4J(tmpFile);
            if (root != null) {
                ArrayList<String> okMsgs = new ArrayList<String>();
                ArrayList<String> warnMsgs = new ArrayList<String>();
                ArrayList<String> criticalMsgs = new ArrayList<String>();
                int issueCnt = 0;

                String mainFont = "<font face='verdana' color='black'>";
                String headHTML = "<htmL><head></head><body bgcolor='#EEEEEE'>" + mainFont;
                StringBuilder sb = new StringBuilder(headHTML);
                htmlPane.setText(sb.toString() + "<BR><BR>Verifying collection...</font></body></html>");

                List<?> items = root.selectNodes("eval"); //$NON-NLS-1$

                stmt0 = DBConnection.getInstance().getConnection()
                        .createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                stmt0.setFetchSize(Integer.MIN_VALUE);

                for (Iterator<?> capIter = items.iterator(); capIter.hasNext();) {
                    Element fieldNode = (Element) capIter.next();
                    //String  name      = fieldNode.attributeValue("name"); //$NON-NLS-1$
                    String desc = fieldNode.attributeValue("desc"); //$NON-NLS-1$
                    String sql = fieldNode.getTextTrim();
                    String cond = fieldNode.attributeValue("cond");
                    String vStr = fieldNode.attributeValue("val");
                    String isFmt = fieldNode.attributeValue("fmt");
                    String stop = fieldNode.attributeValue("stop");
                    boolean doStop = stop != null && stop.equals("true");

                    String display = fieldNode.attributeValue("display");
                    boolean doDsp = display == null || display.equals("true");

                    String paleo = fieldNode.attributeValue("isPaleo");
                    boolean isPaleo = paleo != null && paleo.equals("true");
                    if (isPaleo && !isCollectionPaleo)
                        continue;

                    String paleoTypeStr = fieldNode.attributeValue("paleotype"); //$NON-NLS-1$
                    if (isCollectionPaleo && StringUtils.isNotEmpty(paleoTypeStr)) {
                        if (paleoRelType != paleoLookupHash.get(paleoTypeStr)) {
                            continue;
                        }
                    }

                    sql = ipadExporter.adjustSQL(sql);

                    Object rv = BasicSQLUtils.querySingleObj(sql);
                    Integer retVal = cnvToInt(rv);

                    boolean isError = false;
                    if (retVal != null && StringUtils.isNotEmpty(cond) && StringUtils.isNotEmpty(vStr)) {
                        Integer value = cnvToInt(vStr);
                        if (value != null) {
                            if (cond.equals(">")) {
                                isError = retVal.intValue() > value.intValue();
                            } else if (cond.equals("=")) {
                                isError = retVal.intValue() == value.intValue();
                            } else if (cond.equals("<")) {
                                isError = retVal.intValue() < value.intValue();
                            }
                        }
                    }
                    /*
                                            String fontSt = isError ? "<font color='"+(doStop ? "red" : "orange")+"'>" : "";
                                            String fontEn = isError ? "</font>" : "";
                                            if (StringUtils.isNotEmpty(isFmt) && isFmt.equalsIgnoreCase("true"))
                                            {
                                              sb.append(String.format("<LI>%s%s%s</LI>", fontSt, String.format(desc,  retVal), fontEn));
                                              issueCnt++;
                                            } else
                                            {
                                               sb.append(String.format("<LI>%s%s%s</LI>", fontSt, desc, fontEn));
                                               issueCnt++;
                                            }                        
                     */
                    String fullMsg;
                    if (StringUtils.isNotEmpty(isFmt) && isFmt.equalsIgnoreCase("true")) {
                        fullMsg = String.format(desc, retVal);
                    } else {
                        fullMsg = desc;
                    }

                    if (isError) {
                        if (doStop) {
                            criticalMsgs.add(fullMsg);
                            hasCritical = true;
                        } else {
                            warnMsgs.add(fullMsg);
                        }

                    } else if (doDsp) {
                        okMsgs.add(fullMsg);
                    }

                    issueCnt++;
                    //worker.firePropertyChange(PROGRESS, 0, cnt);
                }
                stmt0.close();

                sb = new StringBuilder(headHTML);
                if (issueCnt == 0) {
                    sb.append("<BR><BR>There were no issues to report.");
                } else {
                    listMsgs(sb, "Passed", okMsgs, "green", true);
                    listMsgs(sb, "Warnings", warnMsgs, "yellow", true);
                    listMsgs(sb, "Critical Errors - Cannot proceed.", criticalMsgs, "red", true);
                }
                sb.append(mainFont + "<BR>Verification Complete.<BR><BR></font></body></html>");

                htmlPane.setText(sb.toString());

                // For external report
                sb = new StringBuilder("<htmL><head><title>Collection Verification</title></head><body>");
                listMsgs(sb, "Passed", okMsgs, "green", false);
                listMsgs(sb, "Warnings", warnMsgs, "yellow", false);
                listMsgs(sb, "Critical Errors - Cannot proceed.", criticalMsgs, "red", false);
                sb.append("</body></html>");
                try {
                    TableWriter tblWriter = new TableWriter(reportPath, "Collection Verification Report", true);
                    tblWriter.println(sb.toString());
                    tblWriter.close();

                } catch (FileNotFoundException e) {
                    e.printStackTrace();
                } catch (UnsupportedEncodingException e) {
                    e.printStackTrace();
                }
            }

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

        } finally {
            try {
                if (stmt0 != null)
                    stmt0.close();
            } catch (Exception ex) {
            }
        }
    }
    okBtn.setEnabled(!hasCritical);
}