Пример #1
0
  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;
    }
  }
Пример #2
0
 @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();
   }
 }
Пример #5
0
  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();
   }
 }
Пример #7
0
 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);
   }
 }
Пример #8
0
 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);
   }
 }
Пример #9
0
  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);
    }
  }
Пример #10
0
  // 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();
      }
    }
  }
Пример #11
0
  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;
  }
Пример #12
0
  /**
   * 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;
    }
  }
Пример #13
0
  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();
  }
Пример #14
0
  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);
    }
  }
Пример #15
0
  @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();
  }
Пример #16
0
 protected void setWarning(SQLWarning warn) {
   if (warnings == null) warnings = warn;
   else warnings.setNextWarning(warn);
   return;
 }
Пример #17
0
  /**
   * 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);
 }