Exemplo n.º 1
1
  /**
   * 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();
  }
Exemplo n.º 2
1
  /**
   * Obtains streams from the Clob and makes sure we can always read the last char in the Clob.
   *
   * <p>See DERBY-4060.
   *
   * @param id id of the Clob to use
   * @param length the length of the Clob
   * @param alphabet the alphabet used to create the content
   * @throws IOException if reading from a stream fails
   * @throws SQLException if something goes wrong
   */
  private void getCharacterStreamLongLastChar(int id, int length, CharAlphabet alphabet)
      throws IOException, SQLException {
    // Get last char from the source stream.
    Reader cmpReader = new LoopingAlphabetReader(length, alphabet);
    cmpReader.skip(length - 1);
    char srcLastChar = (char) cmpReader.read();
    assertTrue(cmpReader.read() == -1);

    PreparedStatement ps = prepareStatement("select CLOBDATA from BLOBCLOB where ID=?");
    ps.setInt(1, id);
    // Read everything first.
    int charsToRead = length;
    ResultSet rs = ps.executeQuery();
    rs.next();
    Reader reader = rs.getClob(1).getCharacterStream(length - charsToRead + 1, charsToRead);
    // Drain the stream, and make sure we are able to read the last char.
    char lastCharRead = getLastCharInStream(reader, charsToRead);
    assertEquals(srcLastChar, lastCharRead);
    reader.close();
    rs.close();

    // Read a portion of the stream.
    charsToRead = length / 4;
    rs = ps.executeQuery();
    rs.next();
    reader = rs.getClob(1).getCharacterStream(length - charsToRead + 1, charsToRead);
    lastCharRead = getLastCharInStream(reader, charsToRead);
    assertEquals(srcLastChar, lastCharRead);
    reader.close();
    rs.close();

    // Read a very small portion of the stream.
    charsToRead = 1;
    rs = ps.executeQuery();
    rs.next();
    reader = rs.getClob(1).getCharacterStream(length - charsToRead + 1, charsToRead);
    lastCharRead = getLastCharInStream(reader, charsToRead);
    assertEquals(srcLastChar, lastCharRead);
    reader.close();
    rs.close();
  }
Exemplo n.º 3
0
  /**
   * 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();
  }
Exemplo n.º 4
0
  /**
   * 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();
  }
Exemplo n.º 5
0
  /**
   * 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;
  }
Exemplo n.º 6
0
  public void testMetaData() {

    String ddl0 =
        "DROP TABLE ADDRESSBOOK IF EXISTS; DROP TABLE ADDRESSBOOK_CATEGORY IF EXISTS; DROP TABLE USER IF EXISTS;";
    String ddl1 =
        "CREATE TABLE USER(USER_ID INTEGER NOT NULL PRIMARY KEY,LOGIN_ID VARCHAR(128) NOT NULL,USER_NAME VARCHAR(254) DEFAULT ' ' NOT NULL,CREATE_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,UPDATE_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,LAST_ACCESS_DATE TIMESTAMP,CONSTRAINT IXUQ_LOGIN_ID0 UNIQUE(LOGIN_ID))";
    String ddl2 =
        "CREATE TABLE ADDRESSBOOK_CATEGORY(USER_ID INTEGER NOT NULL,CATEGORY_ID INTEGER DEFAULT 0 NOT NULL,CATEGORY_NAME VARCHAR(60) DEFAULT '' NOT NULL,CONSTRAINT SYS_PK_ADDRESSBOOK_CATEGORY PRIMARY KEY(USER_ID,CATEGORY_ID),CONSTRAINT FK_ADRBKCAT1 FOREIGN KEY(USER_ID) REFERENCES USER(USER_ID) ON DELETE CASCADE)";
    String ddl3 =
        "CREATE TABLE ADDRESSBOOK(USER_ID INTEGER NOT NULL,ADDRESSBOOK_ID INTEGER NOT NULL,CATEGORY_ID INTEGER DEFAULT 0 NOT NULL,FIRST VARCHAR(64) DEFAULT '' NOT NULL,LAST VARCHAR(64) DEFAULT '' NOT NULL,NOTE VARCHAR(128) DEFAULT '' NOT NULL,CONSTRAINT SYS_PK_ADDRESSBOOK PRIMARY KEY(USER_ID,ADDRESSBOOK_ID),CONSTRAINT FK_ADRBOOK1 FOREIGN KEY(USER_ID,CATEGORY_ID) REFERENCES ADDRESSBOOK_CATEGORY(USER_ID,CATEGORY_ID) ON DELETE CASCADE)";
    String result1 = "1";
    String result2 = "2";
    String result3 = "3";
    String result4 = "4";
    String result5 = "5";

    try {
      stmnt.execute(ddl0);
      stmnt.execute(ddl1);
      stmnt.execute(ddl2);
      stmnt.execute(ddl3);

      DatabaseMetaData md = connection.getMetaData();

      {

        //                System.out.println(md.getDatabaseMajorVersion());
        //                System.out.println(md.getDatabaseMinorVersion());
        System.out.println(md.getDatabaseProductName());
        System.out.println(md.getDatabaseProductVersion());
        System.out.println(md.getDefaultTransactionIsolation());
        System.out.println(md.getDriverMajorVersion());
        System.out.println(md.getDriverMinorVersion());
        System.out.println(md.getDriverName());
        System.out.println(md.getDriverVersion());
        System.out.println(md.getExtraNameCharacters());
        System.out.println(md.getIdentifierQuoteString());

        //                System.out.println(md.getJDBCMajorVersion());
        //                System.out.println(md.getJDBCMinorVersion());
        System.out.println(md.getMaxBinaryLiteralLength());
        System.out.println(md.getMaxCatalogNameLength());
        System.out.println(md.getMaxColumnsInGroupBy());
        System.out.println(md.getMaxColumnsInIndex());
        System.out.println(md.getMaxColumnsInOrderBy());
        System.out.println(md.getMaxColumnsInSelect());
        System.out.println(md.getMaxColumnsInTable());
        System.out.println(md.getMaxConnections());
        System.out.println(md.getMaxCursorNameLength());
        System.out.println(md.getMaxIndexLength());
        System.out.println(md.getMaxProcedureNameLength());
        System.out.println(md.getMaxRowSize());
        System.out.println(md.getMaxSchemaNameLength());
        System.out.println(md.getMaxStatementLength());
        System.out.println(md.getMaxStatements());
        System.out.println(md.getMaxTableNameLength());
        System.out.println(md.getMaxUserNameLength());
        System.out.println(md.getNumericFunctions());
        System.out.println(md.getProcedureTerm());

        //                System.out.println(md.getResultSetHoldability());
        System.out.println(md.getSchemaTerm());
        System.out.println(md.getSearchStringEscape());
        System.out.println(md.getSQLKeywords());

        //                System.out.println(md.getSQLStateType());
        System.out.println(md.getStringFunctions());
        System.out.println(md.getSystemFunctions());
        System.out.println(md.getTimeDateFunctions());
        System.out.println(md.getURL());
        System.out.println(md.getUserName());
        System.out.println(md.importedKeyCascade);
        System.out.println(md.isCatalogAtStart());
        System.out.println(md.isReadOnly());

        ResultSet rs;

        rs = md.getPrimaryKeys(null, null, "USER");

        ResultSetMetaData rsmd = rs.getMetaData();
        String result0 = "";

        for (; rs.next(); ) {
          for (int i = 0; i < rsmd.getColumnCount(); i++) {
            result0 += rs.getString(i + 1) + ":";
          }

          result0 += "\n";
        }

        rs.close();
        System.out.println(result0);
      }

      {
        ResultSet rs;

        rs = md.getBestRowIdentifier(null, null, "USER", 0, true);

        ResultSetMetaData rsmd = rs.getMetaData();
        String result0 = "";

        for (; rs.next(); ) {
          for (int i = 0; i < rsmd.getColumnCount(); i++) {
            result0 += rs.getString(i + 1) + ":";
          }

          result0 += "\n";
        }

        rs.close();
        System.out.println(result0);
      }

      {
        ResultSet rs = md.getImportedKeys(null, null, "ADDRESSBOOK");
        ResultSetMetaData rsmd = rs.getMetaData();

        result1 = "";

        for (; rs.next(); ) {
          for (int i = 0; i < rsmd.getColumnCount(); i++) {
            result1 += rs.getString(i + 1) + ":";
          }

          result1 += "\n";
        }

        rs.close();
        System.out.println(result1);
      }

      {
        ResultSet rs =
            md.getCrossReference(null, null, "ADDRESSBOOK_CATEGORY", null, null, "ADDRESSBOOK");
        ResultSetMetaData rsmd = rs.getMetaData();

        result2 = "";

        for (; rs.next(); ) {
          for (int i = 0; i < rsmd.getColumnCount(); i++) {
            result2 += rs.getString(i + 1) + ":";
          }

          result2 += "\n";
        }

        rs.close();
        System.out.println(result2);
      }

      {
        ResultSet rs = md.getExportedKeys(null, null, "USER");
        ResultSetMetaData rsmd = rs.getMetaData();

        result3 = "";

        for (; rs.next(); ) {
          for (int i = 0; i < rsmd.getColumnCount(); i++) {
            result3 += rs.getString(i + 1) + ":";
          }

          result3 += "\n";
        }

        rs.close();
        System.out.println(result3);
      }

      {
        ResultSet rs = md.getCrossReference(null, null, "USER", null, null, "ADDRESSBOOK_CATEGORY");
        ResultSetMetaData rsmd = rs.getMetaData();

        result4 = "";

        for (; rs.next(); ) {
          for (int i = 0; i < rsmd.getColumnCount(); i++) {
            result4 += rs.getString(i + 1) + ":";
          }

          result4 += "\n";
        }

        rs.close();
        System.out.println(result4);
      }

      {
        stmnt.execute("DROP TABLE T IF EXISTS;");
        stmnt.executeQuery("CREATE TABLE T (I IDENTITY, A CHAR, B CHAR);");
        stmnt.executeQuery(
            "INSERT INTO T VALUES (NULL, 'get_column_name', '" + getColumnName + "');");

        ResultSet rs = stmnt.executeQuery("SELECT I, A, B, A \"aliasA\", B \"aliasB\" FROM T;");
        ResultSetMetaData rsmd = rs.getMetaData();

        result5 = "";

        for (; rs.next(); ) {
          for (int i = 0; i < rsmd.getColumnCount(); i++) {
            result5 += rsmd.getColumnName(i + 1) + ":" + rs.getString(i + 1) + ":";
          }

          result5 += "\n";
        }

        rs.close();

        rs = stmnt.executeQuery("SELECT I, A, B, A \"aliasA\", B \"aliasB\" FROM T;");
        ;
        rsmd = rs.getMetaData();

        for (; rs.next(); ) {
          for (int i = 0; i < rsmd.getColumnCount(); i++) {
            result5 += rsmd.getColumnLabel(i + 1) + ":" + rs.getString(i + 1) + ":";
          }

          result5 += "\n";
        }

        System.out.println(result5);
        System.out.println("first column identity: " + rsmd.isAutoIncrement(1));
        rsmd.isCaseSensitive(1);
        rsmd.isCurrency(1);
        rsmd.isDefinitelyWritable(1);
        rsmd.isNullable(1);
        rsmd.isReadOnly(1);
        rsmd.isSearchable(1);
        rsmd.isSigned(1);
        rsmd.isWritable(1);
        rs.close();

        // test identity with PreparedStatement
        pstmnt = connection.prepareStatement("INSERT INTO T VALUES (?,?,?)");

        pstmnt.setString(1, null);
        pstmnt.setString(2, "test");
        pstmnt.setString(3, "test2");
        pstmnt.executeUpdate();

        pstmnt = connection.prepareStatement("call identity()");

        ResultSet rsi = pstmnt.executeQuery();

        rsi.next();

        int identity = rsi.getInt(1);

        System.out.println("call identity(): " + identity);
        rsi.close();
      }
    } catch (SQLException e) {
      fail(e.getMessage());
    }

    System.out.println("testMetaData complete");

    // assert equality of exported and imported with xref
    assertEquals(result1, result2);
    assertEquals(result3, result4);
  }