public static ResultSet executeSql(final Statement statement, final String sql) { ResultSet results = null; if (statement == null) { return results; } if (isBlank(sql)) { LOGGER.log(Level.FINE, "No SQL provided", new RuntimeException()); return results; } try { statement.clearWarnings(); final boolean hasResults = statement.execute(sql); if (hasResults) { results = statement.getResultSet(); } else { final int updateCount = statement.getUpdateCount(); LOGGER.log( Level.FINE, String.format("No results. Update count of %d for query: %s", updateCount, sql)); } SQLWarning sqlWarning = statement.getWarnings(); while (sqlWarning != null) { LOGGER.log(Level.INFO, sqlWarning.getMessage(), sqlWarning); sqlWarning = sqlWarning.getNextWarning(); } return results; } catch (final SQLException e) { LOGGER.log(Level.WARNING, "Error executing: " + sql, e); return null; } }
@Test public void testWarnings() throws Exception { ConnectionImpl conn = Mockito.mock(ConnectionImpl.class); DQP dqp = Mockito.mock(DQP.class); ResultsFuture<ResultsMessage> results = new ResultsFuture<ResultsMessage>(); Mockito.stub(dqp.executeRequest(Mockito.anyLong(), (RequestMessage) Mockito.anyObject())) .toReturn(results); ResultsMessage rm = new ResultsMessage(); rm.setResults(new List<?>[] {Arrays.asList(1)}); rm.setWarnings(Arrays.asList(new Throwable())); rm.setColumnNames(new String[] {"expr1"}); rm.setDataTypes(new String[] {"string"}); results.getResultsReceiver().receiveResults(rm); Mockito.stub(conn.getDQP()).toReturn(dqp); StatementImpl statement = new StatementImpl(conn, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY) { @Override protected java.util.TimeZone getServerTimeZone() throws java.sql.SQLException { return null; } }; statement.execute("select 'a'"); assertNotNull(statement.getResultSet()); SQLWarning warning = statement.getWarnings(); assertNotNull(warning); assertNull(warning.getNextWarning()); }
/** * Creates a new database and keeps track of it to delete it when the clean up is invoked. * * <p>If the database already exists, a connection to the existing database is returned. * * @param dbName the database name * @param dbAttributes database attributes (i.e. encryption) * @param user user name * @param password user password * @return A connection to the database. * @throws SQLException if creating or connecting to the database fails */ public Connection createDatabase(String dbName, String dbAttributes, String user, String password) throws SQLException { String userAttr = ""; if (user != null) { userAttr = ";user="******";password="******";" + dbAttributes; } if (!userAttr.equals("")) { url += userAttr; } if (url.indexOf(ATTR_CREATE) == -1) { url += ATTR_CREATE; } Connection con = getConnection(url); if (con.getWarnings() != null) { // See if there are more than one warning. SQLWarning w = con.getWarnings(); String warnings = w.getMessage(); while ((w = w.getNextWarning()) != null) { warnings += " || " + w.getMessage(); } BaseJDBCTestCase.fail("Warning(s) when creating database: " + warnings); } // Keep track of the database we just created, so that we can // delete it. DATABASES.add(dbName + userAttr); return con; }
/** * @param out the place to write to * @param warning the SQLWarning */ public static void ShowWarnings(PrintWriter out, SQLWarning warning) { while (warning != null) { String p1 = mapNull(warning.getSQLState(), LocalizedResource.getMessage("UT_NoSqlst_7")); String p2 = mapNull(warning.getMessage(), LocalizedResource.getMessage("UT_NoMessa_8")); out.println(LocalizedResource.getMessage("UT_Warni01", p1, p2)); warning = warning.getNextWarning(); } }
public void testDecimal() { String ddl = "create table test (cash decimal default 23.587);"; String dml = "insert into test values (123.45678911111);"; String query = "select * from test;"; Connection dbconn; try { Class.forName("org.hsqldb_voltpatches.jdbcDriver"); dbconn = DriverManager.getConnection("jdbc:hsqldb:mem:x1", "sa", ""); dbconn.setAutoCommit(true); dbconn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE); Statement stmt = dbconn.createStatement(); stmt.execute(ddl); SQLWarning warn = stmt.getWarnings(); if (warn != null) System.out.println("warn: " + warn.getMessage()); assertTrue(warn == null); long ucount = stmt.executeUpdate(dml); assertTrue(ucount == 1); ResultSet rs = stmt.executeQuery(query); assertTrue(rs != null); ResultSetMetaData rsmd = rs.getMetaData(); assertTrue(rsmd != null); assertTrue(rsmd.getColumnCount() == 1); /*System.out.printf("Typename %s, Type %d, Precision %s, Scale %d, Classname %s\n", rsmd.getColumnTypeName(1), rsmd.getColumnType(1), rsmd.getPrecision(1), rsmd.getScale(1), rsmd.getColumnClassName(1));*/ boolean success = rs.next(); assertTrue(success); BigDecimal x = rs.getBigDecimal(1); assertNotNull(x); // System.out.printf("Value: %.10f\n", x.doubleValue()); BigDecimal expected = new BigDecimal(123.4567); assertTrue(x.subtract(expected).abs().doubleValue() < .01); try { stmt.execute("SHUTDOWN;"); } catch (Exception e) { } ; dbconn.close(); System.gc(); } catch (Exception e) { e.printStackTrace(); assertTrue(false); } }
public static void ShowWarnings(PrintStream out, SQLWarning warning) { while (warning != null) { out.println( "WARNING " + mapNull(warning.getSQLState(), "(no SQLState)") + ": " + mapNull(warning.getMessage(), "(no message)")); warning = warning.getNextWarning(); } }
public void runDDL(String ddl) { try { // LOG.info("Executing " + ddl); Statement stmt = dbconn.createStatement(); /*boolean success =*/ stmt.execute(ddl); SQLWarning warn = stmt.getWarnings(); if (warn != null) sqlLog.warn(warn.getMessage()); // LOG.info("SQL DDL execute result: " + (success ? "true" : "false")); } catch (SQLException e) { hostLog.l7dlog(Level.ERROR, LogKeys.host_Backend_RunDDLFailed.name(), new Object[] {ddl}, e); } }
public static final void clearWarnings(java.sql.Connection db, DBCommandHandler msghandler) { try { java.sql.SQLWarning warn = db.getWarnings(); if (warn != null) { msghandler.showSQLException(warn); while ((warn = warn.getNextWarning()) != null) { msghandler.showSQLException(warn); } } db.clearWarnings(); } catch (java.sql.SQLException sqle) { msghandler.showSQLException(sqle); } }
public void testVarbinary() { String ddl = "create table testvb (cash integer default 23, b varbinary(1024) default NULL);"; String dml = "insert into testvb values (123, 'AAAA');"; String query = "select * from testvb;"; Connection dbconn; try { Class.forName("org.hsqldb_voltpatches.jdbcDriver"); dbconn = DriverManager.getConnection("jdbc:hsqldb:mem:x1", "sa", ""); dbconn.setAutoCommit(true); dbconn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE); Statement stmt = dbconn.createStatement(); stmt.execute(ddl); SQLWarning warn = stmt.getWarnings(); if (warn != null) System.out.println("warn: " + warn.getMessage()); assertTrue(warn == null); long ucount = stmt.executeUpdate(dml); assertTrue(ucount == 1); ResultSet rs = stmt.executeQuery(query); assertTrue(rs != null); ResultSetMetaData rsmd = rs.getMetaData(); assertTrue(rsmd != null); assertTrue(rsmd.getColumnCount() == 2); boolean success = rs.next(); assertTrue(success); int x = rs.getInt(1); assertTrue(x == 123); try { stmt.execute("SHUTDOWN;"); } catch (Exception e) { } ; dbconn.close(); System.gc(); } catch (Exception e) { e.printStackTrace(); assertTrue(false); } }
// Format and print any warnings from the connection private static void checkForWarning(SQLWarning warn) throws SQLException { // If a SQLWarning object was given, display the // warning messages. Note that there could be // multiple warnings chained together if (warn != null) { System.out.println("*** Warning ***\n"); while (warn != null) { System.out.println("SQLState: " + warn.getSQLState()); System.out.println("Message: " + warn.getMessage()); System.out.println("Vendor: " + warn.getErrorCode()); System.out.println(""); warn = warn.getNextWarning(); } } }
public String checkWarning(Statement st, int updateCount) { String result; result = null; try { SQLWarning warning = st.getWarnings(); if (warning != null) { result = warning.getMessage(); } } catch (Exception e) { result = processSQLError(e); } if (updateCount != 1) { result = "Tuple not inserted correctly."; } return result; }
/** * Helper method that derived classes may use to set warnings * * @param warning Warning */ protected void setWarning(SQLWarning warning) { LOGGER.warn("SQL Warning was issued", warning); if (this.warnings == null) { this.warnings = warning; } else { // Chain with existing warnings warning.setNextWarning(this.warnings); this.warnings = warning; } }
public void testWarning() throws Exception { Connection con = TestUtil.openDB(); Statement stmt = con.createStatement(); stmt.execute("CREATE TEMP TABLE t(a int primary key)"); SQLWarning warning = stmt.getWarnings(); // We should get a warning about primary key index creation // it's possible we won't depending on the server's // client_min_messages setting. while (warning != null) { // Verify that the SQLWarning is serializable. ByteArrayOutputStream baos = new ByteArrayOutputStream(); ObjectOutputStream oos = new ObjectOutputStream(baos); oos.writeObject(warning); oos.close(); warning = warning.getNextWarning(); } stmt.close(); con.close(); }
public void connect() { try { // Load the database driver Class.forName("net.sourceforge.jtds.jdbc.Driver"); // Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance(); // Get a connection to the database String url = "jdbc:jtds:sqlserver://147.87.98.131:55783"; conn = DriverManager.getConnection(url, "se2012_red", "Thanuc57ch"); // Get a statement from the connection stmt = conn.createStatement(); // Print all warnings for (SQLWarning warn = conn.getWarnings(); warn != null; warn = warn.getNextWarning()) { System.out.println("SQL Warning:"); System.out.println("State : " + warn.getSQLState()); System.out.println("Message: " + warn.getMessage()); System.out.println("Error : " + warn.getErrorCode()); } } catch (SQLException se) { System.out.println("SQL Exception:"); // Loop through the SQL Exceptions while (se != null) { System.out.println("State : " + se.getSQLState()); System.out.println("Message: " + se.getMessage()); System.out.println("Error : " + se.getErrorCode()); se = se.getNextException(); } } catch (Exception e) { System.out.println(e); } }
@Override public void emptyCache() throws Exception { // DY: performs "CHECKPOINT" then "DBCC DROPCLEANBUFFERS" to clean buffers. ++numberOfCacheEmptying; Timer t = new Timer(); Statement stmt = queryConnection.createStatement(); String createCheckpoint = "CHECKPOINT"; String dropCleanBuffers = "DBCC DROPCLEANBUFFERS"; String dropPlanCache = "DBCC FREEPROCCACHE"; stmt.execute(createCheckpoint); SQLWarning warning = stmt.getWarnings(); while (warning != null) { // System.out.println(warning.getMessage()); warning = warning.getNextWarning(); } String msg = ""; stmt.execute(dropPlanCache); warning = stmt.getWarnings(); while (warning != null) { msg += warning.getMessage(); warning = warning.getNextWarning(); } if (!msg.contains("DBCC execution completed.")) { log.error("Failed to clear db cache."); } msg = ""; stmt.execute(dropCleanBuffers); warning = stmt.getWarnings(); while (warning != null) { msg += warning.getMessage(); warning = warning.getNextWarning(); } if (!msg.contains("DBCC execution completed.")) { log.error("Failed to clear db cache."); } // for now, we just assume the commands ran successfully. (retry will be added after unit // testing is done) log.status(LogLevel.DEBUG, "Successfully emptied the cache."); secondsSpentEmptyingCache += t.lapSeconds(); }
protected void setWarning(SQLWarning warn) { if (warnings == null) warnings = warn; else warnings.setNextWarning(warn); return; }
/** * This method assumes that there the SQL will either return no ResultSet or one ResultSet (not * multiple ResultSets). * * @see org.teiid.designer.jdbc.Request#performInvocation(java.sql.Connection, * org.teiid.designer.jdbc.Response) */ @Override protected IStatus performInvocation(final Response results) { final Connection conn = (Connection) getTarget(); final List statuses = new ArrayList(); Statement statement = null; ResultSet resultSet = null; try { statement = conn.createStatement(); final boolean isResultSet = statement.execute(this.sql); // If the statement resulted in a ResultSet ... if (isResultSet) { resultSet = statement.getResultSet(); Response.addResults(results, resultSet, this.isMetadataRequested()); } else { // Otherwise, just add in the update count try { final int updateCount = statement.getUpdateCount(); final Object value = new Integer(updateCount); Response.addResults(results, value, true); } catch (SQLException e) { statuses.add(JdbcUtil.createIStatus(e)); } } // Add any errors or warnings ... SQLWarning warning = statement.getWarnings(); // Unchain the warnings ... while (warning != null) { statuses.add(JdbcUtil.createIStatus(warning)); warning = warning.getNextWarning(); } } catch (SQLException e) { statuses.add(JdbcUtil.createIStatus(e)); } catch (Throwable e) { statuses.add(JdbcUtil.createIStatus(e)); } finally { if (resultSet != null) { // attempt to close the result set ... try { resultSet.close(); } catch (SQLException e) { statuses.add(JdbcUtil.createIStatus(e)); } finally { resultSet = null; } } if (statement != null) { // attempt to close the statement ... try { statement.close(); } catch (SQLException e) { statuses.add(JdbcUtil.createIStatus(e)); } finally { statement = null; } } } // Process the status(es) that may have been created due to problems/warnings if (statuses.size() == 1) { return (IStatus) statuses.get(0); } if (statuses.size() > 1) { final String text = JdbcPlugin.Util.getString( "QueryRequest.Error_while_processing_sql_against_connection", sql, conn); //$NON-NLS-1$ return JdbcUtil.createIStatus(statuses, text); } // If there are no errors, return null return null; }
/** * Create a new scrollable result set in memory or a named server cursor. * * @param sql The SQL SELECT statement. * @param procName Optional procedure name for cursors based on a stored procedure. * @param parameters Optional stored procedure parameters. * @exception java.sql.SQLException */ private void cursorCreate(String sql, String procName, ParamInfo[] parameters) throws SQLException { // boolean isSelect = false; SQLWarning warning = null; // // Validate the SQL statement to ensure we have a select. // if (resultSetType != ResultSet.TYPE_FORWARD_ONLY || concurrency == ResultSet.CONCUR_UPDATABLE || cursorName != null) { // // We are going to need access to a SELECT statement for // this to work. Reparse the SQL now and check. // ArrayList params = new ArrayList(); String tmp[] = new SQLParser(sql, params, (ConnectionJDBC2) statement.getConnection()).parse(true); if (tmp[2].equals("select") && tmp[3] != null && tmp[3].length() > 0) { // OK We have a select with at least one table. tableName = tmp[3]; isSelect = true; } else { // No good we can't update and we can't declare a cursor cursorName = null; if (concurrency == ResultSet.CONCUR_UPDATABLE) { concurrency = ResultSet.CONCUR_READ_ONLY; warning = new SQLWarning(Messages.get("warning.cursordowngraded", "CONCUR_READ_ONLY"), "01000"); } if (resultSetType == ResultSet.TYPE_SCROLL_SENSITIVE) { resultSetType = ResultSet.TYPE_SCROLL_INSENSITIVE; SQLWarning warning2 = new SQLWarning( Messages.get("warning.cursordowngraded", "TYPE_SCROLL_INSENSITIVE"), "01000"); if (warning != null) { warning.setNextWarning(warning2); } else { warning = warning2; } } } } // // If a cursor name is specified we try and declare a conventional cursor // if (cursorName != null) { // // We need to substitute any parameters now as the prepended DECLARE CURSOR // will throw the parameter positions off. // if (parameters != null && parameters.length > 0) { sql = Support.substituteParameters(sql, parameters, statement.getTds().getTdsVersion()); } StringBuffer cursorSQL = new StringBuffer(sql.length() + cursorName.length() + 128); cursorSQL.append("DECLARE ").append(cursorName).append(" CURSOR FOR ").append(sql); cursorTds.executeSQL( cursorSQL.toString(), procName, parameters, false, statement.getQueryTimeout(), statement.getMaxRows(), statement.getMaxFieldSize(), true); cursorTds.clearResponseQueue(); cursorSQL.setLength(0); cursorSQL.append("\r\nOPEN ").append(cursorName); if (fetchSize > 1 && isSybase) { cursorSQL.append("\r\nSET CURSOR ROWS ").append(fetchSize); cursorSQL.append(" FOR ").append(cursorName); } cursorSQL.append("\r\nFETCH ").append(cursorName); // // OK Declare cursor, open it and fetch first (fetchSize) rows. // cursorTds.executeSQL( cursorSQL.toString(), null, null, false, statement.getQueryTimeout(), statement.getMaxRows(), statement.getMaxFieldSize(), true); while (!cursorTds.getMoreResults() && !cursorTds.isEndOfResponse()) ; if (!cursorTds.isResultSet()) { throw new SQLException(Messages.get("error.statement.noresult"), "24000"); } columns = cursorTds.getColumns(); columnCount = getColumnCount(columns); } else { // // Open a memory cached scrollable or forward only possibly updateable cursor // if (isSelect && (concurrency == ResultSet.CONCUR_UPDATABLE || resultSetType != ResultSet.TYPE_FORWARD_ONLY)) { // Need to execute SELECT .. FOR BROWSE to get // the MetaData we require for updates etc // OK Should have an SQL select statement // append " FOR BROWSE" to obtain table names // NB. We can't use any jTDS temporary stored proc cursorTds.executeSQL( sql + " FOR BROWSE", null, parameters, false, statement.getQueryTimeout(), statement.getMaxRows(), statement.getMaxFieldSize(), true); while (!cursorTds.getMoreResults() && !cursorTds.isEndOfResponse()) ; if (!cursorTds.isResultSet()) { throw new SQLException(Messages.get("error.statement.noresult"), "24000"); } columns = cursorTds.getColumns(); columnCount = getColumnCount(columns); rowData = new ArrayList(INITIAL_ROW_COUNT); // // Load result set into buffer // while (super.next()) { rowData.add(copyRow(currentRow)); } rowsInResult = rowData.size(); initialRowCnt = rowsInResult; pos = POS_BEFORE_FIRST; // // If cursor is built over one table and the table has // key columns then the result set is updateable and / or // can be used as a scroll sensitive result set. // if (!isCursorUpdateable()) { // No so downgrade if (concurrency == ResultSet.CONCUR_UPDATABLE) { concurrency = ResultSet.CONCUR_READ_ONLY; statement.addWarning( new SQLWarning( Messages.get("warning.cursordowngraded", "CONCUR_READ_ONLY"), "01000")); } if (resultSetType == ResultSet.TYPE_SCROLL_SENSITIVE) { resultSetType = ResultSet.TYPE_SCROLL_INSENSITIVE; statement.addWarning( new SQLWarning( Messages.get("warning.cursordowngraded", "TYPE_SCROLL_INSENSITIVE"), "01000")); } } return; } // // Create a read only cursor using direct SQL // cursorTds.executeSQL( sql, procName, parameters, false, statement.getQueryTimeout(), statement.getMaxRows(), statement.getMaxFieldSize(), true); while (!cursorTds.getMoreResults() && !cursorTds.isEndOfResponse()) ; if (!cursorTds.isResultSet()) { throw new SQLException(Messages.get("error.statement.noresult"), "24000"); } columns = cursorTds.getColumns(); columnCount = getColumnCount(columns); rowData = new ArrayList(INITIAL_ROW_COUNT); // // Load result set into buffer // while (super.next()) { rowData.add(copyRow(currentRow)); } rowsInResult = rowData.size(); initialRowCnt = rowsInResult; pos = POS_BEFORE_FIRST; if (warning != null) { statement.addWarning(warning); } } }
/** Adds an SQLWarning to the warning chain. */ synchronized void addWarning(SQLWarning warn) { if (warnings == null) warnings = warn; else warnings.setNextWarning(warn); }