/**
   * Tests the implementation of getCharacterStream(long pos, long length).
   *
   * @throws Exception
   */
  public void testGetCharacterStreamLong() throws Exception {
    String str1 = "This is a test String. This is a test String";

    Reader r1 = new java.io.StringReader(str1);

    PreparedStatement ps = prepareStatement("insert into BLOBCLOB(ID, CLOBDATA) values(?,?)");
    int id = BlobClobTestSetup.getID();
    ps.setInt(1, id);
    ps.setCharacterStream(2, r1);
    ps.execute();
    ps.close();

    Statement st = createStatement();

    ResultSet rs = st.executeQuery("select CLOBDATA from " + "BLOBCLOB where ID=" + id);
    rs.next();
    Clob clob = rs.getClob(1);

    Reader r_1 = clob.getCharacterStream(2L, 5L);
    String str2 = str1.substring(1, 6);
    Reader r_2 = new java.io.StringReader(str2);

    assertEquals(r_2, r_1);

    rs.close();
    st.close();
  }
  /**
   * Test that <code>Clob.getCharacterStream(long,long)</code> works on CLOBs that are streamed from
   * store. (DERBY-2891)
   */
  public void testGetCharacterStreamLongOnLargeClob() throws Exception {
    getConnection().setAutoCommit(false);

    // create large (>32k) clob that can be read from store
    final int size = 33000;
    StringBuilder sb = new StringBuilder(size);
    for (int i = 0; i < size; i += 10) {
      sb.append("1234567890");
    }

    final int id = BlobClobTestSetup.getID();
    PreparedStatement ps =
        prepareStatement("insert into blobclob(id, clobdata) values (?,cast(? as clob))");
    ps.setInt(1, id);
    ps.setString(2, sb.toString());
    ps.executeUpdate();
    ps.close();

    Statement s = createStatement();
    ResultSet rs = s.executeQuery("select clobdata from blobclob where id = " + id);
    assertTrue(rs.next());
    Clob c = rs.getClob(1);

    // request a small region of the clob
    BufferedReader r = new BufferedReader(c.getCharacterStream(4L, 3L));
    assertEquals("456", r.readLine());

    r.close();
    c.free();
    rs.close();
    s.close();
    rollback();
  }
  /**
   * Preconditions: 1. xid is known to the RM or it's in prepared state
   *
   * <p>Implementation deficiency preconditions: 1. xid must be associated with this connection if
   * it's not in prepared state.
   *
   * <p>Postconditions: 1. Transaction is rolled back and disassociated from connection
   */
  public void rollback(Xid xid) throws XAException {
    if (logger.logDebug()) debug("rolling back xid = " + xid);

    // We don't explicitly check precondition 1.

    try {
      if (currentXid != null && xid.equals(currentXid)) {
        state = STATE_IDLE;
        currentXid = null;
        conn.rollback();
        conn.setAutoCommit(localAutoCommitMode);
      } else {
        String s = RecoveredXid.xidToString(xid);

        conn.setAutoCommit(true);
        Statement stmt = conn.createStatement();
        try {
          stmt.executeUpdate("ROLLBACK PREPARED '" + s + "'");
        } finally {
          stmt.close();
        }
      }
    } catch (SQLException ex) {
      throw new PGXAException(
          GT.tr("Error rolling back prepared transaction"), ex, XAException.XAER_RMERR);
    }
  }
  /**
   * Preconditions: 1. xid must be in prepared state in the server
   *
   * <p>Implementation deficiency preconditions: 1. Connection must be in idle state
   *
   * <p>Postconditions: 1. Transaction is committed
   */
  private void commitPrepared(Xid xid) throws XAException {
    try {
      // Check preconditions. The connection mustn't be used for another
      // other XA or local transaction, or the COMMIT PREPARED command
      // would mess it up.
      if (state != STATE_IDLE || conn.getTransactionState() != ProtocolConnection.TRANSACTION_IDLE)
        throw new PGXAException(
            GT.tr("Not implemented: 2nd phase commit must be issued using an idle connection"),
            XAException.XAER_RMERR);

      String s = RecoveredXid.xidToString(xid);

      localAutoCommitMode = conn.getAutoCommit();
      conn.setAutoCommit(true);
      Statement stmt = conn.createStatement();
      try {
        stmt.executeUpdate("COMMIT PREPARED '" + s + "'");
      } finally {
        stmt.close();
        conn.setAutoCommit(localAutoCommitMode);
      }
    } catch (SQLException ex) {
      throw new PGXAException(
          GT.tr("Error committing prepared transaction"), ex, XAException.XAER_RMERR);
    }
  }
  //
  // Examine BLOBs and CLOBs.
  //
  private void vetLargeObjects(
      Connection conn, HashSet<String> unsupportedList, HashSet<String> notUnderstoodList)
      throws Exception {
    Statement stmt = conn.createStatement();

    stmt.execute("CREATE TABLE t (id INT PRIMARY KEY, " + "b BLOB(10), c CLOB(10))");
    stmt.execute(
        "INSERT INTO t (id, b, c) VALUES (1, "
            + "CAST ("
            + TestUtil.stringToHexLiteral("101010001101")
            + "AS BLOB(10)), CAST ('hello' AS CLOB(10)))");

    ResultSet rs = stmt.executeQuery("SELECT id, b, c FROM t");

    rs.next();

    Blob blob = rs.getBlob(2);
    Clob clob = rs.getClob(3);

    vetObject(blob, unsupportedList, notUnderstoodList);
    vetObject(clob, unsupportedList, notUnderstoodList);

    stmt.close();
    conn.rollback();
  }
  /** private method which actually will do all of our work for the sample */
  private void executeSample() {

    String query = "select anEmployee from staff2";
    try {
      Statement stmt = _con.createStatement();
      ;

      // Execute the query which will return an Employee object
      // We will cast this using the Person interface. Note the
      // Person interface class MUST be in your CLASSPATH. You
      // Do not need Employee in your CLASSPATH.
      ResultSet rs = stmt.executeQuery(query);

      output("***Using interface class\n");
      while (rs.next()) {
        Person aPerson = (Person) rs.getObject(1);
        displayMethods(aPerson.getClass());
        output(
            "The person is: "
                + aPerson.toString()
                + "\nFirst Name= "
                + aPerson.getFirstName()
                + "\nLast Name= "
                + aPerson.getLastName()
                + "\n");
      }
      // Now execute the same query, but this time we will use
      // reflection to access the class.  Again, only the interface
      // Person is required in the CLASSPATH
      rs = stmt.executeQuery(query);

      output("***Using reflection\n");
      Object theObj = null;
      while (rs.next()) {
        theObj = rs.getObject(1);
        output("The person is: " + theObj.toString() + "\n");
        Class theClass = theObj.getClass();
        displayMethods(theClass);
        Method m1 = theClass.getMethod("toString", new Class[0]);
        Method m2 = theClass.getMethod("getFirstName", new Class[0]);
        Method m3 = theClass.getMethod("getLastName", new Class[0]);
        output(
            "The person is: "
                + (Object) m1.invoke(theObj, new Object[0])
                + "\nFirst Name= "
                + (Object) m2.invoke(theObj, new Object[0])
                + "\nLast Name= "
                + (Object) m3.invoke(theObj, new Object[0])
                + "\n");
      }
      rs.close();
      stmt.close();
    } catch (SQLException sqe) {
      displaySQLEx(sqe);
    } catch (Exception e) {
      error("Unexpected exception : " + e.toString() + "\n");
      e.printStackTrace();
    }
  }
  /**
   * Inserts a Clob with the specified length, using a stream source, then fetches it from the
   * database and checks the length.
   *
   * @param length number of characters in the Clob
   * @throws IOException if reading from the source fails
   * @throws SQLException if something goes wrong
   */
  private void insertAndFetchTest(long length) throws IOException, SQLException {
    PreparedStatement ps = prepareStatement("insert into BLOBCLOB(ID, CLOBDATA) values(?,?)");
    int id = BlobClobTestSetup.getID();
    ps.setInt(1, id);
    ps.setCharacterStream(2, new LoopingAlphabetReader(length), length);
    long tsStart = System.currentTimeMillis();
    ps.execute();
    println(
        "Inserted "
            + length
            + " chars (length specified) in "
            + (System.currentTimeMillis() - tsStart)
            + " ms");
    Statement stmt = createStatement();
    tsStart = System.currentTimeMillis();
    ResultSet rs = stmt.executeQuery("select CLOBDATA from BLOBCLOB where id = " + id);
    assertTrue("Clob not inserted", rs.next());
    Clob aClob = rs.getClob(1);
    assertEquals("Invalid length", length, aClob.length());
    println("Fetched length (" + length + ") in " + (System.currentTimeMillis() - tsStart) + " ms");
    rs.close();

    // Insert same Clob again, using the lengthless override.
    id = BlobClobTestSetup.getID();
    ps.setInt(1, id);
    ps.setCharacterStream(2, new LoopingAlphabetReader(length));
    tsStart = System.currentTimeMillis();
    ps.executeUpdate();
    println(
        "Inserted "
            + length
            + " chars (length unspecified) in "
            + (System.currentTimeMillis() - tsStart)
            + " ms");
    rs = stmt.executeQuery("select CLOBDATA from BLOBCLOB where id = " + id);
    assertTrue("Clob not inserted", rs.next());
    aClob = rs.getClob(1);
    assertEquals("Invalid length", length, aClob.length());
    println("Fetched length (" + length + ") in " + (System.currentTimeMillis() - tsStart) + " ms");
    rs.close();

    rollback();
  }
  private Statement nextChainedInvoker(Statement methodInvoker) {
    Field[] declaredFields = methodInvoker.getClass().getDeclaredFields();

    for (Field field : declaredFields) {
      Statement statement = statementOrNull(methodInvoker, field);
      if (statement != null) return statement;
    }

    return null;
  }
 private void runMethodInvoker(
     RunNotifier notifier,
     Description description,
     Statement methodInvoker,
     Description methodWithParams) {
   try {
     methodInvoker.evaluate();
   } catch (Throwable e) {
     notifier.fireTestFailure(new Failure(methodWithParams, e));
   }
 }
  /**
   * Preconditions: 1. flag must be one of TMSTARTRSCAN, TMENDRSCAN, TMNOFLAGS or TMSTARTTRSCAN |
   * TMENDRSCAN 2. if flag isn't TMSTARTRSCAN or TMSTARTRSCAN | TMENDRSCAN, a recovery scan must be
   * in progress
   *
   * <p>Postconditions: 1. list of prepared xids is returned
   */
  public Xid[] recover(int flag) throws XAException {
    // Check preconditions
    if (flag != TMSTARTRSCAN
        && flag != TMENDRSCAN
        && flag != TMNOFLAGS
        && flag != (TMSTARTRSCAN | TMENDRSCAN))
      throw new PGXAException(GT.tr("Invalid flag"), XAException.XAER_INVAL);

    // We don't check for precondition 2, because we would have to add some additional state in
    // this object to keep track of recovery scans.

    // All clear. We return all the xids in the first TMSTARTRSCAN call, and always return
    // an empty array otherwise.
    if ((flag & TMSTARTRSCAN) == 0) return new Xid[0];
    else {
      try {
        Statement stmt = conn.createStatement();
        try {
          // If this connection is simultaneously used for a transaction,
          // this query gets executed inside that transaction. It's OK,
          // except if the transaction is in abort-only state and the
          // backed refuses to process new queries. Hopefully not a problem
          // in practise.
          ResultSet rs = stmt.executeQuery("SELECT gid FROM pg_prepared_xacts");
          LinkedList l = new LinkedList();
          while (rs.next()) {
            Xid recoveredXid = RecoveredXid.stringToXid(rs.getString(1));
            if (recoveredXid != null) l.add(recoveredXid);
          }
          rs.close();

          return (Xid[]) l.toArray(new Xid[l.size()]);
        } finally {
          stmt.close();
        }
      } catch (SQLException ex) {
        throw new PGXAException(GT.tr("Error during recover"), ex, XAException.XAER_RMERR);
      }
    }
  }
 private void closeConnection() {
   try {
     if (statement != null) statement.close();
     if (connection != null) {
       JDBCExceptionReporter.logWarnings(connection.getWarnings());
       connection.clearWarnings();
       connectionProvider.closeConnection(connection);
       connectionProvider.close();
     }
   } catch (Exception e) {
     System.err.println("Could not close connection");
     e.printStackTrace();
   }
 }
  /**
   * Insert a row with a large clob into the test table. Read the row from the database and assign
   * the clob value to <code>clob</code>.
   *
   * @return The id of the row that was inserted
   * @throws java.sql.SQLException
   */
  private int initializeLongClob() throws SQLException {
    // Clob needs to be larger than one page for locking to occur
    final int lobLength = 40000;

    // Insert a long Clob
    PreparedStatement ps = prepareStatement("insert into BLOBCLOB(ID, CLOBDATA) values(?,?)");
    int id = BlobClobTestSetup.getID();
    ps.setInt(1, id);
    ps.setCharacterStream(2, new LoopingAlphabetReader(lobLength), lobLength);
    ps.execute();
    ps.close();
    commit();

    // Fetch the Clob object from the database
    Statement st = createStatement();
    ResultSet rs = st.executeQuery("select CLOBDATA from BLOBCLOB where ID=" + id);
    rs.next();
    clob = rs.getClob(1);
    rs.close();
    st.close();

    return id;
  }
  /**
   * Preconditions: 1. xid != null 2. xid is in ended state
   *
   * <p>Implementation deficiency preconditions: 1. xid was associated with this connection
   *
   * <p>Postconditions: 1. Transaction is prepared
   */
  public int prepare(Xid xid) throws XAException {
    if (logger.logDebug()) debug("preparing transaction xid = " + xid);

    // Check preconditions
    if (!currentXid.equals(xid)) {
      throw new PGXAException(
          GT.tr(
              "Not implemented: Prepare must be issued using the same connection that started the transaction"),
          XAException.XAER_RMERR);
    }
    if (state != STATE_ENDED)
      throw new PGXAException(GT.tr("Prepare called before end"), XAException.XAER_INVAL);

    state = STATE_IDLE;
    currentXid = null;

    if (!conn.haveMinimumServerVersion("8.1"))
      throw new PGXAException(
          GT.tr("Server versions prior to 8.1 do not support two-phase commit."),
          XAException.XAER_RMERR);

    try {
      String s = RecoveredXid.xidToString(xid);

      Statement stmt = conn.createStatement();
      try {
        stmt.executeUpdate("PREPARE TRANSACTION '" + s + "'");
      } finally {
        stmt.close();
      }
      conn.setAutoCommit(localAutoCommitMode);

      return XA_OK;
    } catch (SQLException ex) {
      throw new PGXAException(GT.tr("Error preparing transaction"), ex, XAException.XAER_RMERR);
    }
  }
  /**
   * Try to update the row with the given error. Flag a failure if a timeout occurs when not
   * expected, and vice versa.
   *
   * @param id The id of the row to be updated
   * @param timeoutExpected true if it is expected that the update times out
   * @throws java.sql.SQLException
   */
  private void executeParallelUpdate(int id, boolean timeoutExpected) throws SQLException {
    Connection conn2 = openDefaultConnection();
    Statement stmt2 = conn2.createStatement();

    try {
      stmt2.executeUpdate(
          "update BLOBCLOB set BLOBDATA = " + "cast(X'FFFFFF' as blob) where ID=" + id);
      stmt2.close();
      conn2.commit();
      conn2.close();
      if (timeoutExpected) {
        fail("FAIL - should have gotten lock timeout");
      }
    } catch (SQLException se) {
      stmt2.close();
      conn2.rollback();
      conn2.close();
      if (timeoutExpected) {
        assertSQLState(LOCK_TIMEOUT, se);
      } else {
        throw se;
      }
    }
  }
  public void execute(String[] sqls) {
    String sql = null;
    String showSqlText = properties.getProperty("hibernate.show_sql");
    boolean showSql = ("true".equalsIgnoreCase(showSqlText));

    try {
      createConnection();
      statement = connection.createStatement();

      for (int i = 0; i < sqls.length; i++) {
        sql = sqls[i];
        String delimitedSql = sql + getSqlDelimiter();

        if (showSql) log.debug(delimitedSql);
        statement.executeUpdate(delimitedSql);
      }

    } catch (SQLException e) {
      e.printStackTrace();
      throw new RuntimeException("couldn't execute sql '" + sql + "'", e);
    } finally {
      closeConnection();
    }
  }
 public String consultarImagen(
     Connection conn,
     String path,
     String pagina,
     String sucID,
     String vigID,
     String padreID,
     String secundarioID,
     String referenciaID,
     String documentoID,
     String consecutivoID) {
   Vector vRetornoImg = new Vector(1);
   Statement st = null;
   ResultSet rs = null;
   String sql = "";
   try {
     if (consecutivoID.length() <= 0) consecutivoID = "%";
     if (pagina.equalsIgnoreCase("prerequisitodet")) {
       sql =
           " select PREDI_IMAGEN img, PREDI_NOMBREIMAGEN nameimg"
               + " from COT_PREREQUISITODETIMG"
               + " where suc_id = '"
               + sucID
               + "' "
               + "   and vig_id = '"
               + vigID
               + "'"
               + "   and prec_id = '"
               + padreID
               + "'"
               + "   and tpr_id = '"
               + secundarioID
               + "'"
               + "   and tdo_referencia = '"
               + referenciaID
               + "'"
               + "   and tdo_id = '"
               + documentoID
               + "'"
               + "   and predi_consecutivo like '"
               + consecutivoID
               + "'";
     }
     if (pagina.equalsIgnoreCase("requisitosdis")) {
       sql =
           " select REDII_IMAGEN img, REDII_NOMBREIMAGEN nameimg"
               + " from COT_REQHABILITANTEDISIMG "
               + " where suc_id = '"
               + sucID
               + "' "
               + "   and vig_id = '"
               + vigID
               + "'"
               + "   and disc_id = '"
               + padreID
               + "'"
               + "   and tdo_referencia = '"
               + referenciaID
               + "'"
               + "   and tdo_id = '"
               + documentoID
               + "'";
     }
     if (pagina.equalsIgnoreCase("proceso")) {
       sql =
           " select PROEI_IMAGEN img, PROEI_NOMBREIMAGEN nameimg"
               + " from COT_PROCESOESTIMG "
               + " where suc_id = '"
               + sucID
               + "' "
               + "   and vig_id = '"
               + vigID
               + "'"
               + "   and proc_id = '"
               + padreID
               + "'"
               + "   and epo_id = '"
               + secundarioID
               + "'"
               + "   and tdo_referencia = '"
               + referenciaID
               + "'"
               + "   and tdo_id = '"
               + documentoID
               + "'"
               + "   and proei_consecutivo like '"
               + consecutivoID
               + "'";
     }
     st = conn.createStatement();
     rs = st.executeQuery(sql);
     if (rs.next()) {
       vRetornoImg.clear();
       vRetornoImg.addElement(rs.getBlob("img"));
       vRetornoImg.addElement(rs.getString("nameimg"));
       return famhn.upload.central.FileUpload.materializarImagen(path + "/", conn, vRetornoImg);
     }
     return "Pendiente";
   } catch (Exception e) {
     System.out.println("" + e.toString());
     return e.toString();
   } finally {
     try {
       rs.close();
       rs = null;
     } catch (Exception er) {;
     }
     try {
       st.close();
       st = null;
     } catch (Exception err) {;
     }
   }
 }
  public String eliminarImagenes(
      Connection conn,
      String pagina,
      String sucID,
      String vigID,
      String padreID,
      String secundarioID,
      String referenciaID,
      String documentoID,
      String consecutivoID) {
    String sql = "";
    Statement stmt = null;
    UtilidadesDatos ud = new UtilidadesDatos();
    try {
      if (consecutivoID.length() <= 0) consecutivoID = "1";

      if (pagina.equalsIgnoreCase("prerequisitodet")) {
        sql =
            "delete from COT_PREREQUISITODETIMG "
                + "where SUC_ID = '"
                + sucID
                + "'"
                + " and  VIG_ID = '"
                + vigID
                + "'"
                + " and  PREC_ID = '"
                + padreID
                + "'"
                + " and  TPR_ID = '"
                + secundarioID
                + "'"
                + " and  TDO_REFERENCIA = '"
                + referenciaID
                + "'"
                + " and TDO_ID = '"
                + documentoID
                + "'"
                + " and PREDI_CONSECUTIVO = '"
                + consecutivoID
                + "'";
      }
      if (pagina.equalsIgnoreCase("requisitosdis")) {
        sql =
            "delete from COT_REQHABILITANTEDISIMG "
                + "where SUC_ID = '"
                + sucID
                + "'"
                + " and  VIG_ID = '"
                + vigID
                + "'"
                + " and  DISC_ID = '"
                + padreID
                + "'"
                + " and  TDO_REFERENCIA = '"
                + referenciaID
                + "'"
                + " and TDO_ID = '"
                + documentoID
                + "'"
                + " and REDII_CONSECUTIVO = '"
                + consecutivoID
                + "'";
      }
      if (pagina.equalsIgnoreCase("proceso")) {
        sql =
            "delete from COT_PROCESOESTIMG "
                + "where SUC_ID = '"
                + sucID
                + "'"
                + " and  VIG_ID = '"
                + vigID
                + "'"
                + " and  PROC_ID = '"
                + padreID
                + "'"
                + " and  EPO_ID like '"
                + secundarioID
                + "'"
                + " and  TDO_REFERENCIA like '"
                + referenciaID
                + "'"
                + " and TDO_ID like '"
                + documentoID
                + "'"
                + " and PROEI_CONSECUTIVO like '"
                + consecutivoID
                + "'";
      }
      stmt = conn.createStatement();
      stmt.executeUpdate(sql);
      stmt.close();
      return "Ok";
    } catch (Exception e) {
      System.out.println("" + e.toString());
      return e.toString();
    } finally {
      try {
        stmt.close();
        stmt = null;
      } catch (Exception err) {;
      }
    }
  }
  /**
   * Tests the exceptions thrown by the getCharacterStream (long pos, long length) for the following
   * conditions a) pos <= 0 b) pos > (length of LOB) c) length < 0 d) pos + length > (length of
   * LOB).
   *
   * @throws SQLException
   */
  public void testGetCharacterStreamLongExceptionConditions() throws SQLException {
    String str1 = "This is a test String. This is a test String";

    Reader r1 = new java.io.StringReader(str1);

    PreparedStatement ps = prepareStatement("insert into BLOBCLOB(ID, CLOBDATA) values(?,?)");
    int id = BlobClobTestSetup.getID();
    ps.setInt(1, id);
    ps.setCharacterStream(2, r1);
    ps.execute();
    ps.close();

    Statement st = createStatement();

    ResultSet rs = st.executeQuery("select CLOBDATA from " + "BLOBCLOB where ID=" + id);
    rs.next();
    Clob clob = rs.getClob(1);
    // check the case where pos <= 0
    try {
      // set pos as negative
      clob.getCharacterStream(-2L, 5L);
      // Should not come here. The exception has to be thrown.
      fail("FAIL: Expected SQLException for pos being negative " + "not thrown");
    } catch (SQLException sqle) {
      // The SQLState for the exception thrown when pos <= 0 is XJ070
      assertSQLState("XJ070", sqle);
    }

    // check for the case pos > length of clob
    try {
      // set the pos to any value greater than the Clob length
      clob.getCharacterStream(clob.length() + 1, 5L);
      // Should not come here. The exception has to be thrown.
      fail(
          "FAIL: Expected SQLException for position being greater than "
              + "length of LOB not thrown");
    } catch (SQLException sqle) {
      // The SQLState for the exception thrown when pos > length of Clob
      // is XJ076
      assertSQLState("XJ087", sqle);
    }

    // check for the case when length < 0
    try {
      // set length as negative
      clob.getCharacterStream(2L, -5L);
      // Should not come here. The exception has to be thrown.
      fail("Fail: expected exception for the length being negative " + "not thrown");
    } catch (SQLException sqle) {
      // The SQLState for the exception thrown when length < 0 of Clob
      // is XJ071
      assertSQLState("XJ071", sqle);
    }

    // check for the case when pos + length > length of Clob
    try {
      // set pos + length > length of Clob
      clob.getCharacterStream((clob.length() - 4), 10L);
      // Should not come here. The exception has to be thrown.
      fail(
          "Fail: expected exception for the sum of position and length"
              + " being greater than the LOB size not thrown");
    } catch (SQLException sqle) {
      // The SQLState for the exception thrown when length < 0 of Clob
      // is XJ087
      assertSQLState("XJ087", sqle);
    }
  }