List of usage examples for java.sql SQLException getNextException
public SQLException getNextException()
SQLException object by setNextException(SQLException ex). From source file:pl.umk.mat.zawodyweb.www.RequestBean.java
@HttpAction(name = "delseries", pattern = "del/{id}/series") public String deleteSeries(@Param(name = "id", encode = true) int id) { Series s = seriesDAO.getById(id);/* w w w .j a v a 2s . co m*/ if (s != null && rolesBean.canDeleteSeries(s.getContests().getId(), id)) { try { seriesDAO.delete(s); HibernateUtil.getSessionFactory().getCurrentSession().getTransaction().commit(); HibernateUtil.getSessionFactory().getCurrentSession().beginTransaction(); } catch (JDBCException e) { e.printStackTrace(); SQLException ex = e.getSQLException(); while ((ex = ex.getNextException()) != null) { ex.printStackTrace(); } } return "problems"; } else { return "/error/404"; } }
From source file:org.apache.hive.beeline.BeeLine.java
void handleSQLException(SQLException e) { if (e instanceof SQLWarning && !(getOpts().getShowWarnings())) { return;/*from w ww .j av a2 s. com*/ } if (e.getCause() instanceof TTransportException) { switch (((TTransportException) e.getCause()).getType()) { case TTransportException.ALREADY_OPEN: error(loc("hs2-connection-already-open")); break; case TTransportException.END_OF_FILE: error(loc("hs2-unexpected-end-of-file")); break; case TTransportException.NOT_OPEN: error(loc("hs2-could-not-open-connection")); break; case TTransportException.TIMED_OUT: error(loc("hs2-connection-timed-out")); break; case TTransportException.UNKNOWN: error(loc("hs2-unknown-connection-problem")); break; default: error(loc("hs2-unexpected-error")); } } error(loc(e instanceof SQLWarning ? "Warning" : "Error", new Object[] { e.getMessage() == null ? "" : e.getMessage().trim(), e.getSQLState() == null ? "" : e.getSQLState().trim(), new Integer(e.getErrorCode()) })); if (getOpts().getVerbose()) { e.printStackTrace(getErrorStream()); } if (!getOpts().getShowNestedErrs()) { return; } for (SQLException nested = e.getNextException(); nested != null && nested != e; nested = nested.getNextException()) { handleSQLException(nested); } }
From source file:org.pentaho.di.core.database.Database.java
public static KettleDatabaseBatchException createKettleDatabaseBatchException(String message, SQLException ex) { KettleDatabaseBatchException kdbe = new KettleDatabaseBatchException(message, ex); if (ex instanceof BatchUpdateException) { kdbe.setUpdateCounts(((BatchUpdateException) ex).getUpdateCounts()); } else {/*from www. j ava 2s . c o m*/ // Null update count forces rollback of batch kdbe.setUpdateCounts(null); } List<Exception> exceptions = new ArrayList<Exception>(); SQLException nextException = ex.getNextException(); SQLException oldException = null; // This construction is specifically done for some JDBC drivers, these // drivers // always return the same exception on getNextException() (and thus go // into an infinite loop). // So it's not "equals" but != (comments from Sven Boden). while ((nextException != null) && (oldException != nextException)) { exceptions.add(nextException); oldException = nextException; nextException = nextException.getNextException(); } kdbe.setExceptionsList(exceptions); return kdbe; }
From source file:org.seasar.dbflute.logic.sql2entity.cmentity.DfProcedureExecutionMetaExtractor.java
protected void doExtractExecutionMetaData(DataSource dataSource, DfProcedureMeta procedure) throws SQLException { final List<DfProcedureColumnMeta> columnList = procedure.getProcedureColumnList(); if (!needsToCall(columnList)) { final String name = procedure.buildProcedureLoggingName(); _log.info("*not needed to call: " + name + " params=" + buildParameterTypeView(columnList)); return;//from www. j a v a2s .c o m } final List<Object> testValueList = DfCollectionUtil.newArrayList(); setupTestValueList(columnList, testValueList); final boolean existsReturn = existsReturnValue(columnList); final String sql = createSql(procedure, existsReturn, true); Connection conn = null; CallableStatement cs = null; try { _log.info("...Calling: " + sql); conn = dataSource.getConnection(); conn.setAutoCommit(false); cs = conn.prepareCall(sql); final List<DfProcedureColumnMeta> boundColumnList = DfCollectionUtil.newArrayList(); setupBindParameter(conn, cs, columnList, testValueList, boundColumnList); boolean executed; try { executed = cs.execute(); } catch (SQLException e) { // retry without escape because Oracle sometimes hates escape final String retrySql = createSql(procedure, existsReturn, false); try { try { cs.close(); } catch (SQLException ignored) { } cs = conn.prepareCall(retrySql); setupBindParameter(conn, cs, columnList, testValueList, boundColumnList); executed = cs.execute(); _log.info(" (o) retry: " + retrySql); } catch (SQLException ignored) { _log.info(" (x) retry: " + retrySql); throw e; } } if (executed) { int closetIndex = 0; do { ResultSet rs = null; try { rs = cs.getResultSet(); if (rs == null) { break; } final Map<String, DfColumnMeta> columnMetaInfoMap = extractColumnMetaInfoMap(rs, sql); final DfProcedureNotParamResultMeta notParamResult = new DfProcedureNotParamResultMeta(); final String propertyName; if (procedure.isCalledBySelect() && closetIndex == 0) { // for example, table valued function // if the procedure of this type does not have // second or more result set basically // but checks closetIndex just in case propertyName = "returnResult"; } else { // basically here propertyName = "notParamResult" + (closetIndex + 1); } notParamResult.setPropertyName(propertyName); notParamResult.setResultSetColumnInfoMap(columnMetaInfoMap); procedure.addNotParamResult(notParamResult); ++closetIndex; } finally { closeResult(rs); } } while (cs.getMoreResults()); } int index = 0; for (DfProcedureColumnMeta column : boundColumnList) { final DfProcedureColumnType columnType = column.getProcedureColumnType(); if (DfProcedureColumnType.procedureColumnIn.equals(columnType)) { ++index; continue; } final int paramIndex = (index + 1); final Object obj; if (column.isPostgreSQLCursor()) { obj = _postgreSqlResultSetType.getValue(cs, paramIndex); } else if (column.isOracleCursor()) { obj = _oracleResultSetType.getValue(cs, paramIndex); } else { obj = cs.getObject(paramIndex); // as default } if (obj instanceof ResultSet) { ResultSet rs = null; try { rs = (ResultSet) obj; final Map<String, DfColumnMeta> columnMetaInfoMap = extractColumnMetaInfoMap(rs, sql); column.setResultSetColumnInfoMap(columnMetaInfoMap); } finally { closeResult(rs); } } ++index; } } catch (SQLException e) { final ExceptionMessageBuilder br = new ExceptionMessageBuilder(); br.addNotice("Failed to execute the procedure for getting meta data."); br.addItem("SQL"); br.addElement(sql); br.addItem("Parameter"); for (DfProcedureColumnMeta column : columnList) { br.addElement(column.getColumnDisplayName()); } br.addItem("Test Value"); br.addElement(buildTestValueDisp(testValueList)); br.addItem("Exception Message"); br.addElement(DfJDBCException.extractMessage(e)); SQLException nextEx = e.getNextException(); if (nextEx != null) { br.addElement(DfJDBCException.extractMessage(nextEx)); } final String msg = br.buildExceptionMessage(); final DfOutsideSqlProperties prop = getProperties().getOutsideSqlProperties(); if (prop.hasSpecifiedExecutionMetaProcedure()) { throw new DfProcedureExecutionMetaGettingFailureException(msg, e); } else { // if no specified, it continues _continuedFailureMessageMap.put(procedure.getProcedureFullQualifiedName(), msg); _log.info(msg); } } finally { if (cs != null) { cs.close(); } if (conn != null) { conn.rollback(); } } }
From source file:org.pentaho.di.core.database.Database.java
public void printSQLException(SQLException ex) { log.logError("==> SQLException: "); while (ex != null) { log.logError("Message: " + ex.getMessage()); log.logError("SQLState: " + ex.getSQLState()); log.logError("ErrorCode: " + ex.getErrorCode()); ex = ex.getNextException(); log.logError(""); }/*from ww w . j ava2 s. com*/ }
From source file:org.ow2.bonita.persistence.db.DbSessionImpl.java
private String getSqlState(final SQLException e) { String sqlState = e.getSQLState(); if (sqlState == null) { if (e.getCause() != null) { final SQLException nextException = e.getNextException(); if (nextException != null) { sqlState = nextException.getSQLState(); }//from w w w. j a v a 2 s . c om } } return sqlState; }
From source file:us.daveread.basicquery.BasicQuery.java
/** * Populates model with the query results. The query executed is the * currently selected query in the combo-box. * /*from ww w . j a v a 2s .c om*/ * @param rawSqlStatement * The SQL statement to execute * @param model * The model to populate with the results * @param tripleFile * The location to write the results to as triples. */ private void execute(String rawSqlStatement, ListTableModel<Object> model, File tripleFile) { String sqlStatement = rawSqlStatement; Statement stmt = null; ResultSet result = null; ResultSetMetaData meta = null; List<Object> rowData = null; int retValue = 0; SQLWarning warning = null; int[] myType; Object value; String typeName; String colName; String metaName; boolean hasResults = false; boolean hasBLOB = false; Date connAsk = null; Date connGot = null; Date stmtGot = null; Date queryStart = null; Date queryReady = null; Date queryRSFetched = null; Date queryRSProcessed = null; long rows = 0; int cols = 0; boolean hasParams = false; final List<StatementParameter> allParams = new ArrayList<StatementParameter>(); List<Object> outParams = null; modeOfCurrentTable = whichModeValue(); mapOfCurrentTables = new HashMap<String, String>(); // Try to prevent incorrect selection of query type by checking // beginning of SQL statement for obvious stuff // First check "Select" and Describe query types if (!isOkayQueryType(getQuery().getSql())) { // If the query type is wrong, and the user doesn't override then // Get Out Of Here! return; } // If there were BLOB columns included in the last query the connection // will have been left open. Since we are executing a new query we // can close that old connection now. if (conn != null) { try { conn.close(); } catch (Throwable any) { if (LOGGER.isDebugEnabled()) { LOGGER.debug("Error (expected) closing connection", any); } } } conn = null; try { messageOut(Resources.getString("msgExecuteQuery", asQuery.isSelected() ? Resources.getString("msgQuery") : asDescribe.isSelected() ? Resources.getString("msgDescribe") : Resources.getString("msgUpdate"), sqlStatement), STYLE_BOLD); if (poolConnect.isSelected()) { messageOut(Resources.getString("msgPoolStats") + " ", STYLE_SUBTLE, false); if (getDBPool() != null) { messageOut(Resources.getString("msgPoolStatsCount", getDBPool().getNumActive() + "", getDBPool().getNumIdle() + "")); LOGGER.debug("Retrieved existing DB connection pool"); } else { LOGGER.debug("No existing DB pool"); messageOut(Resources.getString("msgPoolNone")); } } if (getDBPool() == null || /* conn == null */ !((String) connectString.getEditor().getItem()).equals(lastConnection) || !userId.getText().equals(lastUserId) || !new String(password.getPassword()).equals(lastPassword)) { removeDBPool(); lastConnection = (String) connectString.getEditor().getItem(); lastUserId = userId.getText(); lastPassword = new String(password.getPassword()); if (poolConnect.isSelected()) { setupDBPool(lastConnection, lastUserId, lastPassword); } messageOut(Resources.getString("msgConnCreated", lastConnection, lastUserId), STYLE_SUBTLE); } connAsk = new java.util.Date(); if (poolConnect.isSelected()) { conn = DriverManager.getConnection("jdbc:apache:commons:dbcp:" + DBPOOL_NAME); LOGGER.debug("Got pooled connection"); messageOut(Resources.getString("msgGotPoolConn"), STYLE_GREEN); } else { conn = DriverManager.getConnection(lastConnection, lastUserId, lastPassword); LOGGER.debug("Got non-pooled connection"); messageOut(Resources.getString("msgGotDirectConn"), STYLE_GREEN); } if (hasParams = sqlStatement.indexOf("$PARAM[") > -1) { sqlStatement = makeParams(sqlStatement, allParams); } connGot = new java.util.Date(); conn.setAutoCommit(autoCommit.isSelected()); conn.setReadOnly(readOnly.isSelected()); if (!hasParams) { stmt = conn.createStatement(); } else { stmt = conn.prepareCall(sqlStatement); setupCall((CallableStatement) stmt, allParams); } stmtGot = new java.util.Date(); try { if (!maxRows.getSelectedItem().equals(Resources.getString("proNoLimit"))) { stmt.setMaxRows(Integer.parseInt((String) maxRows.getSelectedItem())); messageOut("\n" + Resources.getString("msgMaxRows", stmt.getMaxRows() + ""), STYLE_SUBTLE); } } catch (Exception any) { LOGGER.warn("Unable to set maximum rows", any); messageOut(Resources.getString("errFailSetMaxRows", (String) maxRows.getSelectedItem(), any.getMessage()), STYLE_YELLOW); } if (asQuery.isSelected() || asDescribe.isSelected()) { queryStart = new java.util.Date(); if (!hasParams) { int updateCount; // Execute the query synchronously stmt.execute(sqlStatement); messageOut(Resources.getString("msgQueryExecutedByDB"), STYLE_GREEN); // Process the query results and/or report status if ((updateCount = stmt.getUpdateCount()) > -1) { do { LOGGER.debug("Looking for results [update=" + updateCount + "]"); stmt.getMoreResults(); } while ((updateCount = stmt.getUpdateCount()) > -1); } result = stmt.getResultSet(); } else { result = ((PreparedStatement) stmt).executeQuery(); } queryReady = new java.util.Date(); meta = result.getMetaData(); cols = meta.getColumnCount(); } else { queryStart = new java.util.Date(); if (!hasParams) { retValue = stmt.executeUpdate(sqlStatement); } else { retValue = ((PreparedStatement) stmt).executeUpdate(); } queryReady = new java.util.Date(); } if (asQuery.isSelected()) { for (int col = 0; col < cols; ++col) { colName = meta.getColumnName(col + 1); if (colName == null || colName.trim().length() == 0) { colName = Resources.getString("msgUnnamedColumn", meta.getColumnLabel(col + 1)); } if (configDisplayColumnDataType.isSelected()) { metaName = meta.getColumnTypeName(col + 1) + " " + meta.getColumnDisplaySize(col + 1) + " ("; // have had oracle tables report large precision values // for BLOB fields that caused exception to be thrown // by getPrecision() since the value was beyond int try { metaName += meta.getPrecision(col + 1); } catch (Exception any) { metaName += "?"; LOGGER.warn("Unable to get column precision", any); } metaName += "."; metaName += meta.getScale(col + 1); metaName += ")"; colName += " [" + metaName + "]"; } model.addColumn(colName); // Keep collection of tables used for Insert and Update Menu // Selections try { mapOfCurrentTables.put(meta.getTableName(col + 1), null); } catch (Exception any) { // Probably unimplemented method - Sybase driver LOGGER.warn("Failed to obtain table name from metadata", any); messageOut(Resources.getString("errFailReqTableName", any.getMessage()), STYLE_SUBTLE); } } rowData = new ArrayList<Object>(); myType = new int[cols]; for (int col = 0; col < cols; ++col) { typeName = meta.getColumnTypeName(col + 1).toUpperCase(); if (typeName.equals("NUMBER")) { if (meta.getScale(col + 1) > 0) { myType[col] = COLUMN_DATA_TYPE_DOUBLE; // DOUBLE } else if (meta.getPrecision(col + 1) <= MAX_DIGITS_FOR_INT) { myType[col] = COLUMN_DATA_TYPE_INT; // INTEGER } else { myType[col] = COLUMN_DATA_TYPE_LONG; // LONG } } else if (typeName.equals("LONG")) { myType[col] = COLUMN_DATA_TYPE_LONG; } else if (typeName.equals("DATETIME")) { myType[col] = COLUMN_DATA_TYPE_DATETIME; // Date/Time } else if (typeName.equals("DATE")) { myType[col] = COLUMN_DATA_TYPE_DATE; // Date/Time } else if (typeName.equals("BLOB")) { myType[col] = COLUMN_DATA_TYPE_BLOB; hasBLOB = true; } else { myType[col] = 0; // Default - String } } if (tripleFile != null) { try { final RdbToRdf exporter = new RdbToRdf(tripleFile.getAbsolutePath(), getQuery().getSql(), result); exporter.run(); rows = exporter.getLatestNumberOfRowsExported(); messageOut(""); messageOut(Resources.getString("msgEndExportToFile"), STYLE_BOLD); } catch (Throwable throwable) { messageOut(Resources.getString("errFailDataSave", throwable.toString()), STYLE_RED); LOGGER.error("Failed to save data to triples file: " + tripleFile.getAbsolutePath(), throwable); } } else if (fileLogResults.isSelected()) { writeDataAsCSV(sqlStatement, model, DBRESULTS_NAME, result, myType, false); } else { while (result.next()) { ++rows; rowData = new ArrayList<Object>(); for (int col = 0; col < cols; ++col) { value = getResultField(result, col + 1, myType[col]); rowData.add(value); } model.addRowFast(rowData); hasResults = true; } model.updateCompleted(); } queryRSProcessed = new java.util.Date(); } else if (asDescribe.isSelected()) { String colLabel; meta = result.getMetaData(); myType = new int[DESC_TABLE_COLUMN_COUNT]; for (int col = 0; col < DESC_TABLE_COLUMN_COUNT; ++col) { switch (col) { case DESC_TABLE_NAME_COLUMN: // Col Name colLabel = Resources.getString("proColumnName"); myType[col] = COLUMN_DATA_TYPE_STRING; break; case DESC_TABLE_TYPE_COLUMN: // Col Type colLabel = Resources.getString("proColumnType"); myType[col] = COLUMN_DATA_TYPE_STRING; break; case DESC_TABLE_LENGTH_COLUMN: // Col Length colLabel = Resources.getString("proColumnLength"); myType[col] = COLUMN_DATA_TYPE_INT; break; case DESC_TABLE_PRECISION_COLUMN: // Col precision colLabel = Resources.getString("proColPrecision"); myType[col] = COLUMN_DATA_TYPE_INT; break; case DESC_TABLE_SCALE_COLUMN: // Col scale colLabel = Resources.getString("proColScale"); myType[col] = COLUMN_DATA_TYPE_INT; break; case DESC_TABLE_NULLS_OK_COLUMN: // Nulls Okay? colLabel = Resources.getString("proColNullsAllowed"); myType[col] = COLUMN_DATA_TYPE_STRING; break; default: // oops colLabel = Resources.getString("proColUndefined"); break; } if (configDisplayColumnDataType.isSelected()) { colLabel += " ["; colLabel += myType[col] == 0 ? Resources.getString("proColCharType") : Resources.getString("proColNumeric"); colLabel += "]"; } model.addColumn(colLabel); } rowData = new ArrayList<Object>(); for (int col = 0; col < cols; ++col) { rowData = new ArrayList<Object>(); for (int row = 0; row < DESC_TABLE_COLUMN_COUNT; ++row) { switch (row) { case DESC_TABLE_NAME_COLUMN: // Name colName = meta.getColumnName(col + 1); if (colName == null || colName.trim().length() == 0) { colName = Resources.getString("msgUnnamedColumn", meta.getColumnLabel(col + 1)); } value = colName; break; case DESC_TABLE_TYPE_COLUMN: // Type value = meta.getColumnTypeName(col + 1) + " (" + meta.getColumnType(col + 1) + ")"; break; case DESC_TABLE_LENGTH_COLUMN: // Length value = new Integer(meta.getColumnDisplaySize(col + 1)); break; case DESC_TABLE_PRECISION_COLUMN: // Precision try { value = new Integer(meta.getPrecision(col + 1)); } catch (Exception any) { value = "?"; LOGGER.warn("Unable to obtain column precision", any); } break; case DESC_TABLE_SCALE_COLUMN: // Scale value = new Integer(meta.getScale(col + 1)); break; case DESC_TABLE_NULLS_OK_COLUMN: // Nulls Okay? value = meta.isNullable(col + 1) == ResultSetMetaData.columnNullable ? Resources.getString("proYes") : meta.isNullable(col + 1) == ResultSetMetaData.columnNoNulls ? Resources.getString("proNo") : Resources.getString("proUnknown"); break; default: value = null; break; } rowData.add(value); // Keep collection of tables used for Insert and Update Menu // Selections try { mapOfCurrentTables.put(meta.getTableName(col + 1), null); } catch (Exception any) { // Probably unimplemented method - Sybase driver LOGGER.warn("Failed to obtain table name from metadata", any); messageOut(Resources.getString("errFailReqTableName", any.getMessage()), STYLE_SUBTLE); } } model.addRow(rowData); } while (result.next()) { rows++; for (int col = 0; col < cols; ++col) { result.getObject(col + 1); } } queryRSFetched = new java.util.Date(); } else { messageOut("\n" + Resources.getString("msgReturnValue") + " " + retValue, STYLE_BOLD, false); rows = stmt.getUpdateCount(); } messageOut("\n" + Resources.getString("msgRows") + " ", STYLE_NORMAL, false); if (rows == stmt.getMaxRows() && rows > 0) { messageOut("" + rows, STYLE_YELLOW); } else { messageOut("" + rows, STYLE_BOLD); } messageOut(""); } catch (SQLException sql) { LOGGER.error("Error executing SQL", sql); messageOut(Resources.getString("errFailSQL", sql.getClass().getName(), sql.getMessage()), STYLE_RED); userMessage(Resources.getString("errFailSQLText", sql.getMessage()), Resources.getString("errFailSQLTitle"), JOptionPane.ERROR_MESSAGE); while ((sql = sql.getNextException()) != null) { LOGGER.error("Next Exception", sql); } modeOfCurrentTable = -1; } catch (Throwable any) { LOGGER.error("Error executing SQL", any); messageOut(Resources.getString("errFailSQL", any.getClass().getName(), any.getMessage()), STYLE_RED); userMessage(Resources.getString("errFailSQLText", any.getMessage()), Resources.getString("errFailSQLTitle"), JOptionPane.ERROR_MESSAGE); modeOfCurrentTable = -1; } finally { fileSaveBLOBs.setEnabled(hasBLOB); setExportAvailable((hasResults && model.getRowCount() > 0) || tripleFile != null); queryMakeInsert.setEnabled( modeOfCurrentTable == Query.MODE_DESCRIBE || modeOfCurrentTable == Query.MODE_QUERY); if (hasParams) { outParams = getOutParams((CallableStatement) stmt, allParams); } LOGGER.debug("Check for more results"); try { int resultCount = 0; while (stmt.getMoreResults()) { int updateCount; ++resultCount; updateCount = stmt.getUpdateCount(); LOGGER.debug("More results [" + resultCount + "][updateCount=" + updateCount + "]"); } } catch (SQLException sql) { LOGGER.error("Failed checking for more results", sql); messageOut(Resources.getString("errFailAddlResults", sql.getClass().getName(), sql.getMessage())); } LOGGER.debug("No more results"); if (result != null) { try { result.close(); LOGGER.info("Resultset closed"); } catch (Throwable any) { LOGGER.error("Unable to close resultset", any); } } if (stmt != null) { try { warning = stmt.getWarnings(); while (warning != null) { LOGGER.warn("Stmt Warning: " + warning.toString()); messageOut(Resources.getString("errStmtWarning", warning.toString()), STYLE_YELLOW); warning = warning.getNextWarning(); } } catch (Throwable any) { LOGGER.warn("Error retrieving statement SQL warnings", any); } try { stmt.close(); LOGGER.debug("Statement closed"); } catch (Throwable any) { LOGGER.error("Unable to close statement", any); } } if (conn != null) { try { warning = conn.getWarnings(); while (warning != null) { LOGGER.warn("Connt Warning: " + warning.toString()); messageOut(Resources.getString("errConnWarning", warning.toString()), STYLE_YELLOW); warning = warning.getNextWarning(); } } catch (Throwable any) { LOGGER.warn("Error retrieving connection SQL warnings", any); } } // Close the connection if there are no BLOBs. // If the user decides to save a BLOB we will need to DB connection // to remain open, hence we only close here if there are no BLOBs if (!hasBLOB && conn != null) { try { conn.close(); conn = null; LOGGER.debug("DB Connection closed"); } catch (Throwable any) { LOGGER.error("Unable to close DB connection", any); } } reportStats(sqlStatement, connAsk, connGot, stmtGot, queryStart, queryReady, queryRSFetched, queryRSProcessed, rows, cols, asDescribe.isSelected() ? model : null, outParams); // reportResults(SQL, model); } }
From source file:io.bibleget.BibleGetDB.java
public boolean initialize() { try {//from w w w . j av a 2 s. co m instance.conn = DriverManager.getConnection("jdbc:derby:BIBLEGET;create=true", "bibleget", "bibleget"); if (instance.conn == null) { System.out.println("Careful there! Connection not established! BibleGetDB.java line 81"); } else { System.out.println("conn is not null, which means a connection was correctly established."); } DatabaseMetaData dbMeta; dbMeta = instance.conn.getMetaData(); try (ResultSet rs1 = dbMeta.getTables(null, null, "OPTIONS", null)) { if (rs1.next()) { //System.out.println("Table "+rs1.getString("TABLE_NAME")+" already exists !!"); listColNamesTypes(dbMeta, rs1); } else { //System.out.println("Table OPTIONS does not yet exist, now attempting to create..."); try (Statement stmt = instance.conn.createStatement()) { String defaultFont = ""; if (SystemUtils.IS_OS_WINDOWS) { defaultFont = "Times New Roman"; } else if (SystemUtils.IS_OS_MAC_OSX) { defaultFont = "Helvetica"; } else if (SystemUtils.IS_OS_LINUX) { defaultFont = "Arial"; } String tableCreate = "CREATE TABLE OPTIONS (" + "PARAGRAPHALIGNMENT VARCHAR(15), " + "PARAGRAPHLINESPACING INT, " + "PARAGRAPHFONTFAMILY VARCHAR(50), " + "PARAGRAPHLEFTINDENT INT, " + "TEXTCOLORBOOKCHAPTER VARCHAR(15), " + "BGCOLORBOOKCHAPTER VARCHAR(15), " + "BOLDBOOKCHAPTER BOOLEAN, " + "ITALICSBOOKCHAPTER BOOLEAN, " + "UNDERSCOREBOOKCHAPTER BOOLEAN, " + "FONTSIZEBOOKCHAPTER INT, " + "VALIGNBOOKCHAPTER VARCHAR(15), " + "TEXTCOLORVERSENUMBER VARCHAR(15), " + "BGCOLORVERSENUMBER VARCHAR(15), " + "BOLDVERSENUMBER BOOLEAN, " + "ITALICSVERSENUMBER BOOLEAN, " + "UNDERSCOREVERSENUMBER BOOLEAN, " + "FONTSIZEVERSENUMBER INT, " + "VALIGNVERSENUMBER VARCHAR(15), " + "TEXTCOLORVERSETEXT VARCHAR(15), " + "BGCOLORVERSETEXT VARCHAR(15), " + "BOLDVERSETEXT BOOLEAN, " + "ITALICSVERSETEXT BOOLEAN, " + "UNDERSCOREVERSETEXT BOOLEAN, " + "FONTSIZEVERSETEXT INT, " + "VALIGNVERSETEXT VARCHAR(15), " + "PREFERREDVERSIONS VARCHAR(50), " + "NOVERSIONFORMATTING BOOLEAN" + ")"; String tableInsert; tableInsert = "INSERT INTO OPTIONS (" + "PARAGRAPHALIGNMENT," + "PARAGRAPHLINESPACING," + "PARAGRAPHFONTFAMILY," + "PARAGRAPHLEFTINDENT," + "TEXTCOLORBOOKCHAPTER," + "BGCOLORBOOKCHAPTER," + "BOLDBOOKCHAPTER," + "ITALICSBOOKCHAPTER," + "UNDERSCOREBOOKCHAPTER," + "FONTSIZEBOOKCHAPTER," + "VALIGNBOOKCHAPTER," + "TEXTCOLORVERSENUMBER," + "BGCOLORVERSENUMBER," + "BOLDVERSENUMBER," + "ITALICSVERSENUMBER," + "UNDERSCOREVERSENUMBER," + "FONTSIZEVERSENUMBER," + "VALIGNVERSENUMBER," + "TEXTCOLORVERSETEXT," + "BGCOLORVERSETEXT," + "BOLDVERSETEXT," + "ITALICSVERSETEXT," + "UNDERSCOREVERSETEXT," + "FONTSIZEVERSETEXT," + "VALIGNVERSETEXT," + "PREFERREDVERSIONS, " + "NOVERSIONFORMATTING" + ") VALUES (" + "'justify',100,'" + defaultFont + "',0," + "'#0000FF','#FFFFFF',true,false,false,14,'initial'," + "'#AA0000','#FFFFFF',false,false,false,10,'super'," + "'#696969','#FFFFFF',false,false,false,12,'initial'," + "'NVBSE'," + "false" + ")"; boolean tableCreated = stmt.execute(tableCreate); boolean rowsInserted; int count; if (tableCreated == false) { //is false when it's an update count! count = stmt.getUpdateCount(); if (count == -1) { //System.out.println("The result is a ResultSet object or there are no more results."); } else { //this is our expected behaviour: 0 rows affected //System.out.println("The Table Creation statement produced results: "+count+" rows affected."); try (Statement stmt2 = instance.conn.createStatement()) { rowsInserted = stmt2.execute(tableInsert); if (rowsInserted == false) { count = stmt2.getUpdateCount(); if (count == -1) { //System.out.println("The result is a ResultSet object or there are no more results."); } else { //this is our expected behaviour: n rows affected //System.out.println("The Row Insertion statement produced results: "+count+" rows affected."); dbMeta = instance.conn.getMetaData(); try (ResultSet rs2 = dbMeta.getTables(null, null, "OPTIONS", null)) { if (rs2.next()) { listColNamesTypes(dbMeta, rs2); } rs2.close(); } } } else { //is true when it returns a resultset, which shouldn't be the case here try (ResultSet rx = stmt2.getResultSet()) { while (rx.next()) { //System.out.println("This isn't going to happen anyways, so..."); } rx.close(); } } stmt2.close(); } } } else { //is true when it returns a resultset, which shouldn't be the case here try (ResultSet rx = stmt.getResultSet()) { while (rx.next()) { //System.out.println("This isn't going to happen anyways, so..."); } rx.close(); } } stmt.close(); } } rs1.close(); } //System.out.println("Finished with first ResultSet resource, now going on to next..."); try (ResultSet rs3 = dbMeta.getTables(null, null, "METADATA", null)) { if (rs3.next()) { //System.out.println("Table "+rs3.getString("TABLE_NAME")+" already exists !!"); } else { //System.out.println("Table METADATA does not exist, now attempting to create..."); try (Statement stmt = instance.conn.createStatement()) { String tableCreate = "CREATE TABLE METADATA ("; tableCreate += "ID INT, "; for (int i = 0; i < 73; i++) { tableCreate += "BIBLEBOOKS" + Integer.toString(i) + " VARCHAR(2000), "; } tableCreate += "LANGUAGES VARCHAR(500), "; tableCreate += "VERSIONS VARCHAR(2000)"; tableCreate += ")"; boolean tableCreated = stmt.execute(tableCreate); boolean rowsInserted; int count; if (tableCreated == false) { //this is the expected result, is false when it's an update count! count = stmt.getUpdateCount(); if (count == -1) { //System.out.println("The result is a ResultSet object or there are no more results."); } else { //this is our expected behaviour: 0 rows affected //System.out.println("The Table Creation statement produced results: "+count+" rows affected."); //Insert a dummy row, because you cannot update what has not been inserted! try (Statement stmtX = instance.conn.createStatement()) { stmtX.execute("INSERT INTO METADATA (ID) VALUES (0)"); stmtX.close(); } HTTPCaller myHTTPCaller = new HTTPCaller(); String myResponse; myResponse = myHTTPCaller.getMetaData("biblebooks"); if (myResponse != null) { JsonReader jsonReader = Json.createReader(new StringReader(myResponse)); JsonObject json = jsonReader.readObject(); JsonArray arrayJson = json.getJsonArray("results"); if (arrayJson != null) { ListIterator pIterator = arrayJson.listIterator(); while (pIterator.hasNext()) { try (Statement stmt2 = instance.conn.createStatement()) { int index = pIterator.nextIndex(); JsonArray currentJson = (JsonArray) pIterator.next(); String biblebooks_str = currentJson.toString(); //.replaceAll("\"", "\\\\\""); //System.out.println("BibleGetDB line 267: BIBLEBOOKS"+Integer.toString(index)+"='"+biblebooks_str+"'"); String stmt_str = "UPDATE METADATA SET BIBLEBOOKS" + Integer.toString(index) + "='" + biblebooks_str + "' WHERE ID=0"; try { //System.out.println("executing update: "+stmt_str); int update = stmt2.executeUpdate(stmt_str); //System.out.println("executeUpdate resulted in: "+Integer.toString(update)); } catch (SQLException ex) { Logger.getLogger(BibleGetDB.class.getName()).log(Level.SEVERE, null, ex); } stmt2.close(); } } } arrayJson = json.getJsonArray("languages"); if (arrayJson != null) { try (Statement stmt2 = instance.conn.createStatement()) { String languages_str = arrayJson.toString(); //.replaceAll("\"", "\\\\\""); String stmt_str = "UPDATE METADATA SET LANGUAGES='" + languages_str + "' WHERE ID=0"; try { int update = stmt2.executeUpdate(stmt_str); } catch (SQLException ex) { Logger.getLogger(BibleGetDB.class.getName()).log(Level.SEVERE, null, ex); } stmt2.close(); } } } myResponse = myHTTPCaller.getMetaData("bibleversions"); if (myResponse != null) { JsonReader jsonReader = Json.createReader(new StringReader(myResponse)); JsonObject json = jsonReader.readObject(); JsonObject objJson = json.getJsonObject("validversions_fullname"); if (objJson != null) { String bibleversions_str = objJson.toString(); //.replaceAll("\"", "\\\\\""); try (Statement stmt2 = instance.conn.createStatement()) { String stmt_str = "UPDATE METADATA SET VERSIONS='" + bibleversions_str + "' WHERE ID=0"; try { int update = stmt2.executeUpdate(stmt_str); } catch (SQLException ex) { Logger.getLogger(BibleGetDB.class.getName()).log(Level.SEVERE, null, ex); } stmt2.close(); } Set<String> versionsabbrev = objJson.keySet(); if (!versionsabbrev.isEmpty()) { String versionsabbrev_str = ""; for (String s : versionsabbrev) { versionsabbrev_str += ("".equals(versionsabbrev_str) ? "" : ",") + s; } myResponse = myHTTPCaller .getMetaData("versionindex&versions=" + versionsabbrev_str); if (myResponse != null) { jsonReader = Json.createReader(new StringReader(myResponse)); json = jsonReader.readObject(); objJson = json.getJsonObject("indexes"); if (objJson != null) { for (String name : objJson.keySet()) { JsonObjectBuilder tempBld = Json.createObjectBuilder(); JsonObject book_num = objJson.getJsonObject(name); tempBld.add("book_num", book_num.getJsonArray("book_num")); tempBld.add("chapter_limit", book_num.getJsonArray("chapter_limit")); tempBld.add("verse_limit", book_num.getJsonArray("verse_limit")); JsonObject temp = tempBld.build(); String versionindex_str = temp.toString(); //.replaceAll("\"", "\\\\\""); //add new column to METADATA table name+"IDX" VARCHAR(5000) //update METADATA table SET name+"IDX" = versionindex_str try (Statement stmt3 = instance.conn.createStatement()) { String sql = "ALTER TABLE METADATA ADD COLUMN " + name + "IDX VARCHAR(5000)"; boolean colAdded = stmt3.execute(sql); if (colAdded == false) { count = stmt3.getUpdateCount(); if (count == -1) { //System.out.println("The result is a ResultSet object or there are no more results."); } else if (count == 0) { //0 rows affected stmt3.close(); try (Statement stmt4 = instance.conn .createStatement()) { String sql1 = "UPDATE METADATA SET " + name + "IDX='" + versionindex_str + "' WHERE ID=0"; boolean rowsUpdated = stmt4.execute(sql1); if (rowsUpdated == false) { count = stmt4.getUpdateCount(); if (count == -1) { //System.out.println("The result is a ResultSet object or there are no more results."); } else { //should have affected only one row if (count == 1) { //System.out.println(sql1+" seems to have returned true"); stmt4.close(); } } } else { //returns true only when returning a resultset; should not be the case here } } } } else { //returns true only when returning a resultset; should not be the case here } stmt3.close(); } } } } } } } } } else { //is true when it returns a resultset, which shouldn't be the case here ResultSet rx = stmt.getResultSet(); while (rx.next()) { //System.out.println("This isn't going to happen anyways, so..."); } } stmt.close(); } } rs3.close(); } instance.conn.close(); return true; } catch (SQLException ex) { if (ex.getSQLState().equals("X0Y32")) { Logger.getLogger(BibleGetDB.class.getName()).log(Level.INFO, null, "Table OPTIONS or Table METADATA already exists. No need to recreate"); return true; } else if (ex.getNextException().getErrorCode() == 45000) { //this means we already have a connection, so this is good too return true; } else { //Logger.getLogger(BibleGetDB.class.getName()).log(Level.SEVERE, null, ex.getMessage() + " : " + Arrays.toString(ex.getStackTrace())); Logger.getLogger(BibleGetDB.class.getName()).log(Level.SEVERE, null, ex); return false; } } }